Spring boot jdbc data repository by multi data source

[davoodshiraz]

please help me.
I use multi data source in my project

data source properties:

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=db
spring.datasource.username=xxxxx
spring.datasource.password=xxxxx
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

spring.datasource2.url=jdbc:mysql://localhost:3306/db2
spring.datasource2.username=xxxx
spring.datasource2.password=xxx
spring.datasource2.driver-class-name=com.mysql.cj.jdbc.Driver

config class:

@Configuration
@EnableJdbcRepositories(jdbcOperationsRef = "mysqlNamedParameterJdbcOperations", basePackages = "com.example.demo.mysqlModels")
public class Config extends AbstractJdbcConfiguration {

    @Bean("mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mysqlNamedParameterJdbcOperations")
    NamedParameterJdbcOperations mysqlNamedParameterJdbcOperations(@Qualifier("mysqlDataSource") DataSource mysqlDataSource) {
        return new NamedParameterJdbcTemplate(mysqlDataSource);
    }
}

@Configuration
@EnableJdbcRepositories(jdbcOperationsRef = "mssqlNamedParameterJdbcOperations", basePackages = "com.example.demo.mssqlModels")
public class Config2 extends AbstractJdbcConfiguration {

    @Bean("mssqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource mssqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mssqlNamedParameterJdbcOperations")
    NamedParameterJdbcOperations mssqlNamedParameterJdbcOperations(@Qualifier("mssqlDataSource") DataSource mssqlDataSource) {
        return new NamedParameterJdbcTemplate(mssqlDataSource);
    }
}

repository in com.example.demo.mssqlModels:

public interface MssqlRepository extends PagingAndSortingRepository<MyEntity, Integer> {}

repository in com.example.demo.mysqlModels:

public interface MysqlRepository extends PagingAndSortingRepository<MyEntity, Integer> {}

my service:

@Slf4j
@Service
public class MyService {

   @Autowired
   private final MssqlRepository mssqlRepository;

   @Autowired
   private final MysqlRepository mysqlRepository;

    @PostConstruct
    public void init() {
        log.info("mssql result {}", mssqlRepository.findAll());
        log.info("mysql result {}", mysqlRepository.findAll());
    }
}

but result is same and both repositories read data from mysql datasource
thanks

[philwebb]

I've edited your comment to improve the formatting. You might want to check out this Mastering Markdown guide for future reference.

[philwebb]

It's hard to tell from the description if this is a bug in Spring Boot, Spring Data JDBC or a configuration issue. You could try debugging your application to check that JdbcRepositoryFactoryBean.setJdbcOperations is called correctly. If you can't get anywhere with that, please provide a sample application as either a zip file or a GitHub project so that we can diagnose the problem.

[wilkinsona]

This has also been posted as a question on Stack Overflow where a couple of other people are also trying to help.

@davoodshiraz please don’t cross-post like this as it ends up in people duplicating effort and wasting their time. In the interests of avoid any more of that, I’m going to close this in favour of the post on Stack Overflow. Updating it with a minimal, reproducible example would make it easier for people to help. We can re-open this issue if it turns out that it is a Spring Boot bug.

[oburgosm]

I have this problem with SB 2.7.2

I have created a very simple sample project to reproduce the problem @philwebb

https://github.com/oburgosm/spring-data-jdbc-test/

[wilkinsona]

@oburgosm Both of your repositories are sharing the same DataAccessStrategy that's defined as a bean in the context. This strategy is an instance of DefaultDataAccessStrategy which is using the @Primary NamedParameterJdbcOperations so all data access if being performed against the first data source.

You can configure a reference to a custom DataAccessStrategy on @EnableJdbcRepositories but defining one isn't entirely straightforward. I'm also not sure why you can configure a custom jdbcOperationsRef which is then ignored. jdbcOperationsRef also isn't mentioned in the reference documentation. In short, I'm getting a bit out of my depth here as this is specific to Spring Data JDBC and it out of Spring Boot's control. Please open a Spring Data JDBC issue so that they can take a look and hopefully make some improvements.

If you do open an issue, please comment here with a link to it so that people can follow along.

[oburgosm]

Thanks for your response @wilkinsona . I just found that there is an issue to support multiple datasources in spring-data-jdbc, but it is still open. I'll follow that issue.

[Hronom]

I think Spring Data should be refactored to support new proeprty datasources like this:

spring:
  datasources:
    primary:
      url: jdbc:postgresql://localhost:5432/primary_db
      username: postgres
      password: postgres
      driver-class-name: "org.postgresql.Driver"
      hikari:
        schema: primary_schema
      flyway:
        enabled: true
        locations: classpath:db/migration/primary
        schemas: ${spring.datasource.primary.hikari.schema}
        default-schema: ${spring.datasource.primary.hikari.schema}
        baseline-on-migrate: true

So you don't need to deal with beans and just add a new source e.g. primary, secodnary, third.
In @Repository annotation there should be property dataSourceName where you specify what data source to use.

[bclozel]

@Hronom that's #15732