Testing with miltiple databases

[wavesrcomn]

Hello everyone!
I have a project that need to use several databases for tests.
I want to autowire different repositories that uses different databases connections in one test, for example:

@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = TestConfig.class)
class MultipleDatabasesTest {
    @Autowired
    EntityFromFirstDBRepository entityFromFirstDBRepository;
    @Autowired
    EntityFromSecondDBRepository entityFromSecondDBRepository;

    @Test
    void test() {
        entityFromFirstDBRepository.findAll();
        entityFromSecondDBRepository.findAll();
    }
}

But I can't implement it using spring-data-jdbc, it allows me two use only one database connection.
I prepare a sample project to better understanding the situation and you can try by yourself, but you have to run your own two databases.

There are tree gradle modules: module for first database, module for second database and module for tests.
root module
|- first-db-client
|- second-db-client
|- tests

First two modules I want to use as two separate dependencies in future.
Here is my root build.gradle file.

plugins {
    id 'java-library'
}

def springVersion = '5.3.20'
def springDataJdbcVersion = '2.4.1'
def junitVersion = '5.8.2'
def junitEngineVersion = '1.8.2'
def lombokVersion = '1.18.24'
def postgresqlVersion = '42.3.6'

subprojects {
    apply plugin: "java-library"

    group 'org.test.multiple.databases'
    version '1.0-SNAPSHOT'

    repositories {
        mavenCentral()
    }

    dependencies {
        compileOnly ("org.projectlombok:lombok:$lombokVersion")
        implementation (
                "org.springframework:spring-context:$springVersion",
                "org.springframework.data:spring-data-jdbc:$springDataJdbcVersion",
                "org.junit.jupiter:junit-jupiter-api:$junitVersion",
                "org.junit.platform:junit-platform-engine:$junitEngineVersion",
                "org.postgresql:postgresql:$postgresqlVersion"
        )
        testImplementation (
                "org.springframework:spring-test:$springVersion",
                "org.junit.jupiter:junit-jupiter-engine:$junitVersion"
        )
        annotationProcessor(
                "org.projectlombok:lombok:$lombokVersion"
        )
    }

    test {
        useJUnitPlatform()
    }
}

tests module depends on two databases modules:

dependencies {
    api project(":first-db-client")
    api project(":second-db-client")
}

Let's take a look at first-db-client.
It has a simple entity, repository for it and configuration class.

@Data
@Table("")
@Accessors(chain = true)
@Builder(toBuilder = true)
@FieldNameConstants
public class FirstDBEntity {
    //fields
}

@First
@Transactional(value = "firstTransactionManager")
public interface FirstDBEntityRepository extends PagingAndSortingRepository<FirstDBEntity, String> {
}

@Configuration
@EnableJdbcRepositories(jdbcOperationsRef = "firstNamedParameterJdbcOperations",
        transactionManagerRef = "firstTransactionManager",
        basePackages = "org.test.multiple.databases.first")
public class FirstDbConfig extends AbstractJdbcConfiguration {
    @Bean
    @First
    DataSource firstDataSource() {
        PGSimpleDataSource source = new PGSimpleDataSource();
        source.setServerNames(new String[] { "xxx.xxx.x.x" });
        source.setPortNumbers(new int[] { 5434 });
        source.setDatabaseName("fist");
        source.setUser("user");
        source.setPassword("password");
        return source;
    }

    @Bean
    @First
    NamedParameterJdbcOperations firstNamedParameterJdbcOperations(@First DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }

    @Override
    @Bean
    @First
    public DataAccessStrategy dataAccessStrategyBean(@First NamedParameterJdbcOperations operations, JdbcConverter jdbcConverter,
                                                     JdbcMappingContext context, Dialect dialect) {
        return new DefaultDataAccessStrategy(new SqlGeneratorSource(context, jdbcConverter, dialect), context,
                jdbcConverter, operations, new SqlParametersFactory(context, jdbcConverter, dialect),
                new InsertStrategyFactory(operations, new BatchJdbcOperations(operations.getJdbcOperations()), dialect));
    }

    @Override
    @Bean
    @First
    public JdbcConverter jdbcConverter(JdbcMappingContext mappingContext, @First NamedParameterJdbcOperations operations,
                                       @Lazy RelationResolver relationResolver, JdbcCustomConversions conversions, Dialect dialect) {

        JdbcArrayColumns arrayColumns = dialect instanceof JdbcDialect ? ((JdbcDialect) dialect).getArraySupport()
                : JdbcArrayColumns.DefaultSupport.INSTANCE;
        DefaultJdbcTypeFactory jdbcTypeFactory = new DefaultJdbcTypeFactory(operations.getJdbcOperations(), arrayColumns);

        return new BasicJdbcConverter(mappingContext, relationResolver, conversions, jdbcTypeFactory,
                dialect.getIdentifierProcessing());
    }

    @Override
    @Bean
    @First
    public Dialect jdbcDialect(@First NamedParameterJdbcOperations operations) {
        return DialectResolver.getDialect(operations.getJdbcOperations());
    }

    @Bean
    @First
    TransactionManager firstTransactionManager(@First DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

second-db-client has the similar structure except it has different entity and repository and another DataSource bean.

@Data
@Table("")
@Accessors(chain = true)
@Builder(toBuilder = true)
@FieldNameConstants
public class SecondDbEntity {
    //fields
}

@Second
@Transactional(value = "secondTransactionManager")
public interface SecondDBEntityRepository extends PagingAndSortingRepository<SecondDbEntity, String> {
}

@Configuration
@EnableJdbcRepositories(jdbcOperationsRef = "firstNamedParameterJdbcOperations",
        transactionManagerRef = "secondTransactionManager",
        basePackages = "org.test.multiple.databases.second")
public class SecondDbConfig extends AbstractJdbcConfiguration {
    @Bean
    @Second
    DataSource secondDataSource() {
        PGSimpleDataSource source = new PGSimpleDataSource();
        source.setServerNames(new String[] { "xxx.xxx.x.x" });
        source.setPortNumbers(new int[] { 5433 });
        source.setDatabaseName("second");
        source.setUser("user");
        source.setPassword("password");
        return source;
    }

    @Bean
    @Second
    NamedParameterJdbcOperations secondNamedParameterJdbcOperations(@Second DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }

    @Override
    @Bean
    @Second
    public DataAccessStrategy dataAccessStrategyBean(@Second NamedParameterJdbcOperations operations, JdbcConverter jdbcConverter,
                                                     JdbcMappingContext context, Dialect dialect) {
        return new DefaultDataAccessStrategy(new SqlGeneratorSource(context, jdbcConverter, dialect), context,
                jdbcConverter, operations, new SqlParametersFactory(context, jdbcConverter, dialect),
                new InsertStrategyFactory(operations, new BatchJdbcOperations(operations.getJdbcOperations()), dialect));
    }

    @Override
    @Bean
    @Second
    public JdbcConverter jdbcConverter(JdbcMappingContext mappingContext, @Second NamedParameterJdbcOperations operations,
                                       @Lazy RelationResolver relationResolver, JdbcCustomConversions conversions, Dialect dialect) {

        JdbcArrayColumns arrayColumns = dialect instanceof JdbcDialect ? ((JdbcDialect) dialect).getArraySupport()
                : JdbcArrayColumns.DefaultSupport.INSTANCE;
        DefaultJdbcTypeFactory jdbcTypeFactory = new DefaultJdbcTypeFactory(operations.getJdbcOperations(), arrayColumns);

        return new BasicJdbcConverter(mappingContext, relationResolver, conversions, jdbcTypeFactory,
                dialect.getIdentifierProcessing());
    }

    @Override
    @Bean
    @Second
    public Dialect jdbcDialect(@Second NamedParameterJdbcOperations operations) {
        return DialectResolver.getDialect(operations.getJdbcOperations());
    }

    @Bean
    @Second
    TransactionManager secondTransactionManager(@Second DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

When I run the test it successfuly execute only first query and second one fails with an error with message something like this:
PreparedStatementCallback; bad SQL grammar [ sql query ]; nested exception is org.postgresql.util.PSQLException: ERROR: relation 'table name' does not exist

It means that both repositories uses the same database connection and this is the problem I'm trying to solve.
As you can see I'm trying to use @Qualifier annotation, specific bean names, overriding AbstractJdbcConfiguration beans to separate two connections, all my tries is there, but nothing is working.
What I'm doing wrong? How can I implement this case? Is it possible?
If not it would cool feature.
Can you please help?

PS: I don't want to use spring-data-jpa, because it is too heavy and has too much functionality for my needs. I don't want to use JdbcTemplate, because I want to use PagingAndSortingRepository sugar not to write simple queries. And sory for my English, it's not my native language.

[schauder]

Duplicate of #544