SpringBoot實現多數據源配置詳解
作者：Springboot實戰案例錦集
因為我們使用的是Hikari數據源，所以這里我是直接copy默認系統Hikari的屬性文件。也就是這個文件：org.springframework.boot.autoconfigure.jdbc.DataSourceProperties 為啥我不直接繼承這個類而是在自己的項目中新建這么一個類？
開發環境：JDK1.8+SpringBoot2.4.12+Oracle
這里我們假設要使用兩個數據源分別為：master和slave。
- pom.xml 依賴包
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.github.noraui</groupId>
<artifactId>ojdbc7</artifactId>
<version>12.1.0.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
</dependencies>- application.yml配置
server:
port: 50000
---
spring:
jpa:
hibernate:
ddlAuto: update
openInView: true
showSql: false
databasePlatform: org.hibernate.dialect.Oracle10gDialect
---
# 第一個數據源
master:
datasource:
driverClassName: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@localhost:1521/orcl
username: t0
password: t0
type: com.zaxxer.hikari.HikariDataSource
hikari:
minimumIdle: 10
maximumPoolSize: 200
autoCommit: true
idleTimeout: 30000
poolName: MasterDatabookHikariCP
maxLifetime: 1800000
connectionTimeout: 30000
connectionTestQuery: SELECT 1 FROM DUAL
# 第二個數據源
slave:
datasource:
driverClassName: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@localhost:1521/orcl
username: t1
password: t1
type: com.zaxxer.hikari.HikariDataSource
hikari:
minimumIdle: 10
maximumPoolSize: 200
autoCommit: true
idleTimeout: 30000
poolName: SlaveDatabookHikariCP
maxLifetime: 1800000
connectionTimeout: 30000
connectionTestQuery: SELECT 1 FROM DUAL
---
# mybatis 配置，分表對應到不同的包中
master:
mybatis:
config-location: classpath:/MyBatis-conf.xml
type-aliases-package: com.pack.domain #master數據源對應的包
mapper-locations:
- classpath:/com/pack/mapper/oracle/*.xml #master數據源對應mapper文件
slave:
mybatis:
config-location: classpath:/MyBatis-conf.xml
type-aliases-package: com.pack.slave.domain #slave數據源對應的包
mapper-locations:
- classpath:/com/pack/slave/mapper/oracle/*.xml #slave數據源對應mapper文件
---
# jpa相關的配置
master:
jpa:
repos: com.pack.base.repository #master數據源對應的包配置
domain: com.pack.domain #master對應的實體包
slave:
jpa:
repos: com.pack.slave.repository #salve數據源對應的包配置
domain: com.pack.slave.domain #slave對應的實體包以上就是兩個數據源對應相關的配置了，大家注意看里面的注釋。接下來我們看mabatis和jpa對應的類相關的配置了，都是固定的配置。
- 數據源屬性配置對應的java類配置
BaseProperties類
public class BaseDataSourceProperties implements BeanClassLoaderAware, InitializingBean {
private ClassLoader classLoader;
private String name;
private boolean generateUniqueName;
private Class<? extends DataSource> type;
private String driverClassName;
private String url;
private String username;
private String password;
private String jndiName;
private DataSourceInitializationMode initializationMode = DataSourceInitializationMode.EMBEDDED;
private String platform = "all";
private List<String> schema;
private String schemaUsername;
private String schemaPassword;
private List<String> data;
private String dataUsername;
private String dataPassword;
private boolean continueOnError = false;
private String separator = ";";
private Charset sqlScriptEncoding;
private EmbeddedDatabaseConnection embeddedDatabaseConnection = EmbeddedDatabaseConnection.NONE;
private Xa xa = new Xa();
private String uniqueName;
@Override
public void setBeanClassLoader(ClassLoader classLoader) {
this.classLoader = classLoader;
}
@Override
public void afterPropertiesSet() throws Exception {
this.embeddedDatabaseConnection = EmbeddedDatabaseConnection
.get(this.classLoader);
}
public DataSourceBuilder<?> initializeDataSourceBuilder() {
return DataSourceBuilder.create(getClassLoader()).type(getType())
.driverClassName(determineDriverClassName()).url(determineUrl())
.username(determineUsername()).password(determinePassword());
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public boolean isGenerateUniqueName() {
return this.generateUniqueName;
}
public void setGenerateUniqueName(boolean generateUniqueName) {
this.generateUniqueName = generateUniqueName;
}
public Class<? extends DataSource> getType() {
return this.type;
}
public void setType(Class<? extends DataSource> type) {
this.type = type;
}
public String getDriverClassName() {
return this.driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public String determineDriverClassName() {
if (StringUtils.hasText(this.driverClassName)) {
Assert.state(driverClassIsLoadable(),
() -> "Cannot load driver class: " + this.driverClassName);
return this.driverClassName;
}
String driverClassName = null;
if (StringUtils.hasText(this.url)) {
driverClassName = DatabaseDriver.fromJdbcUrl(this.url).getDriverClassName();
}
if (!StringUtils.hasText(driverClassName)) {
driverClassName = this.embeddedDatabaseConnection.getDriverClassName();
}
if (!StringUtils.hasText(driverClassName)) {
throw new DataSourceBeanCreationException(
"Failed to determine a suitable driver class", this,
this.embeddedDatabaseConnection);
}
return driverClassName;
}
private boolean driverClassIsLoadable() {
try {
ClassUtils.forName(this.driverClassName, null);
return true;
}
catch (UnsupportedClassVersionError ex) {
// Driver library has been compiled with a later JDK, propagate error
throw ex;
}
catch (Throwable ex) {
return false;
}
}
public String getUrl() {
return this.url;
}
public void setUrl(String url) {
this.url = url;
}
public String determineUrl() {
if (StringUtils.hasText(this.url)) {
return this.url;
}
String databaseName = determineDatabaseName();
String url = (databaseName != null)
? this.embeddedDatabaseConnection.getUrl(databaseName) : null;
if (!StringUtils.hasText(url)) {
throw new DataSourceBeanCreationException(
"Failed to determine suitable jdbc url", this,
this.embeddedDatabaseConnection);
}
return url;
}
public String determineDatabaseName() {
if (this.generateUniqueName) {
if (this.uniqueName == null) {
this.uniqueName = UUID.randomUUID().toString();
}
return this.uniqueName;
}
if (StringUtils.hasLength(this.name)) {
return this.name;
}
if (this.embeddedDatabaseConnection != EmbeddedDatabaseConnection.NONE) {
return "testdb";
}
return null;
}
public String getUsername() {
return this.username;
}
public void setUsername(String username) {
this.username = username;
}
public String determineUsername() {
if (StringUtils.hasText(this.username)) {
return this.username;
}
if (EmbeddedDatabaseConnection.isEmbedded(determineDriverClassName())) {
return "sa";
}
return null;
}
public String getPassword() {
return this.password;
}
public void setPassword(String password) {
this.password = password;
}
public String determinePassword() {
if (StringUtils.hasText(this.password)) {
return this.password;
}
if (EmbeddedDatabaseConnection.isEmbedded(determineDriverClassName())) {
return "";
}
return null;
}
public String getJndiName() {
return this.jndiName;
}
public void setJndiName(String jndiName) {
this.jndiName = jndiName;
}
public DataSourceInitializationMode getInitializationMode() {
return this.initializationMode;
}
public void setInitializationMode(DataSourceInitializationMode initializationMode) {
this.initializationMode = initializationMode;
}
public String getPlatform() {
return this.platform;
}
public void setPlatform(String platform) {
this.platform = platform;
}
public List<String> getSchema() {
return this.schema;
}
public void setSchema(List<String> schema) {
this.schema = schema;
}
public String getSchemaUsername() {
return this.schemaUsername;
}
public void setSchemaUsername(String schemaUsername) {
this.schemaUsername = schemaUsername;
}
public String getSchemaPassword() {
return this.schemaPassword;
}
public void setSchemaPassword(String schemaPassword) {
this.schemaPassword = schemaPassword;
}
public List<String> getData() {
return this.data;
}
public void setData(List<String> data) {
this.data = data;
}
public String getDataUsername() {
return this.dataUsername;
}
public void setDataUsername(String dataUsername) {
this.dataUsername = dataUsername;
}
public String getDataPassword() {
return this.dataPassword;
}
public void setDataPassword(String dataPassword) {
this.dataPassword = dataPassword;
}
public boolean isContinueOnError() {
return this.continueOnError;
}
public void setContinueOnError(boolean continueOnError) {
this.continueOnError = continueOnError;
}
public String getSeparator() {
return this.separator;
}
public void setSeparator(String separator) {
this.separator = separator;
}
public Charset getSqlScriptEncoding() {
return this.sqlScriptEncoding;
}
public void setSqlScriptEncoding(Charset sqlScriptEncoding) {
this.sqlScriptEncoding = sqlScriptEncoding;
}
public ClassLoader getClassLoader() {
return this.classLoader;
}
public Xa getXa() {
return this.xa;
}
public void setXa(Xa xa) {
this.xa = xa;
}
public static class Xa {
private String dataSourceClassName;
private Map<String, String> properties = new LinkedHashMap<>();
public String getDataSourceClassName() {
return this.dataSourceClassName;
}
public void setDataSourceClassName(String dataSourceClassName) {
this.dataSourceClassName = dataSourceClassName;
}
public Map<String, String> getProperties() {
return this.properties;
}
public void setProperties(Map<String, String> properties) {
this.properties = properties;
}
}
static class DataSourceBeanCreationException extends BeanCreationException {
private static final long serialVersionUID = 1L;
private final BaseDataSourceProperties properties;
private final EmbeddedDatabaseConnection connection;
DataSourceBeanCreationException(String message, BaseDataSourceProperties properties,
EmbeddedDatabaseConnection connection) {
super(message);
this.properties = properties;
this.connection = connection;
}
public BaseDataSourceProperties getProperties() {
return this.properties;
}
public EmbeddedDatabaseConnection getConnection() {
return this.connection;
}
}
}mybatis對應的base屬性文件
public class BaseMybatisProperties {
private String configLocation;
private String[] mapperLocations;
private String typeAliasesPackage;
private String typeHandlersPackage;
private boolean checkConfigLocation = false;
private ExecutorType executorType;
private Configuration configuration;
public String getConfigLocation() {
return this.configLocation;
}
public void setConfigLocation(String configLocation) {
this.configLocation = configLocation;
}
@Deprecated
public String getConfig() {
return this.configLocation;
}
@Deprecated
public void setConfig(String config) {
this.configLocation = config;
}
public String[] getMapperLocations() {
return this.mapperLocations;
}
public void setMapperLocations(String[] mapperLocations) {
this.mapperLocations = mapperLocations;
}
public String getTypeHandlersPackage() {
return this.typeHandlersPackage;
}
public void setTypeHandlersPackage(String typeHandlersPackage) {
this.typeHandlersPackage = typeHandlersPackage;
}
public String getTypeAliasesPackage() {
return this.typeAliasesPackage;
}
public void setTypeAliasesPackage(String typeAliasesPackage) {
this.typeAliasesPackage = typeAliasesPackage;
}
public boolean isCheckConfigLocation() {
return this.checkConfigLocation;
}
public void setCheckConfigLocation(boolean checkConfigLocation) {
this.checkConfigLocation = checkConfigLocation;
}
public ExecutorType getExecutorType() {
return this.executorType;
}
public void setExecutorType(ExecutorType executorType) {
this.executorType = executorType;
}
public Configuration getConfiguration() {
return configuration;
}
public void setConfiguration(Configuration configuration) {
this.configuration = configuration;
}
public Resource[] resolveMapperLocations() {
List<Resource> resources = new ArrayList<Resource>();
if (this.mapperLocations != null) {
for (String mapperLocation : this.mapperLocations) {
Resource[] mappers;
try {
mappers = new PathMatchingResourcePatternResolver().getResources(mapperLocation);
resources.addAll(Arrays.asList(mappers));
} catch (IOException e) {
}
}
}
Resource[] mapperLocations = new Resource[resources.size()];
mapperLocations = resources.toArray(mapperLocations);
return mapperLocations;
}
}因為我們使用的是Hikari數據源，所以這里我是直接copy默認系統Hikari的屬性文件。
也就是這個文件：org.springframework.boot.autoconfigure.jdbc.DataSourceProperties 為啥我不直接繼承這個類而是在自己的項目中新建這么一個類，是因為我發現這個類有這個注解
@ConfigurationProperties(prefix = "spring.datasource")
怕的是它的這個注解會覆蓋我接下來兩個類的注解（我主要是懶得測試，所以直接copy一份無所謂了）。
接下來看看具體master和slave兩個數據源的屬性文件：
- Properties（jpa和mybatis）文件
@Component
@ConfigurationProperties(prefix = "master.datasource")
public class MasterDataSourceProperties extends BaseDataSourceProperties {
}@Component
@ConfigurationProperties(prefix = "slave.datasource")
public class SlaveDataSourceProperties extends BaseDataSourceProperties {
}@Component
@ConfigurationProperties(prefix = "master.mybatis")
public class MasterMybatisProperties extends BaseMybatisProperties {
}@Component
@ConfigurationProperties(prefix = "slave.mybatis")
public class SlaveMybatisProperties extends BaseMybatisProperties {
}接下來是數據源的配置了。
- 數據源配置類
@Configuration
public class HikariDataSourceConfig {
@Bean
@Primary
public HikariDataSource masterDataSource(MasterDataSourceProperties properties) {
HikariDataSource dataSource = createDataSource(properties,
HikariDataSource.class);
if (StringUtils.hasText(properties.getName())) {
dataSource.setPoolName(properties.getName());
}
return dataSource;
}
@Bean
public HikariDataSource slaveDataSource(SlaveDataSourceProperties properties) {
HikariDataSource dataSource = createDataSource(properties,
HikariDataSource.class);
if (StringUtils.hasText(properties.getName())) {
dataSource.setPoolName(properties.getName());
}
return dataSource;
}
@SuppressWarnings("unchecked")
protected static <T> T createDataSource(BaseDataSourceProperties properties,
Class<? extends DataSource> type) {
return (T) properties.initializeDataSourceBuilder().type(type).build();
}
}因為我們配置的是多個數據源所有其中一個數據源必須加入這個注解@Primary
接下來是jpa的EntityManagerFactory工廠的配置了
- EntityManagerFactory配置
public class EntityManagerFactoryConfig {
@Configuration
@EnableJpaRepositories(basePackages = {
"${master.jpa.repos}" }, entityManagerFactoryRef = "masterEntityManagerFactory", transactionManagerRef = "masterJPATransactionManager")
static class MasterEntityManagerFactory {
@Resource(name = "masterDataSource")
private DataSource masterDataSource;
@Value("${master.jpa.domain}")
private String masterDomainPkg;
@Bean
@Primary
public LocalContainerEntityManagerFactoryBean masterEntityManagerFactory(EntityManagerFactoryBuilder builder) {
Map<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", "update");
properties.put("hibernate.id.new_generator_mappings", true);
properties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName()) ;
return builder.dataSource(masterDataSource).packages(masterDomainPkg).persistenceUnit("master")
.properties(properties).build();
}
@Bean
@Primary
public PlatformTransactionManager masterJPATransactionManager(EntityManagerFactoryBuilder builder) {
JpaTransactionManager tm = new JpaTransactionManager(masterEntityManagerFactory(builder).getObject());
return tm;
}
}
@Configuration
@EnableJpaRepositories(basePackages = {
"${slave.jpa.repos}" }, entityManagerFactoryRef = "slaveEntityManagerFactory", transactionManagerRef = "slaveJPATransactionManager")
@ConditionalOnProperty(name = "multiple.ds.enabled", havingValue = "true")
static class SlaveEntityManagerFactory {
@Resource(name = "slaveDataSource")
private DataSource slaveDataSource;
@Value("${slave.jpa.domain}")
private String slaveDomainPkg;
@Bean
public LocalContainerEntityManagerFactoryBean slaveEntityManagerFactory(EntityManagerFactoryBuilder builder) {
Map<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", "update");
properties.put("hibernate.id.new_generator_mappings", true);
properties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName()) ;
return builder.dataSource(slaveDataSource).packages(slaveDomainPkg).persistenceUnit("slave")
.properties(properties).build();
}
@Bean
public PlatformTransactionManager slaveJPATransactionManager(EntityManagerFactoryBuilder builder) {
JpaTransactionManager tm = new JpaTransactionManager(slaveEntityManagerFactory(builder).getObject());
return tm;
}
}
}- mybatis SqlSessionFactory工廠配置
public class SqlSessionFactoryConfig {
@Configuration
static class MasterSqlSessionFactory {
@Resource
private MasterMybatisProperties properties;
@Autowired(required = false)
private Interceptor[] interceptors;
@Autowired
private ResourceLoader resourceLoader = new DefaultResourceLoader();
@Autowired(required = false)
private DatabaseIdProvider databaseIdProvider;
@Bean
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(dataSource);
factory.setVfs(SpringBootVFS.class);
if (StringUtils.hasText(this.properties.getConfigLocation())) {
factory.setConfigLocation(this.resourceLoader.getResource(this.properties.getConfigLocation()));
}
factory.setConfiguration(properties.getConfiguration());
if (!ObjectUtils.isEmpty(this.interceptors)) {
factory.setPlugins(this.interceptors);
}
if (this.databaseIdProvider != null) {
factory.setDatabaseIdProvider(this.databaseIdProvider);
}
if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) {
factory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());
}
if (StringUtils.hasLength(this.properties.getTypeHandlersPackage())) {
factory.setTypeHandlersPackage(this.properties.getTypeHandlersPackage());
}
if (!ObjectUtils.isEmpty(this.properties.resolveMapperLocations())) {
factory.setMapperLocations(this.properties.resolveMapperLocations());
}
return factory.getObject();
}
@Bean
public DataSourceTransactionManager masterTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean
public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory")SqlSessionFactory sqlSessionFactory) {
ExecutorType executorType = this.properties.getExecutorType();
if (executorType != null) {
return new SqlSessionTemplate(sqlSessionFactory, executorType);
} else {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
}
@Configuration
@ConditionalOnProperty(name = "multiple.ds.enabled", havingValue = "true")
static class SlaveSqlSessionFactory {
@Resource
private SlaveMybatisProperties properties;
@Autowired(required = false)
private Interceptor[] interceptors;
@Autowired
private ResourceLoader resourceLoader = new DefaultResourceLoader();
@Autowired(required = false)
private DatabaseIdProvider databaseIdProvider;
@Bean
public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(dataSource);
factory.setVfs(SpringBootVFS.class);
if (StringUtils.hasText(this.properties.getConfigLocation())) { factory.setConfigLocation(this.resourceLoader.getResource(this.properties.getConfigLocation()));
}
factory.setConfiguration(properties.getConfiguration());
if (!ObjectUtils.isEmpty(this.interceptors)) {
factory.setPlugins(this.interceptors);
}
if (this.databaseIdProvider != null) {
factory.setDatabaseIdProvider(this.databaseIdProvider);
}
if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) {
factory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());
}
if (StringUtils.hasLength(this.properties.getTypeHandlersPackage())) { factory.setTypeHandlersPackage(this.properties.getTypeHandlersPackage());
}
if (!ObjectUtils.isEmpty(this.properties.resolveMapperLocations())) {
factory.setMapperLocations(this.properties.resolveMapperLocations());
}
return factory.getObject();
}
@Bean
public DataSourceTransactionManager slaveTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean
public SqlSessionTemplate slaveSqlSessionTemplate(@Qualifier("slaveSqlSessionFactory")SqlSessionFactory sqlSessionFactory) {
ExecutorType executorType = this.properties.getExecutorType();
if (executorType != null) {
return new SqlSessionTemplate(sqlSessionFactory, executorType);
} else {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
}
}接下來還需要配置mapper相關的配置
- mapper配置
public class MapperScanConfig {
@Configuration
@MapperScan(basePackages = {"com.pack.base.mapper"}, sqlSessionTemplateRef = "masterSqlSessionTemplate")
static class MasterMapper {
}
@Configuration
@MapperScan(basePackages = {"com.pack.slave.mapper"}, sqlSessionTemplateRef = "slaveSqlSessionTemplate")
@ConditionalOnProperty(name = "multiple.ds.enabled", havingValue = "true")
static class SlaveMapper {
}
}到這里我們所有的相關配置就完成了，接下來我們只需建立對應的包即可。
圖片
MyBatis-conf.xml配置文件內容如下：
圖片
測試：
建立com.pack.domain包，然后新建類Users.java
@Entity
@Table(name = "T_USERS")
@Data
public class Users {
@Id
private Long id;
private String username ;
private String password ;
private String realName ;
private String phone ;
private String idNo ;
@Column(length=4000)
private String authority ;
@Column(columnDefinitinotallow="int default 0")
private Integer status = 0 ;
}建立包com.pack.slave.domain，然后新建類
@Entity
@Table(name = "T_PERSON")
@Data
public class Person{
@Id
private Long id;
private String name ;
private String email ;
}啟動服務器分別在不同的用戶下查看表是否建立，如果都建立了就表示成功。
完畢！！！
責任編輯：武曉燕
來源：
Spring全家桶實戰案例源碼
