• 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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    然后每个数据库插入不同的数据，方便区分是否成功。

    # 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');


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    Springboot项目开始

    新建一个Springboot项目，可以使用start.spring.io或者idea创建项目。

    项目目录如下：
    在这里插入图片描述
    pom.xml 文件

            <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <scope>runtimescope>
        dependency>
        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
            <optional>trueoptional>
        dependency>
        <dependency>
            <groupId>com.baomidougroupId>
            <artifactId>mybatis-plus-boot-starterartifactId>
            <version>3.5.2version>
        dependency>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    数据源路由器

    AbstractRoutingDatasource 需要知道要路由到哪个实际 DataSource 的信息。该信息通常称为上下文。在示例中，我们将使用 DBTypeEnum 的概念作为我们的上下文，并具有以下实现：
    DBTypeEnum.java

    public enum DBTypeEnum {
    MAIN, CLIENT_A, CLIENT_B;
}

    • 1
    • 2
    • 3

    新建一个 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();
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    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();
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    配置文件 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;
    }

}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    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);

}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    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);
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    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);
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    UserMapper.xml

    
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>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    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 "添加成功";
    }


}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82

    运行项目，调用接口，就可以查看变化了。

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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
  • 相关阅读:
    vite + vue3 + js 搭建组件库 + 核心配置与使用
    Spring Cloud Alibaba Sentinel 初体验
    Android 中字符串空格占位
    Linux 学习的六个过程
    LSTM 浅析
    记一次iOS审核被拒条例4.1和2.3.7的通关经历
    初次接触Sharding-JDBC并实际应用
    mapreduce搭建
    C++学习day1
    智慧财务管家，记录分析收支明细，轻松掌握财务情况并随时打印保存！
  • 原文地址：https://blog.csdn.net/wm9028/article/details/126291229