spring boot project integrates mybatis plus to adapt multiple data sources + reflection to obtain annotation data

Many times, our projects may not use a database or a type of database. According to the business, we can be divided into the following types: single database, single data source, multi database, single data source, multi database, multi data source. In this case, we can use dynamic datasource spring boot starter, which is a springboot based initiator for rapid integration of multiple data sources.

characteristic

• It supports data source grouping, and is suitable for multiple scenarios. It is a pure multi database read-write separation, one master multi slave hybrid mode.
• Support database sensitive configuration information encryption (ENC).
• Each database supports independent initialization of table structure schema and database.
• Support startup without data source and lazy loading of data source (create connection when necessary).
• Support user-defined annotation and inherit DS(3.2.0 +).
• Provide and simplify rapid integration of Druid, HikariCp, mybatis plus, Quartz, ShardingJdbc, P6sy, Jndi components.
• Provide custom data source scheme (such as loading all from database).
• Provide the scheme of dynamically adding and removing data sources after the project is started.
• Provide pure read-write separation scheme in Mybatis environment.
• Provides a scheme for parsing data sources using spel dynamic parameters. Built in spel, session and header, support customization.
• Support nested switching of multi-layer data sources. (ServiceA >>> ServiceB >>> ServiceC).
• Provides a distributed transaction scheme based on seata. Attachment: native spring transactions are not supported.
• Provide local multi data source transaction scheme. Attachment: native spring transactions are not supported.

appointment

1. This framework only does the core thing of switching data sources, and does not limit your specific operations. You can do any CRUD after switching data sources.
2. Profile all dashes_ The header of the split data source is the name of the group, and the data sources with the same group name will be placed under a group.
3. The switching data source can be a group name or a specific data source name. The group name is switched by load balancing algorithm.
4. The default data source name is master. You can use spring datasource. dynamic. Modify the primary node.
5. Annotations on methods take precedence over annotations on classes.
6. DS supports inheriting DS on abstract classes, but does not support inheriting DS on interfaces.

usage method

Introduce dependency

// orm framework mybatis plus
implementation group: 'com.baomidou', name: 'mybatis-plus-boot-starter', version: '3.4.3.1'
// The multi data source core relies on dynamic datasource spring boot starter
implementation group: 'com.baomidou', name: 'dynamic-datasource-spring-boot-starter', version: '3.4.0'

// Various databases
// dm
compile group: 'com.dameng', name: 'Dm8JdbcDriver17', version: '8.1.1.49'
// compile('com.dameng:Dm7JdbcDriver17:7.6.0.77')
// NPC Jincang
implementation 'com.kingbase8:kingbase8:8.2.0'
// Avatar database
implementation 'com.shentong:stoscar:1.0'
//mysql
implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.25'

Configure data sources

If the multi data source mode is used, it is in spring The connection pool configuration under datasource needs to be transferred to spring datasource. It only takes effect under dynamic.

hikari in spring Configuration under datasource and spring datasource. Dynamic is slightly different, which should be noted.

# configuration file
spring:
  datasource:
    # Multi data source configuration
    dynamic:
      # Link pool configuration
      hikari:
        #Minimum number of free connections
        min-idle: 5
        #Connections returned from the pool are automatically committed by default
        is-auto-commit: true
        #Maximum idle connection time, 10 seconds
        idle-timeout: 10000
        #Connection pool name
        pool-name: CsseHikariCP
        #Maximum lifetime of connections in the pool
        max-lifetime: 1800000
        #Timeout for database connection
        connection-timeout: 30000
        #Test SQL
        connection-test-query: SELECT 1
      primary: dm #Set the default data source or data source group. The default value is master
      strict: true #Strictly match the data source. The default is false True throws an exception when it does not match the specified data source, and false uses the default data source
      datasource:
        dm:
          url: jdbc:dm://192.168.40.205
          username: xxxxx
          password: xxxxx
          driver-class-name: dm.jdbc.driver.DmDriver
        kingbases:
          url: jdbc:kingbase8://192.168.17.151:54321/PLATFORM_APP_STORE
          username: xxxxx
          password: xxxxx
          driver-class-name: com.kingbase8.Driver
        oscar:
          url: jdbc:oscar://192.168.17.40:2003/OSRDB?serverTimezone=UTC&useSSL=FALSE
          username: xxxxx
          password: xxxxx
          driver-class-name: com.oscar.Driver
        mysql:
          url: jdbc:mysql://192.168.40.206:3306/am?characterEncoding=UTF-8&serverTimezone=UTC
          username: xxxxx
          password: xxxxx
          driver-class-name: com.mysql.cj.jdbc.Driver
          
       #...... ellipsis
       #The above will configure a default library master, and there are two sub libraries under a group slave_ 1,slave_ two
# Multi master multi slave pure multi Library (remember to set the primary) hybrid configuration
spring:                               spring:                               spring:
  datasource:                           datasource:                           datasource:
    dynamic:                              dynamic:                              dynamic:
      datasource:                           datasource:                           datasource:
        master_1:                             mysql:                                master:
        master_2:                             oracle:                               slave_1:
        slave_1:                              sqlserver:                            slave_2:
        slave_2:                              postgresql:                           oracle_1:
        slave_3:                              h2:                                   oracle_2:

Specific usage

For scenarios without multi database and multi-mode support, you can directly change the data source configuration. If you need to use multi database and multi-mode in the scenario and want to operate through annotation or user-defined configuration, you can use the following two modes

Use @ DS to switch data sources

@DS can be annotated on methods or classes. In principle, method annotations take precedence over class annotations.

annotation	result
No @ DS	Default data source
@DS("dsName")	dsName can be a group name or a specific library name

@Service
@DS("slave")
public class UserServiceImpl implements UserService {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  public List selectAll() {
    return  jdbcTemplate.queryForList("select * from user");
  }
  
  @Override
  @DS("slave_1")
  public List selectByCondition() {
    return  jdbcTemplate.queryForList("select * from user where age >10");
  }
}

Custom MybatisPlusConfig is recommended

By customizing MybatisPlusConfig, we can realize the dynamic splicing of database patterns, so as to better realize multi tenant and multi data source scenarios

package com.csse.platform.appstore.api.config.mybatisplus;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.csse.platform.appstore.api.context.ApiContext;
import com.google.common.collect.Lists;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import org.apache.commons.io.FilenameUtils;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.StandardEnvironment;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.util.ClassUtils;

import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * @Author : Master Xu who wrote the code
 * @Company : China Soft Information System Engineering Co., Ltd
 * @Date : Created in 14:32 2020/4/22
 */
@Configuration
@MapperScan("com.csse.platform.appstore.api.mapper")
public class MybatisPlusConfig {
    private static final String SYSTEM_TENANT_ID = "tenant_id";
    private static final List<String> IGNORE_TENANT_TABLES = Lists.newArrayList("tenant");
    static final String DEFAULT_RESOURCE_PATTERN = "*.class";
    @Value("${spring.datasource.dynamic.primary}")
    private String primaryDbType;
    @Value("${database.databaseSchema}")
    private String databaseSchema;
    @Autowired
    private ApiContext apiContext;


    /**
     * Customize MybatisPlusInterceptor
     * <p>
     * Implement the above-mentioned support for injecting tenant id, intercepting and modifying sql, and multiple data source paging modes
     *
     * @return MybatisPlusInterceptor
     */
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();

        // multi-tenancy 
        mybatisPlusInterceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
            @Override
            public Expression getTenantId() {
                // Take the service provider ID of the current request from the current system context and inject it into SQL through the parser.
                Long currentTenantId = apiContext.getCurrentTenantId();
                if (null == currentTenantId) {
                    throw new RuntimeException("#1129 getCurrentTenantId error.");
                }
                return new LongValue(currentTenantId);
            }

            @Override
            public String getTenantIdColumn() {
                return SYSTEM_TENANT_ID;
            }

            // This is the default method, which returns false by default, indicating that all tables need to spell multi tenant conditions
            @Override
            public boolean ignoreTable(String tableName) {
                // Ignore some tables: for example, the Tenant table itself does not need to perform such processing.
                return IGNORE_TENANT_TABLES.stream().anyMatch((e) -> e.equalsIgnoreCase(tableName));
            }
        }));

        // Dynamic splicing
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        HashMap<String, TableNameHandler> map = new HashMap<String, TableNameHandler>(2) {{
            // Operation sheet
            // put("platform_app_classify", (sql, tableName) -> { return "PLATFORM_APP_STORE." + tableName; });
            // Traverse all tables
            List<String> tables = new ArrayList<>();
            tables.addAll(listByClassAnnotation("com.csse.platform.appstore.api.entity"));
            tables.addAll(listByClassAnnotation("com.csse.platform.appstore.api.entity.view"));
            tables.forEach(table -> put(table, (sql, tableName) -> {
                return databaseSchema + tableName;
            }));
        }};
        dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map);
        mybatisPlusInterceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);

        // paging
        // If the paging plug-in is used, please add TenantLineInnerInterceptor first and then paginationinnerinterceptor
        // If paging plug-in is used, MybatisConfiguration#useDeprecatedExecutor = false must be set
        if (primaryDbType.equals(DbType.DM.getDb())) {
            mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.DM));
        } else if (primaryDbType.equals(DbType.KINGBASE_ES.getDb())) {
            mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.KINGBASE_ES));
        } else if (primaryDbType.equals(DbType.OSCAR.getDb())) {
            mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.OSCAR));
        } else if (primaryDbType.equals(DbType.MYSQL.getDb())) {
            mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        }
        return mybatisPlusInterceptor;
    }

    /**
     * Auto fill function
     *
     * @return globalConfig
     */
    @Bean
    public GlobalConfig globalConfig() {
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setMetaObjectHandler(new MetaHandler());
        return globalConfig;
    }

    /**
     * Scan all packages and obtain the table name marked by @ TableName in the class through reflection
     *
     * @param packageName Package name
     * @return List<String> listByClassAnnotation
     */
    public static List<String> listByClassAnnotation(String packageName) {
        List<String> tableNameList = new ArrayList<>();
        try {
            String packageSearchPath = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX +
                    resolveBasePackage(packageName) + File.separator + DEFAULT_RESOURCE_PATTERN;
            Resource[] resources = new PathMatchingResourcePatternResolver().getResources(packageSearchPath);
            for (Resource resource : resources) {
                URL url = resource.getURL();
                File file = new File(url.getPath());
                String cname = packageName + "." + FilenameUtils.getBaseName(file.getName());
                Class<?> aClass = Class.forName(cname);
                if (aClass.isAnnotationPresent(TableName.class)) {
                    TableName declaredAnnotation = aClass.getDeclaredAnnotation(TableName.class);
                    tableNameList.add(declaredAnnotation.value());
                }
            }
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
        return tableNameList;
    }

    public static String resolveBasePackage(String basePackage) {
        return ClassUtils.convertClassNameToResourcePath(new StandardEnvironment().resolveRequiredPlaceholders(basePackage));
    }

}