Multiple DataSources using Spring Data JDBC and CrudRepository Interface

Question

i have a not trivial question:

My Case:

• Using Spring Data JDBC
• Using Two databases
• Usage of CrudRepository

As you can see here in Spring Data JDBC you can extends CrudRepository and get with Spring all Crud Operations out of the box - without an explicit implementation!

It's an easy 4 step process for:

1. define your Properties
2. define your Entities
3. define an interface which extends CrudRepository and
4. make usage of that interface

But in case of using two databases, there is a 5. Step in which you have to define a @Configuration class.

I did that these 5 steps as following:

0. Pom.xml

 <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jdbc</artifactId>
    </dependency>
    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
      <exclusions>
        <exclusion>
          <groupId>org.junit.vintage</groupId>
          <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
  </dependencies>

1. Define your Properties

application.properties

## D1
datasource.db1.driverClassName=...
datasource.db1.username=...
datasource.db1.password=...
datasource.db1.jdbcUrl=...
## D2
datasource.db2.driverClassName=...
datasource.db2.username=...
datasource.db2.password=...
datasource.db2.jdbcUrl=...

2. Define your Entities (one for each DB)

Student.java // for db1

@Table("STUDENT_TABLE")
public class Student{
    @Id
    @Column("MAT_NR")
    private BigDecimal matNr;

    @Column("NAME")
    private String name;
}

Teacher.java // for db2

@Table("TEACHER_TABLE")
public class Teacher{
    @Id
    @Column("EMPLOYEE_NR")
    private BigDecimal employeeNr;

    @Column("NAME")
    private String name;
}

3. Define your Repositories (one for each DB)

StudentRepository.java // for DB1

@Repository
public interface StudentRepository extends CrudRepository<Student, BigDecimal> {}

TeacherRepository.java // for DB2

@Repository
public interface TeacherRepository extends CrudRepository<Teacher, BigDecimal> {}

4. Define your @Configuration class (one for each DB)

• you can also take both in one class but I did in in that way:

Db1Config.java

@Configuration
public class Db1Config {
    @Primary
    @Bean("db1DataSource")
    @ConfigurationProperties("datasource.db1")
    public DataSource db1DataSource() {
        return DataSourceBuilder.create().build();
    }
}

Db2Config.java

@Configuration
public class Db2Config {
    @Bean("db2DataSource")
    @ConfigurationProperties("datasource.db2")
    public DataSource db2DataSource() {
        return DataSourceBuilder.create().build();
    }
}

5. Make usage of your interface repositories

Application.java

@SpringBootApplication
public class Application implements CommandLineRunner {

    @Autowired @Qualifier("studentRepository") StudentRepository studentRepository
    @Autowired @Qualifier("teacherRepository") TeacherRepository teacherRepository 

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        studentRepository.findById(30688).ifPresent(System.out::println); // DB1
        teacherRepository.findById(5).ifPresent(System.out::println); // DB2
    }
}

These is working fine!

The problem here is, that TeacherRepository not queries DB2, it queries DB1.

which results in an Error: [...]: Unknown table name:TEACHER.

Does anyone knows how i can configure that TeacherRepository use DB2 as DataSource ?

# Please note before you Answer:

Here i'm using Spring Data JDBC and not Spring Data JPA. I know that it works in Spring Data JPA like described here https://www.baeldung.com/spring-data-jpa-multiple-databases. I know also that i can make usage of these JdbcTemplate. But in that way, i have to write these CRUD Operations by myself which is described here and that’s not what need.

An answer would be nice of course.

Thanks for your help.

Answer 1

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 SOURCE
spring.sql-server-ds.jdbcUrl= jdbc:sqlserver://localhost:1554;databaseName=TestDB
spring.sql-server-ds.username= uteappl
spring.sql-server-ds.password= mypassword

## H2 DATA SOURCE
spring.h2-ds.jdbcUrl= jdbc:h2:mem:testdb;mode=MySQL
spring.h2-ds.username= sa
spring.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);
}

Answer 2

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.

Answer 3

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")
@Configuration
public class Db1Config {

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

Answer 4

Similar to Rimidal, I don't believe this works. The docs here indicate that you need a NamedParameterJdbcOperations Bean and (optionally) a TransactionManager Bean.: https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#jdbc.java-config

The NamedParameterJdbcOperations is the JDBCTemplate that CrudRepositories will use to hit the database.

There doeesn't seem to be a way to associate the different NamedParameterJdbcOperations/JDBCTemplates with different Repositories. In my testing this doesn't work anyway. Whichever NamedParameterJdbcOperations Bean is marked @Primary is the one all CrudRepository operations are fed though, regardless of segregating things into different packages and explicitely telling the @Configuration classes which packages to use with @EnableJdbcRepositories.