Springboot MyBatis Multiple Data Source Switching

In practical development, we may use multiple databases for a project, usually one database for a data source.In this example, switch between multiple data sources in two ways
1) Manual switch
2) Switch using annotations

Code structure:

Brief Principle:

1) DataSourceType lists the key-key for all data sources

2) DataSourceContextHolder is a thread-safe DataSourceEnum container and provides a way to set and obtain DataSourceEnum from it

3) DynamicDataSource inherits AbstractRoutingDataSource and overrides the method determineCurrentLookupKey(), in which the DatabaseContextHolder is used to get the DatabaseType of the current thread

4) bean-value to generate two DataSource s in MyBatisConfig

5) The key-value pairs consisting of 1) and 4) in MyBatisConfig are written to the targetDataSources property of the DynamicDataSource dynamic data source (of course, one of the two data sources is also set to the defaultTargetDataSource property of the DynamicDataSource)

6) Inject DynamicDataSource as a primary data source into the dataSource property of SqlSessionFactory and use the dataSource as a transactionManager input to construct the DataSourceTransactionManager

7) When using it, use DatabaseContextHolder to set the key of the data source to be used in the dao or service layer, then call mapper layer to do the appropriate operation. It is recommended to do this in the dao layer (of course, you can also use spring aop + custom annotations)

8) A DataSourceTypeAnno annotation is defined to describe the data source used when the method is executed

9) The method used by the DataSourceAspect facet to capture the DataSourceTypeAnno annotation, setting the data source based on the corresponding values within the DataSourceTypeAnno annotation

Note: When operating at the mapper tier, the determineCurrentLookupKey() method is called to get a data source (get the data source: first search in the targetDataSources according to the settings, and then defaultTargetDataSource if not), followed by a database operation.

Maven Dependency

<?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>

        <!-- Use aspectj Time Required -->
        <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 configuration

#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 interface file location
#mybatis.typeAliasesPackage=
# Location of mybatis *.xml file
#mybatis.mapperLocations=

mysql database data preparation

#Create one table for each master and slaver database
CREATE TABLE t_shop (
    id        INT PRIMARY KEY,
    shop_name CHAR(64) NOT NULL
);
# Insert a record into the master library
INSERT INTO t_shop(id, shop_name) VALUES (1, 'MasterShop');
# Insert a record into the slaver Library
INSERT INTO t_shop(id, shop_name) VALUES (1, 'SlaverShop');

Shop Instance Class

package com.example.springboot.multidatasource.domain;

/**
 * Author: Wang Junchao
 * 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

Role: List the key s of the data source

package com.example.springboot.multidatasource.common;

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

DataSourceContextHolder

Role: Builds a DataSourceEnum container and provides a way to set and obtain DataSorceEnum from it

package com.example.springboot.multidatasource.common;

/**
 * Author: Wang Junchao
 * 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

Role: Use DatabaseContextHolder to get DataSoureEnum for the current thread

package com.example.springboot.multidatasource.common;

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

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

MyBatisConfig

Generate two data sources (master DataSource, slaverDataSource) by reading the application.properties file
Construct a dynamic data source dataSource using the two data sources generated above
@Primary: Specifies which implementation classes to choose by default when multiple implementation classes can be injected into the same interface, rather than having the @Autowire annotation error (typically used with multiple data sources)
@Qualifier: An injection with a specified name, used when an interface has multiple implementation classes (in this case, there are three instances of the DataSource type, and DynamicDataSource is also a DataSource, which requires a specified name injection)
@Bean: The name of the generated bean instance is the method name (for example, the name used in the @Qualifier annotation above is the method name of the first two data sources that were injected using the @Bean annotation)
Construct SqlSessionFactory and Transaction Manager from dynamic data sources (the latter can be removed if no transaction is required)

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: Wang Junchao
 * Date: 2017-04-09 08:30
 * All Rights Reserved !!!
 */
@Configuration
@MapperScan(basePackages = "com.example.springboot.multidatasource.mapper")
public class MyBatisConfig {

    /**
     * @return
     * @throws Exception
     * @Primary This comment indicates which implementation class to choose by default when multiple implementation classes can be injected into the same interface, rather than error-reporting the @autowire comment
     */
    @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 Injection by name, usually one of several instances of the same type (for example, multiple instances of DataSource type)
     */
    @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);// This method is AbstractRoutingDataSource's method
        dataSource.setDefaultTargetDataSource(masterDataSource);// The default datasource setting is myTestDbDataSource

        return dataSource;
    }

    /**
     * Create SqlSessionFactory from Data Source
     */
    @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);// Specify the data source (this must exist, otherwise an error will occur)
        // The next two sentences are for *.xml files only, if the XML file is not needed for the entire persistence layer operation (only annotations will do), they are not added
//        factoryBean.setTypeAliasesPackage(typeAliasesPackage);//Specify base package
//        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));//

        return factoryBean.getObject();
    }

    /**
     * Configure Transaction Manager
     */
    @Bean
    public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {
        return new DataSourceTransactionManager(dataSource);
    }
}

ShopMapper

Data Mapping File

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: Wang Junchao
 * 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: Wang Junchao
 * 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: Wang Junchao
 * 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

Program Startup Entry

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: Wang Junchao
 * 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 Data Source Type Comment

@Retention(RetentionPolicy.RUNTIME) says this comment is visible at run time
@Target(ElementType.METHOD)//Comments can be used in methods

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;

/**
 * Data Source Type Annotation
 *
 * Author: Wang Junchao
 * Date: 2017-04-09 09:50
 * All Rights Reserved !!!
 */
@Retention(RetentionPolicy.RUNTIME) // Visible at runtime
@Target(ElementType.METHOD) // Notes can be used in methods
public @interface DataSourceTypeAnno {
    DataSourceEnum value() default DataSourceEnum.master;
}

DataSourceAspect data source facets

A method for capturing and switching data source annotations based on the type of data source on the annotation

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: Wang Junchao
 * 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;
    }
}

results of enforcement