Using Spring Boot, I need to read and update data from 2 different datasources.
For this I have configured my application based on https://www.baeldung.com/spring-boot-configure-multiple-datasources and https://www.baeldung.com/spring-data-jpa-modifying-annotation

Reading from the 2 datasources is working fine, but when trying to update, the following error appears:

org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query

What is incorrect or missing?

How to update all datasources in the same transaction?

(side question: is there an easier way to use several datasources than creating jpa configuration classes?)

Thanks

The whole source code is:

datasource 1:

com/example/jpa/db1/entity/Db1Table1Entity.java

@Entity
@Table(name = "table1", catalog = "db1")
@ToString
@RequiredArgsConstructor
@Accessors(fluent = true)
@Getter
@Setter
public class Db1Table1Entity implements Serializable {

  @Id
  @Column(name = "a")
  private String a;

  @Column(name = "b")
  private String b;
}

com/example/jpa/db1/repo/Db1Table1Repository.java

@Repository
public interface Db1Table1Repository extends JpaRepository<Db1Table1Entity, String> {

  Optional<Db1Table1Entity> findByA(final String a);

  @Modifying
  @Query("UPDATE Db1Table1Entity e SET e.b = :b WHERE e.a = :a")
  void updateByA(@Param("a") String a, @Param("b") String b);
}

com/example/jpa/db1/Db1JpaConfiguration.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = {"com.example.jpa.db1.repo"},
    entityManagerFactoryRef = "db1EntityManagerFactory",
    transactionManagerRef = "db1TransactionManager")
public class Db1JpaConfiguration {

  @Primary
  @Bean
  public LocalContainerEntityManagerFactoryBean db1EntityManagerFactory(
      final EntityManagerFactoryBuilder builder, final DataSource db1DataSource) {

    return builder
        .dataSource(db1DataSource)
        .packages("com.example.jpa.db1")
        .persistenceUnit("db1")
        .build();
  }

  @Bean
  @ConfigurationProperties("spring.datasource.db1")
  public DataSourceProperties db1DataSourceProperties() {
    return new DataSourceProperties();
  }

  @Primary
  @Bean
  @ConfigurationProperties("spring.datasource.db1.hikari")
  public DataSource db1xDataSource() {
    return db1DataSourceProperties().initializeDataSourceBuilder().build();
  }

  @Primary
  @Bean
  public PlatformTransactionManager db1TransactionManager(
      final EntityManagerFactory db1EntityManagerFactory) {
    return new JpaTransactionManager(db1EntityManagerFactory);
  }
}

datasource 2:

com/example/jpa/db2/entity/Db2Table1Entity.java

@Entity
@Table(name = "table1", catalog = "db2")
@ToString
@RequiredArgsConstructor
@Accessors(fluent = true)
@Getter
@Setter
public class Db2Table1Entity implements Serializable {

  @Id
  @Column(name = "a")
  private String a;

  @Column(name = "b")
  private String b;
}

com/example/jpa/db2/repo/Db2Table1Repository.java

@Repository
public interface Db2Table1Repository extends JpaRepository<Db2Table1Entity, String> {

  Optional<Db2Table1Entity> findByA(final String a);

  @Modifying
  @Query("UPDATE Db2Table1Entity e SET e.b = :b WHERE e.a = :a")
  void updateByA(@Param("a") String a, @Param("b") String b);
}

com/example/jpa/db2/Db2JpaConfiguration.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = {"com.example.jpa.db2.repo"},
    entityManagerFactoryRef = "db2EntityManagerFactory",
    transactionManagerRef = "db2TransactionManager")
public class Db2JpaConfiguration {

  @Bean
  public LocalContainerEntityManagerFactoryBean db2EntityManagerFactory(
      final EntityManagerFactoryBuilder builder, final DataSource db2DataSource) {

    return builder
        .dataSource(db2DataSource)
        .packages("com.example.jpa.db2")
        .persistenceUnit("db2")
        .build();
  }

  @Bean
  @ConfigurationProperties("spring.datasource.db2")
  public DataSourceProperties db2DataSourceProperties() {
    return new DataSourceProperties();
  }

  @Bean
  @ConfigurationProperties("spring.datasource.db2.hikari")
  public DataSource db2DataSource() {
    return db2DataSourceProperties().initializeDataSourceBuilder().build();
  }

  @Bean
  public PlatformTransactionManager db2TransactionManager(
      final EntityManagerFactory db2EntityManagerFactory) {
    return new JpaTransactionManager(db2EntityManagerFactory);
  }
}

service:

com/example/jpa/DbService.java

@Service
public class DbService {
  private final Db1Table1Repository repo1;
  private final Db2Table1Repository repo2;

  public DbService(Db1Table1Repository repo1, Db2Table1Repository repo2) {
    this.repo1 = repo1;
    this.repo2 = repo2;
  }

  public List<String> find(final String a) {
    return List.of(
        repo1.findByA(a).map(Db1Table1Entity::b).orElse(""),
        repo2.findByA(a).map(Db2Table1Entity::b).orElse(""));
  }

  @Transactional
  public void update(final String a, final String b) {
    repo1.updateByA(a, b);
    repo2.updateByA(a, b);
  }
}

test data:

INSERT
IGNORE INTO
        db1.table1(
            a,
            b
        )
    VALUES(
        "info-a1",
        "info-b1"
    ),
    (
        "info-a2",
        "info-b2"
    );
INSERT
IGNORE INTO
        db2.table1(
            a,
            b
        )
    VALUES(
        "info-a1",
        "info-b1"
    ),
    (
        "info-a2",
        "info-b2"
    );

tests:

@DataJpaTest(properties = "spring.test.database.replace=NONE")
@Isolated
@Import(value = {DbService.class, Db1JpaConfiguration.class, Db2JpaConfiguration.class})
class DbServiceIT {
  @Autowired private DbService sut;

  public static Stream<Arguments> data() {
    return Stream.of(
        Arguments.of("info-a1", List.of("info-b1", "info-b1")),
        Arguments.of("info-a2", List.of("info-b2", "info-b2")),
        Arguments.of("xxx-yyy", List.of("", "")));
  }

  @ParameterizedTest
  @MethodSource("data")
  void find(final String a, final List<String> expected) {
    final var result = sut.find(a);
    Assertions.assertThat(result).containsExactlyInAnyOrderElementsOf(expected);
  }

  @ParameterizedTest
  @MethodSource("data")
  void update(final String a, final List<String> expected) {
    final var newValue = "new info";
    sut.update(a, newValue);

    final var result = sut.find(a);
    Assertions.assertThat(result).hasSameSizeAs(expected).allMatch(r -> r.equals(newValue));
  }
}