Spring Boot 中配置多數(shù)據(jù)源
多數(shù)據(jù)源可以理解為多數(shù)據(jù)庫，甚至可以是多個不同類型的數(shù)據(jù)庫，比如一個是MySql，一個是Oracle。隨著項目的擴大，有時需要數(shù)據(jù)庫的拆分或者引入另一個數(shù)據(jù)庫，這時就需要配置多個數(shù)據(jù)源。
SpringBoot中使用多數(shù)據(jù)源還是比較簡單的，為了演示方便，我們在MySql中創(chuàng)建兩個數(shù)據(jù)庫：ds1、ds2，并在ds1數(shù)據(jù)庫中創(chuàng)建student表，在ds2數(shù)據(jù)庫中創(chuàng)建teacher表。數(shù)據(jù)庫腳本如下：
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`class` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('123456', 'zhangsan', '北京');
INSERT INTO `student` VALUES ('123457', 'lisi', '上海');
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`class` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('0000001', 'wangwu', '上海');
SET FOREIGN_KEY_CHECKS = 1;基于MyBatis的多數(shù)據(jù)源實現(xiàn)
首先創(chuàng)建一個MyBatis項目，項目結(jié)構(gòu)如下:
數(shù)據(jù)庫連接配置如下：
spring:
datasource:
ds1: #數(shù)據(jù)源1，默認數(shù)據(jù)源
url: jdbc:mysql://172.16.10.54:3306/ds1?serverTimezone=GMT&useSSL=false&useUnicode=true&characterEncoding=utf8
username: root
password: root
typ: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
filters: stat
maxActive: 2
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
ds2: #數(shù)據(jù)源2
url: jdbc:mysql://172.16.10.54:3306/ds2?serverTimezone=GMT&useSSL=false&useUnicode=true&characterEncoding=utf8
username: root
password: root
typ: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
filters: stat
maxActive: 2
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
mybatis:
mapper-locations: classpath:mapper/*.xml重寫SpringBoot的數(shù)據(jù)源配置：
package org.jeemp.db.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* @author JackRen
* @date 2021/3/3
*
**/
@Configuration
@MapperScan(basePackages = {"org.jeemp.db.mapper.ds1"}, sqlSessionFactoryRef = "sqlSessionFactory1")
public class Datasource1Configuration {
@Value("${mybatis.mapper-locations}")
private String mapperLocation;
@Value("${spring.datasource.ds1.url}")
private String jdbcUrl;
@Value("${spring.datasource.ds1.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.ds1.username}")
private String username;
@Value("${spring.datasource.ds1.password}")
private String password;
@Value("${spring.datasource.ds1.initialSize}")
private int initialSize;
@Value("${spring.datasource.ds1.minIdle}")
private int minIdle;
@Value("${spring.datasource.ds1.maxActive}")
private int maxActive;
@Bean(name = "dataSource1")
@Primary
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(jdbcUrl);
dataSource.setDriverClassName(driverClassName);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
return dataSource;
}
@Bean("sqlSessionFactory1")
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource1") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources(mapperLocation));
return sqlSessionFactoryBean.getObject();
}
@Bean("sqlSessionTemplate1")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
@Bean("transactionManager1")
public DataSourceTransactionManager transactionManager(@Qualifier("dataSource1")DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
package org.jeemp.db.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.io.IOException;
/**
* @author JackRen
* @date 2021/3/3
*
**/
@Configuration
@MapperScan(basePackages = {"org.jeemp.db.mapper.ds2"}, sqlSessionFactoryRef = "sqlSessionFactory2")
public class Datasource2Configuration {
@Value("${mybatis.mapper-locations}")
private String mapperLocation;
@Value("${spring.datasource.ds2.url}")
private String jdbcUrl;
@Value("${spring.datasource.ds2.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.ds2.username}")
private String username;
@Value("${spring.datasource.ds2.password}")
private String password;
@Value("${spring.datasource.ds2.initialSize}")
private int initialSize;
@Value("${spring.datasource.ds2.minIdle}")
private int minIdle;
@Value("${spring.datasource.ds2.maxActive}")
private int maxActive;
@Bean(name = "dataSource2")
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(jdbcUrl);
dataSource.setDriverClassName(driverClassName);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
return dataSource;
}
@Bean("sqlSessionFactory2")
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource2") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources(mapperLocation));
return sqlSessionFactoryBean.getObject();
}
@Bean("sqlSessionTemplate2")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
@Bean("transactionManager2")
public DataSourceTransactionManager transactionManager(@Qualifier("dataSource2") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
這里和單數(shù)據(jù)源不同的地方在于對 dataSource 、 sqlSessionFactory 、 sqlSessionTemplate、 transactionManager 都進行了單獨的配置。另外，數(shù)據(jù)源1和數(shù)據(jù)源2主要存在兩點不同：
@MapperScan 中的包掃描路徑不一樣，數(shù)據(jù)源1只掃描 org.jeemp.db.mapper.ds1 路徑下的 Mapper ，數(shù)據(jù)源2負責 org.jeemp.db.mapper.ds2下Mapper ，所以在前面創(chuàng)建的時候我們要把 StudentMapper 和 TeacherMapper 分開。因為在這里已經(jīng)配置了 @MapperScan ，所以在啟動類中必須不能在存在 @MapperScan 注解
數(shù)據(jù)源1中多一個 @Primary 注解，這是告訴Spring我們使用的默認數(shù)據(jù)源，也是多數(shù)據(jù)源項目中必不可少的。
測試下：
先查詢下學生
再查詢下老師
評論
圖片
表情