With multiple `@EnableJdbcRepositories`, fetching `@MappedCollection` always uses the primary `DataSource`

[kzander91]

Spring Data Relational/JDBC: 4.0.1 (also broken with 4.0.2)

This affects setups with multiple DataSource beans and thus multiple @EnableJdbcRepositories annotations. We encountered this after upgrading from Spring Data JDBC 3 to 4 (as part of upgrading to Spring Boot 4), where we migrated from the now-deprecated @EnableJdbcRepositories.jdbcOperationsRef to @EnableJdbcRepositories.jdbcAggregateOperationsRef, which required us to define a whole bunch of additional beans.

It is therefore possible that our configuration is wrong; we were struggling to find guidance on how to configure SD JDBC with multiple data sources, because all examples we found online are outdated and/or refer to SD JPA.

Issue

When fetching entities that have a @MappedCollection attribute with the secondary repositories, the additional queries performed by Spring Data to fetch those child entities is always using the primary DataSource instead of the same DataSource used to fetch the parent entities.

Reproducer

demo20.zip

Extract and run

./mvnw test

Detailed Description

The reproducer has two packages com.example.demo.primary and com.example.demo.secondary.

In the primary package, a @Primary DataSource bean is defined and @EnableJdbcRepositories is declared:

@Configuration
@EnableJdbcRepositories
public class PrimaryConfig {

    @Bean
    @Primary
    HikariDataSource primaryDataSource() {
        var ds = new HikariDataSource();
        ds.setDriverClassName(EmbeddedDatabaseConnection.H2.getDriverClassName());
        ds.setJdbcUrl(EmbeddedDatabaseConnection.H2.getUrl("PRIMARY"));
        return ds;
    }

}

We also have an entity like this:

public record PrimaryEntity(@Id Long id, @MappedCollection Set<PrimaryChildEntity> children) {}

public record PrimaryChildEntity(@Id Long id) {}

And a repository:

public interface PrimaryEntityRepository extends ListCrudRepository<PrimaryEntity, Long> {}

The expectation here is that Spring Data will configure the repositories defined in this package to use this primary data source.

In the secondary package, we define an additional DataSource and the various supporting beans required to setup the secondary Spring Data JDBC repository. Note that we're using defaultCandidate = false and a custom @Secondary qualifier to not interfere with the primary configuration that mostly relies on auto configuration:

@Configuration
@EnableJdbcRepositories(transactionManagerRef = "secondaryTransactionManager",
        jdbcAggregateOperationsRef = "secondaryJdbcAggregateTemplate")
public class SecondaryConfig {

    @Secondary
    @Bean(defaultCandidate = false)
    HikariDataSource secondaryDataSource() {
        var ds = new HikariDataSource();
        ds.setDriverClassName(EmbeddedDatabaseConnection.H2.getDriverClassName());
        ds.setJdbcUrl(EmbeddedDatabaseConnection.H2.getUrl("SECONDARY"));
        return ds;
    }

    @Secondary
    @Bean(defaultCandidate = false)
    JdbcTransactionManager secondaryTransactionManager(@Secondary DataSource secondaryDataSource) {
        return new JdbcTransactionManager(secondaryDataSource);
    }

    @Secondary
    @Bean(defaultCandidate = false)
    JdbcTemplate secondaryJdbcTemplate(@Secondary DataSource secondaryDataSource) {
        return new JdbcTemplate(secondaryDataSource);
    }

    @Secondary
    @Bean(defaultCandidate = false)
    NamedParameterJdbcTemplate secondaryNamedParameterJdbcTemplate(@Secondary JdbcOperations secondaryJdbcTemplate) {
        return new NamedParameterJdbcTemplate(secondaryJdbcTemplate);
    }

    @Secondary
    @Bean(defaultCandidate = false)
    JdbcAggregateTemplate secondaryJdbcAggregateTemplate(
            @Secondary NamedParameterJdbcOperations operations,
            ApplicationContext applicationContext,
            JdbcConverter jdbcConverter,
            JdbcMappingContext mappingContext,
            JdbcDialect dialect) {
        DataAccessStrategy dataAccessStrategy = JdbcConfiguration.createDataAccessStrategy(
                operations,
                jdbcConverter,
                null,
                dialect
        );
        return new JdbcAggregateTemplate(applicationContext, mappingContext, jdbcConverter, dataAccessStrategy);
    }

}

Then we have entities and a repository like before:

public record SecondaryEntity(@Id Long id, @MappedCollection Set<SecondaryChildEntity> children) {}

public record SecondaryChildEntity(@Id Long id) {}

public interface SecondaryEntityRepository extends ListCrudRepository<SecondaryEntity, Long> {}

Here, the expectation is that the secondary repository uses the secondaryDataSource for all queries.

---

An ApplicationRunner in the main package now demonstrates the issue, by

1. creating the corresponding tables primary_entity and primary_child_entity using the primary DataSource,
2. creating the corresponding tables secondary_entity and secondary_child_entity using the secondary DataSource,
3. inserting a record into each table,
4. running PrimaryEntityRepository.findAll() and then SecondaryEntityRepository.findAll() with these statements:

   log.info("Retrieving primary entities...");
   log.info("Retrieved primary entities: {}", primaryRepository.findAll());
   log.info("Retrieving secondary entities...");
   log.info("Retrieved secondary entities: {}", secondaryRepository.findAll());

With debug logging enabled, we see the following in the logs:

com.example.demo.Demo20Application       : Retrieving primary entities...
o.s.jdbc.support.JdbcTransactionManager  : Creating new transaction with name [org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.findAll]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly
o.s.jdbc.support.JdbcTransactionManager  : Acquired Connection [HikariProxyConnection@1182469998 wrapping conn0: url=jdbc:h2:mem:PRIMARY user=] for JDBC transaction
o.s.jdbc.datasource.DataSourceUtils      : Setting JDBC Connection [HikariProxyConnection@1182469998 wrapping conn0: url=jdbc:h2:mem:PRIMARY user=] read-only
o.s.jdbc.support.JdbcTransactionManager  : Switching JDBC Connection [HikariProxyConnection@1182469998 wrapping conn0: url=jdbc:h2:mem:PRIMARY user=] to manual commit
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT "PRIMARY_ENTITY"."ID" AS "ID" FROM "PRIMARY_ENTITY"]
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT "PRIMARY_CHILD_ENTITY"."ID" AS "ID" FROM "PRIMARY_CHILD_ENTITY" WHERE "PRIMARY_CHILD_ENTITY"."PRIMARY_ENTITY" = ?]
o.s.jdbc.support.JdbcTransactionManager  : Initiating transaction commit
o.s.jdbc.support.JdbcTransactionManager  : Committing JDBC transaction on Connection [HikariProxyConnection@1182469998 wrapping conn0: url=jdbc:h2:mem:PRIMARY user=]
o.s.jdbc.datasource.DataSourceUtils      : Resetting read-only flag of JDBC Connection [HikariProxyConnection@1182469998 wrapping conn0: url=jdbc:h2:mem:PRIMARY user=]
o.s.jdbc.support.JdbcTransactionManager  : Releasing JDBC Connection [HikariProxyConnection@1182469998 wrapping conn0: url=jdbc:h2:mem:PRIMARY user=] after transaction
com.example.demo.Demo20Application       : Retrieved primary entities: [PrimaryEntity[id=42, children=[PrimaryChildEntity[id=1]]]]
com.example.demo.Demo20Application       : Retrieving secondary entities...
o.s.jdbc.support.JdbcTransactionManager  : Creating new transaction with name [org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.findAll]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly
o.s.jdbc.support.JdbcTransactionManager  : Acquired Connection [HikariProxyConnection@2041676336 wrapping conn9: url=jdbc:h2:mem:SECONDARY user=] for JDBC transaction
o.s.jdbc.datasource.DataSourceUtils      : Setting JDBC Connection [HikariProxyConnection@2041676336 wrapping conn9: url=jdbc:h2:mem:SECONDARY user=] read-only
o.s.jdbc.support.JdbcTransactionManager  : Switching JDBC Connection [HikariProxyConnection@2041676336 wrapping conn9: url=jdbc:h2:mem:SECONDARY user=] to manual commit
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT "SECONDARY_ENTITY"."ID" AS "ID" FROM "SECONDARY_ENTITY"]
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT "SECONDARY_CHILD_ENTITY"."ID" AS "ID" FROM "SECONDARY_CHILD_ENTITY" WHERE "SECONDARY_CHILD_ENTITY"."SECONDARY_ENTITY" = ?]
o.s.jdbc.datasource.DataSourceUtils      : Fetching JDBC Connection from DataSource
o.s.jdbc.support.JdbcTransactionManager  : Initiating transaction rollback
o.s.jdbc.support.JdbcTransactionManager  : Rolling back JDBC transaction on Connection [HikariProxyConnection@2041676336 wrapping conn9: url=jdbc:h2:mem:SECONDARY user=]
o.s.jdbc.datasource.DataSourceUtils      : Resetting read-only flag of JDBC Connection [HikariProxyConnection@2041676336 wrapping conn9: url=jdbc:h2:mem:SECONDARY user=]
o.s.jdbc.support.JdbcTransactionManager  : Releasing JDBC Connection [HikariProxyConnection@2041676336 wrapping conn9: url=jdbc:h2:mem:SECONDARY user=] after transaction
o.s.boot.SpringApplication               : Application run failed

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT "SECONDARY_CHILD_ENTITY"."ID" AS "ID" FROM "SECONDARY_CHILD_ENTITY" WHERE "SECONDARY_CHILD_ENTITY"."SECONDARY_ENTITY" = ?]
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:112) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:102) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1548) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:688) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:731) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:751) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:803) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:224) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.findAllByPath(DefaultDataAccessStrategy.java:350) ~[spring-data-jdbc-4.0.1.jar:4.0.1]
	at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.findAllByPath(DefaultDataAccessStrategy.java:70) ~[spring-data-jdbc-4.0.1.jar:4.0.1]
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:565) ~[na:na]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359) ~[spring-aop-7.0.2.jar:7.0.2]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-7.0.2.jar:7.0.2]
	at jdk.proxy2/jdk.proxy2.$Proxy56.findAllByPath(Unknown Source) ~[na:na]
	at org.springframework.data.jdbc.core.convert.MappingJdbcConverter$ResolvingRelationalPropertyValueProvider.getPropertyValue(MappingJdbcConverter.java:450) ~[spring-data-jdbc-4.0.1.jar:4.0.1]
	at org.springframework.data.jdbc.core.convert.MappingJdbcConverter$ResolvingRelationalPropertyValueProvider.getPropertyValue(MappingJdbcConverter.java:389) ~[spring-data-jdbc-4.0.1.jar:4.0.1]
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter$2.getPropertyValue(MappingRelationalConverter.java:518) ~[spring-data-relational-4.0.1.jar:4.0.1]
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter$2.getPropertyValue(MappingRelationalConverter.java:494) ~[spring-data-relational-4.0.1.jar:4.0.1]
	at org.springframework.data.mapping.model.PersistentEntityParameterValueProvider.getParameterValue(PersistentEntityParameterValueProvider.java:71) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter$ConvertingParameterValueProvider.getParameterValue(MappingRelationalConverter.java:1300) ~[spring-data-relational-4.0.1.jar:4.0.1]
	at org.springframework.data.mapping.model.ValueExpressionParameterValueProvider.getParameterValue(ValueExpressionParameterValueProvider.java:57) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator.extractInvocationArguments(ClassGeneratingEntityInstantiator.java:324) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator$EntityInstantiatorAdapter.createInstance(ClassGeneratingEntityInstantiator.java:296) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator.createInstance(ClassGeneratingEntityInstantiator.java:95) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter.read(MappingRelationalConverter.java:485) ~[spring-data-relational-4.0.1.jar:4.0.1]
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter.readAggregate(MappingRelationalConverter.java:376) ~[spring-data-relational-4.0.1.jar:4.0.1]
	at org.springframework.data.relational.core.conversion.MappingRelationalConverter.readAggregate(MappingRelationalConverter.java:334) ~[spring-data-relational-4.0.1.jar:4.0.1]
	at org.springframework.data.jdbc.core.convert.MappingJdbcConverter.readAndResolve(MappingJdbcConverter.java:352) ~[spring-data-jdbc-4.0.1.jar:4.0.1]
	at org.springframework.data.jdbc.core.convert.EntityRowMapper.mapRow(EntityRowMapper.java:65) ~[spring-data-jdbc-4.0.1.jar:4.0.1]
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:110) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:62) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.lambda$query$0(JdbcTemplate.java:738) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:669) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:731) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:751) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:803) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:224) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:236) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.findAll(DefaultDataAccessStrategy.java:298) ~[spring-data-jdbc-4.0.1.jar:4.0.1]
	at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.findAll(DefaultDataAccessStrategy.java:70) ~[spring-data-jdbc-4.0.1.jar:4.0.1]
	at org.springframework.data.jdbc.core.JdbcAggregateTemplate.findAll(JdbcAggregateTemplate.java:407) ~[spring-data-jdbc-4.0.1.jar:4.0.1]
	at org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.findAll(SimpleJdbcRepository.java:95) ~[spring-data-jdbc-4.0.1.jar:4.0.1]
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:565) ~[na:na]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359) ~[spring-aop-7.0.2.jar:7.0.2]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:278) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:169) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:545) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:290) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:690) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-7.0.2.jar:7.0.2]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:171) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:146) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-7.0.2.jar:7.0.2]
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:69) ~[spring-data-commons-4.0.1.jar:4.0.1]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-7.0.2.jar:7.0.2]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:370) ~[spring-tx-7.0.2.jar:7.0.2]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-7.0.2.jar:7.0.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-7.0.2.jar:7.0.2]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:135) ~[spring-tx-7.0.2.jar:7.0.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-7.0.2.jar:7.0.2]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:222) ~[spring-aop-7.0.2.jar:7.0.2]
	at jdk.proxy2/jdk.proxy2.$Proxy61.findAll(Unknown Source) ~[na:na]
	at com.example.demo.Demo20Application.lambda$runner$0(Demo20Application.java:74) ~[classes/:na]
	at org.springframework.boot.SpringApplication.lambda$callRunner$0(SpringApplication.java:788) ~[spring-boot-4.0.1.jar:4.0.1]
	at org.springframework.util.function.ThrowingConsumer$1.acceptWithException(ThrowingConsumer.java:82) ~[spring-core-7.0.2.jar:7.0.2]
	at org.springframework.util.function.ThrowingConsumer.accept(ThrowingConsumer.java:60) ~[spring-core-7.0.2.jar:7.0.2]
	at org.springframework.util.function.ThrowingConsumer$1.accept(ThrowingConsumer.java:86) ~[spring-core-7.0.2.jar:7.0.2]
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800) ~[spring-boot-4.0.1.jar:4.0.1]
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:788) ~[spring-boot-4.0.1.jar:4.0.1]
	at org.springframework.boot.SpringApplication.lambda$callRunners$0(SpringApplication.java:776) ~[spring-boot-4.0.1.jar:4.0.1]
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:186) ~[na:na]
	at java.base/java.util.stream.SortedOps$SizedRefSortingSink.end(SortedOps.java:357) ~[na:na]
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:571) ~[na:na]
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:560) ~[na:na]
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:153) ~[na:na]
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:176) ~[na:na]
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:265) ~[na:na]
	at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:632) ~[na:na]
	at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:776) ~[spring-boot-4.0.1.jar:4.0.1]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:328) ~[spring-boot-4.0.1.jar:4.0.1]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1365) ~[spring-boot-4.0.1.jar:4.0.1]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1354) ~[spring-boot-4.0.1.jar:4.0.1]
	at com.example.demo.Demo20Application.main(Demo20Application.java:22) ~[classes/:na]
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "SECONDARY_CHILD_ENTITY" not found; SQL statement:
SELECT "SECONDARY_CHILD_ENTITY"."ID" AS "ID" FROM "SECONDARY_CHILD_ENTITY" WHERE "SECONDARY_CHILD_ENTITY"."SECONDARY_ENTITY" = ? [42102-240]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:514) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8049) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8020) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.readTableOrView(Parser.java:7999) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.readTablePrimary(Parser.java:1772) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.readTableReference(Parser.java:2252) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parseSelectFromPart(Parser.java:2705) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parseSelect(Parser.java:2813) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parseQueryPrimary(Parser.java:2695) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parseQueryTerm(Parser.java:2550) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2529) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2522) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parseQueryExpression(Parser.java:2515) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parseQuery(Parser.java:2482) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parsePrepared(Parser.java:613) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parse(Parser.java:584) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.parse(Parser.java:559) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.command.Parser.prepareCommand(Parser.java:487) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:647) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:563) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1160) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:93) ~[h2-2.4.240.jar:2.4.240]
	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:315) ~[h2-2.4.240.jar:2.4.240]
	at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:328) ~[HikariCP-7.0.2.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-7.0.2.jar:na]
	at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:238) ~[spring-jdbc-7.0.2.jar:7.0.2]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) ~[spring-jdbc-7.0.2.jar:7.0.2]
	... 82 common frames omitted

We see that the initial query performed by SecondaryEntityRepository to fetch the SecondaryEntity succeeds, but fetching the corresponding child entity fails because the table doesn't exist.

While debugging this, I found that the underlying DataSource used by MappingJdbcConverter$ResolvingRelationalPropertyValueProvider.getPropertyValue to fetch the @MappedCollection is the primary one instead of the secondary one.

[kzander91]

After a bit of experimenting I have been able to work around this by having both the PrimaryConfig and the SecondaryConfig extend AbstractJdbcConfiguration, and overriding every bean definition in the SecondaryConfig like this:

@Configuration
@EnableJdbcRepositories(transactionManagerRef = "secondaryTransactionManager",
        jdbcAggregateOperationsRef = "secondaryJdbcAggregateTemplate")
public class SecondaryConfig extends AbstractJdbcConfiguration {

    @Secondary
    @Bean(defaultCandidate = false)
    HikariDataSource secondaryDataSource() {
        var ds = new HikariDataSource();
        ds.setDriverClassName(EmbeddedDatabaseConnection.H2.getDriverClassName());
        ds.setJdbcUrl(EmbeddedDatabaseConnection.H2.getUrl("SECONDARY"));
        return ds;
    }

    @Secondary
    @Bean(defaultCandidate = false)
    JdbcTransactionManager secondaryTransactionManager(@Secondary DataSource secondaryDataSource) {
        return new JdbcTransactionManager(secondaryDataSource);
    }

    @Secondary
    @Bean(defaultCandidate = false)
    JdbcTemplate secondaryJdbcTemplate(@Secondary DataSource secondaryDataSource) {
        return new JdbcTemplate(secondaryDataSource);
    }

    @Secondary
    @Bean(defaultCandidate = false)
    NamedParameterJdbcTemplate secondaryNamedParameterJdbcTemplate(@Secondary JdbcOperations secondaryJdbcTemplate) {
        return new NamedParameterJdbcTemplate(secondaryJdbcTemplate);
    }


    @Override
    @Secondary
    @Bean(defaultCandidate = false, name = "secondaryJdbcManagedTypes")
    public RelationalManagedTypes jdbcManagedTypes() throws ClassNotFoundException {
        return super.jdbcManagedTypes();
    }

    @Override
    @Secondary
    @Bean(defaultCandidate = false, name = "secondaryJdbcMappingContext")
    public JdbcMappingContext jdbcMappingContext(@Secondary Optional<NamingStrategy> namingStrategy, @Secondary JdbcCustomConversions customConversions, @Secondary RelationalManagedTypes jdbcManagedTypes) {
        return super.jdbcMappingContext(namingStrategy, customConversions, jdbcManagedTypes);
    }

    @Override
    @Secondary
    @Bean(defaultCandidate = false, name = "secondaryIdGeneratingBeforeSaveCallback")
    public IdGeneratingEntityCallback idGeneratingBeforeSaveCallback(@Secondary JdbcMappingContext mappingContext, @Secondary NamedParameterJdbcOperations operations, @Secondary JdbcDialect dialect) {
        return super.idGeneratingBeforeSaveCallback(mappingContext, operations, dialect);
    }

    @Override
    @Secondary
    @Bean(defaultCandidate = false, name = "secondaryJdbcConverter")
    public JdbcConverter jdbcConverter(@Secondary JdbcMappingContext mappingContext, @Secondary NamedParameterJdbcOperations operations, @Secondary @Lazy RelationResolver relationResolver, @Secondary JdbcCustomConversions conversions, @Secondary JdbcDialect dialect) {
        return super.jdbcConverter(mappingContext, operations, relationResolver, conversions, dialect);
    }

    @Override
    @Secondary
    @Bean(defaultCandidate = false, name = "secondaryJdbcCustomConversions")
    public JdbcCustomConversions jdbcCustomConversions() {
        return super.jdbcCustomConversions();
    }

    @Override
    @Secondary
    @Bean(defaultCandidate = false, name = "secondaryJdbcAggregateTemplate")
    public JdbcAggregateTemplate jdbcAggregateTemplate(ApplicationContext applicationContext, @Secondary JdbcMappingContext mappingContext, @Secondary JdbcConverter converter, @Secondary DataAccessStrategy dataAccessStrategy) {
        return super.jdbcAggregateTemplate(applicationContext, mappingContext, converter, dataAccessStrategy);
    }

    @Override
    @Secondary
    @Bean(defaultCandidate = false, name = "secondaryDataAccessStrategyBean")
    public DataAccessStrategy dataAccessStrategyBean(@Secondary NamedParameterJdbcOperations operations, @Secondary JdbcConverter jdbcConverter, @Secondary JdbcMappingContext context, @Secondary JdbcDialect dialect) {
        return super.dataAccessStrategyBean(operations, jdbcConverter, context, dialect);
    }

    @Override
    @Secondary
    @Bean(defaultCandidate = false, name = "secondaryJdbcDialect")
    public JdbcDialect jdbcDialect(@Secondary NamedParameterJdbcOperations operations) {
        return super.jdbcDialect(operations);
    }

}

This seems awfully verbose to me (and brittle: I'll have to keep my SecondaryConfig in sync with AbstractJdbcConfiguration in all future versions...). Is this really the way to go?

[mp911de]

Thanks for reaching out. Spring Data JDBC is a collection of components (Dialect, CustomConversions, MappingContext, JdbcConverter, RelationResolver via DataAccessStrategy, JdbcAggregateTemplate, plus DataSource, JdbcTemplate, NamedJdbcTemplate and TransactionManager and sometimes even more) of which each individual component has its responsibility and depends on its previous component.

Spring Boot's configuration is based on AbstractJdbcConfiguration with Spring Boot backing off from providing beans if it sees a specific bean of the corresponding type. That brings us back to AbstractJdbcConfiguration.

While investigating your reproducer I discovered that overriding AbstractJdbcConfiguration methods isn't sufficient as duplicate beans by type cannot be easily resolved without amending bean method parameter names (e.g. @Lazy RelationResolver dataAccessStrategyBean in jdbcConverter or jdbcAggregateTemplate). It makes sense to solve this issue at least by aligning bean method argument names with bean names for easier override setups.

Is this really the way to go?

For the time being, that is one option if you don't want to maintain your own configuration infrastructure.

There's another approach you could explore and that could lead to an improvement in Spring Data JDBC. We've extracted quite a few configuration methods to JdbcConfiguration. Using two data sources is a theme we've observed a few times already. It would make sense to explore a configuration option through a programmatic builder API that can provide all required beans and accepting a DataSource, Dialect along with a naming scheme and customizers for MappingContext, JdbcConverter and JdbcCustomConversions.

Spring Framework 7 introduced BeanRegistrar. I could imagine an interface with a usage style as follows:

@Bean
BeanRegistrar dataJdbcInfrastructure(DataSource myDataSource) {

    return MyJdbcConfigurationRegistrar.builder()
        .withDataSource(myDataSource) // alt: withDataSourceRef("myDataSource"), .withNamedParameterJdbcOperations(…)/.withJdbcOperations(…)
        .withDialect(JdbcH2Dialect.INSTANCE)
        .withCustomConversions(…) // provide a custom conversions object, alt: withCustomConversionsCustomizer(Consumer<JdbcConverterConfigurer>)
        .withQueryMappingConfiguration(…)
        .build(beanName -> "secondary" + beanName); 
}

Introducing a programmatic configuration alternative removes loosely coupled moving parts that you would otherwise be introduced to config classes through configuration overrides. A programmatic configuration provides with well-defined contracts with some flexibility. There's still quite some complexity with all the fallback variants.

Let me know what you think and whether you'd be interested in exploring such an approach.

[kzander91]

Thanks for the quick response, @mp911de!

The BeanRegistrar approach looks like a nice alternative, especially for use cases like mine where I don't really care about how all the individual components are setup (or even: what all the individual components are) and I "just" want the framework to use this DataSource for these repositories, and that DataSource for those repositories.

Using two data sources is a theme we've observed a few times already

That is what I have seen as well during research while initially setting up our app. Maybe some guidance in the docs would be worthwhile, especially now with SD 4, where most of the existing examples found online are outdated.