SpringBoot和Mybatis配置多数据源连接多个数据库
原文地址
目前业界操作数据库的框架一般是 Mybatis,但在很多业务场景下,我们需要在一个工程里配置多个数据源来实现业务逻辑。在SpringBoot中也可以实现多数据源并配合Mybatis框架编写xml文件来执行SQL。在SpringBoot中,配置多数据源的方式十分便捷。
pom.xml
<!-- Spring Boot Mybatis 依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.2.0</version>
</dependency>
<!-- MySQL 连接驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
<!-- Druid 数据连接池依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.18</version>
</dependency>
application.properties 配置两个数据源配置
# 多数据源配置
# #以下为新增
# druid:
# # 指明是否在从池中取出连接前进行检验,如果检验失败, 则从池中去除连接并尝试取出另一个,
# #注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串
# test-on-borrow: false
# # 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.
# #注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串
# test-while-idle: true
# # 指明是否在归还到池中前进行检验,注意: 设置为true后如果要生效,
# #validationQuery参数必须设置为非空字符串
# test-on-return: false
# # SQL查询,用来验证从连接池取出的连接,在将连接返回给调用者之前.
# #如果指定,则查询必须是一个SQL SELECT并且必须返回至少一行记录
# validation-query: select 1
spring:
datasource:
druid:
druid:
est-on-borrow: true
test-while-idle: true
type: com.zaxxer.hikari.HikariDataSource
hikari:
auto-commit: false
connection-timeout: 30000
idle-timeout: 25000
login-timeout: 5
max-lifetime: 30000
read-only: false
validation-timeout: 3000
maximum-pool-size: 15
minimum-idle: 10
data-source-properties:
cachePrepStmts: true
prepStmtCacheSize: 250
prepStmtCacheSqlLimit: 2048
useServerPrepStmts: true
useLocalSessionState: true
rewriteBatchedStatements: true
cacheResultSetMetadata: true
cacheServerConfiguration: true
elideSetAutoCommits: true
maintainTimeStats: false
master:
datasource:
url: jdbc:mysql://xxxxxx:3306/xxxxx?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: xxx
password: xxx
driverClassName: com.mysql.cj.jdbc.Driver
second:
datasource:
url: jdbc:mysql://xxxx:3306/xxxxxx?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8&
username: xxxxx
password: xxxx
driverClassName: com.mysql.cj.jdbc.Driver
# mybatis:
# mapper-locations: classpath:mapper/*/*.xml
数据源配置
- 多数据源配置的时候注意,必须要有一个主数据源,即 MasterDataSourceConfig 配置
– @Primary 标志这个 Bean 如果在多个同类 Bean 候选时,该 Bean
优先被考虑。「多数据源配置的时候注意,必须要有一个主数据源,用 @Primary 标志该 Bean
– @MapperScan 扫描 Mapper 接口并容器管理,包路径精确到 master,为了和下面 cluster 数据源做到精确区分
– @Value 获取全局配置文件 application.properties 的 kv
配置,并自动装配sqlSessionFactoryRef 表示定义了 key ,表示一个唯一 SqlSessionFactory 实例
MasterDataSourceConfig的代码:
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
// 精确到 master 目录,以便跟其他数据源隔离
static final String PACKAGE = "org.spring.springboot.dao.master";
static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
@Value("${master.datasource.url}")
private String url;
@Value("${master.datasource.username}")
private String user;
@Value("${master.datasource.password}")
private String password;
@Value("${master.datasource.driverClassName}")
private String driverClass;
@Bean(name = "masterDataSource")
@Primary
public DataSource masterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "masterTransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(masterDataSource());
}
@Bean(name = "masterSqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(masterDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(MasterDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
第二个数据源SecondDataSourceConfig的配置如下:
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfig {
// 精确到 cluster 目录,以便跟其他数据源隔离
static final String PACKAGE = "com.springboot.dao.second";
static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";
@Value("${second.datasource.url}")
private String url;
@Value("${second.datasource.username}")
private String user;
@Value("${second.datasource.password}")
private String password;
@Value("${second.datasource.driverClassName}")
private String driverClass;
@Bean(name = "secondDataSource")
public DataSource clusterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "secondTransactionManager")
public DataSourceTransactionManager clusterTransactionManager() {
return new DataSourceTransactionManager(clusterDataSource());
}
@Bean(name = "secondSqlSessionFactory")
public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("secondDataSource") DataSource clusterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(clusterDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(SecondDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
到此,不同的数据源配置就已经完成,剩下的只需要将将Mybatis的xml文件和DAO层的接口写好,并在Service层注入,直接使用就行。
Service层的代码:
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Autowired
private SchoolDao schoolDao;
public UserVo getUser(Long id) {
UserVo userVo = userDao.findById(id);
SchoolVo schoolVo = schoolDao.findByName("清华");
userVo.setSchoolVo(schoolVo);
return userVo;
}
}
Mybatis的xml文件UserDao.xml和SchoolDao.xml的内容:
UserDao.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springboot.dao.master.UserDao">
<resultMap id="BaseResultMap" type="com.springboot.vo.UserVo">
<result column="id" property="id" />
<result column="user_name" property="userName" />
</resultMap>
<sql id="Base_Column_List">
id, user_name
</sql>
<select id="findById" resultMap="BaseResultMap" parameterType="java.lang.Long">
select
<include refid="Base_Column_List" />
from
user
where
id = #{id}
</select>
</mapper>
SchoolDao.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springboot.dao.second.SchoolDao">
<resultMap id="BaseResultMap" type="com.springboot.vo.SchoolVo">
<result column="id" property="id" />
<result column="school_name" property="schoolName" />
<result column="school_describe" property="schoolDescribe" />
</resultMap>
<sql id="Base_Column_List">
id, school_name, school_describe
</sql>
<select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">
select
<include refid="Base_Column_List" />
from
school
where
school_name = #{schoolName}
</select>
</mapper>
TestController
@Controller
@RequestMapping(value = "/api/v1/test")
public class TestController {
@Autowired
private UserService userService;
@RequestMapping(value = "/getUser")
@ResponseBody
public UserVo getUser(@RequestParam(value = "id") Long id) {
return userService.getUser(id);
}
}
创建数据库和表SQL:
CREATE DATABASE springbootdb;
CREATE DATABASE springbootdb_second;
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`user_name` varchar(25) DEFAULT NULL COMMENT '用户名称',
`description` varchar(25) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
CREATE TABLE `school` (
`id` bigint(20) NOT NULL,
`school_name` varchar(64) DEFAULT NULL COMMENT '学校名',
`school_describe` varchar(128) DEFAULT NULL COMMENT '学校描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into `springbootdb`.`user` ( `user_name`, `description`) values ( 'shuai', 'so handsome');
insert into `springbootdb_second`.`school` ( `id`, `school_name`, `school_describe`) values ( '1', '清华', '自强不息,厚德载物');
创建好数据后,整个工程的机构如下:
启动程序,在浏览器输入:http://localhost:8080/api/v1/test/getUser?id=1即可返回结果。