Spring+MyBatis implementation of database read and write separation scheme

Recommended fourth: github.com/shawntime/s…

Plan 1

The MyBatis configuration file is used to create the read/write separation of the two DataSource, and the mapperLocations property of each SqlSessionFactoryBean object is used to define the two read/write configuration files. Configure all read operations in the read file and all write operations in the write file.

Advantage: Simple implementation
Disadvantages: maintenance trouble, need to modify the original XML file, do not support more read, not easy to expand
implementation

<bean id="abstractDataSource" abstract="true" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
      destroy-method="close">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <! -- Configure a timeout for obtaining a connection -->
    <property name="maxWait" value="60000"/>
    <! -- Configure how often to check for idle connections that need to be closed in milliseconds -->
    <property name="timeBetweenEvictionRunsMillis" value="60000"/>
    <! -- Configure the minimum duration of a connection in the pool, in milliseconds -->
    <property name="minEvictableIdleTimeMillis" value="300000"/>
    <property name="validationQuery" value="SELECT 'x'"/>
    <property name="testWhileIdle" value="true"/>
    <property name="testOnBorrow" value="false"/>
    <property name="testOnReturn" value="false"/>
    <! -- Open PSCache and specify the size of the PSCache on each connection -->
    <property name="poolPreparedStatements" value="true"/>
    <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
    <property name="filters" value="config"/>
    <property name="connectionProperties" value="config.decrypt=true" />
</bean>

<bean id="readDataSource" parent="abstractDataSource">
    <! Url, user, password -->
    <property name="url" value="${read.jdbc.url}"/>
    <property name="username" value="${read.jdbc.user}"/>
    <property name="password" value="${read.jdbc.password}"/>
    <! -- configure initialization size, min, Max -->
    <property name="initialSize" value="${read.jdbc.initPoolSize}"/>
    <property name="minIdle" value="10"/>
    <property name="maxActive" value="${read.jdbc.maxPoolSize}"/>
</bean>

<bean id="writeDataSource" parent="abstractDataSource">
    <! Url, user, password -->
    <property name="url" value="${write.jdbc.url}"/>
    <property name="username" value="${write.jdbc.user}"/>
    <property name="password" value="${write.jdbc.password}"/>
    <! -- configure initialization size, min, Max -->
    <property name="initialSize" value="${write.jdbc.initPoolSize}"/>
    <property name="minIdle" value="10"/>
    <property name="maxActive" value="${write.jdbc.maxPoolSize}"/>
</bean>

<bean id="readSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <! -- Instantiate sqlSessionFactory using the data source and SQL mapping file -->
    <property name="dataSource" ref="readDataSource"/>
    <property name="mapperLocations" value="classpath:mapper/read/*.xml"/>
</bean>

<bean id="writeSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <! -- Instantiate sqlSessionFactory using the data source and SQL mapping file -->
    <property name="dataSource" ref="writeDataSource"/>
    <property name="mapperLocations" value="classpath:mapper/write/*.xml"/>
</bean>
Copy the code

Scheme 2

By Spring AOP implement, speaking, reading and writing in the business layer separation, before calling the DAO layer definition section, using the Spring AbstractRoutingDataSource solve the problem of multiple data sources, dynamically select the data source

Advantages: The annotated method is used to configure the data source on each DAO method. The original code has less change momentum, is easy to expand, and supports more reading
Disadvantages: Annotations need to be configured on each DAO method, manual administration, error prone
implementation

// Define enumeration type, read and write
public enum DynamicDataSourceGlobal {
    READ, WRITE;
}
Copy the code

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/** * RUNTIME * Define annotations * The compiler will record annotations in class files, and the VM will keep annotations at RUNTIME, so they can be read reflectively. *@author shma1664
 *
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface DataSource {

    public DynamicDataSourceGlobal value(a) default DynamicDataSourceGlobal.READ;

}
Copy the code

User: mashaohua * Date: 2016-07-07 13:35 * Desc: */
public class DynamicDataSourceHolder {

    private static final ThreadLocal<DynamicDataSourceGlobal> holder = new ThreadLocal<DynamicDataSourceGlobal>();

    public static void putDataSource(DynamicDataSourceGlobal dataSource){
        holder.set(dataSource);
    }

    public static DynamicDataSourceGlobal getDataSource(a){
        return holder.get();
    }

    public static void clearDataSource(a) { holder.remove(); }}Copy the code

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

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ThreadLocalRandom;
import java.util.concurrent.atomic.AtomicLong;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

/** * Created by IDEA * User: mashaohua * Date: 2016-07-14 10:56 * Desc: Separation of read and write for dynamic data source */
public class DynamicDataSource extends AbstractRoutingDataSource {

    private Object writeDataSource; // Write the data source

    private List<Object> readDataSources; // Multiple read data sources

    private int readDataSourceSize; // Number of read data sources

    private int readDataSourcePollPattern = 0; // Obtain the data source mode, 0: random, 1: polling

    private AtomicLong counter = new AtomicLong(0);

    private static final Long MAX_POOL = Long.MAX_VALUE;

    private final Lock lock = new ReentrantLock();

    @Override
    public void afterPropertiesSet(a) {
        if (this.writeDataSource == null) {
            throw new IllegalArgumentException("Property 'writeDataSource' is required");
        }
        setDefaultTargetDataSource(writeDataSource);
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DynamicDataSourceGlobal.WRITE.name(), writeDataSource);
        if (this.readDataSources == null) {
            readDataSourceSize = 0;
        } else {
            for(int i=0; i<readDataSources.size(); i++) {
                targetDataSources.put(DynamicDataSourceGlobal.READ.name() + i, readDataSources.get(i));
            }
            readDataSourceSize = readDataSources.size();
        }
        setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey(a) {

        DynamicDataSourceGlobal dynamicDataSourceGlobal = DynamicDataSourceHolder.getDataSource();

        if(dynamicDataSourceGlobal == null
                || dynamicDataSourceGlobal == DynamicDataSourceGlobal.WRITE
                || readDataSourceSize <= 0) {
            return DynamicDataSourceGlobal.WRITE.name();
        }

        int index = 1;

        if(readDataSourcePollPattern == 1) {
            // Polling mode
            long currValue = counter.incrementAndGet();
            if((currValue + 1) >= MAX_POOL) {
                try {
                    lock.lock();
                    if((currValue + 1) >= MAX_POOL) {
                        counter.set(0); }}finally {
                    lock.unlock();
                }
            }
            index = (int) (currValue % readDataSourceSize);
        } else {
            // Random mode
            index = ThreadLocalRandom.current().nextInt(0, readDataSourceSize);
        }
        return dynamicDataSourceGlobal.name() + index;
    }

    public void setWriteDataSource(Object writeDataSource) {
        this.writeDataSource = writeDataSource;
    }

    public void setReadDataSources(List<Object> readDataSources) {
        this.readDataSources = readDataSources;
    }

    public void setReadDataSourcePollPattern(int readDataSourcePollPattern) {
        this.readDataSourcePollPattern = readDataSourcePollPattern; }}Copy the code

import org.apache.log4j.Logger;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.reflect.MethodSignature;

import java.lang.reflect.Method;

/** * Created by IDEA * User: mashaohua * Date: 2016-07-07 13:39 * Desc
public class DynamicDataSourceAspect {

    private static final Logger logger = Logger.getLogger(DynamicDataSourceAspect.class);

    public void pointCut(a){};

    public void before(JoinPoint point)
    { Object target = point.getTarget(); String methodName = point.getSignature().getName(); Class<? >[] clazz = target.getClass().getInterfaces(); Class<? >[] parameterTypes = ((MethodSignature) point.getSignature()).getMethod().getParameterTypes();try {
            Method method = clazz[0].getMethod(methodName, parameterTypes);
            if(method ! =null&& method.isAnnotationPresent(DataSource.class)) { DataSource data = method.getAnnotation(DataSource.class); DynamicDataSourceHolder.putDataSource(data.value()); }}catch (Exception e) {
            logger.error(String.format("Choose DataSource error, method:%s, msg:%s", methodName, e.getMessage())); }}public void after(JoinPoint point) { DynamicDataSourceHolder.clearDataSource(); }}Copy the code


      
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="Http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd">

    <bean id="abstractDataSource" abstract="true" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <! -- Configure a timeout for obtaining a connection -->
        <property name="maxWait" value="60000"/>
        <! -- Configure how often to check for idle connections that need to be closed in milliseconds -->
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
        <! -- Configure the minimum duration of a connection in the pool, in milliseconds -->
        <property name="minEvictableIdleTimeMillis" value="300000"/>
        <property name="validationQuery" value="SELECT 'x'"/>
        <property name="testWhileIdle" value="true"/>
        <property name="testOnBorrow" value="false"/>
        <property name="testOnReturn" value="false"/>
        <! -- Open PSCache and specify the size of the PSCache on each connection -->
        <property name="poolPreparedStatements" value="true"/>
        <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
        <property name="filters" value="config"/>
        <property name="connectionProperties" value="config.decrypt=true" />
    </bean>

    <bean id="dataSourceRead1" parent="abstractDataSource">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <! Url, user, password -->
        <property name="url" value="${read1.jdbc.url}"/>
        <property name="username" value="${read1.jdbc.user}"/>
        <property name="password" value="${read1.jdbc.password}"/>
        <! -- configure initialization size, min, Max -->
        <property name="initialSize" value="${read1.jdbc.initPoolSize}"/>
        <property name="minIdle" value="${read1.jdbc.minPoolSize}"/>
        <property name="maxActive" value="${read1.jdbc.maxPoolSize}"/>
    </bean>

    <bean id="dataSourceRead2" parent="abstractDataSource">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <! Url, user, password -->
        <property name="url" value="${read2.jdbc.url}"/>
        <property name="username" value="${read2.jdbc.user}"/>
        <property name="password" value="${read2.jdbc.password}"/>
        <! -- configure initialization size, min, Max -->
        <property name="initialSize" value="${read2.jdbc.initPoolSize}"/>
        <property name="minIdle" value="${read2.jdbc.minPoolSize}"/>
        <property name="maxActive" value="${read2.jdbc.maxPoolSize}"/>
    </bean>

    <bean id="dataSourceWrite" parent="abstractDataSource">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <! Url, user, password -->
        <property name="url" value="${write.jdbc.url}"/>
        <property name="username" value="${write.jdbc.user}"/>
        <property name="password" value="${write.jdbc.password}"/>
        <! -- configure initialization size, min, Max -->
        <property name="initialSize" value="${write.jdbc.initPoolSize}"/>
        <property name="minIdle" value="${write.jdbc.minPoolSize}"/>
        <property name="maxActive" value="${write.jdbc.maxPoolSize}"/>
    </bean>

    <bean id="dataSource" class="com.test.api.dao.datasource.DynamicDataSource">
        <property name="writeDataSource" ref="dataSourceWrite" />
        <property name="readDataSources">
            <list>
                <ref bean="dataSourceRead1" />
                <ref bean="dataSourceRead2" />
            </list>
        </property>
        <! -- Polling mode -->
        <property name="readDataSourcePollPattern" value="1" />
        <property name="defaultTargetDataSource" ref="dataSourceWrite"/>
    </bean>

    <tx:annotation-driven transaction-manager="transactionManager"/>

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <! -- Configuration item for myBatis -->
    <! SqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <! -- Instantiate sqlSessionFactory using the data source and SQL mapping file -->
        <property name="dataSource" ref="dataSource"/>
        <property name="mapperLocations" value="classpath:mapper/*.xml"/>
    </bean>

    <! -- Configure the scanner -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <! Scan all mapped interface classes under a package and its subpackages -->
        <property name="basePackage" value="com.test.api.dao.inte"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>

    <! -- Configure database annotation AOP -->
    <bean id="dynamicDataSourceAspect" class="com.test.api.dao.datasource.DynamicDataSourceAspect" />
    <aop:config>
        <aop:aspect id="c" ref="dynamicDataSourceAspect">
            <aop:pointcut id="tx" expression="execution(* com.test.api.dao.inte.. *. * (..) )"/>
            <aop:before pointcut-ref="tx" method="before"/>
            <aop:after pointcut-ref="tx" method="after"/>
        </aop:aspect>
    </aop:config>
    <! -- Configure database annotation AOP -->
</beans>
Copy the code

Plan 3

Through Mybatis Plugin in the business layer to achieve the database read and write separation, before Mybatis to create Statement object by interceptor to select the real data source, in the interceptor according to the method name (SELECT, UPDATE, INSERT, delete) select the data source.

Advantages: the original code unchanged, support more read, easy to expand
Disadvantages:
implementation

/** * Created by IDEA * User: mashaohua * Date: 2016-07-19 15:40 * Desc: Creates a Connection agent interface */
public interface ConnectionProxy extends Connection {

    /** * Route to the correct connection based on the key required by the incoming read/write separation *@paramKey Data source identifier *@return* /
    Connection  getTargetConnection(String key);
}
Copy the code

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import javax.sql.DataSource;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.jdbc.datasource.lookup.DataSourceLookup;
import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;
import org.springframework.util.Assert;

public abstract class AbstractDynamicDataSourceProxy extends AbstractDataSource implements InitializingBean {

    private List<Object> readDataSources;
    private List<DataSource> resolvedReadDataSources;

    private Object writeDataSource;
    private DataSource resolvedWriteDataSource;

    private int readDataSourcePollPattern = 0;

    private int readDsSize;

    private boolean defaultAutoCommit = true;
    private int defaultTransactionIsolation = Connection.TRANSACTION_READ_COMMITTED;

    public static final String READ = "read";

    public static final String WRITE = "write";

    private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();

    @Override
    public Connection getConnection(a) throws SQLException {
        return (Connection) Proxy.newProxyInstance(
                com.autohome.api.dealer.tuan.dao.rwmybatis.ConnectionProxy.class.getClassLoader(),
                new Class[] {com.autohome.api.dealer.tuan.dao.rwmybatis.ConnectionProxy.class},
                new RWConnectionInvocationHandler());
    }

    @Override
    public Connection getConnection(String username, String password)
            throws SQLException {
        return (Connection) Proxy.newProxyInstance(
                com.autohome.api.dealer.tuan.dao.rwmybatis.ConnectionProxy.class.getClassLoader(),
                new Class[] {com.autohome.api.dealer.tuan.dao.rwmybatis.ConnectionProxy.class},
                new RWConnectionInvocationHandler(username,password));
    }

    public int getReadDsSize(a){
        return readDsSize;
    }

    public List<DataSource> getResolvedReadDataSources(a) {
        return resolvedReadDataSources;
    }

    public void afterPropertiesSet(a) throws Exception {

        if(writeDataSource == null) {throw new IllegalArgumentException("Property 'writeDataSource' is required");
        }
        this.resolvedWriteDataSource = resolveSpecifiedDataSource(writeDataSource);

        resolvedReadDataSources = new ArrayList<DataSource>(readDataSources.size());
        for(Object item : readDataSources){
            resolvedReadDataSources.add(resolveSpecifiedDataSource(item));
        }
        readDsSize = readDataSources.size();
    }

    protected DataSource determineTargetDataSource(String key) {
        Assert.notNull(this.resolvedReadDataSources, "DataSource router not initialized");
        if(WRITE.equals(key)){
            return resolvedWriteDataSource;
        }else{
            returnloadReadDataSource(); }}public Logger getParentLogger(a) {
        // NOOP Just ignore
        return null;
    }

    /** * get the real data source *@param dataSource (jndi | real data source)
     * @return
     * @throws IllegalArgumentException
     */
    protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
        if (dataSource instanceof DataSource) {
            return (DataSource) dataSource;
        }
        else if (dataSource instanceof String) {
            return this.dataSourceLookup.getDataSource((String) dataSource);
        }
        else {
            throw new IllegalArgumentException(
                    "Illegal data source value - only [javax.sql.DataSource] and String supported: "+ dataSource); }}protected abstract DataSource loadReadDataSource(a);

    public void setReadDsSize(int readDsSize) {
        this.readDsSize = readDsSize;
    }

    public List<Object> getReadDataSources(a) {
        return readDataSources;
    }

    public void setReadDataSources(List<Object> readDataSources) {
        this.readDataSources = readDataSources;
    }

    public Object getWriteDataSource(a) {
        return writeDataSource;
    }

    public void setWriteDataSource(Object writeDataSource) {
        this.writeDataSource = writeDataSource;
    }

    public void setResolvedReadDataSources(List<DataSource> resolvedReadDataSources) {
        this.resolvedReadDataSources = resolvedReadDataSources;
    }

    public DataSource getResolvedWriteDataSource(a) {
        return resolvedWriteDataSource;
    }

    public void setResolvedWriteDataSource(DataSource resolvedWriteDataSource) {
        this.resolvedWriteDataSource = resolvedWriteDataSource;
    }

    public int getReadDataSourcePollPattern(a) {
        return readDataSourcePollPattern;
    }

    public void setReadDataSourcePollPattern(int readDataSourcePollPattern) {
        this.readDataSourcePollPattern = readDataSourcePollPattern;
    }

    /** * Invocation handler that defers fetching an actual JDBC Connection * until first creation of a Statement. */
    private class RWConnectionInvocationHandler implements InvocationHandler {

        private String username;

        private String password;

        private Boolean readOnly = Boolean.FALSE;

        private Integer transactionIsolation;

        private Boolean autoCommit;

        private boolean closed = false;

        private Connection target;

        public RWConnectionInvocationHandler(a) {}public RWConnectionInvocationHandler(String username, String password) {
            this(a);this.username = username;
            this.password = password;
        }

        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            // Invocation on ConnectionProxy interface coming in...

            if (method.getName().equals("equals")) {
                // We must avoid fetching a target Connection for "equals".
                // Only consider equal when proxies are identical.
                return (proxy == args[0]? Boolean.TRUE : Boolean.FALSE); }else if (method.getName().equals("hashCode")) {
                // We must avoid fetching a target Connection for "hashCode",
                // and we must return the same hash code even when the target
                // Connection has been fetched: use hashCode of Connection proxy.
                return new Integer(System.identityHashCode(proxy));
            }
            else if (method.getName().equals("getTargetConnection")) {
                // Handle getTargetConnection method: return underlying connection.
                return getTargetConnection(method,args);
            }

            if(! hasTargetConnection()) {// No physical target Connection kept yet ->
                // resolve transaction demarcation methods without fetching
                // a physical JDBC Connection until absolutely necessary.

                if (method.getName().equals("toString")) {
                    return "RW Routing DataSource Proxy";
                }
                else if (method.getName().equals("isReadOnly")) {
                    return this.readOnly;
                }
                else if (method.getName().equals("setReadOnly")) {
                    this.readOnly = (Boolean) args[0];
                    return null;
                }
                else if (method.getName().equals("getTransactionIsolation")) {
                    if (this.transactionIsolation ! =null) {
                        return this.transactionIsolation;
                    }
                    return defaultTransactionIsolation;
                    // Else fetch actual Connection and check there,
                    // because we didn't have a default specified.
                }
                else if (method.getName().equals("setTransactionIsolation")) {
                    this.transactionIsolation = (Integer) args[0];
                    return null;
                }
                else if (method.getName().equals("getAutoCommit")) {
                    if (this.autoCommit ! =null)
                        return this.autoCommit;
                    return defaultAutoCommit;
                    // Else fetch actual Connection and check there,
                    // because we didn't have a default specified.
                }
                else if (method.getName().equals("setAutoCommit")) {
                    this.autoCommit = (Boolean) args[0];
                    return null;
                }
                else if (method.getName().equals("commit")) {
                    // Ignore: no statements created yet.
                    return null;
                }
                else if (method.getName().equals("rollback")) {
                    // Ignore: no statements created yet.
                    return null;
                }
                else if (method.getName().equals("getWarnings")) {
                    return null;
                }
                else if (method.getName().equals("clearWarnings")) {
                    return null;
                }
                else if (method.getName().equals("isClosed")) {
                    return (this.closed ? Boolean.TRUE : Boolean.FALSE);
                }
                else if (method.getName().equals("close")) {
                    // Ignore: no target connection yet.
                    this.closed = true;
                    return null;
                }
                else if (this.closed) {
                    // Connection proxy closed, without ever having fetched a
                    // physical JDBC Connection: throw corresponding SQLException.
                    throw new SQLException("Illegal operation: connection is closed"); }}// Target Connection already fetched,
            // or target Connection necessary for current operation ->
            // invoke method on target connection.
            try {
                return method.invoke(target, args);
            }
            catch (InvocationTargetException ex) {
                throwex.getTargetException(); }}/** * Return whether the proxy currently holds a target Connection. */
        private boolean hasTargetConnection(a) {
            return (this.target ! =null);
        }

        /** * Return the target Connection, fetching it and initializing it if necessary. */
        private Connection getTargetConnection(Method operation,Object[] args) throws SQLException {

            if (this.target == null) {
                String key = (String) args[0];
                // No target Connection held -> fetch one.
                if (logger.isDebugEnabled()) {
                    logger.debug("Connecting to database for operation '" + operation.getName() + "'");
                }

                // Fetch physical Connection from DataSource.
                this.target = (this.username ! =null)? determineTargetDataSource(key).getConnection(this.username, this.password) :
                        determineTargetDataSource(key).getConnection();

                // If we still lack default connection properties, check them now.
                //checkDefaultConnectionProperties(this.target);

                // Apply kept transaction settings, if any.
                if (this.readOnly.booleanValue()) {
                    this.target.setReadOnly(this.readOnly.booleanValue());
                }
                if (this.transactionIsolation ! =null) {
                    this.target.setTransactionIsolation(this.transactionIsolation.intValue());
                }
                if (this.autoCommit ! =null && this.autoCommit.booleanValue() ! =this.target.getAutoCommit()) {
                    this.target.setAutoCommit(this.autoCommit.booleanValue()); }}else {
                // Target Connection already held -> return it.
                if (logger.isDebugEnabled()) {
                    logger.debug("Using existing database connection for operation '" + operation.getName() + "'"); }}return this.target; }}}Copy the code

import javax.sql.DataSource;
import java.util.concurrent.ThreadLocalRandom;
import java.util.concurrent.atomic.AtomicLong;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

/** * Created by IDEA * User: mashaohua * Date: 2016-07-19 16:04 * Desc: */
public class DynamicRoutingDataSourceProxy extends AbstractDynamicDataSourceProxy {

    private AtomicLong counter = new AtomicLong(0);

    private static final Long MAX_POOL = Long.MAX_VALUE;

    private final Lock lock = new ReentrantLock();

    @Override
    protected DataSource loadReadDataSource(a) {
        int index = 1;

        if(getReadDataSourcePollPattern() == 1) {
            // Polling mode
            long currValue = counter.incrementAndGet();
            if((currValue + 1) >= MAX_POOL) {
                try {
                    lock.lock();
                    if((currValue + 1) >= MAX_POOL) {
                        counter.set(0); }}finally {
                    lock.unlock();
                }
            }
            index = (int) (currValue % getReadDsSize());
        } else {
            // Random mode
            index = ThreadLocalRandom.current().nextInt(0, getReadDsSize());
        }
        returngetResolvedReadDataSources().get(index); }}Copy the code

import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;

import java.sql.Connection;
import java.util.Properties;

/** * interceptor */
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class DynamicPlugin implements Interceptor {

    public Object intercept(Invocation invocation) throws Throwable {
        
       
        Connection conn = (Connection)invocation.getArgs()[0];
        // If our proxy is used, route the data source
        if(conn instanceof com.autohome.api.dealer.tuan.dao.rwmybatis.ConnectionProxy){
            StatementHandler statementHandler = (StatementHandler) invocation
                    .getTarget();

            MappedStatement mappedStatement = null;
            if (statementHandler instanceof RoutingStatementHandler) {
                StatementHandler delegate = (StatementHandler) ReflectionUtils
                        .getFieldValue(statementHandler, "delegate");
                mappedStatement = (MappedStatement) ReflectionUtils.getFieldValue(
                        delegate, "mappedStatement");
            } else {
                mappedStatement = (MappedStatement) ReflectionUtils.getFieldValue(
                        statementHandler, "mappedStatement");
            }
            String key = AbstractDynamicDataSourceProxy.WRITE;

            if(mappedStatement.getSqlCommandType() == SqlCommandType.SELECT){
                key = AbstractDynamicDataSourceProxy.READ;
            }else{
                key = AbstractDynamicDataSourceProxy.WRITE;
            }

            ConnectionProxy connectionProxy = (ConnectionProxy)conn;
            connectionProxy.getTargetConnection(key);

        }
        
        return invocation.proceed();
        
    }

    public Object plugin(Object target) {
        
        return Plugin.wrap(target, this);
    }

    public void setProperties(Properties properties) {
        //NOOP}}Copy the code

import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;

import java.lang.reflect.*;

public class ReflectionUtils {

    private static final Log logger = LogFactory.getLog(ReflectionUtils.class);

    /** * set the value of the object property directly, ignoring the private/protected modifier and not going through the setter function. */
    public static void setFieldValue(final Object object, final String fieldName, final Object value) {
        Field field = getDeclaredField(object, fieldName);

        if (field == null)
            throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + object + "]");

        makeAccessible(field);

        try {
            field.set(object, value);
        } catch (IllegalAccessException e) {

        }
    }

    /** * read the value of the property of the object directly, ignoring the private/protected modifier and without getters */
    public static Object getFieldValue(final Object object, final String fieldName) {
        Field field = getDeclaredField(object, fieldName);

        if (field == null)
            throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + object + "]");

        makeAccessible(field);

        Object result = null;
        try {
            result = field.get(object);
        } catch (IllegalAccessException e) {

        }
        return result;
    }

    /** * Call the object method directly, ignoring the private/protected modifier
    public static Object invokeMethod(final Object object, final String methodName, finalClass<? >[] parameterTypes,final Object[] parameters) throws InvocationTargetException {
        Method method = getDeclaredMethod(object, methodName, parameterTypes);
        if (method == null)
            throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + object + "]");

        method.setAccessible(true);

        try {
            return method.invoke(object, parameters);
        } catch (IllegalAccessException e) {

        }

        return null;
    }

    /** * loop up to get the object's DeclaredField. */
    protected static Field getDeclaredField(final Object object, final String fieldName) {
        for(Class<? > superClass = object.getClass(); superClass ! = Object.class; superClass = superClass .getSuperclass()) {try {
                return superClass.getDeclaredField(fieldName);
            } catch (NoSuchFieldException e) {
            }
        }
        return null;
    }

    /** * loop up to get the object's DeclaredField. */
    protected static void makeAccessible(final Field field) {
        if(! Modifier.isPublic(field.getModifiers()) || ! Modifier.isPublic(field.getDeclaringClass().getModifiers())) { field.setAccessible(true); }}/** * loop up to get the object's DeclaredMethod. */
    protected static Method getDeclaredMethod(Object object, String methodName, Class
       [] parameterTypes) {
        for(Class<? > superClass = object.getClass(); superClass ! = Object.class; superClass = superClass .getSuperclass()) {try {
                return superClass.getDeclaredMethod(methodName, parameterTypes);
            } catch (NoSuchMethodException e) {

            }
        }
        return null;
    }

    * eg. * Public UserDao extends HibernateDao<User> **@param clazz The class to introspect
     * @return the first generic declaration, or Object.class if cannot be determined
     */
    @SuppressWarnings("unchecked")
    public static <T> Class<T> getSuperClassGenricType(final Class clazz) {
        return getSuperClassGenricType(clazz, 0);
    }

    * eg. * Public UserDao extends HibernateDao<User> **@param clazz The class to introspect
     * @return the first generic declaration, or Object.class if cannot be determined
     */
    @SuppressWarnings("unchecked")
    public static Class getSuperClassGenricType(final Class clazz, final int index) {

        Type genType = clazz.getGenericSuperclass();

        if(! (genTypeinstanceof ParameterizedType)) {
            logger.warn(clazz.getSimpleName() + "'s superclass not ParameterizedType");
            return Object.class;
        }

        Type[] params = ((ParameterizedType) genType).getActualTypeArguments();

        if (index >= params.length || index < 0) {
            logger.warn("Index: " + index + ", Size of " + clazz.getSimpleName() + "'s Parameterized Type: "
                    + params.length);
            return Object.class;
        }
        if(! (params[index]instanceof Class)) {
            logger.warn(clazz.getSimpleName() + " not set the actual class on superclass generic parameter");
            return Object.class;
        }

        return (Class) params[index];
    }

    /** * Casts the checked Exception to unchecked Exception. */
    public static IllegalArgumentException convertToUncheckedException(Exception e) {
        if (e instanceof IllegalAccessException || e instanceof IllegalArgumentException
                || e instanceof NoSuchMethodException)
            return new IllegalArgumentException("Refelction Exception.", e);
        else
            return newIllegalArgumentException(e); }}Copy the code


      
<! DOCTYPEconfiguration PUBLIC "-//mybatis.org//DTD SQL Map Config 3.0//EN"  
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <plugins>
        <plugin interceptor="com.test.api.dao.mybatis.DynamicPlugin">
        </plugin>
    </plugins>
     
</configuration>
Copy the code


      
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="Http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd">

    <bean id="abstractDataSource" abstract="true" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <! -- Configure a timeout for obtaining a connection -->
        <property name="maxWait" value="60000"/>
        <! -- Configure how often to check for idle connections that need to be closed in milliseconds -->
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
        <! -- Configure the minimum duration of a connection in the pool, in milliseconds -->
        <property name="minEvictableIdleTimeMillis" value="300000"/>
        <property name="validationQuery" value="SELECT 'x'"/>
        <property name="testWhileIdle" value="true"/>
        <property name="testOnBorrow" value="false"/>
        <property name="testOnReturn" value="false"/>
        <! -- Open PSCache and specify the size of the PSCache on each connection -->
        <property name="poolPreparedStatements" value="true"/>
        <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
        <property name="filters" value="config"/>
        <property name="connectionProperties" value="config.decrypt=true" />
    </bean>

    <bean id="dataSourceRead1" parent="abstractDataSource">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <! Url, user, password -->
        <property name="url" value="${read1.jdbc.url}"/>
        <property name="username" value="${read1.jdbc.user}"/>
        <property name="password" value="${read1.jdbc.password}"/>
        <! -- configure initialization size, min, Max -->
        <property name="initialSize" value="${read1.jdbc.initPoolSize}"/>
        <property name="minIdle" value="${read1.jdbc.minPoolSize}"/>
        <property name="maxActive" value="${read1.jdbc.maxPoolSize}"/>
    </bean>

    <bean id="dataSourceRead2" parent="abstractDataSource">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <! Url, user, password -->
        <property name="url" value="${read2.jdbc.url}"/>
        <property name="username" value="${read2.jdbc.user}"/>
        <property name="password" value="${read2.jdbc.password}"/>
        <! -- configure initialization size, min, Max -->
        <property name="initialSize" value="${read2.jdbc.initPoolSize}"/>
        <property name="minIdle" value="${read2.jdbc.minPoolSize}"/>
        <property name="maxActive" value="${read2.jdbc.maxPoolSize}"/>
    </bean>

    <bean id="dataSourceWrite" parent="abstractDataSource">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <! Url, user, password -->
        <property name="url" value="${write.jdbc.url}"/>
        <property name="username" value="${write.jdbc.user}"/>
        <property name="password" value="${write.jdbc.password}"/>
        <! -- configure initialization size, min, Max -->
        <property name="initialSize" value="${write.jdbc.initPoolSize}"/>
        <property name="minIdle" value="${write.jdbc.minPoolSize}"/>
        <property name="maxActive" value="${write.jdbc.maxPoolSize}"/>
    </bean>

    <bean id="dataSource" class="com.test.api.dao.datasource.DynamicRoutingDataSourceProxy">
        <property name="writeDataSource" ref="dataSourceWrite" />
        <property name="readDataSources">
            <list>
                <ref bean="dataSourceRead1" />
                <ref bean="dataSourceRead2" />
            </list>
        </property>
        <! -- Polling mode -->
        <property name="readDataSourcePollPattern" value="1" />
    </bean>

    <tx:annotation-driven transaction-manager="transactionManager"/>

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <! -- Configuration item for myBatis -->
    <! SqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <! -- Instantiate sqlSessionFactory using the data source and SQL mapping file -->
        <property name="dataSource" ref="dataSource"/>
        <property name="mapperLocations" value="classpath:mapper/*.xml"/>
        <property name="configLocation" value="classpath:mybatis-plugin-config.xml" />
    </bean>

    <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg ref="sqlSessionFactory" />
    </bean>
    <! -- Scan all mapper in directory and generate proxy class according to mapper.xml -->
    <bean id="mapper" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.test.api.dao.inte" />
        <property name="sqlSessionTemplate" ref="sqlSessionTemplate"></property>
    </bean>

</beans>
Copy the code

Plan 4

If your background is spring + mybatis structure, can through the spring AbstractRoutingDataSource and mybatis Plugin interceptor implementation separation, very friendly, speaking, reading and writing of the original code does not need any change. The fourth option is recommended

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

import java.util.HashMap;
import java.util.Map;

/** * Created by IDEA * User: mashaohua * Date: 2016-07-14 10:56 * Desc: Separation of read and write for dynamic data source */
public class DynamicDataSource extends AbstractRoutingDataSource {

    private Object writeDataSource; // Write the data source

    private Object readDataSource; // Read the data source

    @Override
    public void afterPropertiesSet(a) {
        if (this.writeDataSource == null) {
            throw new IllegalArgumentException("Property 'writeDataSource' is required");
        }
        setDefaultTargetDataSource(writeDataSource);
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DynamicDataSourceGlobal.WRITE.name(), writeDataSource);
        if(readDataSource ! =null) {
            targetDataSources.put(DynamicDataSourceGlobal.READ.name(), readDataSource);
        }
        setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey(a) {

        DynamicDataSourceGlobal dynamicDataSourceGlobal = DynamicDataSourceHolder.getDataSource();

        if(dynamicDataSourceGlobal == null
                || dynamicDataSourceGlobal == DynamicDataSourceGlobal.WRITE) {
            return DynamicDataSourceGlobal.WRITE.name();
        }

        return DynamicDataSourceGlobal.READ.name();
    }

    public void setWriteDataSource(Object writeDataSource) {
        this.writeDataSource = writeDataSource;
    }

    public Object getWriteDataSource(a) {
        return writeDataSource;
    }

    public Object getReadDataSource(a) {
        return readDataSource;
    }

    public void setReadDataSource(Object readDataSource) {
        this.readDataSource = readDataSource; }}Copy the code

/** * Created by IDEA * User: mashaohua * Date: 2016-07-14 10:58 * Desc: */
public enum DynamicDataSourceGlobal {
    READ, WRITE;
}
Copy the code

public final class DynamicDataSourceHolder {

    private static final ThreadLocal<DynamicDataSourceGlobal> holder = new ThreadLocal<DynamicDataSourceGlobal>();

    private DynamicDataSourceHolder(a) {
        //
    }

    public static void putDataSource(DynamicDataSourceGlobal dataSource){
        holder.set(dataSource);
    }

    public static DynamicDataSourceGlobal getDataSource(a){
        return holder.get();
    }

    public static void clearDataSource(a) { holder.remove(); }}Copy the code

import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;

/** * Created by IDEA * User: mashaohua * Date: 2016-08-10 14:34 * Desc: */
public class DynamicDataSourceTransactionManager extends DataSourceTransactionManager {

    /** * Read-only transactions to read libraries, read/write transactions to write libraries *@param transaction
     * @param definition
     */
    @Override
    protected void doBegin(Object transaction, TransactionDefinition definition) {

        // Set the data source
        boolean readOnly = definition.isReadOnly();
        if(readOnly) {
            DynamicDataSourceHolder.putDataSource(DynamicDataSourceGlobal.READ);
        } else {
            DynamicDataSourceHolder.putDataSource(DynamicDataSourceGlobal.WRITE);
        }
        super.doBegin(transaction, definition);
    }

    /** * Clean up the local thread's data source *@param transaction
     */
    @Override
    protected void doCleanupAfterCompletion(Object transaction) {
        super.doCleanupAfterCompletion(transaction); DynamicDataSourceHolder.clearDataSource(); }}Copy the code

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.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.support.TransactionSynchronizationManager;

import java.util.Locale;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

/** * Created by IDEA * User: mashaohua * Date: 2016-08-10 11:09 * Desc: */
@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 DynamicPlugin implements Interceptor {

    protected static final Logger logger = LoggerFactory.getLogger(DynamicPlugin.class);

    private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";

    private static final Map<String, DynamicDataSourceGlobal> cacheMap = new ConcurrentHashMap<>();

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        boolean synchronizationActive = TransactionSynchronizationManager.isSynchronizationActive();
        if(! synchronizationActive) { Object[] objects = invocation.getArgs(); MappedStatement ms = (MappedStatement) objects[0];

            DynamicDataSourceGlobal dynamicDataSourceGlobal = null;

            if((dynamicDataSourceGlobal = cacheMap.get(ms.getId())) == null) {
                / / read method
                if(ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
                    / /! SELECT LAST_INSERT_ID()) method to query the primary key (SELECT LAST_INSERT_ID()) for incrementing the ID, using the primary library
                    if(ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
                        dynamicDataSourceGlobal = DynamicDataSourceGlobal.WRITE;
                    } else {
                        BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]);
                        String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]"."");
                        if(sql.matches(REGEX)) {
                            dynamicDataSourceGlobal = DynamicDataSourceGlobal.WRITE;
                        } else{ dynamicDataSourceGlobal = DynamicDataSourceGlobal.READ; }}}else{
                    dynamicDataSourceGlobal = DynamicDataSourceGlobal.WRITE;
                }
                logger.warn("Setting method [{}] use {} Strategy, SqlCommandType [{}].", ms.getId(), dynamicDataSourceGlobal.name(), ms.getSqlCommandType().name());
                cacheMap.put(ms.getId(), dynamicDataSourceGlobal);
            }
            DynamicDataSourceHolder.putDataSource(dynamicDataSourceGlobal);
        }

        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof Executor) {
            return Plugin.wrap(target, this);
        } else {
            returntarget; }}@Override
    public void setProperties(Properties properties) {
        //}}Copy the code

Spring+MyBatis implementation of database read and write separation scheme

Plan 1

Scheme 2

Plan 3

Plan 4

Related Posts

Mybatisplus dependencies conflict in Springboot project

If Logback configuration files are written this way, TPS increases by a factor of 10

How to query hundreds of millions of data in seconds in 3 days?