SpringBoot：5.SpringBoot+Mybatis多数据源配置实现

在做架构中，有的时候会遇到下面两种情况：

• 读写分离，主库负责写入，从库负责读取。
• 因为数据量较大，需要在主库中存放平台主要表结构，将会大量产生的数据按日期分表放到从库中。（比如我们公司做车载GPS的，GPS数据量就比较大，所以就把GPS信息以及相关的报警信息按日期分表放入到从库中）

对于这两种情况，就需要在项目中加入多数据源，以便操作不同的数据库。而在实际开发中，一般会根据实际情况选择数据源的管理方式：

• 在项目中集成多数据源，实现数据源的切换。
• 通过数据库中间件，例如mycat、cobar等，通过一定的规则来让指定的语句到指定的数据库中执行。

考虑到公司项目每天产生的GPS数据量并不是很大，一般一天在1000万条数据，只要在从库中按日期分表，每天生产一张表用于存放GPS信息，所以选择了在项目中集成多数据源的方式。

Spring Boot+Mybatis多数据源实现的方式

1.引入依赖

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.9.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.w3cjava</groupId>
    <artifactId>05.Spring-Boot-Mul-Mybatis</artifactId>
    <version>0.1</version>
    <name>05.Spring-Boot-Mul-Mybatis</name>
    <description>Mul-Mybatis project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <maven-jar-plugin.version>3.0.0</maven-jar-plugin.version>
    </properties>

    <dependencies>
        <!-- springboot核心包-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>    
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <!-- springboot-aop包,AOP切面注解,Aspectd等相关注解 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>            
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <!-- jdbcTemple  -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>        
        <!-- mysql数据库连接包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.2</version>
        </dependency>
        <!-- 开发测试环境修改文件实时生效包,生产默认不使用 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.动态数据源配置

2.1 @DataBaseSource

用于在Service指定主从库

package com.w3cjava.common.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface DataBaseSource {
    String value() default "master";
}

2.2 DataSourceContextHolder

数据源获取与设置容器

package com.w3cjava.common.datasource;

public class DataSourceContextHolder {
    /**
     * 默认数据源
     */
    public static final String DEFAULT_DS = "master";
 
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
 
    // 设置数据源名
    public static void setDB(String dbType) {
        System.out.println("切换到{"+dbType+"}数据源");
        contextHolder.set(dbType);
    }
 
    // 获取数据源名
    public static String getDb() {
        return (contextHolder.get());
    }
 
    // 清除数据源名
    public static void clearDB() {
        contextHolder.remove();
    }
}

2.3 DynamicDataSource

动态源获取

package com.w3cjava.common.datasource;


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

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        System.out.println("数据源为" + DataSourceContextHolder.getDb());
        return DataSourceContextHolder.getDb();
    }
}

2.4 动态数据源配置DataSourceConfig

通过AOP在不同数据源之间动态切换

package com.w3cjava.common.config;

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

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import com.w3cjava.common.datasource.DynamicDataSource;

@Configuration
public class DataSourceConfig {
    public Logger logger = LoggerFactory.getLogger(this.getClass());
    // 数据源master
    @Bean(name = "master")
    @ConfigurationProperties(prefix = "spring.datasource.master") // application.properteis中对应属性的前缀
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    // 数据源slave
    @Bean(name = "slave")
    @ConfigurationProperties(prefix = "spring.datasource.slave") // application.properteis中对应属性的前缀
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 动态数据源: 通过AOP在不同数据源之间动态切换
     * 
     * @return
     */
    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        // 默认数据源
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource());
        // 配置多数据源
        Map<Object, Object> dsMap = new HashMap<Object, Object>();
        dsMap.put("master", masterDataSource());
        dsMap.put("slave", slaveDataSource());

        dynamicDataSource.setTargetDataSources(dsMap);
        return dynamicDataSource;
    }

    /**
     * 配置@Transactional注解事物
     * 
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }
    
}

2.5 DataSourceExchange切面

配置数据源改变时的切面

package com.w3cjava.common.datasource;

import java.lang.reflect.Method;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import com.w3cjava.common.annotation.DataBaseSource;

@Aspect
@Component
public class DataSourceExchange{
    public Logger logger = LoggerFactory.getLogger(this.getClass());
    @Pointcut(value="execution(* com.w3cjava.modules.*.dao.*.*(..))")
    public void dbPointCut() {

    }
    /**
     * 方法结束后
     */
    @AfterReturning(value="execution(* com.w3cjava.modules.*.dao.*.*(..)) ")
    public void afterReturning(JoinPoint point){
        logger.info("当前1数据源："+DataSourceContextHolder.getDb());
        DataSourceContextHolder.clearDB();
        logger.info("数据源已移除！");
        logger.info("当前2数据源："+DataSourceContextHolder.getDb());
    }
    
    /**
     * 拦截目标方法，获取由@DataSource指定的数据源标识，设置到线程存储中以便切换数据源
     */
    
    @SuppressWarnings("rawtypes")
    @Before(value="execution(* com.w3cjava.modules.*.dao.*.*(..))")
    public void before(JoinPoint point){
        //获得当前访问的class
        Class<?> className = point.getTarget().getClass();
        //获得访问的方法名
        String methodName = point.getSignature().getName();
        //得到方法的参数的类型
        Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes();
        
        try {
            Method method = className.getMethod(methodName, argClass);
            DataBaseSource dataSource = AnnotationUtils.findAnnotation(method, DataBaseSource.class);
            if(dataSource!=null) {
                DataSourceContextHolder.setDB(dataSource.value());
            }else {
                DataSourceContextHolder.setDB(DataSourceContextHolder.DEFAULT_DS);
            }
            logger.info("数据源切换至："+DataSourceContextHolder.getDb());
        } catch (Exception e) {
            e.printStackTrace();
        }
        
    }
}

3. 测试业务模型

3.1 User实体

package com.w3cjava.modules.user.entity;

public class User{
    private String id;
    private String name;
    private Integer age;
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
    }
    
    
}

3.2 UserDao层及xml

package com.w3cjava.modules.user.dao;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import com.w3cjava.common.annotation.DataBaseSource;
import com.w3cjava.modules.user.entity.User;

@Mapper
public interface UserDao{
    //使用xml配置形式查询
    @DataBaseSource("master")
    public int insertMaster(User entity);
    @DataBaseSource("slave")
    public int insertSlave(User entity);
    
    
    
    @DataBaseSource("slave")
    public List<User> getSlaveAllUser();
    @DataBaseSource("master")
    public List<User> getMasterAllUser();
}

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.w3cjava.modules.user.dao.UserDao">
    <sql id="testColumns">
        a.id AS "id",
        a.name AS "name",
        a.age AS "age"
    </sql>
    
    <sql id="testJoins">
    </sql>
    <!-- 查询所有user -->
     <select id="getSlaveAllUser" resultType="com.w3cjava.modules.user.entity.User">
            select 
                <include refid="testColumns"/>
             from user a
     </select>
     <select id="getMasterAllUser" resultType="com.w3cjava.modules.user.entity.User">
            select 
                <include refid="testColumns"/>  
            from user a
     </select>       
    <insert id="insertMaster">
        INSERT INTO user(
            id,
            name,
            age
        ) VALUES (
            #{id},
            #{name},
            #{age}
        )
    </insert>
    <insert id="insertSlave">
        INSERT INTO user(
            id,
            name,
            age
        ) VALUES (
            #{id},
            #{name},
            #{age}
        )
    </insert>    
</mapper>

3.3 UserService层

package com.w3cjava.modules.user.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.w3cjava.modules.user.dao.UserDao;
import com.w3cjava.modules.user.entity.User;
@Service
public class UserService{
    @Autowired
    private UserDao userDao;
    //使用数据源master查询
    //@Transactional(readOnly=true)
    public List<User> getAllUserMaster(){
        return userDao.getMasterAllUser();
    }
    //使用数据源slave查询
    //@Transactional(readOnly=true)
    public List<User> getAllUserSlave(){
        return userDao.getSlaveAllUser();
    }
 
    
    
    @Transactional(readOnly=true)
    public String getAllUserTest(){
        List<User> list1 = userDao.getMasterAllUser();
        List<User> list2 = userDao.getSlaveAllUser();
        return "master:"+list1+"</br>slave:"+list2;
    }    
    
    //使用数据源master插入数据
    //@Transactional(readOnly=false)
    public int saveMaster(User user){
        int m = userDao.insertMaster(user);
        return m;
    }
    
    //使用数据源slave插入数据
    //@Transactional(readOnly=false)
    public int saveSlave(User user){
        int m = userDao.insertSlave(user);
        return m;
    }   
}

3.4 UserController层

package com.w3cjava.modules.user.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.w3cjava.common.utils.IdGen;
import com.w3cjava.modules.user.entity.User;
import com.w3cjava.modules.user.service.UserService;

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;
    //不使用事务注解@Transactional
    @RequestMapping(value = "/getDb1AllUser")
    public String getDbAllUser() {
        List<User> list1 = userService.getAllUserMaster();
        for (int i = 0; i < list1.size(); i++) {
            System.out.println(list1.get(i).getId()+"-"+list1.get(i).getName()+"-"+list1.get(i).getAge());
        }
        List<User> list2 = userService.getAllUserSlave();
        for (int i = 0; i < list2.size(); i++) {
            System.out.println(list2.get(i).getId()+"-"+list2.get(i).getName()+"-"+list2.get(i).getAge());
        }       
        return "master:"+list1+"</br>slave:"+list2;
    }

    
    
    //使用事务注解@Transactional
    @RequestMapping(value = "/getDbAllUserTest")
    public String getDbAllUserTest() {
        String list = userService.getAllUserTest();
        return list;
    }
    //主库master user信息
    @RequestMapping(value = "/getAllUserMaster")
    public String getAllUserMaster() {
        List<User> list = userService.getAllUserMaster();
        return "master:"+list;
    } 
    
    //从库slave user信息
    @RequestMapping(value = "/getAllUserSlave")
    public String getAllUserSlave() {
        List<User> list = userService.getAllUserSlave();
        return "slave:"+list;
    }
 
    @SuppressWarnings("unused")
    @RequestMapping(value = "/saveMaster")
    public String saveMaster() {
        User user = new User();
        user.setId(IdGen.uuid());
        user.setName("MasterTom");
        user.setAge(20);
        Integer rows = userService.saveMaster(user);//返回的是结果行数
        return "{id:"+user.getId()+"}";
    }
    
    
    
    @SuppressWarnings("unused")
    @RequestMapping(value = "/saveSlave")
    public String saveSlave() {
        User user = new User();
        user.setId(IdGen.uuid());
        user.setName("SlaveTom");
        user.setAge(20);
        Integer rows = userService.saveSlave(user);//返回的是结果行数
        return "{id:"+user.getId()+"}";
    }
}

user表id采用32位字符串形式，所以需要一个生成ID的工具类IdGen

package com.w3cjava.common.utils;
import java.util.UUID;
/**
 * 
 * @author    w3cjava
 * @date    2018年8月29日
 * @desc    封装各种生成唯一性ID算法的工具类.
 */
public class IdGen{
    /**
     * 封装JDK自带的UUID, 通过Random数字生成, 中间无-分割.
     */
    public static String uuid() {
        return UUID.randomUUID().toString().replaceAll("-", "");
    }

}

4.启动类

package com.w3cjava;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
/**
 * 
 * @class  SpringBootMulMybatisApplication
 * @version SpringBoot 2.1.9
 * @author cos
 * @desc   整合Mybatis实现多数据源配置
 *
 */
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
public class SpringBootMulMybatisApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringBootMulMybatisApplication.class, args);
        
    }

}

配置文件application.properties

server.port=10001
#springboot\u591A\u6570\u636E\u6E90\u914D\u7F6E
#\u6570\u636E\u6E901
spring.datasource.master.jdbc-url=jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.master.username=root
spring.datasource.master.password=123456
spring.datasource.master.driver-Class-Name=com.mysql.jdbc.Driver
spring.datasource.master.max-idle=10
spring.datasource.master.max-wait=10000
spring.datasource.master.min-idle=5
spring.datasource.master.initial-size=5
#\u6570\u636E\u6E902
spring.datasource.slave.jdbc-url=jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.slave.username=root
spring.datasource.slave.password=123456
spring.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.slave.max-idle=10
spring.datasource.slave.max-wait=10000
spring.datasource.slave.min-idle=5
spring.datasource.slave.initial-size=5
#mybatis
mybatis.mapper-locations=classpath*:mapper/*.xml

数据库SQL，库test1和test2均使用如下sql创建表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` varchar(32) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5. 启动程序测试

通过SpringBootMulMybatisApplication run运行启动程序。

分别访问如下请求查看效果

http://localhost:10001/user/saveMaster 主库插入

http://localhost:10001/user/saveSlave 从库插入

http://localhost:10001/user/getAllUserMaster 获取主库数据

http://localhost:10001/user//getAllUserSlave 获取从库数据

http://localhost:10001/user/getDb1AllUser 获取主从库数据，无事务

http://localhost:10001/user/getDbAllUserTest 获取主从库数据，有事务，失败

备注：在获取主从库数据时，如果增加了事务@Transactional时导致获取的数据错误，经测试一般先查master库，slave库一般就查不到，先查slave库，master库查不到，通过切面切换主从库出现的异常还不知道如何解决。有人知道如何解决的可以提供下。

6. 文章源码

05.Spring-Boot-Mul-Mybatis
欢迎扫面下列二维码关注“余弦的自留地”公众微信号

万物之中，希望至美