Multiple DataSources using Spring Data JDBC and CrudRepository Interface

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.