Multiple DataSources using Spring Data JDBC and CrudRepository Interface

I had a similar problem. My solution had to have my repositories put in 2 separate packages, as per Chris Savory answer, and then define 2 @Configuration classes defining 1 JdbcOperation each.Here's my full configuration (I have an SQL Server and an H2 data sources):

application.properties

Please note that these properties are Hikari CP specific. Mileage may vary if you chose a different CP (i.e. Tomcat)

## SQL SERVER DATA SOURCEspring.sql-server-ds.jdbcUrl= jdbc:sqlserver://localhost:1554;databaseName=TestDBspring.sql-server-ds.username= uteapplspring.sql-server-ds.password= mypassword## H2 DATA SOURCEspring.h2-ds.jdbcUrl= jdbc:h2:mem:testdb;mode=MySQLspring.h2-ds.username= saspring.h2-ds.password= password

First H2 @Configuration

@Configuration@EnableJdbcRepositories(jdbcOperationsRef = "h2JdbcOperations", basePackages = "com.twinkie.repository.h2")public class H2JdbcConfiguration extends AbstractJdbcConfiguration {  @Bean  @ConfigurationProperties(prefix = "spring.h2-ds")  public DataSource h2DataSource() {    return DataSourceBuilder.create().build();  }  @Bean  NamedParameterJdbcOperations h2JdbcOperations(@Qualifier("h2DataSource") DataSource sqlServerDs) {    return new NamedParameterJdbcTemplate(sqlServerDs);  }  @Bean  public DataSourceInitializer h2DataSourceInitializer(      @Qualifier("h2DataSource") final DataSource dataSource) {    ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator(        new ClassPathResource("schema.sql"));    DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();    dataSourceInitializer.setDataSource(dataSource);    dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);    return dataSourceInitializer;  }}

Second SQL Server @Configuration

@Configuration@EnableJdbcRepositories("com.twinkie.repository.sqlserver")public class SqlServerJdbcConfiguration {  @Bean  @Primary  @ConfigurationProperties(prefix = "spring.sql-server-ds")  public DataSource sqlServerDataSource() {    return DataSourceBuilder.create().build();  }  @Bean  @Primary  NamedParameterJdbcOperations jdbcOperations(      @Qualifier("sqlServerDataSource") DataSource sqlServerDs) {    return new NamedParameterJdbcTemplate(sqlServerDs);  }}

Then I have my repositories (please note the different packages).

SQL Server

package com.twinkie.repository.sqlserver;import com.twinkie.model.SoggettoAnag;import java.util.List;import org.springframework.data.jdbc.repository.query.Query;import org.springframework.data.repository.CrudRepository;public interface SoggettoAnagRepository extends CrudRepository<SoggettoAnag, Long> {  @Query("SELECT * FROM LLA_SOGGETTO_ANAG WHERE sys_timestamp > :sysTimestamp ORDER BY sys_timestamp ASC")  List<SoggettoAnag> findBySysTimestampGreaterThan(Long sysTimestamp);}

H2

package com.twinkie.repository.h2;import com.twinkie.model.GlSync;import java.util.Optional;import org.springframework.data.jdbc.repository.query.Modifying;import org.springframework.data.jdbc.repository.query.Query;import org.springframework.data.repository.Repository;public interface GlSyncRepository extends Repository<GlSync, String> {  @Modifying  @Query("INSERT INTO GL_SYNC (table_name, last_rowversion) VALUES (:tableName, :rowVersion) ON DUPLICATE KEY UPDATE last_rowversion = :rowVersion")  boolean save(String tableName, Long rowVersion);  @Query("SELECT table_name, last_rowversion FROM gl_sync WHERE table_name = :tableName")  Optional<GlSync> findById(String tableName);}

Put your Entity and Repository classes/interfaces into different packages. Then you will need to tell Spring Jpa where to scan for those pakcages in your separate config files

@EnableJpaRepositories(basePackages = { "com.yourpackage.repositories1" },        entityManagerFactoryRef = "entityManagerFactory",        transactionManagerRef = "transactionManager")@Configurationpublic class Db1Config {

@EnableJpaRepositories(basePackages = { "com.yourpackage.repositories2" },        entityManagerFactoryRef = "entityManagerFactory",        transactionManagerRef = "transactionManager")@Configurationpublic class Db2Config {

I think you are almost done with configuration but one part is missing in my opinion. You create Db1Config and Db2Config and you distinguished between them. But how does spring know what to use and where. My guess is: you have to provide two TransactionManagers(I used for the same problem) and connect repositories (appropriate). In case TransactionManager is not in @EnableJDVCRepositories please provide more about your code (pom.xml?) I am almost sure you have to create at least two beans more.

I would start here with a research. That is how spring does it for one datasource and one transaction manager.