Spring + Mybatis project implements dynamic switching data source instance detail

Project background: In the development of the project, the database USES read and write separation, all query statements go to the slave library, in addition to the master library.

The easiest way to do this is to simply create two packages and point the previous set of configuration copy1 from the data source to another package, but the scale is limited, so use the following approach.

Two articles are referred as follows:

https://www.ofstack.com/article/111840.htm

https://www.ofstack.com/article/111842.htm

These two articles have analyzed the principle, the following is only to write their own implementation process other not to describe.

The realization idea is:

Step 1: Dynamically switch data sources: Configure two DataSource, configure two SqlSessionFactory to point to two different DataSource, both SqlSessionFactory to use one SqlSessionTemplate, rewrite the SqlSessionTemplate class provided by Mybatis, and finally configure Mybatis to scan automatically.

Step 2 USES the aop slice to intercept all methods in the dao layer because of the naming characteristics of the dao layer methods, such as all queries sql beginning with select, or get beginning, etc., intercepting these methods and switching the current data source to the slave library.

The configuration in spring is as follows:

Main library data source configuration:

 <bean id="masterDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">2 <property name="driverClass" value="${master_mysql_jdbc_driver}" />
 <property name="jdbcUrl" value="${master_mysql_jdbc_url}" />
 <property name="user" value="${master_mysql_jdbc_user}" />
 <property name="password" value="${master_mysql_jdbc_password}" />
 </bean>

From the library data source configuration:

 <bean id="masterDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
 <property name="driverClass" value="${slave_mysql_jdbc_driver}" />
 <property name="jdbcUrl" value="${slave_mysql_jdbc_url}" />
 <property name="user" value="${slave_mysql_jdbc_user}" />
 <property name="password" value="${slave_mysql_jdbc_password}" />
 </bean>

Main library SqlSessionFactory configuration:

 <bean id="masterSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
 <property name="dataSource" ref="masterDataSource" />
 <property name="mapperLocations" value="classpath:com/sincetimes/slg/dao/*.xml"/>
 </bean>

Slave library SqlSessionFactory configuration:

 <bean id="slaveSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
 <property name="dataSource" ref="slaveDataSource" />
 <property name="mapperLocations" value="classpath:com/sincetimes/slg/dao/*.xml"/>
 </bean>

Both SqlSessionFactory use the same SqlSessionTemplate configuration:

 <bean id="MasterAndSlaveSqlSessionTemplate" class="com.sincetimes.slg.framework.core.DynamicSqlSessionTemplate">
 <constructor-arg index="0" ref="masterSqlSessionFactory" />
 <property name="targetSqlSessionFactorys">
 <map> 
 <entry value-ref="masterSqlSessionFactory" key="master"/> 
 <entry value-ref="slaveSqlSessionFactory" key="slave"/> 
 </map> 
 </property>
 </bean>

Configure Mybatis to scan dao automatically

 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
 <property name="basePackage" value="com.sincetimes.slg.dao" />
 <property name="sqlSessionTemplateBeanName" value="MasterAndSlaveSqlSessionTemplate" />
 </bean>

I rewrote the SqlSessionTemplate code as follows:

package com.sincetimes.slg.framework.core;
import static java.lang.reflect.Proxy.newProxyInstance;
import static org.apache.ibatis.reflection.ExceptionUtil.unwrapThrowable;
import static org.mybatis.spring.SqlSessionUtils.closeSqlSession;
import static org.mybatis.spring.SqlSessionUtils.getSqlSession;
import static org.mybatis.spring.SqlSessionUtils.isSqlSessionTransactional;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.MyBatisExceptionTranslator;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.dao.support.PersistenceExceptionTranslator;
import org.springframework.util.Assert;
import com.sincetimes.slg.framework.util.SqlSessionContentHolder;
/**
 * 
 * TODO  rewrite SqlSessionTemplate
 * @author ccg
 * @version 1.0
 * Created 2017 years 4 month 21 day   In the afternoon 3:15:15
 */
public class DynamicSqlSessionTemplate extends SqlSessionTemplate {
 private final SqlSessionFactory sqlSessionFactory;
 private final ExecutorType executorType;
 private final SqlSession sqlSessionProxy;
 private final PersistenceExceptionTranslator exceptionTranslator;
 private Map<Object, SqlSessionFactory> targetSqlSessionFactorys;
 private SqlSessionFactory defaultTargetSqlSessionFactory;
 public void setTargetSqlSessionFactorys(Map<Object, SqlSessionFactory> targetSqlSessionFactorys) {
 this.targetSqlSessionFactorys = targetSqlSessionFactorys;
 }
 public Map<Object, SqlSessionFactory> getTargetSqlSessionFactorys(){
 return targetSqlSessionFactorys;
 }
 public void setDefaultTargetSqlSessionFactory(SqlSessionFactory defaultTargetSqlSessionFactory) {
 this.defaultTargetSqlSessionFactory = defaultTargetSqlSessionFactory;
 }
 public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
 this(sqlSessionFactory, sqlSessionFactory.getConfiguration().getDefaultExecutorType());
 }
 public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType) {
 this(sqlSessionFactory, executorType, new MyBatisExceptionTranslator(sqlSessionFactory.getConfiguration()
 .getEnvironment().getDataSource(), true));
 }
 public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType,
 PersistenceExceptionTranslator exceptionTranslator) {
 super(sqlSessionFactory, executorType, exceptionTranslator);
 this.sqlSessionFactory = sqlSessionFactory;
 this.executorType = executorType;
 this.exceptionTranslator = exceptionTranslator;
 this.sqlSessionProxy = (SqlSession) newProxyInstance(
 SqlSessionFactory.class.getClassLoader(),
 new Class[] { SqlSession.class }, 
 new SqlSessionInterceptor());
 this.defaultTargetSqlSessionFactory = sqlSessionFactory;
 }
 @Override
 public SqlSessionFactory getSqlSessionFactory() {
 SqlSessionFactory targetSqlSessionFactory = targetSqlSessionFactorys.get(SqlSessionContentHolder.getContextType());
 if (targetSqlSessionFactory != null) {
 return targetSqlSessionFactory;
 } else if (defaultTargetSqlSessionFactory != null) {
 return defaultTargetSqlSessionFactory;
 } else {
 Assert.notNull(targetSqlSessionFactorys, "Property 'targetSqlSessionFactorys' or 'defaultTargetSqlSessionFactory' are required");
 Assert.notNull(defaultTargetSqlSessionFactory, "Property 'defaultTargetSqlSessionFactory' or 'targetSqlSessionFactorys' are required");
 }
 return this.sqlSessionFactory;
 }
 @Override
 public Configuration getConfiguration() {
 return this.getSqlSessionFactory().getConfiguration();
 }
 public ExecutorType getExecutorType() {
 return this.executorType;
 }
 public PersistenceExceptionTranslator getPersistenceExceptionTranslator() {
 return this.exceptionTranslator;
 }
 /**
 * {@inheritDoc}
 */
 public <T> T selectOne(String statement) {
 return this.sqlSessionProxy.<T> selectOne(statement);
 }
 /**
 * {@inheritDoc}
 */
 public <T> T selectOne(String statement, Object parameter) {
 return this.sqlSessionProxy.<T> selectOne(statement, parameter);
 }
 /**
 * {@inheritDoc}
 */
 public <K, V> Map<K, V> selectMap(String statement, String mapKey) {
 return this.sqlSessionProxy.<K, V> selectMap(statement, mapKey);
 }
 /**
 * {@inheritDoc}
 */
 public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey) {
 return this.sqlSessionProxy.<K, V> selectMap(statement, parameter, mapKey);
 }
 /**
 * {@inheritDoc}
 */
 public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowBounds) {
 return this.sqlSessionProxy.<K, V> selectMap(statement, parameter, mapKey, rowBounds);
 }
 /**
 * {@inheritDoc}
 */
 public <E> List<E> selectList(String statement) {
 return this.sqlSessionProxy.<E> selectList(statement);
 }
 /**
 * {@inheritDoc}
 */
 public <E> List<E> selectList(String statement, Object parameter) {
 return this.sqlSessionProxy.<E> selectList(statement, parameter);
 }
 /**
 * {@inheritDoc}
 */
 public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) {
 return this.sqlSessionProxy.<E> selectList(statement, parameter, rowBounds);
 }
 /**
 * {@inheritDoc}
 */
 public void select(String statement, ResultHandler handler) {
 this.sqlSessionProxy.select(statement, handler);
 }
 /**
 * {@inheritDoc}
 */
 public void select(String statement, Object parameter, ResultHandler handler) {
 this.sqlSessionProxy.select(statement, parameter, handler);
 }
 /**
 * {@inheritDoc}
 */
 public void select(String statement, Object parameter, RowBounds rowBounds, ResultHandler handler) {
 this.sqlSessionProxy.select(statement, parameter, rowBounds, handler);
 }
 /**
 * {@inheritDoc}
 */
 public int insert(String statement) {
 return this.sqlSessionProxy.insert(statement);
 }
 /**
 * {@inheritDoc}
 */
 public int insert(String statement, Object parameter) {
 return this.sqlSessionProxy.insert(statement, parameter);
 }
 /**
 * {@inheritDoc}
 */
 public int update(String statement) {
 return this.sqlSessionProxy.update(statement);
 }
 /**
 * {@inheritDoc}
 */
 public int update(String statement, Object parameter) {
 return this.sqlSessionProxy.update(statement, parameter);
 }
 /**
 * {@inheritDoc}
 */
 public int delete(String statement) {
 return this.sqlSessionProxy.delete(statement);
 }
 /**
 * {@inheritDoc}
 */
 public int delete(String statement, Object parameter) {
 return this.sqlSessionProxy.delete(statement, parameter);
 }
 /**
 * {@inheritDoc}
 */
 public <T> T getMapper(Class<T> type) {
 return getConfiguration().getMapper(type, this);
 }
 /**
 * {@inheritDoc}
 */
 public void commit() {
 throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
 }
 /**
 * {@inheritDoc}
 */
 public void commit(boolean force) {
 throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
 }
 /**
 * {@inheritDoc}
 */
 public void rollback() {
 throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
 }
 /**
 * {@inheritDoc}
 */
 public void rollback(boolean force) {
 throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
 }
 /**
 * {@inheritDoc}
 */
 public void close() {
 throw new UnsupportedOperationException("Manual close is not allowed over a Spring managed SqlSession");
 }
 /**
 * {@inheritDoc}
 */
 public void clearCache() {
 this.sqlSessionProxy.clearCache();
 }
 /**
 * {@inheritDoc}
 */
 public Connection getConnection() {
 return this.sqlSessionProxy.getConnection();
 }
 /**
 * {@inheritDoc}
 * @since 1.0.2
 */
 public List<BatchResult> flushStatements() {
 return this.sqlSessionProxy.flushStatements();
 }
 /**
 * Proxy needed to route MyBatis method calls to the proper SqlSession got from Spring's Transaction Manager It also
 * unwraps exceptions thrown by {@code Method#invoke(Object, Object...)} to pass a {@code PersistenceException} to
 * the {@code PersistenceExceptionTranslator}.
 */
 private class SqlSessionInterceptor implements InvocationHandler {
 public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
 final SqlSession sqlSession = getSqlSession(
  DynamicSqlSessionTemplate.this.getSqlSessionFactory(),
  DynamicSqlSessionTemplate.this.executorType, 
  DynamicSqlSessionTemplate.this.exceptionTranslator);
 try {
 Object result = method.invoke(sqlSession, args);
 if (!isSqlSessionTransactional(sqlSession, DynamicSqlSessionTemplate.this.getSqlSessionFactory())) {
  // force commit even on non-dirty sessions because some databases require
  // a commit/rollback before calling close()
  sqlSession.commit(true);
 }
 return result;
 } catch (Throwable t) {
 Throwable unwrapped = unwrapThrowable(t);
 if (DynamicSqlSessionTemplate.this.exceptionTranslator != null && unwrapped instanceof PersistenceException) {
  Throwable translated = DynamicSqlSessionTemplate.this.exceptionTranslator
  .translateExceptionIfPossible((PersistenceException) unwrapped);
  if (translated != null) {
  unwrapped = translated;
  }
 }
 throw unwrapped;
 } finally {
 closeSqlSession(sqlSession, DynamicSqlSessionTemplate.this.getSqlSessionFactory());
 }
 }
 }
}

The SqlSessionContentHolder class code is as follows:

package com.sincetimes.slg.framework.util;
public abstract class SqlSessionContentHolder {
 public final static String SESSION_FACTORY_MASTER = "master";
 public final static String SESSION_FACTORY_SLAVE = "slave";
 private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>(); 
 public static void setContextType(String contextType) { 
 contextHolder.set(contextType); 
 } 
 public static String getContextType() { 
 return contextHolder.get(); 
 } 
 public static void clearContextType() { 
 contextHolder.remove(); 
 } 
}

Finally, the section is written to handle all the dao methods. The code is simple as follows:

package com.sincetimes.slg.framework.core;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import com.sincetimes.slg.framework.util.SqlSessionContentHolder;
@Aspect
public class DynamicDataSourceAspect {
 @Pointcut("execution( * com.sincetimes.slg.dao.*.*(..))")
 public void pointCut(){
 }
 @Before("pointCut()")
 public void before(JoinPoint jp){
 String methodName = jp.getSignature().getName(); 
 //dao Method queries the walk away library 
 if(methodName.startsWith("query") || methodName.startsWith("get") || methodName.startsWith("count") || methodName.startsWith("list")){
 SqlSessionContentHolder.setContextType(SqlSessionContentHolder.SESSION_FACTORY_SLAVE);
 }else{
 SqlSessionContentHolder.setContextType(SqlSessionContentHolder.SESSION_FACTORY_MASTER);
 }
 }
}