Springboot MyBatis多数据源切换

Springboot MyBatis多数据源切换

在实际开发中，我们一个项目可能会用到多个数据库，通常一个数据库对应一个数据源。本示例，通过两种方式实现多数据源切换
1）手动切换
2）使用注解进行切换

代码结构：

简要原理：

1）DataSourceType列出所有的数据源的key—key

2）DataSourceContextHolder是一个线程安全的DataSourceEnum容器，并提供了向其中设置和获取DataSourceEnum的方法

3）DynamicDataSource继承AbstractRoutingDataSource并重写其中的方法determineCurrentLookupKey()，在该方法中使用DatabaseContextHolder获取当前线程的DatabaseType

4）MyBatisConfig中生成2个数据源DataSource的bean—value

5）MyBatisConfig中将1）和4）组成的key-value对写入到DynamicDataSource动态数据源的targetDataSources属性（当然，同时也会设置2个数据源其中的一个为DynamicDataSource的defaultTargetDataSource属性中）

6）将DynamicDataSource作为primary数据源注入到SqlSessionFactory的dataSource属性中去，并且该dataSource作为transactionManager的入参来构造DataSourceTransactionManager

7）使用的时候，在dao层或service层先使用DatabaseContextHolder设置将要使用的数据源key，然后再调用mapper层进行相应的操作，建议放在dao层去做（当然也可以使用spring aop+自定注解去做）

8）定义了一个DataSourceTypeAnno注解，用于说明方法执行时使用的数据源

9）DataSourceAspect切面用于捕获DataSourceTypeAnno注解的方法，根据DataSourceTypeAnno注解内对应的值设置数据源

注意：在mapper层进行操作的时候，会先调用determineCurrentLookupKey()方法获取一个数据源（获取数据源：先根据设置去targetDataSources中去找，若没有，则选择defaultTargetDataSource），之后在进行数据库操作。

Maven依赖

<?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.example.springboot</groupId>
    <artifactId>multi-datasource</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.2.RELEASE</version>
        <relativePath/> 
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.0</version>
        </dependency>

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

        <!-- 使用aspectj时需要 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
    </dependencies>

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

application.properties配置

#the master datasource
datasource.master.driverClassName=com.mysql.jdbc.Driver
datasource.master.url=jdbc:mysql://localhost:3306/master?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
datasource.master.username=root
datasource.master.password=123456
#the slaver datasource
datasource.slaver.driverClassName=com.mysql.jdbc.Driver
datasource.slaver.url=jdbc:mysql://localhost:3306/slaver?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
datasource.slaver.username=root
datasource.slaver.password=123456
# mybatis接口文件位置
#mybatis.typeAliasesPackage=
# mybatis *.xml文件的位置
#mybatis.mapperLocations=

mysql数据库数据准备

#向master和slaver两个数据库中各建立一张表
CREATE TABLE t_shop (
    id        INT PRIMARY KEY,
    shop_name CHAR(64) NOT NULL
);
# 向master库中插入一条记录
INSERT INTO t_shop(id, shop_name) VALUES (1, 'MasterShop');
# 向slaver库中插入一条记录
INSERT INTO t_shop(id, shop_name) VALUES (1, 'SlaverShop');

Shop实例类

package com.example.springboot.multidatasource.domain;

/**
 * Author: 王俊超
 * Date: 2017-04-09 08:58
 * All Rights Reserved !!!
 */
public class Shop {
    private int id;
    private String shopName;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getShopName() {
        return shopName;
    }

    public void setShopName(String shopName) {
        this.shopName = shopName;
    }

    @Override
    public String toString() {
        return "Shop{" +
                "id=" + id +
                ", shopName='" + shopName + '\'' +
                '}';
    }
}

DataSourceEnum

作用：列举数据源的key

package com.example.springboot.multidatasource.common;

/**
 * Author: 王俊超
 * Date: 2017-04-09 08:30
 * All Rights Reserved !!!
 */
public enum DataSourceEnum {
    master,slaver;
}

DataSourceContextHolder

作用：构建一个DataSourceEnum容器，并提供了向其中设置和获取DataSorceEnum的方法

package com.example.springboot.multidatasource.common;

/**
 * Author: 王俊超
 * Date: 2017-04-09 08:30
 * All Rights Reserved !!!
 */
public class DataSourceContextHolder {
    private static final ThreadLocal<DataSourceEnum> CONTEXT_HOLDER = new ThreadLocal<DataSourceEnum>() {

        @Override
        protected DataSourceEnum initialValue() {
            return DataSourceEnum.master;
        }
    };


    public static void setDataSourceType(DataSourceEnum type) {
        CONTEXT_HOLDER.set(type);
    }

    public static DataSourceEnum getDataSourceType() {
        return CONTEXT_HOLDER.get();
    }

    public static void resetDataSourceType() {
        CONTEXT_HOLDER.set(DataSourceEnum.master);
    }
}

DynamicDataSource

作用：使用DatabaseContextHolder获取当前线程的DataSoureEnum

package com.example.springboot.multidatasource.common;

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

/**
 * Author: 王俊超
 * Date: 2017-04-09 08:30
 * All Rights Reserved !!!
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}

MyBatisConfig

通过读取application.properties文件生成两个数据源（masterDataSource，slaverDataSource）
使用以上生成的两个数据源构造动态数据源dataSource
@Primary：指定在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@Autowire注解报错（一般用于多数据源的情况下）
@Qualifier：指定名称的注入，当一个接口有多个实现类的时候使用（在本例中，有三个DataSource类型的实例，DynamicDataSource也是一种DataSource，需要指定名称注入）
@Bean：生成的bean实例的名称是方法名（例如上边的@Qualifier注解中使用的名称是前边两个数据源的方法名，而这两个数据源也是使用@Bean注解进行注入的）
通过动态数据源构造SqlSessionFactory和事务管理器（如果不需要事务，后者可以去掉）

package com.example.springboot.multidatasource.config;

import com.example.springboot.multidatasource.common.DataSourceEnum;
import com.example.springboot.multidatasource.common.DynamicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * Author: 王俊超
 * Date: 2017-04-09 08:30
 * All Rights Reserved !!!
 */
@Configuration
@MapperScan(basePackages = "com.example.springboot.multidatasource.mapper")
public class MyBatisConfig {

    /**
     * @return
     * @throws Exception
     * @Primary 该注解表示在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@autowire注解报错
     */
    @Primary
    @Bean("masterDataSource")
    @ConfigurationProperties(prefix = "datasource.master")
    public DataSource masterDataSource() throws Exception {
        return DataSourceBuilder.create().build();
    }

    @Bean("slaverDataSource")
    @ConfigurationProperties(prefix = "datasource.slaver")
    public DataSource slaverDataSource() throws Exception {
        return DataSourceBuilder.create().build();
    }

    /**
     * @Qualifier 根据名称进行注入，通常是在具有相同的多个类型的实例的一个注入（例如有多个DataSource类型的实例）
     */
    @Bean("dynamicDataSource")
    public DynamicDataSource dynamicDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                               @Qualifier("slaverDataSource") DataSource slaverDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        targetDataSources.put(DataSourceEnum.master, masterDataSource);
        targetDataSources.put(DataSourceEnum.slaver, slaverDataSource);

        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
        dataSource.setDefaultTargetDataSource(masterDataSource);// 默认的datasource设置为myTestDbDataSource

        return dataSource;
    }

    /**
     * 根据数据源创建SqlSessionFactory
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DynamicDataSource dynamicDataSource,
                                               @Value("mybatis.typeAliasesPackage") String typeAliasesPackage,
                                               @Value("mybatis.mapperLocations") String mapperLocations) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dynamicDataSource);// 指定数据源(这个必须有，否则报错)
        // 下边两句仅仅用于*.xml文件，如果整个持久层操作不需要使用到xml文件的话（只用注解就可以搞定），则不加
//        factoryBean.setTypeAliasesPackage(typeAliasesPackage);// 指定基包
//        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));//

        return factoryBean.getObject();
    }

    /**
     * 配置事务管理器
     */
    @Bean
    public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {
        return new DataSourceTransactionManager(dataSource);
    }
}

ShopMapper

数据映射文件

package com.example.springboot.multidatasource.mapper;

import com.example.springboot.multidatasource.domain.Shop;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

/**
 * Author: 王俊超
 * Date: 2017-04-09 08:58
 * All Rights Reserved !!!
 */

public interface ShopMapper {
    @Select("SELECT * FROM t_shop WHERE id = #{id}")
    @Results(value = { @Result(id = true, column = "id", property = "id"),
            @Result(column = "shop_name", property = "shopName") })
    public Shop getShop(@Param("id") int id);
}

ShopDao

package com.example.springboot.multidatasource.service;

import com.example.springboot.multidatasource.annotation.DataSourceTypeAnno;
import com.example.springboot.multidatasource.common.DataSourceEnum;
import com.example.springboot.multidatasource.dao.ShopDao;
import com.example.springboot.multidatasource.domain.Shop;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Author: 王俊超
 * Date: 2017-04-09 09:09
 * All Rights Reserved !!!
 */
@Service("shopService")
public class ShopService {
    @Autowired
    private ShopDao dao;

    public Shop getShop(int id) {
        return dao.getShop(id);
    }

    public Shop getMasterShopByHandle(int id) {
        return dao.getMasterShopByHandle(id);
    }

    public Shop getSlaverShopByHandle(int id) {
        return dao.getSlaverShopByHandle(id);
    }

    @DataSourceTypeAnno(DataSourceEnum.master)
    public Shop getMasterShopByAnnotation(int id) {
        return dao.getMasterShopByAnnotation(id);
    }

    @DataSourceTypeAnno(DataSourceEnum.slaver)
    public Shop getSlaverShopByAnnotation(int id) {
        return dao.getSlaverShopByAnnotation(id);
    }
}

ShopController

package com.example.springboot.multidatasource.controller;

import com.example.springboot.multidatasource.annotation.DataSourceTypeAnno;
import com.example.springboot.multidatasource.common.DataSourceEnum;
import com.example.springboot.multidatasource.domain.Shop;
import com.example.springboot.multidatasource.service.ShopService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

/**
 * Author: 王俊超
 * Date: 2017-04-09 09:12
 * All Rights Reserved !!!
 */
@RestController
public class ShopController {
    @Autowired
    private ShopService service;

    @RequestMapping(value = "/getShop")
    public Shop getShop(@RequestParam("id") int id) {
        return service.getShop(id);
    }

    @RequestMapping(value = "/getMasterShopByHandle")
    public Shop getMasterShopByHandle(int id) {
        return service.getMasterShopByHandle(id);
    }

    @RequestMapping(value = "/getSlaverShopByHandle")
    public Shop getSlaverShopByHandle(int id) {
        return service.getSlaverShopByHandle(id);
    }

    @RequestMapping(value = "/getMasterShopByAnnotation")
    @DataSourceTypeAnno(DataSourceEnum.master)
    public Shop getMasterShopByAnnotation(int id) {
        return service.getMasterShopByAnnotation(id);
    }

    @RequestMapping(value = "/getSlaverShopByAnnotation")
    public Shop getSlaverShopByAnnotation(int id) {
        return service.getSlaverShopByAnnotation(id);
    }
}

MultiDataSourceApplication

程序启动入口

package com.example.springboot.multidatasource;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;

/**
 * Author: 王俊超
 * Date: 2017-04-09 09:14
 * All Rights Reserved !!!
 */
@SpringBootApplication
@EnableAutoConfiguration
@EnableWebMvc
public class MultiDataSourceApplication  {
    public static void main(String[] args) {
        SpringApplication.run(MultiDataSourceApplication.class, args);
    }
}

DataSourceTypeAnno数据源类型注解

@Retention(RetentionPolicy.RUNTIME) 说是此注解在运行时可见
@Target(ElementType.METHOD) // 注解可以用在方法上

package com.example.springboot.multidatasource.annotation;

import com.example.springboot.multidatasource.common.DataSourceEnum;

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

/**
 * 数据源类型注解
 *
 * Author: 王俊超
 * Date: 2017-04-09 09:50
 * All Rights Reserved !!!
 */
@Retention(RetentionPolicy.RUNTIME) // 在运行时可见
@Target(ElementType.METHOD) // 注解可以用在方法上
public @interface DataSourceTypeAnno {
    DataSourceEnum value() default DataSourceEnum.master;
}

DataSourceAspect 数据源切面

用于捕获使用数据源注解的方法，并且根据注解上的数据源类型进行切换

package com.example.springboot.multidatasource.aspect;

import com.example.springboot.multidatasource.annotation.DataSourceTypeAnno;
import com.example.springboot.multidatasource.common.DataSourceContextHolder;
import com.example.springboot.multidatasource.common.DataSourceEnum;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

/**
 * Author: 王俊超
 * Date: 2017-04-09 09:54
 * All Rights Reserved !!!
 */
@Component
@Aspect
public class DataSourceAspect {
    @Pointcut("execution(* com.example.springboot.multidatasource.dao..*(..)) " +
            "&& @annotation(com.example.springboot.multidatasource.annotation.DataSourceTypeAnno)")
    public void dataSourcePointcut() {
    }

    @Around("dataSourcePointcut()")
    public Object doAround(ProceedingJoinPoint pjp) {
        MethodSignature methodSignature = (MethodSignature) pjp.getSignature();
        Method method = methodSignature.getMethod();
        DataSourceTypeAnno typeAnno = method.getAnnotation(DataSourceTypeAnno.class);
        DataSourceEnum sourceEnum = typeAnno.value();

        if (sourceEnum == DataSourceEnum.master) {
            DataSourceContextHolder.setDataSourceType(DataSourceEnum.master);
        } else if (sourceEnum == DataSourceEnum.slaver) {
            DataSourceContextHolder.setDataSourceType(DataSourceEnum.slaver);
        }

        Object result = null;
        try {
            result = pjp.proceed();
        } catch (Throwable throwable) {
            throwable.printStackTrace();
        } finally {
            DataSourceContextHolder.resetDataSourceType();
        }

        return result;
    }
}

执行结果