SpringBoot2.x+MybatisPlus+HikariCP多数据源动态配置

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创建项目。

项目目录如下：

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"}