Implementation of database operation read-write separation based on Spring and Mybatis interceptor

First, configure the master-slave synchronization of the database:

The last article wrote: https://www.cnblogs.com/xuyiqing/p/10647133.html

 

Why should we separate reading from writing?

In general, most of the read operations of Web applications are much more than the write operations

In order to improve efficiency, we can consider the separation of reading and writing

 

DBProxy middleware can be used for read-write separation, which is not discussed here

It's using the Mabatis interceptor

The general idea is:

Get the SQL statement to analyze. If it is select, allocate the data from the database

Instead, assign the primary database data source

 

Spring override route:

package org.dreamtech.o2o.dao.split;

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

/**
 * Data source route rewriting for read-write separation
 * 
 * @author Xu Yiqing
 *
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceHolder.getDbType();
    }

}

 

Custom tool class:

package org.dreamtech.o2o.dao.split;

/**
 * Assist in read-write separation
 * 
 * @author Xu Yiqing
 *
 */
public class DynamicDataSourceHolder {
    private static ThreadLocal<String> contextHolder = new ThreadLocal<String>();
    public static final String DB_MASTER = "master";
    public static final String DB_SLAVE = "slave";

    /**
     * Get dbType
     * 
     * @return dbType
     */
    public static String getDbType() {
        String db = contextHolder.get();
        if (db == null) {
            db = DB_MASTER;
        }
        return db;
    }

    /**
     * Set dbType of thread
     * 
     * @param str
     *            type
     */
    public static void setDbType(String str) {
        contextHolder.set(str);
    }

    /**
     * Clean up connection types
     */
    public static void clearDBType() {
        contextHolder.remove();
    }

}

 

Mybatis filter:

package org.dreamtech.o2o.dao.split;

import java.util.Locale;
import java.util.Properties;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.keygen.SelectKeyGenerator;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import org.springframework.transaction.support.TransactionSynchronizationManager;

/**
 * Mybatis Interceptor to separate reading from writing
 * 
 * @author Xu Yiqing
 *
 */
@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 }) })
public class DynamicDataSourceInterceptor implements Interceptor {
    private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";

    /**
     * Core method
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
        Object[] objects = invocation.getArgs();
        MappedStatement ms = (MappedStatement) objects[0];
        String lookupKey = DynamicDataSourceHolder.DB_MASTER;
        if (synchronizationActive != true) {
            if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
                if (ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
                    lookupKey = DynamicDataSourceHolder.DB_MASTER;
                } else {
                    BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]);
                    String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " ");
                    if (sql.matches(REGEX)) {
                        lookupKey = DynamicDataSourceHolder.DB_MASTER;
                    } else {
                        lookupKey = DynamicDataSourceHolder.DB_SLAVE;
                    }
                }
            }
        } else {
            lookupKey = DynamicDataSourceHolder.DB_MASTER;
        }
        DynamicDataSourceHolder.setDbType(lookupKey);
        return invocation.proceed();
    }

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

    @Override
    public void setProperties(Properties arg0) {
    }

}

 

 

To configure the address of the primary and secondary servers:

For convenience, their user name and password are the same

jdbc.properties:

jdbc.driver=com.mysql.jdbc.Driver
jdbc.master.url=jdbc:mysql://192.168.111.134:3306/o2o?useUnicode=true&characterEncoding=utf8
jdbc.slave.url=jdbc:mysql://192.168.111.135:3306/o2o?useUnicode=true&characterEncoding=utf8
jdbc.username=work
jdbc.password=xuyiqing

 

mybatis-config.xml: mybatis configuration file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
......
    <!-- custom interceptor  -->
    <plugins>
        <plugin interceptor="org.dreamtech.o2o.dao.split.DynamicDataSourceInterceptor" />
    </plugins>
......
</configuration>

 

spring-dao.xml configuration file:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-4.3.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">
......
    <context:property-placeholder location="classpath:jdbc.properties" />
    <!-- data source -->
    <bean id="abstractDataSource" abstract="true"
        class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="maxPoolSize" value="30" />
        <property name="minPoolSize" value="10" />
        <property name="autoCommitOnClose" value="false" />
        <property name="checkoutTimeout" value="10000" />
        <property name="acquireRetryAttempts" value="2" />
    </bean>
    <!-- Master database -->
    <bean id="master" parent="abstractDataSource">
        <property name="driverClass" value="${jdbc.driver}" />
        <property name="jdbcUrl" value="${jdbc.master.url}" />
        <property name="user" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>
    <!-- From database -->
    <bean id="slave" parent="abstractDataSource">
        <property name="driverClass" value="${jdbc.driver}" />
        <property name="jdbcUrl" value="${jdbc.slave.url}" />
        <property name="user" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>
    <!-- Configure dynamic data sources -->
    <bean id="dynamicDataSource" class="org.dreamtech.o2o.dao.split.DynamicDataSource">
        <property name="targetDataSources">
            <map>
                <entry value-ref="master" key="master"></entry>
                <entry value-ref="slave" key="slave"></entry>
            </map>
        </property>
    </bean>
    <!-- Lazy load data source -->
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <property name="targetDataSource">
            <ref bean="dynamicDataSource" />
        </property>
    </bean>
......
</beans>

Tags: Java JDBC Apache Mybatis Spring

Posted on Sun, 01 Dec 2019 20:09:31 -0500 by ajsuk