[springboot DB series] mybatis realizes multi data source switching based on AOP


[springboot DB series] mybatis realizes multi data source switching based on AOP

[springboot DB series] mybatis realizes multi data source switching based on abstractroutingdatasource and AOP

The previous blog post introduced the configuration of multiple data sources in mybatis. In short, one data source is specified by one configuration, and mapper of different data sources is specified separately. This article will introduce another way, with the help ofAbstractRoutingDataSourceTo achieve dynamic switching of data sources, and through user-defined annotation mode + AOP to achieve data source specification

<!– more –>

1. Environmental preparation

1. Database related

Take MySQL as an example for demonstration. Because multiple data sources are needed, the simplest case is multiple logical libraries on a physical library. This paper is based on the operation of native mysql

Create databasetestAndstory, there is a table under both librariesmoney(the same name is the same as the structure table, but the data is different.)

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Name ` varchar (20) not null default '' comment 'user name',
  `Money ` int (26) not null default '0' comment 'money',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_ at` timestamp NOT NULL DEFAULT CURRENT_ Timestamp comment 'creation time',
  `update_ at` timestamp NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ Timestamp comment 'update time',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)

[springboot DB series] mybatis realizes multi data source switching based on AOP

2. Project environment

With the help ofSpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEADevelopment

Here’s the corepom.xml(source code can be obtained at the end of the article)


Profile informationapplication.yml

#Database related configuration, please note that this configuration is inconsistent with the previous blog post, and the reason will be given later
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://
        username: root
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://
        username: root

#Log related
    root: info
          core: debug

2. Multi data source configuration

It is strongly recommended that those who have not read the previous blog post should first take a look at the configuration and use of mybatis multi data sources in the previous blog post [DB series]

Before starting, it is necessary to review the main problems of mybatis multi data source configuration

  • Adding one more data source requires more configuration
  • Mapper files need to be subcontracted, which is a potential pitfall for developers

For the above, the purpose we want to achieve is also very clear, to solve the above two problems

1. AbstractRoutingDataSource

The key to realize multiple data sources, from the name can be seen, it is used to route specific data sources, its core code is as follows

//Returns the selected data source
protected DataSource determineTargetDataSource() {
    Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
    Object lookupKey = this.determineCurrentLookupKey();
    DataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey);
    if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
        dataSource = this.resolvedDefaultDataSource;

    if (dataSource == null) {
        throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
    } else {
        return dataSource;

protected abstract Object determineCurrentLookupKey();

amongdetermineCurrentLookupKeyWe need to implement it ourselves, which data source to return

2. Implementation of dynamic data source

We create oneDynamicDataSourceAbstract class inherited from above

public class DynamicDataSource extends AbstractRoutingDataSource {
    protected Object determineCurrentLookupKey() {
        String dataBaseType = DSTypeContainer.getDataBaseType();
        return dataBaseType;

Pay attention to the above implementation method, how to determine the specific return data source?

One way to consider this is to add an annotation to the mapper file@DS, which specifies the corresponding data source, and then determines the specific data source to be executed during execution;

Because the above implementation does not pass parameters, we consider using thread context to pass information

public class DSTypeContainer {
    private static final ThreadLocal<String> TYPE = new ThreadLocal<String>();

    public static String defaultType;

     *Set the data source type to the current procedure
     * @param dataBase
    public static void setDataBaseType(String dataBase) {
        if (StringUtils.isEmpty(dataBase)) {
            dataBase = defaultType;
        System.err.println ("[change current data source to]:" + database) ";

     *Get data source type
     * @return
    public static String getDataBaseType() {
        String database = TYPE.get();
        System.err.println ("[get the type of current data source]:" + database) ";
        return database;

     *Clear data type
    public static void clearDataBaseType() {

3. Annotation implementation

Although the data source selection strategy is given above, it can be obtained from the thread contextDataBaseTypeBut how to plug this data into the thread context?

The solution we need to support must be to intercept SQL before it is executed and write itDataBaseTypeSo we can considerxxxMapperInterface, defines an annotation, intercepts its access execution, obtains the data source specified in the annotation before execution, and writes the context clearly after execution

A basic data source annotation@DS

public @interface DS {
    String value() default "";

Annotation interception

public class DsAspect {

    //Intercepts method calls with DS annotations on a class
    public Object dsAround(ProceedingJoinPoint proceedingJoinPoint) throws Throwable {
        DS ds = (DS) proceedingJoinPoint.getSignature().getDeclaringType().getAnnotation(DS.class);
        try {
            //Write thread context, which DB should be used
            DSTypeContainer.setDataBaseType(ds == null ? null : ds.value());
            return proceedingJoinPoint.proceed();
        } finally {
            //Clear context information

4. Registration configuration

Next is the key data source configuration. We need to register nowDynamicDataSourceAnd then offer it to youSqlSessionFactoryHere, we hope to solve the problem that we don’t need to modify the configuration even if we add more data sources, so we adjusted the configuration structure of data sources

        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://
        username: root
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://
        username: root

Then give a configuration class to load the above configurationDSProperties

@ConfigurationProperties(prefix = "spring.dynamic")
public class DSProperties {
    private Map<String, DataSourceProperties> datasource;

And then ourAutoConfigurationClass implementation is relatively clear (it is recommended to compare the configuration class in the last blog post)

@MapperScan(basePackages = {"com.git.hui.boot.multi.datasource.mapper"},
        sqlSessionFactoryRef = "SqlSessionFactory")
public class DynamicDataSourceConfig {

    @Bean(name = "dynamicDataSource")
    public DynamicDataSource DataSource(DSProperties dsProperties) {
        Map targetDataSource = new HashMap<>(8);
        dsProperties.getDatasource().forEach((k, v) -> {
            targetDataSource.put(k, v.initializeDataSourceBuilder().build());
        DynamicDataSource dataSource = new DynamicDataSource();

        //Set the default database. The following assignment method is not recommended. It's just for convenience
        DSTypeContainer.defaultType = (String) targetDataSource.keySet().stream().findFirst().get();
        return dataSource;

    @Bean(name = "SqlSessionFactory")
    public SqlSessionFactory test1SqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/*/*.xml"));
        return bean.getObject();

5. Database entity class

Project structure chart

[springboot DB series] mybatis realizes multi data source switching based on AOP

All the previous things are related to general configuration. Next, the specific database operation related entity class and mapper class are given

Database entity classStoryMoneyEntity

public class StoryMoneyEntity {
    private Integer id;

    private String name;

    private Long money;

    private Integer isDeleted;

    private Timestamp createAt;

    private Timestamp updateAt;

Mapper defines the interfaceStoryMoneyMapper + TestMoneyMapper

@DS(value = "story")
public interface StoryMoneyMapper {
    List<StoryMoneyEntity> findByIds(List<Integer> ids);

@DS(value = "test")
public interface TestMoneyMapper {
    List<TestMoneyEntity> findByIds(List<Integer> ids);

Corresponding XML file

<?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.git.hui.boot.multi.datasource.mapper.StoryMoneyMapper">
    <resultMap id="BaseResultMap" type="com.git.hui.boot.multi.datasource.entity.StoryMoneyEntity">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="money" property="money" jdbcType="INTEGER"/>
        <result column="is_deleted" property="isDeleted" jdbcType="TINYINT"/>
        <result column="create_at" property="createAt" jdbcType="TIMESTAMP"/>
        <result column="update_at" property="updateAt" jdbcType="TIMESTAMP"/>
    <sql id="money_po">
      id, `name`, money, is_deleted, create_at, update_at

    <select id="findByIds" parameterType="list" resultMap="BaseResultMap">
        <include refid="money_po"/>
        from money where id in
        <foreach item="id" collection="list" separator="," open="(" close=")" index="">

<! -- omit the second XML file, and the content is basically the same -- >

Encapsulation class of database operationStoryMoneyRepository + TestMoneyRepository

public class StoryMoneyRepository {
    private StoryMoneyMapper storyMoneyMapper;

    public void query() {
        List<StoryMoneyEntity> list = storyMoneyMapper.findByIds(Arrays.asList(1, 1000));

public class TestMoneyRepository {
    private TestMoneyMapper testMoneyMapper;

    public void query() {
        List<TestMoneyEntity> list = testMoneyMapper.findByIds(Arrays.asList(1, 1000));

6. Testing

Finally, under a simple test, whether the dynamic data source switching is effective

public class Application {

    public Application(StoryMoneyRepository storyMoneyRepository, TestMoneyRepository testMoneyRepository) {

    public static void main(String[] args) {

The output log is as follows

[springboot DB series] mybatis realizes multi data source switching based on AOP

6. Summary

In this paper, a method based onAbstractRoutingDataSource + AOPThe implementation of dynamic data source switching uses the following three knowledge points

  • AbstractRoutingDataSourceRealize dynamic data source switch
  • custom@DSAnnotation + AOP specifies the data source corresponding to mapper
  • ConfigurationPropertiesThe method supports adding data sources without modifying the configuration

2. Others

0. Project

Related blog

  • [DB series] mybatis multi data source configuration and use
  • The configuration and use of multiple data sources in jdbctemplate
  • [DB series] automatic generation of mybatis plus code
  • [DB series] mybatisplus integration
  • [DB series] mybatis + annotation integration
  • [DB series] mybatis + XML integration

Source code

  • Engineering: https://github.com/liuyueyi/spring-boot-demo
  • Source code: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/109-multi-datasource-mybatis

1. A grey blog

The above content is purely a statement of one family. Due to limited personal ability, it is inevitable that there are omissions and mistakes. If you find bugs or have better suggestions, you are welcome to criticize and correct them

The following is a gray personal blog, recording all the learning and work of the blog, welcome to visit

  • A grey blog personal blog https://blog.hhui.top
  • A grey blog spring special blog http://spring.hhui.top
  • WeChat official account: a gray blog

[springboot DB series] mybatis realizes multi data source switching based on AOP