动态数据源 Spring Boot整合MyBatis配置多数据源

Spring Boot 专栏：https://blog.csdn.net/dkbnull/category_9278145.html Spring Cloud 专栏：https://blog.csdn.net/dkbnull/category_9287932.html GitHub：https://github.com/dkbnull/SpringBootDemo Gitee：https://gitee.com/dkbnull/SpringBootDemo

多数据源即动态数据源，随着项目开发逐渐扩大，单个数据源、单一数据源已经无法满足需求项目的支撑需求。

或是单一数据库无法承载大数据量的访问，需使用多个数据库进行数据的读写分离；

或是某些特殊业务需求，需操作不同的数据库。

在 Spring Boot整合MyBatis连接数据库 文章中，展示了Spring Boot整合MyBatis连接数据库的方法，基于此，Spring Boot 整合MyBatis 配置多数据源。

0 开发环境

JDK：1.8Spring Boot：2.1.1.RELEASEMySQL：5.7.13

1 引入依赖

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.3.2

mysql

mysql-connector-java

8.0.28

runtime

org.projectlombok

lombok

1.18.30

provided

2 引入数据源

server:

port: 8090

spring:

datasource:

master:

jdbc-url: jdbc:mysql://127.0.0.1:3306/test_master?characterEncoding=utf8&serverTimezone=GMT%2B8

username: root

password: root

driver-class-name: com.mysql.cj.jdbc.Driver

slave:

jdbc-url: jdbc:mysql://127.0.0.1:3306/test_slave?characterEncoding=utf8&serverTimezone=GMT%2B8

username: root

password: root

driver-class-name: com.mysql.cj.jdbc.Driver

#

mybatis:

mapper-locations: classpath:mapper/**/*.xml

type-aliases-package: cn.wbnull.springbootdemo.entity

该配置方式下，需要操作的两个数据库的Mapper需放置在不同文件夹下，如下图所示：

3 配置master库的源连接

@Configuration

@MapperScan(basePackages = "cn.wbnull.springbootdemo.mapper.master", sqlSessionFactoryRef = "masterSqlSessionFactory")

public class MasterDataSourceConfig {

@Primary

@Bean("masterDataSource")

@ConfigurationProperties(prefix = "spring.datasource.master")

public DataSource masterDataSource() {

return DataSourceBuilder.create().build();

}

@Primary

@Bean("masterDataSourceTransactionManager")

public DataSourceTransactionManager masterDataSourceTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {

return new DataSourceTransactionManager(dataSource);

}

@Primary

@Bean("masterSqlSessionFactory")

public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {

SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();

sqlSessionFactory.setDataSource(dataSource);

Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/master/*.xml");

sqlSessionFactory.setMapperLocations(resources);

return sqlSessionFactory.getObject();

}

}

4 配置slave库的源连接

@Configuration

@MapperScan(basePackages = "cn.wbnull.springbootdemo.mapper.slave", sqlSessionFactoryRef = "slaveSqlSessionFactory")

public class SlaveDataSourceConfig {

@Bean("slaveDataSource")

@ConfigurationProperties(prefix = "spring.datasource.slave")

public DataSource slaveDataSource() {

return DataSourceBuilder.create().build();

}

@Bean("slaveDataSourceTransactionManager")

public DataSourceTransactionManager slaveDataSourceTransactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {

return new DataSourceTransactionManager(dataSource);

}

@Bean("slaveSqlSessionFactory")

public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {

SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();

sqlSessionFactory.setDataSource(dataSource);

Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/slave/*.xml");

sqlSessionFactory.setMapperLocations(resources);

return sqlSessionFactory.getObject();

}

}

5 测试

5.1 新建数据库表

CREATE SCHEMA `test_master` DEFAULT CHARACTER SET utf8mb4 ;

CREATE TABLE `test_master`.`user` (

`id` INT NOT NULL AUTO_INCREMENT,

`name` VARCHAR(45) NOT NULL,

PRIMARY KEY (`id`));

INSERT INTO `test_master`.`user` (`name`) VALUES ('张三');

INSERT INTO `test_master`.`user` (`name`) VALUES ('李四');

INSERT INTO `test_master`.`user` (`name`) VALUES ('王五');

INSERT INTO `test_master`.`user` (`name`) VALUES ('周六');

CREATE SCHEMA `test_slave` DEFAULT CHARACTER SET utf8mb4 ;

CREATE TABLE `test_slave`.`user_info` (

`id` INT NOT NULL AUTO_INCREMENT,

`userCode` VARCHAR(20) NOT NULL,

`userName` VARCHAR(45) NULL,

`password` VARCHAR(40) NOT NULL,

PRIMARY KEY (`id`));

INSERT INTO `test_slave`.`user_info` (`id`, `userCode`, `userName`, `password`) VALUES ('1', 'zhangsan', '张三三', 'zhangsan');

INSERT INTO `test_slave`.`user_info` (`id`, `userCode`, `userName`, `password`) VALUES ('2', 'lisi', '李四四', 'lisi');

INSERT INTO `test_slave`.`user_info` (`id`, `userCode`, `userName`, `password`) VALUES ('3', 'wangwu', '王五五', 'wangwu');

INSERT INTO `test_slave`.`user_info` (`id`, `userCode`, `userName`, `password`) VALUES ('4', 'zhouliu', '周六六', 'zhouliu');

5.2 新建实体类

@Data

public class User {

private int id;

private String name;

}

@Data

public class UserInfo {

private Integer id;

private String userCode;

private String userName;

private String password;

}

5.3 新建Mapper

@Repository

public interface UserMapper {

void add(@Param("user") User user);

List query();

void update(@Param("id") int id, @Param("name") String name);

void delete(@Param("id") int id);

}

@Repository

public interface UserInfoMapper {

List query();

}

5.4 新建映射文件

id, name

INSERT INTO user()

VALUES

(

#{user.id},

#{user.name}

)

SELECT * FROM user

UPDATE user SET name = '${name}' WHERE id = '${id}'

DELETE FROM user where id = '${id}'

id, userCode, userName, password

SELECT * FROM user_info

5.5 新建Service

@Service

public class UserService {

@Autowired

private UserMapper userMapper;

public String add(String name) {

User user = new User();

user.setName(name);

userMapper.add(user);

return "操作成功";

}

public List query() {

return userMapper.query();

}

public String update(int id, String name) {

userMapper.update(id, name);

return "操作成功";

}

public String delete(int id) {

userMapper.delete(id);

return "操作成功";

}

}

@Service

public class UserInfoService {

@Autowired

private UserInfoMapper userInfoMapper;

public List query() {

return userInfoMapper.query();

}

}

5.6 新建Controller

@RestController

@RequestMapping("user")

public class UserController {

@Autowired

public UserService userService;

@PostMapping(value = "add")

public String add(@RequestParam(value = "name") String name) {

return userService.add(name);

}

@PostMapping(value = "query")

public List query() {

return userService.query();

}

@PostMapping(value = "update")

public String update(@RequestParam(value = "id") int id, @RequestParam(value = "name") String name) {

return userService.update(id, name);

}

@PostMapping(value = "delete")

public String delete(@RequestParam(value = "id") int id) {

return userService.delete(id);

}

}

@Controller

@RequestMapping("userInfo")

public class UserInfoController {

@Autowired

public UserInfoService userInfoService;

@PostMapping(value = "query")

public List query() {

return userInfoService.query();

}

}

5.7 测试

使用Postman测试，输出结果如下

5.8.1 master select

5.8.2 master insert

数据库中插入成功

5.8.3 master update

数据库中更新成功

5.8.4 master delete

数据库中删除成功

5.8.5 slave select

截至这里，Spring Boot已经成功整合MyBatis多数据源，并连接上了数据库，且测试正常。

推荐链接