使用 Spring Data JDBC 和 CrudRepository 接口的多个数据源 [英] Multiple DataSources using Spring Data JDBC and CrudRepository Interface
问题描述
我有一个重要的问题:
我的案例:
- 使用Spring Data JDBC
- 使用两个数据库
CrudRepository
的使用
正如您在 Spring 中看到的此处数据 JDBC 您可以扩展 CrudRepository
并使用 Spring 获得所有开箱即用的 Crud 操作 - 无需显式实现!
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!
这是一个简单的 4 步过程:
It's an easy 4 step process for:
- 定义您的属性
- 定义您的实体
- 定义一个接口来扩展 CrudRepository 和
- 使用该接口
但是在使用两个数据库的情况下,有一个 5. 步骤,您必须定义一个 @Configuration
类.
But in case of using two databases, there is a 5. Step in which you have to define a @Configuration
class.
我做了以下 5 个步骤:
I did that these 5 steps as following:
<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.定义您的属性
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.定义您的实体(每个数据库一个)
Student.java//用于 db1
@Table("STUDENT_TABLE")
public class Student{
@Id
@Column("MAT_NR")
private BigDecimal matNr;
@Column("NAME")
private String name;
}
Teacher.java//用于 db2
@Table("TEACHER_TABLE")
public class Teacher{
@Id
@Column("EMPLOYEE_NR")
private BigDecimal employeeNr;
@Column("NAME")
private String name;
}
3.定义您的存储库(每个数据库一个)
StudentRepository.java//用于 DB1
@Repository
public interface StudentRepository extends CrudRepository<Student, BigDecimal> {}
TeacherRepository.java//用于 DB2
@Repository
public interface TeacherRepository extends CrudRepository<Teacher, BigDecimal> {}
4.定义你的@Configuration 类(每个数据库一个)
- 你也可以在一门课上同时参加这两个课程,但我是这样做的:
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.使用您的界面存储库
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
}
}
这些工作正常!
导致错误:[...]: Unknown table name:TEACHER
.
#回答前请注意:
这里我使用的是 Spring Data JDBC 而不是 Spring Data JPA.我知道它在 Spring Data JPA 中工作,就像这里描述的 https://www.baeldung.com/spring-data-jpa-multiple-databases.我也知道我可以使用这些 JdbcTemplate
.但是那样的话,我必须自己编写这些 CRUD 操作,这些操作在 here 和这不是必需的.
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.
答案当然很好.
感谢您的帮助.
推荐答案
我遇到了类似的问题.根据 Chris Savory 的回答,我的解决方案必须将我的存储库放入 2 个单独的包中,然后定义 2 个 @Configuration 类,每个类定义 1 个 JdbcOperation.这是我的完整配置(我有一个 SQL Server 和一个 H2 数据源):
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
application.properties
请注意,这些属性是 Hikari CP 特定的.如果您选择不同的 CP(即 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
第一个 H2 @Configuration
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;
}
}
第二个 SQL Server @Configuration
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);
}
这篇关于使用 Spring Data JDBC 和 CrudRepository 接口的多个数据源的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!