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 配置两个数据源配置
# master 数据源配置
master.datasource.url=jdbc:mysql://localhost:3306/springbootdb?useUnicode=true&characterEncoding=utf8
master.datasource.username=root
master.datasource.password=321
master.datasource.driverClassName=com.mysql.jdbc.Driver
# second 数据源配置
second.datasource.url=jdbc:mysql://localhost:3306/springbootdb_second?useUnicode=true&characterEncoding=utf8
second.datasource.username=root
second.datasource.password=321
second.datasource.driverClassName=com.mysql.jdbc.Driver
- 数据源配置
多数据源配置的时候注意,必须要有一个主数据源,即
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即可返回结果。
github地址:Spring Boot 教程、技术栈、示例代码
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。