Introduction to off the shelf tools

MyCat

Active and good performance open source database middleware. It is a transparent database agent, which needs to ensure the high availability of services in the production environment.

ShardingSphere

ShardingSphere is composed of JDBC, Proxy and Sidecar (under planning), which can be deployed independently and support mixed deployment. ShardingSphere Proxy has the same positioning as MyCat, while ShardingSphere JDBC provides additional services in the JDBC layer of Java.

It is also very convenient for SpringBoot to integrate shardingsphere JDBC. It can be used immediately after introducing packages and writing configuration documents. However, there is a little problem in the transaction, that is, after the write operation in the transaction, the subsequent read operations are read from the main database; In other words, before the write operation, the read in the transaction is still read from the library, which may cause dirty write.

Using the Mybatis interceptor

Most of the read-write separation at the code level intercepts sql and redirects the database by judging the read-write type of sql. Shardingsphere JDBC is no exception.

Mybatis allows us to customize the Interceptor. We need to implement the Interceptor interface and add the @ Intercepts annotation on the custom Interceptor class. In the @ Intercepts annotation, we can specify the interception method.

Open dry

Multiple data sources

Since the separation of reading and writing is carried out at the code level, there must be reading and writing libraries. The multi data source function is used here. Instead of using the default multi data source generation method of mybatis / mybatis plus, the multi data source is configured by yourself. In fact, you can also use the default generation method. The purpose of writing it by yourself is to better understand the principle inside. [the format configured in the configuration file is configured according to the format configured by mybatis plus multiple data sources]

code

Multi data source configuration

/**
 * Main database
 */
@Bean
@ConfigurationProperties("spring.datasource.dynamic.datasource.master")
public DataSource masterDataSource(){
    log.info("Load master data source master DataSource.");
    return DruidDataSourceBuilder.create().build();
}

/**
 * Database slave Library
 */
@Bean
@ConfigurationProperties("spring.datasource.dynamic.datasource.slave1")
public DataSource slave1DataSource(){
    log.info("Load from data source slave1 DataSource.");
    return DruidDataSourceBuilder.create().build();
}

/**
 * Dynamic data source
 */
@Bean
public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                      @Qualifier("slave1DataSource") DataSource slave1DataSource) {
    log.info("load[masterDataSource-slave1DataSource]Set as dynamic data source DynamicDataSource.");
    Map<Object, Object> targetDataSources = new HashMap<>(2);
    targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
    targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);

    DynamicDataSource dynamicDataSource = new DynamicDataSource();
    dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
    dynamicDataSource.setTargetDataSources(targetDataSources);

    return dynamicDataSource;
}

DBTypeEnum

public enum DBTypeEnum {
    /**Main library*/
    MASTER,

    /**From library 1*/
    SLAVE1
}

DynamicDataSource

The key of the data source is specified here. Before the execution of each sql sentence, the determineCurrentLookupKey will be executed to obtain the data source. DbContextHolder.get() is to get the key of the specified data source in the current thread, which will be specified in the custom interceptor.

public class DynamicDataSource extends AbstractRoutingDataSource {

    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DbContextHolder.get();
    }
}

public class DbContextHolder {
    private static final ThreadLocal<DBTypeEnum> CONTEXT_HOLDER = new ThreadLocal<>();
    private static final AtomicInteger COUNTER = new AtomicInteger(-1);

    public static void set(DBTypeEnum dbType) {
        log.debug("Switch to{}", dbType.name());
        CONTEXT_HOLDER.set(dbType);
    }

    public static DBTypeEnum get() {
        return CONTEXT_HOLDER.get();
    }

    public static DBTypeEnum getMaster() {
        return DBTypeEnum.MASTER;
    }

    public static DBTypeEnum getSlave() {
        // Multiple slave libraries can be polled
        int index = COUNTER.getAndIncrement() % 2;
        if (COUNTER.get() > 9999) {
            COUNTER.set(-1);
        }
        return DBTypeEnum.SLAVE1;
    }

}

Interceptor

In the previous step, we defined multiple data sources and set the basis for data source selection (DbContextHolder.get()). This step is to set this basis in the interceptor according to certain rules.

code

Interceptor

@Intercepts({
        @Signature(type = Executor.class, method = "update", args = {
                MappedStatement.class, Object.class }),
        @Signature(type = Executor.class, method = "query", args = {
                MappedStatement.class, Object.class, RowBounds.class,
                ResultHandler.class }),
        @Signature(type = Executor.class, method = "close", args = {boolean.class})
})
public class DbSelectorInterceptor implements Interceptor {
    private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";

    private static final Map<String, DBTypeEnum> CACHE_MAP = new ConcurrentHashMap<>();

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        String methodName = invocation.getMethod().getName();
        String closeMethodName = "close";
        boolean synchronizationActive = TransactionSynchronizationManager.isSynchronizationActive();
        DBTypeEnum databaseType = null;
        if(!synchronizationActive && !closeMethodName.equals(methodName)) {
            Object[] objects = invocation.getArgs();
            MappedStatement ms = (MappedStatement) objects[0];

            if((databaseType = CACHE_MAP.get(ms.getId())) == null) {
                //Reading method
                if(ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
                    //! selectKey is the self increment ID query primary key (SELECT LAST_INSERT_ID()) method, using the main database
                    if(ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
                        databaseType = DbContextHolder.getMaster();
                    } else {
                        BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]);
                        String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " ");
                        if(sql.matches(REGEX)) {
                            databaseType = DbContextHolder.getMaster();
                        } else {
                            databaseType = DbContextHolder.getSlave();
                        }
                    }
                }else{
                    databaseType = DbContextHolder.getMaster();
                }
                log.debug("Setting method[{}] use [{}] Strategy, SqlCommandType [{}]..", ms.getId(), databaseType.name(), ms.getSqlCommandType().name());
                CACHE_MAP.put(ms.getId(), databaseType);
            }

        } else {
            if (synchronizationActive) {
                log.debug("affair use [{}] Strategy", DBTypeEnum.MASTER.name());
            } else {
                log.debug("close Method reset to [{}] Strategy", DBTypeEnum.MASTER.name());
            }
            databaseType = DbContextHolder.getMaster();
        }
        DbContextHolder.set(databaseType);

        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof Executor) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }
}

This code is relatively long, but there are only three core logics:

1. If the transaction is started, the master database is used;
2. If the connection is currently closed, reset to the main library; [ps: what will happen if you forget not to add it]
3. Other situations are judged according to the keywords select, update and delete in the sql statement;

Configuring Interceptors

Here, the interceptor is configured based on mybatis plus.

    @Bean
    public MybatisSqlSessionFactoryBean sqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource,
                                               @Qualifier("slave1DataSource") DataSource slave1DataSource) throws Exception {
        log.info("Custom configuration mybatis-plus of SqlSessionFactory.");

        MybatisSqlSessionFactoryBean mybatisPlus = new MybatisSqlSessionFactoryBean();
        mybatisPlus.setDataSource(myRoutingDataSource(masterDataSource, slave1DataSource));

        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        ///Custom configuration
        mybatisPlus.setConfiguration(configuration);

         set up mapper.xml Path to file
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        org.springframework.core.io.Resource[] resource = resolver.getResources("classpath:mapper/webservice/*.xml");
        mybatisPlus.setMapperLocations(resource);
        //Add a plug-in to SqlSessionFactory to take effect
        mybatisPlus.setPlugins(paginationInterceptor(), new DbSelectorInterceptor());
        globalConfig.setMetaObjectHandler(this);
        mybatisPlus.setGlobalConfig(globalConfig);
        return mybatisPlus;
    }

Actually, it refers to com baomidou. mybatisplus. autoconfigure. Mybatisplusautoconfiguration #sqlsessionfactory, weave DbSelectorInterceptor into.

above