SpringBoot2.x+MybatisPlus+HikariCP多数据源动态配置
环境
SpringBoot 2.7.0
MybatisPlus 3.5.2
一、准备工作
准备三个数据库 testdb testdb1 testdb2
类似主服务器 、从服务器、从服务器
创建一个user表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` (`id`, `name`, `create_time`) VALUES (1, '小明', '2022-08-11 14:54:51');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
然后每个数据库插入不同的数据，方便区分是否成功。
# testdb
INSERT INTO `user` (`id`, `name`, `create_time`) VALUES (1, '小明', '2022-08-11 14:54:51');
# testdb1
INSERT INTO `user` (`id`, `name`, `create_time`) VALUES (1, '小明1', '2022-08-11 14:54:51');
# testdb2
INSERT INTO `user` (`id`, `name`, `create_time`) VALUES (1, '小明2', '2022-08-11 14:54:51');
Springboot项目开始
新建一个Springboot项目，可以使用start.spring.io或者idea创建项目。
项目目录如下：
1660215709194.jpg
pom.xml 文件
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
数据源路由器
AbstractRoutingDatasource 需要知道要路由到哪个实际 DataSource 的信息。该信息通常称为上下文。在示例中，我们将使用 DBTypeEnum 的概念作为我们的上下文，并具有以下实现：
DBTypeEnum.java
public enum DBTypeEnum {
MAIN, CLIENT_A, CLIENT_B;
}
新建一个 DBContextHolder.java
package com.wumeng.dynamicmultidatabase.config;
/**
* @author wumeng 2022/8/11 3:02 下午
*/
public class DBContextHolder {
private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
public static void setCurrentDb(DBTypeEnum dbType) {
contextHolder.set(dbType);
}
public static DBTypeEnum getCurrentDb() {
return contextHolder.get();
}
public static void clear() {
contextHolder.remove();
}
}
MultiRoutingDataSource.java
package com.wumeng.dynamicmultidatabase.config;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* @author wumeng 2022/8/11 3:03 下午
*/
public class MultiRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DBContextHolder.getCurrentDb();
}
}
配置文件 PersistenceConfiguration.java
package com.wumeng.dynamicmultidatabase.config;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* @author wumeng 2022/8/11 3:13 下午
*/
@Configuration
//@Component
public class PersistenceConfiguration {
// @Primary
@Bean(name = "mainDataSource")
@ConfigurationProperties("app.datasource.main")
public DataSource mainDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean(name = "clientADataSource")
@ConfigurationProperties("app.datasource.clienta")
public DataSource clientADataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean(name = "clientBDataSource")
@ConfigurationProperties("app.datasource.clientb")
public DataSource clientBDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Primary
@Bean(name = "multiRoutingDataSource")
public DataSource multiRoutingDataSource() {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DBTypeEnum.MAIN, mainDataSource());
targetDataSources.put(DBTypeEnum.CLIENT_A, clientADataSource());
targetDataSources.put(DBTypeEnum.CLIENT_B, clientBDataSource());
MultiRoutingDataSource multiRoutingDataSource = new MultiRoutingDataSource();
multiRoutingDataSource.setDefaultTargetDataSource(mainDataSource());
multiRoutingDataSource.setTargetDataSources(targetDataSources);
return multiRoutingDataSource;
}
}
application.properties
server.port=8081
# mapper
mybatis.mapper-locations=classpath:mapper/*Mapper.xml
# 数据库下划线自动转驼峰标示关闭
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
logging.level.com.wumeng.service.modules.report.dao=error
app.datasource.main.driver-class-name=com.mysql.cj.jdbc.Driver
app.datasource.main.jdbc-url=jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
app.datasource.main.username=root
app.datasource.main.password=12345678
app.datasource.clienta.driver-class-name=com.mysql.cj.jdbc.Driver
app.datasource.clienta.jdbc-url=jdbc:mysql://localhost:3306/testdb1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
app.datasource.clienta.username=root
app.datasource.clienta.password=12345678
app.datasource.clientb.driver-class-name=com.mysql.cj.jdbc.Driver
app.datasource.clientb.jdbc-url=jdbc:mysql://localhost:3306/testdb1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
app.datasource.clientb.username=root
app.datasource.clientb.password=12345678
Mybatis 实体类代码
/modules/user/dao/user.java
package com.wumeng.dynamicmultidatabase.modules.user.dao;
import lombok.Data;
import java.sql.Timestamp;
/**
* @author wumeng 2022/6/8 4:49 下午
*/
@Data
public class User {
private Integer id;
private String name;
private Timestamp create_time;
}
UserMapper.java
package com.wumeng.dynamicmultidatabase.modules.user.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wumeng.dynamicmultidatabase.modules.user.dao.User;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;
import java.sql.Timestamp;
import java.util.Map;
/**
* @author wumeng 2022/6/8 5:18 下午
*/
@Component
public interface UserMapper extends BaseMapper<User> {
@Insert("insert into User(id,name,create_time) values(#{id},#{name},#{create_time})")
int add(User User);
@Update("update User set name=#{name},create_time=#{create_time} where id=#{id}")
int update(User User);
//@Delete("delete from User where sno=#{sno}")
void deleteById(int id);
@Select("select * from User where id=#{id}")
@Results(id = "User",value= {
@Result(property = "id", column = "id", javaType = int.class),
@Result(property = "name", column = "name", javaType = String.class),
@Result(property = "create_time", column = "create_time", javaType = Timestamp.class)
})
User queryUserById(int id);
User queryUserByState(String state);
Map<String,Object> getDynamicUser(String tableName,String columns);
}
UserService.java
package com.wumeng.dynamicmultidatabase.modules.user.service;
import com.wumeng.dynamicmultidatabase.modules.user.dao.User;
/**
* @author wumeng 2022/6/8 5:23 下午
*/
public interface UserService {
int add(User user);
int update(User user);
void deleteById(int id);
User queryUserById(int id);
}
UserServiceImpl.java
package com.wumeng.dynamicmultidatabase.modules.user.service.impl;
import com.wumeng.dynamicmultidatabase.modules.user.dao.User;
import com.wumeng.dynamicmultidatabase.modules.user.mapper.UserMapper;
import com.wumeng.dynamicmultidatabase.modules.user.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public int add(User user) {
return userMapper.add(user);
}
@Override
public int update(User user) {
return userMapper.update(user);
}
@Override
public void deleteById(int id) {
userMapper.deleteById(id);
}
@Override
public User queryUserById(int id) {
return userMapper.queryUserById(id);
}
}
UserMapper.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.wumeng.dynamicmultidatabase.modules.user.mapper.UserMapper">
<select id="deleteById" parameterType="java.lang.String">
delete from student where id = #{id}
</select>
<select id="queryUserByState" resultType="java.util.List">
SELECT * FROM User
<where>
<if test="state != null">
and state = #{state}
</if>
</where>
</select>
<select id="getDynamicUser" resultType="java.util.Map" parameterType="java.lang.String" statementType="STATEMENT">
select
${columns}
from ${tableName}
</select>
</mapper>
UserController.java接口
package com.wumeng.dynamicmultidatabase.modules.user.controller;
import com.wumeng.dynamicmultidatabase.config.DBContextHolder;
import com.wumeng.dynamicmultidatabase.config.DBTypeEnum;
import com.wumeng.dynamicmultidatabase.modules.user.dao.User;
import com.wumeng.dynamicmultidatabase.modules.user.service.UserService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.sql.Timestamp;
/**
* @author wumeng 2022/6/8 4:35 下午
*/
@RestController
@RequestMapping("/user")
public class UserController {
private Logger logger = LoggerFactory.getLogger(this.getClass());
@Autowired
private UserService userService;
// query_user?id=1 -> main DB
// query_user?id=1&client=client-a -> Client A DB
// query_user?id=1&client=client-b -> Client B DB
@RequestMapping(value = "/query_user", method = RequestMethod.GET)
public User queryUserById(int id, String client) {
switch (client) {
case "client-a":
DBContextHolder.setCurrentDb(DBTypeEnum.CLIENT_A);
break;
case "client-b":
DBContextHolder.setCurrentDb(DBTypeEnum.CLIENT_B);
break;
}
logger.info("查询id = " + id + " client = " + client);
return this.userService.queryUserById(id);
}
@RequestMapping(value = "/deleteUser", method = RequestMethod.GET)
public String deleteUserById(int id, String client) {
switch (client) {
case "client-a":
DBContextHolder.setCurrentDb(DBTypeEnum.CLIENT_A);
break;
case "client-b":
DBContextHolder.setCurrentDb(DBTypeEnum.CLIENT_B);
break;
}
this.userService.deleteById(id);
logger.info("删除id" + id);
return "删除成功";
}
@GetMapping("/addUser/{id}")
public String addUser(@PathVariable(value = "id") Integer id, @RequestParam(value = "name") String name, @RequestParam(value = "client") String client) {
switch (client) {
case "client-a":
DBContextHolder.setCurrentDb(DBTypeEnum.CLIENT_A);
break;
case "client-b":
DBContextHolder.setCurrentDb(DBTypeEnum.CLIENT_B);
break;
}
User user = new User();
user.setName(name);
user.setCreate_time(new Timestamp(System.currentTimeMillis()));
int addCode = this.userService.add(user);
logger.info("id " + id + "add code " + addCode);
return "添加成功";
}
}
运行项目，调用接口，就可以查看变化了。
http://localhost:8081/user/query_user?id=1&client=
{"id":1,"name":"小明","create_time":"2022-08-11T14:54:51.000+00:00"}
http://localhost:8081/user/query_user?id=1&client=client-a
{"id":1,"name":"小明1","create_time":"2022-08-11T14:54:51.000+00:00"}
http://localhost:8081/user/query_user?id=1&client=client-b
{"id":1,"name":"小明2","create_time":"2022-08-11T14:54:51.000+00:00"}
