SpringBoot 动态数据源

SpringBoot 实现动态数据源切换

Spring Boot + Mybatis Plus + Druid + MySQL 实现动态数据源切换及动态 SQL 语句执行。

项目默认加载 application.yml 中配置的数据源，只有在调用数据源切换时创建数据连接。

Druid 实现动态数据源切换

相关依赖

 <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
</dependency>

application.yml Druid 配置

spring:
  #Druid 连接池通用配置
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/demo?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&useSSL=false
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      # 下面为连接池的补充设置，应用到上面所有数据源中
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      min-evictable-idle-time-millis: 300000
      # sql 校验
      validation-query: select count(1) from sys.objects Where type='U' And type_desc='USER_TABLE'
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      pool-prepared-statements: true
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat # wall 若开启 wall，会把 if 中的 and 判断为注入进行拦截
      use-global-data-source-stat: true
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      # 指定当连接超过废弃超时时间时，是否立刻删除该连接
      remove-abandoned: true
      # 指定连接应该被废弃的时间
      remove-abandoned-timeout: 60000
      # 是否追踪废弃statement或连接，默认为: false
      log-abandoned: false

Druid 配置

package com.demo.utils.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.servlet.Filter;
import javax.servlet.Servlet;
import java.util.HashMap;
import java.util.Map;

/**
 * @ClassName： DruidConfig.java
 * @Description： Druid配置
 * @Author： tanyp
 * @Date： 2022/2/18 10:29
 **/
@Configuration
public class DruidConfig {

    @Value("${spring.datasource.type}")
    private String db_type;

//    @Value("${spring.datasource.driver-class-name}")
//    private String db_driver_name;

    @Value("${spring.datasource.url}")
    private String db_url;

    @Value("${spring.datasource.username}")
    private String db_user;

    @Value("${spring.datasource.password}")
    private String db_pwd;

    // 连接池初始化大小
    @Value("${spring.datasource.druid.initial-size}")
    private int initialSize;

    // 连接池最小值
    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;

    // 连接池最大值
    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;

    // 配置获取连接等待超时的时间
    @Value("${spring.datasource.druid.max-wait}")
    private int maxWait;

    // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private int timeBetweenEvictionRunsMillis;

    // 配置一个连接在池中最小生存的时间，单位是毫秒
    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private int minEvictableIdleTimeMillis;

    // 用来验证数据库连接的查询语句,这个查询语句必须是至少返回一条数据的SELECT语句
    @Value("${spring.datasource.druid.validation-query}")
    private String validationQuery;

    // 检测连接是否有效
    @Value("${spring.datasource.druid.test-while-idle}")
    private boolean testWhileIdle;

    // 申请连接时执行validationQuery检测连接是否有效。做了这个配置会降低性能。
    @Value("${spring.datasource.druid.test-on-borrow}")
    private boolean testOnBorrow;

    // 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
    @Value("${spring.datasource.druid.test-on-return}")
    private boolean testOnReturn;

    // 是否缓存preparedStatement，也就是PSCache。
    @Value("${spring.datasource.druid.pool-prepared-statements}")
    private boolean poolPreparedStatements;

    // 指定每个连接上PSCache的大小。
    @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
    private int maxPoolPreparedStatementPerConnectionSize;

    // 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    @Value("${spring.datasource.druid.filters}")
    private String filters;

    // 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    @Value("${spring.datasource.druid.connect-properties}")
    private String connectionProperties;

    // 指定当连接超过废弃超时时间时，是否立刻删除该连接
    @Value("${spring.datasource.druid.remove-abandoned}")
    private boolean removeAbandoned;

    // 指定连接应该被废弃的时间
    @Value("${spring.datasource.druid.remove-abandoned-timeout}")
    private int removeAbandonedTimeout;

    // 使用DBCP connection pool，是否追踪废弃statement或连接，默认为: false
    @Value("${spring.datasource.druid.log-abandoned}")
    private boolean logAbandoned;

    @Bean
    public DynamicDataSource druidDataSource() {
        Map<Object, Object> map = new HashMap<>();
        DynamicDataSource dynamicDataSource = DynamicDataSource.getInstance();

        DruidDataSource defaultDataSource = new DruidDataSource();
//        defaultDataSource.setDriverClassName(db_driver_name);
        defaultDataSource.setUrl(db_url);
        defaultDataSource.setUsername(db_user);
        defaultDataSource.setPassword(db_pwd);
        defaultDataSource.setInitialSize(initialSize);
        defaultDataSource.setMinIdle(minIdle);
        defaultDataSource.setMaxActive(maxActive);
        defaultDataSource.setMaxWait(maxWait);
        defaultDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        defaultDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        defaultDataSource.setValidationQuery(validationQuery);
        defaultDataSource.setTestWhileIdle(testWhileIdle);
        defaultDataSource.setTestOnBorrow(testOnBorrow);
        defaultDataSource.setTestOnReturn(testOnReturn);
        defaultDataSource.setPoolPreparedStatements(poolPreparedStatements);
        defaultDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        defaultDataSource.setRemoveAbandoned(removeAbandoned);
        defaultDataSource.setRemoveAbandonedTimeout(removeAbandonedTimeout);
        defaultDataSource.setLogAbandoned(logAbandoned);
        dynamicDataSource.setDefaultTargetDataSource(defaultDataSource);

        map.put("default", defaultDataSource);
        dynamicDataSource.setTargetDataSources(map);
        dynamicDataSource.setDefaultTargetDataSource(defaultDataSource);
        return dynamicDataSource;
    }

    @Bean
    public ServletRegistrationBean<Servlet> druid() {
        // 现在要进行druid监控的配置处理操作
        ServletRegistrationBean<Servlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        // 白名单,多个用逗号分割， 如果allow没有配置或者为空，则允许所有访问
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        // 黑名单,多个用逗号分割 (共同存在时，deny优先于allow)
        //servletRegistrationBean.addInitParameter("deny", "127.0.0.1");
        // 控制台管理用户名
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        // 控制台管理密码
        servletRegistrationBean.addInitParameter("loginPassword", "admin");
        // 是否可以重置数据源，禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean<Filter> filterRegistrationBean() {
        FilterRegistrationBean<Filter> filterRegistrationBean = new FilterRegistrationBean<>();
        filterRegistrationBean.setFilter(new WebStatFilter());
        // 所有请求进行监控处理
        filterRegistrationBean.addUrlPatterns("/*");
        // 添加不需要忽略的格式信息
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.css,/druid/*");
        return filterRegistrationBean;
    }

}

数据源上下文

package com.demo.utils.datasource;

/**
 * @ClassName： DataSourceContextHolder.java
 * @Description： 数据源上下文
 * @Author： tanyp
 * @Date： 2022/2/18 10:04
 **/
public class DataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    /**
     * @MonthName： setDBType
     * @Description： 设置当前线程持有的数据源
     * @Author： tanyp
     * @Date： 2022/2/18 10:07
     * @Param： [dbType]
     * @return： void
     **/
    public static synchronized void setDBType(String dbType) {
        contextHolder.set(dbType);
    }

    /**
     * @MonthName： getDBType
     * @Description： 获取当前线程持有的数据源
     * @Author： tanyp
     * @Date： 2022/2/18 10:07
     * @Param： []
     * @return： java.lang.String
     **/
    public static String getDBType() {
        return contextHolder.get();
    }

    /**
     * @MonthName： clearDBType
     * @Description： 清除当前线程持有的数据源
     * @Author： tanyp
     * @Date： 2022/2/18 10:07
     * @Param： []
     * @return： void
     **/
    public static void clearDBType() {
        contextHolder.remove();
    }

}

数据源信息

package com.demo.utils.datasource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import java.util.HashMap;
import java.util.Map;

/**
 * @ClassName： DynamicDataSource.java
 * @Description： 数据源信息
 * @Author： tanyp
 * @Date： 2022/2/18 10:26
 **/
public class DynamicDataSource extends AbstractRoutingDataSource {

    private static DynamicDataSource instance;

    private static byte[] lock = new byte[0];

    private static Map<Object, Object> dataSourceMap = new HashMap<>();

    @Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        super.setTargetDataSources(targetDataSources);
        dataSourceMap.putAll(targetDataSources);
        super.afterPropertiesSet();
    }

    public Map<Object, Object> getDataSourceMap() {
        return dataSourceMap;
    }

    public static synchronized DynamicDataSource getInstance() {
        if (instance == null) {
            synchronized (lock) {
                if (instance == null) {
                    instance = new DynamicDataSource();
                }
            }
        }
        return instance;
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDBType();
    }

}

切换数据源

以数据库 ip + 端口 + 数据库名作为 key 和数据库连接的映射关系。

package com.demo.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.demo.utils.datasource.DataSourceContextHolder;
import com.demo.utils.datasource.DynamicDataSource;
import lombok.extern.slf4j.Slf4j;

import java.util.Map;
import java.util.Objects;

/**
 * @ClassName： DruidDataSourceUtil.java
 * @Description： 用于查找并切换数据源
 * @Author： tanyp
 * @Date： 2022/2/18 10:34
 **/
@Slf4j
public class DruidDataSourceUtil {

    /**
     * @MonthName： addOrChangeDataSource
     * @Description： 切换数据源
     * @Author： tanyp
     * @Date： 2022/2/18 10:38
     * @Param： dbip：IP地址
     * dbport：端口号
     * dbname：数据库名称
     * dbuser：用户名称
     * dbpwd：密码
     * @return： void
     **/
    public static void addOrChangeDataSource(String dbip, String dbport, String dbname, String dbuser, String dbpwd) {
        try {
            DataSourceContextHolder.setDBType("default");

            // 数据库连接key：ip + 端口 + 数据库名
            String key = "db" + dbip + dbport + dbname;

            // 创建动态数据源
            Map<Object, Object> dataSourceMap = DynamicDataSource.getInstance().getDataSourceMap();
            if (!dataSourceMap.containsKey(key + "master") && Objects.nonNull(key)) {
                String url = "jdbc:mysql://" + dbip + ":" + dbport + "/" + dbname + "?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&useSSL=false";
                log.info("插入新数据库连接信息为：{}", url);

                DruidDataSource dynamicDataSource = new DruidDataSource();
                // dynamicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
                dynamicDataSource.setUsername(dbuser);
                dynamicDataSource.setUrl(url);
                dynamicDataSource.setPassword(dbpwd);
                dynamicDataSource.setInitialSize(50);
                dynamicDataSource.setMinIdle(5);
                dynamicDataSource.setMaxActive(1000);
                dynamicDataSource.setMaxWait(500); // 如果失败，当前的请求可以返回
                dynamicDataSource.setTimeBetweenEvictionRunsMillis(60000);
                dynamicDataSource.setMinEvictableIdleTimeMillis(300000);
                dynamicDataSource.setValidationQuery("SELECT 1 FROM DUAL");
                dynamicDataSource.setTestWhileIdle(true);
                dynamicDataSource.setTestOnBorrow(false);
                dynamicDataSource.setTestOnReturn(false);
                dynamicDataSource.setPoolPreparedStatements(true);
                dynamicDataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
                dynamicDataSource.setRemoveAbandoned(true);
                dynamicDataSource.setRemoveAbandonedTimeout(180);
                dynamicDataSource.setLogAbandoned(true);
                dynamicDataSource.setConnectionErrorRetryAttempts(0); // 失败后重连的次数
                dynamicDataSource.setBreakAfterAcquireFailure(true); // 请求失败之后中断

                dataSourceMap.put(key + "master", dynamicDataSource);

                DynamicDataSource.getInstance().setTargetDataSources(dataSourceMap);
                // 切换为动态数据源实例
                DataSourceContextHolder.setDBType(key + "master");
            } else {
                // 切换为动态数据源实例
                DataSourceContextHolder.setDBType(key + "master");
            }
        } catch (Exception e) {
            log.error("=====创建据库连接异常：{}", e);
        }
    }

}

以上动态数据源加载及切换已完成。

使用 MyBatis Plus 动态执行 SQL 语句

加载动态数据源执行 SQL （增、删、改、查）

package com.demo.service.impl;

import com.demo.constants.Constants;
import com.demo.mapper.DynamicSqlMapper;
import com.demo.service.DynamicDataSourceService;
import com.demo.utils.DataUtils;
import com.demo.utils.DruidDataSourceUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.Map;

/**
 * @ClassName： DynamicDataSourceServiceImpl.java
 * @Description： 动态数据源
 * @Author： tanyp
 * @Date： 2022/2/18 10:43
 **/
@Slf4j
@Service("dynamicDataSourceService")
public class DynamicDataSourceServiceImpl implements DynamicDataSourceService {

    @Autowired
    private DynamicSqlMapper dynamicSqlMapper;

    /**
     * @MonthName： dynamicExecutive
     * @Description： 加载动态数据源执行SQL
     * @Author： tanyp
     * @Date： 2022/2/28 10:46
     * @Param： {
     * "dbip":"IP地址",
     * "dbport":"端口号",
     * "dbname":"数据库名称",
     * "dbuser":"用户名称",
     * "dbpwd":"密码",
     * "type":"执行类型：SELECT、INSERT、UPDATE、DELETE",
     * "paramSQL":"需要执行的SQL",
     * "param":{} // SQL中的参数
     * }
     * @return： java.util.Map<java.lang.String, java.lang.Object>
     **/
    @Override
    public Map<String, Object> dynamicExecutive(Map<String, Object> params) {
        Map<String, Object> result = null;
        try {
            DruidDataSourceUtil.addOrChangeDataSource(
                    String.valueOf(params.get("dbip")),
                    String.valueOf(params.get("dbport")),
                    String.valueOf(params.get("dbname")),
                    String.valueOf(params.get("dbuser")),
                    String.valueOf(params.get("dbpwd"))
            );
        } catch (Exception e) {
            log.error("=====创建据库连接异常:{}", e);
            result.put("data", "创建据库连接异常，请检查连接信息是否有误！");
        }

        try {
            // 执行动态SQL
            Object data = null;
            String type = String.valueOf(params.get("type"));
            String paramSQL = String.valueOf(params.get("paramSQL"));
            Map<String, Object> param = (HashMap) params.get("param");

            // 参数替换
            String sql = DataUtils.strRreplace(paramSQL, param);

            log.info("======请求SQL语句:{}======", sql);

            switch (type) {
                case Constants.SELECT:
                    data = dynamicSqlMapper.dynamicsSelect(sql);
                    break;
                case Constants.INSERT:
                    data = dynamicSqlMapper.dynamicsInsert(sql);
                    break;
                case Constants.UPDATE:
                    data = dynamicSqlMapper.dynamicsUpdate(sql);
                    break;
                case Constants.DELETE:
                    data = dynamicSqlMapper.dynamicsDelete(sql);
                    break;
                default:
                    data = "请求参数【type】有误，请核查！";
                    break;
            }

            result = new HashMap<>();
            result.put("data", data);
        } catch (Exception e) {
            log.error("=====执行SQL异常:{}", e);
            result.put("data", "执行SQL异常，请检查SQL语句是否有误！");
        }
        return result;
    }

}

动态 SQL 执行器

package com.demo.mapper;

import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @ClassName： DynamicSqlMapper.java
 * @Description： 动态SQL执行器
 * @Author： tanyp
 * @Date： 2022/2/28 10:21
 **/
@Mapper
public interface DynamicSqlMapper {

    @Select({"${sql}"})
    @ResultType(Object.class)
    List<Map<String, Object>> dynamicsSelect(@Param("sql") String sql);

    @Insert({"${sql}"})
    @ResultType(Integer.class)
    Integer dynamicsInsert(@Param("sql") String sql);

    @Update({"${sql}"})
    @ResultType(Integer.class)
    Integer dynamicsUpdate(@Param("sql") String sql);

    @Delete({"${sql}"})
    @ResultType(Integer.class)
    Integer dynamicsDelete(@Param("sql") String sql);

}

SQL 占位符处理

package com.demo.utils;

import lombok.extern.slf4j.Slf4j;

import java.util.Map;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @ClassName： DataUtils.java
 * @Description： 数据处理
 * @Author： tanyp
 * @Date： 2022/2/28 9:21
 **/
@Slf4j
public class DataUtils {

    private static final Pattern pattern = Pattern.compile("\\#\\{(.*?)\\}");
    private static Matcher matcher;

    /**
     * @MonthName： strRreplace
     * @Description： 字符串站位处理
     * @Author： tanyp
     * @Date： 2022/2/28 9:21
     * @Param： [content, param]
     * @return： java.lang.String
     **/
    public static String strRreplace(String content, Map<String, Object> param) {
        if (Objects.isNull(param)) {
            return null;
        }
        try {
            matcher = pattern.matcher(content);
            while (matcher.find()) {
                String key = matcher.group();
                String keyclone = key.substring(2, key.length() - 1).trim();
                boolean containsKey = param.containsKey(keyclone);
                if (containsKey && Objects.nonNull(param.get(keyclone))) {
                    String value = "'" + param.get(keyclone) + "'";
                    content = content.replace(key, value);
                }
            }
            return content;
        } catch (Exception e) {
            log.error("字符串站位处理：{}", e);
            return null;
        }
    }

}

测试

POST 请求接口

http://127.0.0.1:8001/dynamicExecutive

请求参数

{
    "dbip":"127.0.0.1",
    "dbport":"3306",
    "dbname":"demo",
    "dbuser":"root",
    "dbpwd":"root",
    "type":"SELECT",
    "paramSQL":"SELECT id, code, name, path, message, status, classify, params, icon, update_time, create_time FROM component where id = #{id}",
    "param":{
        "id":"611fb3e553371b9d42f8583391cc8478"
        }
    }

正常返回值

{
  "code": 200,
  "message": "操作成功",
  "result": {
    "code": 200,
    "message": "操作成功！",
    "result": {
      "data": [
        {
          "path": "127.0.0.1",
          "classify": "8ab3f21e1607a0374fb2d82f7fcaee98",
          "update_time": "2022-03-08 17:59:11",
          "code": "dynamicDataSourceService",
          "create_time": "2022-03-07 14:51:15",
          "name": "动态数据源",
          "icon": "Rank",
          "id": "611fb3e553371b9d42f8583391cc8478",
          "message": "加载动态数据源执行SQL",
          "status": 0
        }
      ]
    },
    "dateTime": "2022-03-11T09:56:31.87"
  }
}