springboot+mybatis-plus 使用动态数据源

原创已于 2026-03-02 15:21:52 修改 · 542 阅读

20 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

于 2026-02-03 14:45:19 首次发布

springboot+mybatis-plus 使用动态数据源

项目说明

项目说明

这是一个基于 SpringBoot + MyBatis-Plus 的动态多数据源管理核心包,实现了完全通用的多数据源切换方案。

✨ 核心特性

启动时自动加载 - 从配置表自动加载所有数据库配置
动态连接池管理 - 使用HikariCP为每个数据库创建独立连接池
透明数据源切换 - 根据请求头X-DBCODE自动切换数据源
MyBatis-Plus完美集成 - 享受MyBatis-Plus的所有强大功能
零业务侵入 - Controller/Service/Mapper无需关心数据源切换
线程安全 - 使用ThreadLocal确保线程隔离
开箱即用的CRUD - 继承BaseMapper自动拥有增删改查方法

架构设计

┌─────────────────────────────────────────────────────────┐
│                     HTTP请求							  |
│              Header: X-DBCODE: db001                    |
└─────────────────────────────────────────────────────────┘
                          ↓
┌─────────────────────────────────────────────────────────┐
│              DataSourceInterceptor                      │
│         提取X-DBCODE → ThreadLocal						  │
└─────────────────────────────────────────────────────────┘
                          ↓
┌─────────────────────────────────────────────────────────┐
│                   Controller                            │
│              @GetMapping("/api/users")                  │
└─────────────────────────────────────────────────────────┘
                          ↓
┌─────────────────────────────────────────────────────────┐
│                    Service                              │
│            userService.getAllUsers()                    │
└─────────────────────────────────────────────────────────┘
                          ↓
┌─────────────────────────────────────────────────────────┐
│                    Mapper                               │
│          userMapper.selectAll()                         │
└─────────────────────────────────────────────────────────┘
                          ↓
┌─────────────────────────────────────────────────────────┐
│           DynamicRoutingDataSource                      │
│    根据ThreadLocal中的dbCode路由到对应数据源                │
└─────────────────────────────────────────────────────────┘
                          ↓
┌─────────────────────────────────────────────────────────┐
│              HikariDataSource (db001的连接池) 			  │
└─────────────────────────────────────────────────────────┘

项目结构

db-core/
├── pom.xml                                    # Maven配置
├── sql/
│   └── init.sql                              # 数据库初始化脚本
└── src/
    └── main/
        ├── java/com/db/core/
        │   ├── DbCoreApplication.java       # 启动类
        │   ├── config/                       # 配置类
        │   │   ├── MyBatisPlusConfig.java        # MyBatisPlus配置
        │   │   ├── WebConfig.java            # Web配置
        │   │   └── ApplicationShutdownHook.java
        │   ├── context/                      # 上下文
        │   │   └── DataSourceContextHolder.java
        │   ├── datasource/                   # 数据源管理
        │   │   ├── DynamicRoutingDataSource.java
        │   │   └── DynamicDataSourceManager.java
        │   ├── interceptor/                  # 拦截器
        │   │   └── DataSourceInterceptor.java
        │   ├── loader/                       # 加载器
        │   │   └── DataSourceLoader.java
        │   ├── entity/                       # 实体类
        │   │   ├── DbConfig.java
        │   │   └── User.java
        │   ├── mapper/                       # Mapper接口
        │   │   ├── DbMapper.java
        │   │   ├── DemoMapper.java
        │   ├── service/                      # 服务层
        │   │   └── DemoService.java
        │   └── controller/                   # 控制层
        │       └── DemoController.java  # 用于测试
        └── resources/
            └── application.yml               # 配置文件

🚀 快速开始

1. 准备数据库

执行 sql/init.sql 脚本,会自动创建:

db_config配置表
demo测试数据

# db_config 所在库的配置，写在配置文件application 中
CREATE TABLE `db_config` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `code` varchar(64) DEFAULT NULL,
    `url` varchar(64) DEFAULT NULL,
    `login_name` varchar(64) DEFAULT NULL,
    `password` varchar(64) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2018506341747748897 DEFAULT CHARSET=utf8;
# 修改为自己数据库地址， db_1,db_2,db_3 分别对应不同的库，自己手动创建
insert into db_config(code, login_name, password,url)
VALUES
    ('00001','root','123456','jdbc:mysql://127.0.0.1:3304/db_1?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&autoReconnect=true&rewriteBatchedStatements=true'),
    ('00002','root','123456','jdbc:mysql://127.0.0.1:3304/db_2?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&autoReconnect=true&rewriteBatchedStatements=true'),
    ('00003','root','123456','jdbc:mysql://127.0.0.1:3304/db_3?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&autoReconnect=true&rewriteBatchedStatements=true')
;

# 每个库db_1,db_2,db_3 都要新建此表，用于测试写入和查询
CREATE TABLE `demo_data` (
    `id` bigint(20) NOT NULL,
    `name` varchar(64) DEFAULT NULL,
    `code` varchar(64) DEFAULT NULL,
    `db_desc` text,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2018506341747748897 DEFAULT CHARSET=utf8;

2. 新建空springboot 项目，添加依赖

引入依赖：pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.db</groupId>
    <artifactId>db-config</artifactId>
    <version>1.0.0</version>
    <packaging>pom</packaging>

    <name>db-config</name>
    <description>核心包-动态多数据源(MyBatis版)</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.18</version>
        <relativePath/>
    </parent>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <mybatis-plus.version>3.5.5</mybatis-plus.version>
        <hikaricp.version>4.0.3</hikaricp.version>
    </properties>

    <dependencies>
        <!-- Spring Boot Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- MyBatis-Plus Spring Boot Starter -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>

        <!-- HikariCP连接池 -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>${hikaricp.version}</version>
        </dependency>

        <!-- MySQL驱动 根据自己mysql版本自己更改 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.40</version>
        </dependency>

        <!-- Oracle驱动(可选) -->
        <!--
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>21.9.0.0</version>
        </dependency>
        -->

        <!-- AOP -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>com.google.collections</groupId>
            <artifactId>google-collections</artifactId>
            <version>1.0</version>
        </dependency>

        <!-- Spring Boot Configuration Processor -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- Spring Boot Test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

配置文件 application.yml

server:
  port: 8080

spring:
  application:
    name: eid-core
  
  # db-config库数据源配置(用于加载数据库配置信息)
  datasource:
    dc: # 名称随便起，后面使用时记得改
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://172.16.199.23:3346/ca?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&autoReconnect=true
      username: bysa
      password: Hzbytest
      # HikariCP配置
      hikari:
        maximum-pool-size: 10
        minimum-idle: 5
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000
        connection-test-query: SELECT 1

# MyBatis-Plus配置
mybatis-plus:
  # 驼峰命名转换
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # Mapper XML文件位置
  mapper-locations: classpath:mapper/**/*.xml
  # 类型别名包
  type-aliases-package: com.eid.entity
  # 全局配置
  global-config:
    db-config:
      # 主键类型(AUTO-数据库自增)
      id-type: AUTO
      # 逻辑删除配置
      logic-delete-field: deleted
      logic-delete-value: 1
      logic-not-delete-value: 0

# 日志配置
logging:
  level:
    root: info
    com.eid.core: DEBUG
    com.eid.core.mapper: DEBUG
    com.zaxxer.hikari: INFO
  pattern:
    console: "%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n"

3. 配置类(config)

MyBatisPlusConfig

package com.eid.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.eid.ds.DynamicDataSourceManager;
import com.eid.ds.DynamicRoutingDataSource;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.annotation.MapperScan;
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.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

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

/**
 * MyBatis-Plus动态数据源配置
 */
@Slf4j
@Configuration
@MapperScan(basePackages = "com.db.mapper", sqlSessionFactoryRef = "sqlSessionFactory")
public class MyBatisPlusConfig {

    @Autowired
    private DynamicDataSourceManager dataSourceManager;

    /**
     * MyBatis-Plus分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 添加分页插件
        PaginationInnerInterceptor paginationInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
        // 设置请求的页面大于最大页后操作,true调回到首页,false继续请求,默认false
        paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量,默认500条,-1不受限制
        paginationInterceptor.setMaxLimit(-1L);
        interceptor.addInnerInterceptor(paginationInterceptor);
        return interceptor;
    }

    /**
     * 数据配置表(主数据源,用于加载数据库配置信息)
     */
    @Bean(name = "dcDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.dc")
    public DataSource caDataSource() {
        return DataSourceBuilder.create()
                .type(HikariDataSource.class)
                .build();
    }

    /**
     * 动态路由数据源
     * 这是MyBatis实际使用的数据源
     */
    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource(@Qualifier("dcDataSource") DataSource caDataSource) {
        DynamicRoutingDataSource routingDataSource = new DynamicRoutingDataSource();
        // 初始化时的数据源映射(只包含配置表的数据源,用于启动时加载配置)
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("dc", caDataSource);
        // 设置默认数据源为配置表所在库
        routingDataSource.setDefaultTargetDataSource(caDataSource);
        routingDataSource.setTargetDataSources(targetDataSources);
        // 将路由数据源注册到管理器
        dataSourceManager.setRoutingDataSource(routingDataSource);
        return routingDataSource;
    }

    /**
     * SqlSessionFactory配置 (使用MyBatis-Plus)
     */
    @Primary
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource) throws Exception {
        // 使用MyBatis-Plus的SqlSessionFactoryBean
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource);
        
        // 设置MyBatis-Plus配置
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);  // 驼峰命名转换
        configuration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);  // SQL日志
        sqlSessionFactoryBean.setConfiguration(configuration);
        
        // 设置分页插件
        sqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor());
        
        // 设置Mapper XML文件位置
        sqlSessionFactoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/**/*.xml"));
        
        // 设置类型别名包
//        sqlSessionFactoryBean.setTypeAliasesPackage("com.eid.entity");
        
        return sqlSessionFactoryBean.getObject();
    }

    /**
     * 事务管理器
     */
    @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("dynamicDataSource") DataSource dynamicDataSource) {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dynamicDataSource);
        return transactionManager;

    }
}

DataSourceInterceptor

package com.eid.config;

import com.eid.context.DataSourceContextHolder;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.web.servlet.HandlerInterceptor;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * 数据源切换拦截器
 * 从请求头X-DBCDODE中获取db_code,并设置到ThreadLocal中
 */
@Slf4j
@Component
public class DataSourceInterceptor implements HandlerInterceptor {

    /**
     * 请求头名称
     */
    private static final String DBCODE_HEADER = "X-DBCODE";

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) {
        // 优先从请求头获取dbCode
        String db_code = request.getHeader(DBCODE_HEADER);
        
        // 如果请求头中没有,则从请求参数中获取
        if (!StringUtils.hasText(db_code)) {
            db_code = request.getParameter(DBCODE_HEADER);
        }
        
        // 如果找到db_code ,则设置到上下文中
        if (StringUtils.hasText(db_code )) {
            DataSourceContextHolder.setDataSource(db_code);
            log.debug("请求URI: {}, 数据源: {}", request.getRequestURI(), db_code);
        } else {
            log.debug("请求URI: {}, 未指定数据源", request.getRequestURI());
        }
        
        return true;
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) {
        // 请求完成后清除ThreadLocal,避免内存泄漏
        DataSourceContextHolder.clearDataSource();
    }
}

WebConfig

package com.eid.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

/**
 * Web配置类
 * 注册数据源拦截器
 */
@Configuration
public class WebConfig implements WebMvcConfigurer {

    @Autowired
    private DataSourceInterceptor dataSourceInterceptor;

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(dataSourceInterceptor)
                .addPathPatterns("/**")  // 拦截所有请求
                .excludePathPatterns(    // 排除不需要数据源的路径
                        "/error",
                        "/static/**",
                        "/public/**",
                        "/favicon.ico"
                );
    }
}

ApplicationShutdownHook

package com.eid.config;

import com.eid.ds.DynamicDataSourceManager;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationListener;
import org.springframework.context.event.ContextClosedEvent;
import org.springframework.stereotype.Component;

/**
 * 应用关闭钩子
 * 在应用关闭时自动关闭所有数据源连接池
 */
@Slf4j
@Component
public class ApplicationShutdownHook implements ApplicationListener<ContextClosedEvent> {

    @Autowired
    private DynamicDataSourceManager dataSourceManager;

    @Override
    public void onApplicationEvent(ContextClosedEvent event) {
        log.info("应用正在关闭,开始清理数据源连接池...");
        
        try {
            dataSourceManager.closeAllDataSources();
            log.info("所有数据源连接池已成功关闭");
        } catch (Exception e) {
            log.error("关闭数据源连接池时发生异常", e);
        }
    }
}

4. 其他文件不一一列举后面有整个项目打包地址

测试效果：

1. 启动项目

启动成功后会看到:

====== 开始加载动态数据源配置 ======
从db_config库加载到 3 条数据库配置
正在创建数据源: code=001, dbHost=jdbc:mysql://127.0.0.1:3304/xxxxx
✓ 数据源创建成功: code=001
正在创建数据源: code=002, dbHost=jdbc:mysql://127.0.0.1:3304/xxxxx
✓ 数据源创建成功: code=002
正在创建数据源: code=003, dbHost=jdbc:mysql://127.0.0.1:3304/xxxxx
✓ 数据源创建成功: code=003
====== 动态数据源加载完成! 成功: 3, 失败: 0 ======
已加载的数据源: [001, 002, 003]

2.测试查询：

请求：
在这里插入图片描述

查询结果显示

{
    "data": [
        {
            "id": 2018571036450074626,
            "name": "name_2026-02-03 14:23:32_6",
            "code": "code_1770099812967_6",
            "dbDesc": "desc_6"
        },
        {
            "id": 2018571036441686020,
            "name": "name_2026-02-03 14:23:32_5",
            "code": "code_1770099812967_5",
            "dbDesc": "desc_5"
        },
        {
            "id": 2018571036441686019,
            "name": "name_2026-02-03 14:23:32_4",
            "code": "code_1770099812967_4",
            "dbDesc": "desc_4"
        }
    ],
    "page_info": "每页显示：3,总页数：19,当前页；1",
    "total": 55
}