MySQL和SQL server两个数据源配置使用
背景:最近项目上有这方面的需求,就百度找找合适的配置。结果很多都是SpringBoot 1.x 的,而且也很少有刚好配置MySQL和SQL server这俩玩意儿的。
于是,就给我这SpringBoot 2.5.6 的收拾了一套还算简约的配置。
场景:
SpringBoot 2.5.6
一个MySQL8数据源,一个SQL server数据源
MySQL数据库操作使用Jpa框架,SQL server部分使用JdbcTemplate
配置文件application.yml
需要注意:只有一个MySQL数据源时,url配置如下:spring.datasource.url=....
有多个数据源时,需要在中间加个词来区分不同数据源的配置,primary\secondary或者其他名称都可以。url需要改成 jdbc-url
数据源完整配置如下(已省略项目其他基础配置部分比如端口号之类的)
#jpa配置
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.jpa.show-sql=true
spring.jpa.open-in-view=false
#mysql数据源
spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
spring.datasource.primary.username=abc
spring.datasource.primary.password=123456
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
#sql server 数据源
spring.datasource.second.jdbc-url=jdbc:sqlserver://localhost:1433;database=DB2
spring.datasource.second.username=abc
spring.datasource.second.password=123456
spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
#hikari连接池的配置 (这段可以忽略)
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.minimum-idle=1
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.idle-timeout=60000
spring.datasource.hikari.max-lifetime=70000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.connection-test-query=SELECT 1
数据源配置类DataSourceConfig
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
/***
* 配置主数据源
* @return
*/
@Primary
@Bean
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.second")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
return new JdbcTemplate(secondaryDataSource);
}
}
由于MySQL数据源使用Jpa框架,需要另外单独配置
主数据源Jpa配置PrimaryConfig
import java.util.Map;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactoryPrimary",
transactionManagerRef = "transactionManagerPrimary",
basePackages = {"com.example.demo.dao"} //持久层所在的包
)
public class PrimaryConfig {
@Autowired
@Qualifier("primaryDataSource")
private DataSource primaryDataSource;
@Autowired
private JpaProperties jpaProperties;
@Autowired
private HibernateProperties hibernateProperties;
private Map<String, Object> getVendorProperties() {
return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
}
@Primary
@Bean(name = "entityManagerPrimary")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
}
@Primary
@Bean(name = "entityManagerFactoryPrimary")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) {
return builder
.dataSource(primaryDataSource)
.packages("com.example.demo.domain") //设置实体类所在位置
.persistenceUnit("primaryPersistenceUnit")
.properties(getVendorProperties())
.build();
}
@Primary
@Bean(name = "transactionManagerPrimary")
public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
}
}
随后配置就完成了,SQL server的部分由于项目中用的不多,所以使用jdbcTemplate,就不需要额外的配置了。
其实多数据源的配置,和单数据源的配置相差不大,使用什么框架什么数据库对应什么配置。只不过在单数据源的时候,往往不需要什么配置。。
欢迎大家交流与讨论