spring mybits plus动态切换数据库

以前的做法可能是，在我们配置文件定义多个datasource，然后在dao中根据增、删、改、查去选择不同的datasource。这样做的话，可能就需要我们在代码里面硬编码选择数据源的过程，这样做显然不够友好，会产生很多冗余，重复的代码。

那有没有可以让我们的程序自动去选择路由数据源，而我代码中还是像以前那样，只关心业务逻辑，至于怎么选，选什么全都交给框架去实现。这样做的话，是不是瞬间感觉到代码清晰了很多了。那下面我们就来一步一步的实现看看。

用到的技术

spring提供的 AbstractRoutingDataSource 类。该类就是在多数据源下，会根据determineCurrentLookupKey() 这个方法返回的路由key，在动态选择数据源
spring AOP。需要在执行sql的方法前拦截请求，把该线程请求的方法，类名，参数等信息设置到线程局部变量（ThreadLocal）里面，然后determineCurrentLookupKey这个方法就可以根据线程的数据动态的选择数据源了。

代码

配置默认数据库(主数据库), FilterThreadLocalContext 拦截所有请求, 截取请求的域名,设置线程变量ThreadLocal 查找切换那个数据库.

DBConfigure.java

package com.example.springmybitsdynamicdatasource.config.db;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.MybatisXMLLanguageDriver;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.transaction.SpringManagedTransactionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

import javax.sql.DataSource;

@Configuration
public class DBConfigure extends WebMvcConfigurerAdapter {

    @Autowired
    private Environment environment;

    @Autowired
    private RoutingDataSource routingDataSource;

    @Bean("dataSource")
    @Primary
    @ConfigurationProperties("spring.datasource")
    public DataSource dataSource() {
        /**
         * 配置默认数据库
         */
        HikariDataSource dataSource = new HikariDataSource();
        String url = environment.getProperty("spring.datasource.url");
        dataSource.setJdbcUrl(url);
        return dataSource;
    }

    /**
     * mybatis-plus sqlSessionFactory config
     *
     * @param ds
     * @param globalConfig
     * @return
     * @throws Exception
     */
    @Bean(name = "sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource ds) throws Exception {
        MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
        factoryBean.setDataSource(routingDataSource);

        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        factoryBean.setConfiguration(configuration);
        factoryBean.setTransactionFactory(new SpringManagedTransactionFactory());

        return factoryBean.getObject();
    }

    /**
     * 请求拦截器,  这个会拦截所有的请求
     * @return
     */
    @Bean
    public FilterRegistrationBean FilterThreadLocalContext() {
        FilterRegistrationBean registration = new FilterRegistrationBean();
        registration.setFilter(new FilterThreadLocalContext());
        registration.addUrlPatterns("/*");
        registration.setName("FilterThreadLocalContext");
        registration.setOrder(1);
        return registration;
    }
}

FilterThreadLocalContext.java

package com.example.springmybitsdynamicdatasource.config.db;

import javax.servlet.*;
import java.io.IOException;

public class FilterThreadLocalContext implements Filter {
    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        // 拦截请求, 选择数据库
        String domain = servletRequest.getServerName();


        ThreadLocalContext.get().setDataSource(domain);
        filterChain.doFilter(servletRequest, servletResponse);
    }
}

MultipleDataSource.java

程序启动查询数据库数据源, 添加到数据源里面.

package com.example.springmybitsdynamicdatasource.config.db;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.example.springmybitsdynamicdatasource.SpringContext;
import com.example.springmybitsdynamicdatasource.dao.DbDatabaseDao;
import com.example.springmybitsdynamicdatasource.dao.StudentDao;
import com.example.springmybitsdynamicdatasource.entity.DbDatabaseEntity;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

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

@Component
public class MultipleDataSource implements CommandLineRunner {
    private RoutingDataSource routingDataSource;

    public void loadDatasource() {
        // 往连接池注入数据库
        DbDatabaseDao dbDatabaseDao = SpringContext.getBean(DbDatabaseDao.class);
        routingDataSource = SpringContext.getBean(RoutingDataSource.class);

        // 数据源
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        List<DbDatabaseEntity> dbs = dbDatabaseDao.selectList(new LambdaQueryWrapper<>());

        // 把链接添加到连接池
        for (DbDatabaseEntity db : dbs) {
            HikariDataSource newDb  = this.initDataSource(db.getUrl(),db.getDbUser(),db.getDbPwd(),db.getDriverClass());
            targetDataSources.put(db.getDomain(), newDb);
        }

        routingDataSource.setTargetDataSources(targetDataSources);// 设置数据源路由
        routingDataSource.afterPropertiesSet();
    }


    private HikariDataSource initDataSource(String url, String dbUser, String dbPwd, String driverClass) {
        // 初始化数据源
        HikariDataSource new_ds = new HikariDataSource();
        new_ds.setJdbcUrl(url);
        new_ds.setUsername(dbUser);
        new_ds.setPassword(dbPwd);
        new_ds.setDriverClassName(driverClass);
        return new_ds;
    }


    @Override
    public void run(String... args) throws Exception {
        this.loadDatasource();
    }
}

RoutingDataSource.java

最重要的, 查询sql前调用 determineCurrentLookupKey,切换要执行的数据库.

package com.example.springmybitsdynamicdatasource.config.db;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Component("routingDataSource")
public class RoutingDataSource extends AbstractRoutingDataSource {

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


    @Autowired
    public RoutingDataSource(@Qualifier("dataSource") DataSource dataSource) {
        targetDataSources.put("dataSource", dataSource);
        super.setDefaultTargetDataSource(dataSource);
        super.setTargetDataSources(this.targetDataSources);
    }

    @Override
    protected Object determineCurrentLookupKey() {
        if (!StringUtils.isEmpty(ThreadLocalContext.get().getDataSource())) {
            return ThreadLocalContext.get().getDataSource();
        }
        // 返回空就是 默认数据库
        return null;
    }
}

数据库脚本

主数据库,dynamic_main

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : localhost:3306
 Source Schema         : dynamic_main

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 24/12/2023 17:26:08
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for db_database
-- ----------------------------
DROP TABLE IF EXISTS `db_database`;
CREATE TABLE `db_database` (
  `id` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '编号',
  `database_name` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库名称',
  `url` varchar(1000) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库连接字符串',
  `driver_class` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库连接驱动',
  `db_user` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库名称',
  `db_pwd` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库密码',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `is_deleted` int(11) DEFAULT NULL COMMENT '是否删除',
  `domain` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT 'domain,拦截前端请求,通过domain 查询要切换的数据库',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of db_database
-- ----------------------------
BEGIN;
INSERT INTO `db_database` VALUES ('1', 'dynamic_1', 'jdbc:mysql://127.0.0.1:3306/dynamic_1?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true', 'com.mysql.cj.jdbc.Driver', 'root', 'abc123', '2023-12-24 10:03:50', 0, 'localhost');
INSERT INTO `db_database` VALUES ('2', 'dynamic_2', 'jdbc:mysql://127.0.0.1:3306/dynamic_2?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true', 'com.mysql.cj.jdbc.Driver', 'root', 'abc123', '2023-12-24 10:03:50', 0, '127.0.0.1');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

子数据库, dynamic_1

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : localhost:3306
 Source Schema         : dynamic_1

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 24/12/2023 17:26:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '编号',
  `name` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '学生姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', 'localhost');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

子数据库, dynamic_2

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : localhost:3306
 Source Schema         : dynamic_2

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 24/12/2023 17:26:26
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '编号',
  `name` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '学生姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '127.0.0.1');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Gitee

https://gitee.com/zhaops_learning/spring-cloud-learning/tree/master/spring-mybits-dynamic-datasource

spring cloud

#java #springboot #mybits plus

spring mybits plus动态切换数据库

https://zhaops-hub.github.io/2023/12/24/spring cloud/spring mybits plus动态切换数据库/

作者

赵培胜

发布于

2023年12月24日

许可协议