如何配置和使用两个数据源?
例如，这是我的第一个数据源:
application.properties
#first db
spring.datasource.url = [url]
spring.datasource.username = [username]
spring.datasource.password = [password]
spring.datasource.driverClassName = oracle.jdbc.OracleDriver
#second db ...
应用程序类
@SpringBootApplication
public class SampleApplication
{
public static void main(String[] args) {
SpringApplication.run(SampleApplication.class, args);
}
}
如何修改应用。属性添加另一个数据源?我如何自动装配它以供不同的存储库使用?
当前回答
我的需求略有不同，但使用了两个数据源。
我为来自同一个包的同一个JPA实体使用了两个数据源。一个用于在服务器启动时执行DDL以创建/更新表，另一个用于运行时执行DML。
DDL连接应该在DDL语句执行之后关闭，以防止在代码的任何地方进一步使用超级用户特权。
属性
spring.datasource.url=jdbc:postgresql://Host:port
ddl.user=ddluser
ddl.password=ddlpassword
dml.user=dmluser
dml.password=dmlpassword
spring.datasource.driver-class-name=org.postgresql.Driver
数据源配置类
// DDL数据源的第一个配置类
public class DatabaseDDLConfig {
@Bean
public LocalContainerEntityManagerFactoryBean ddlEntityManagerFactoryBean() {
LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
PersistenceProvider persistenceProvider = new
org.hibernate.jpa.HibernatePersistenceProvider();
entityManagerFactoryBean.setDataSource(ddlDataSource());
entityManagerFactoryBean.setPackagesToScan(new String[] {
"com.test.two.data.sources"});
HibernateJpaVendorAdapter vendorAdapter = new
HibernateJpaVendorAdapter();
entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
HashMap<String, Object> properties = new HashMap<>();
properties.put("hibernate.dialect",
"org.hibernate.dialect.PostgreSQLDialect");
properties.put("hibernate.physical_naming_strategy",
"org.springframework.boot.orm.jpa.hibernate.
SpringPhysicalNamingStrategy");
properties.put("hibernate.implicit_naming_strategy",
"org.springframework.boot.orm.jpa.hibernate.
SpringImplicitNamingStrategy");
properties.put("hibernate.hbm2ddl.auto", "update");
entityManagerFactoryBean.setJpaPropertyMap(properties);
entityManagerFactoryBean.setPersistenceUnitName("ddl.config");
entityManagerFactoryBean.setPersistenceProvider(persistenceProvider);
return entityManagerFactoryBean;
}
@Bean
public DataSource ddlDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
dataSource.setUrl(env.getProperty("spring.datasource.url"));
dataSource.setUsername(env.getProperty("ddl.user");
dataSource.setPassword(env.getProperty("ddl.password"));
return dataSource;
}
@Bean
public PlatformTransactionManager ddlTransactionManager() {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(ddlEntityManagerFactoryBean().getObject());
return transactionManager;
}
}
// DML数据源的第二个配置类
public class DatabaseDMLConfig {
@Bean
@Primary
public LocalContainerEntityManagerFactoryBean dmlEntityManagerFactoryBean() {
LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
PersistenceProvider persistenceProvider = new org.hibernate.jpa.HibernatePersistenceProvider();
entityManagerFactoryBean.setDataSource(dmlDataSource());
entityManagerFactoryBean.setPackagesToScan(new String[] { "com.test.two.data.sources" });
JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
entityManagerFactoryBean.setJpaProperties(defineJpaProperties());
entityManagerFactoryBean.setPersistenceUnitName("dml.config");
entityManagerFactoryBean.setPersistenceProvider(persistenceProvider);
return entityManagerFactoryBean;
}
@Bean
@Primary
public DataSource dmlDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
dataSource.setUrl(envt.getProperty("spring.datasource.url"));
dataSource.setUsername("dml.user");
dataSource.setPassword("dml.password");
return dataSource;
}
@Bean
@Primary
public PlatformTransactionManager dmlTransactionManager() {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(dmlEntityManagerFactoryBean().getObject());
return transactionManager;
}
}
// DDL数据源在代码中的使用。
public class DDLServiceAtStartup {
//Import persistence unit ddl.config for ddl purpose.
@PersistenceUnit(unitName = "ddl.config")
private EntityManagerFactory entityManagerFactory;
public void executeDDLQueries() throws ContentServiceSystemError {
try {
EntityManager entityManager = entityManagerFactory.createEntityManager();
entityManager.getTransaction().begin();
entityManager.createNativeQuery("query to create/update table").executeUpdate();
entityManager.flush();
entityManager.getTransaction().commit();
entityManager.close();
//Close the ddl data source to avoid from further use in code.
entityManagerFactory.close();
} catch(Exception ex) {}
}
//在代码中使用DML数据源。
public class DDLServiceAtStartup {
@PersistenceUnit(unitName = "dml.config")
private EntityManagerFactory entityManagerFactory;
public void createRecord(User user) {
userDao.save(user);
}
}
其他回答
给你。
添加到您的应用程序中。属性文件:
#first db
spring.datasource.url = [url]
spring.datasource.username = [username]
spring.datasource.password = [password]
spring.datasource.driverClassName = oracle.jdbc.OracleDriver
#second db ...
spring.secondDatasource.url = [url]
spring.secondDatasource.username = [username]
spring.secondDatasource.password = [password]
spring.secondDatasource.driverClassName = oracle.jdbc.OracleDriver
在任何带@Configuration注解的类中添加以下方法:
@Bean
@Primary
@ConfigurationProperties(prefix="spring.datasource")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix="spring.secondDatasource")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
参考官方文件
创建多个数据源的工作方式与创建第一个数据源相同。如果您正在为JDBC或JPA使用默认的自动配置，那么您可能希望将其中一个标记为@Primary(然后任何@Autowired注入都将接收该配置)。
@Bean
@Primary
@ConfigurationProperties(prefix="datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix="datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
在Spring Boot application.properties中声明一个数据源
spring.datasource.company.url=jdbc:mysql://localhost/company_db?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.company.username=root
spring.datasource.company.password=root
spring.datasource.company.platform=mysql
spring.datasource.employee.url=jdbc:mysql://localhost/employee_db?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.employee.username=root
spring.datasource.employee.password=root
spring.datasource.employee.platform=mysql
使用多个数据源时，我们需要声明多个bean Spring应用程序上下文中的不同映射。 使用配置类
@Configuration
@EnableJpaRepositories(basePackages = "com.example.multiple.datasources.entity.company",
entityManagerFactoryRef = "companyEntityManagerFactory",
transactionManagerRef = "companyTransactionManager")
public class CompanyDataSourceConfiguration {
@Bean
@ConfigurationProperties("spring.datasource.company")
public DataSourceProperties companyDataSourceProperties() {
return new DataSourceProperties();
}
@Bean
@ConfigurationProperties("spring.datasource.company.configuration")
public DataSource companyDataSource() {
return companyDataSourceProperties().initializeDataSourceBuilder()
.type(HikariDataSource.class).build();
}
@Bean(name = "companyEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean companyEntityManagerFactory(EntityManagerFactoryBuilder builder) {
return builder.dataSource(companyDataSource()).packages(Company.class).build();
}
@Bean
public PlatformTransactionManager companyTransactionManager(
final @Qualifier("companyEntityManagerFactory") LocalContainerEntityManagerFactoryBean companyEntityManagerFactory
) {
return new JpaTransactionManager(companyEntityManagerFactory.getObject());
}
}
我们需要声明其中一个数据源为@Primary。这是因为 EntityManagerFactoryBuilder在jpbasaseconfiguration和中声明 该类需要注入单个数据源。
@Configuration
@EnableJpaRepositories(basePackages = "com.example.multiple.datasources.entity.employee",
entityManagerFactoryRef = "employeeEntityManagerFactory",
transactionManagerRef = "employeeTransactionManager")
public class EmployeeDatasourceConfiguration {
@Bean
@Primary
@ConfigurationProperties("spring.datasource.employee")
public DataSourceProperties employeeDataSourceProperties() {
return new DataSourceProperties();
}
@Bean
@Primary
@ConfigurationProperties("spring.datasource.employee.configuration")
public DataSource employeeDataSource() {
return employeeDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
}
@Primary
@Bean("employeeEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean employeeEntityManagerFactory(EntityManagerFactoryBuilder builder) {
return builder.dataSource(employeeDataSource()).packages(Employee.class).build();
}
@Primary
@Bean
public PlatformTransactionManager employeeTransactionManager(
final @Qualifier("employeeEntityManagerFactory") LocalContainerEntityManagerFactoryBean employeeEntityManagerFactory
) {
return new JpaTransactionManager(employeeEntityManagerFactory.getObject());
}
}
如果两个数据源位于相同的db location/server上，@Primary注释用于下面的方法效果很好。
@Bean(name = "datasource1")
@ConfigurationProperties("database1.datasource")
@Primary
public DataSource dataSource(){
return DataSourceBuilder.create().build();
}
@Bean(name = "datasource2")
@ConfigurationProperties("database2.datasource")
public DataSource dataSource2(){
return DataSourceBuilder.create().build();
}
如果数据源在不同的服务器上，最好使用@Component和@Primary注释。下面的代码片段适用于位于不同位置的两个不同数据源
database1.datasource.url = jdbc:mysql://127.0.0.1:3306/db1
database1.datasource.username = root
database1.datasource.password = mysql
database1.datasource.driver-class-name=com.mysql.jdbc.Driver
database2.datasource1.url = jdbc:mysql://192.168.113.51:3306/db2
database2.datasource1.username = root
database2.datasource1.password = mysql
database2.datasource1.driver-class-name=com.mysql.jdbc.Driver
@Configuration
@Primary
@Component
@ComponentScan("com.db1.bean")
class DBConfiguration1{
@Bean("db1Ds")
@ConfigurationProperties(prefix="database1.datasource")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
}
@Configuration
@Component
@ComponentScan("com.db2.bean")
class DBConfiguration2{
@Bean("db2Ds")
@ConfigurationProperties(prefix="database2.datasource1")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
}
我还必须从Spring Boot应用程序中建立到2个数据源的连接，这并不容易——Spring Boot文档中提到的解决方案不起作用。经过长时间的网上挖掘，我让它工作，主要思想是从这篇文章和一堆其他地方。
下面的解决方案是用Kotlin编写的，适用于Spring Boot 2.1.3和Hibernate Core 5.3.7。主要问题是，仅仅设置不同的DataSource配置是不够的，还必须为两个数据库配置EntityManagerFactory和TransactionManager。
下面是第一个(主)数据库的配置:
@Configuration
@EnableJpaRepositories(
entityManagerFactoryRef = "firstDbEntityManagerFactory",
transactionManagerRef = "firstDbTransactionManager",
basePackages = ["org.path.to.firstDb.domain"]
)
@EnableTransactionManagement
class FirstDbConfig {
@Bean
@Primary
@ConfigurationProperties(prefix = "spring.datasource.firstDb")
fun firstDbDataSource(): DataSource {
return DataSourceBuilder.create().build()
}
@Primary
@Bean(name = ["firstDbEntityManagerFactory"])
fun firstDbEntityManagerFactory(
builder: EntityManagerFactoryBuilder,
@Qualifier("firstDbDataSource") dataSource: DataSource
): LocalContainerEntityManagerFactoryBean {
return builder
.dataSource(dataSource)
.packages(SomeEntity::class.java)
.persistenceUnit("firstDb")
// Following is the optional configuration for naming strategy
.properties(
singletonMap(
"hibernate.naming.physical-strategy",
"org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl"
)
)
.build()
}
@Primary
@Bean(name = ["firstDbTransactionManager"])
fun firstDbTransactionManager(
@Qualifier("firstDbEntityManagerFactory") firstDbEntityManagerFactory: EntityManagerFactory
): PlatformTransactionManager {
return JpaTransactionManager(firstDbEntityManagerFactory)
}
}
这是第二个数据库的配置:
@Configuration
@EnableJpaRepositories(
entityManagerFactoryRef = "secondDbEntityManagerFactory",
transactionManagerRef = "secondDbTransactionManager",
basePackages = ["org.path.to.secondDb.domain"]
)
@EnableTransactionManagement
class SecondDbConfig {
@Bean
@ConfigurationProperties("spring.datasource.secondDb")
fun secondDbDataSource(): DataSource {
return DataSourceBuilder.create().build()
}
@Bean(name = ["secondDbEntityManagerFactory"])
fun secondDbEntityManagerFactory(
builder: EntityManagerFactoryBuilder,
@Qualifier("secondDbDataSource") dataSource: DataSource
): LocalContainerEntityManagerFactoryBean {
return builder
.dataSource(dataSource)
.packages(EntityFromSecondDb::class.java)
.persistenceUnit("secondDb")
.build()
}
@Bean(name = ["secondDbTransactionManager"])
fun secondDbTransactionManager(
@Qualifier("secondDbEntityManagerFactory") secondDbEntityManagerFactory: EntityManagerFactory
): PlatformTransactionManager {
return JpaTransactionManager(secondDbEntityManagerFactory)
}
}
数据源的属性是这样的:
spring.datasource.firstDb.jdbc-url=
spring.datasource.firstDb.username=
spring.datasource.firstDb.password=
spring.datasource.secondDb.jdbc-url=
spring.datasource.secondDb.username=
spring.datasource.secondDb.password=
属性的问题是我必须定义jdbc-url而不是url，否则就会出现异常。
注。 此外，您的数据库中可能有不同的命名方案，这就是我的情况。由于Hibernate 5不支持所有以前的命名方案，我不得不使用这个答案中的解决方案——也许它也会帮助到某些人。
