Spring + Mybatis 项目实现动态切换数据源

男娘i 2022-05-31 14:42 206阅读 0赞

项目背景：项目开发中数据库使用了读写分离，所有查询语句走从库，除此之外走主库。

最简单的办法其实就是建两个包，把之前数据源那一套配置copy一份，指向另外的包，但是这样扩展很有限，所有采用下面的办法。

参考了两篇文章如下：

[http://blog.csdn.net/zl3450341/article/details/20150687][http_blog.csdn.net_zl3450341_article_details_20150687]

[http://www.blogjava.net/hoojo/archive/2013/10/22/405488.html][http_www.blogjava.net_hoojo_archive_2013_10_22_405488.html]

这两篇文章都对原理进行了分析，下面只写自己的实现过程其他不再叙述。

实现思路是：

第一步，实现动态切换数据源：配置两个DataSource，配置两个SqlSessionFactory指向两个不同的DataSource，两个SqlSessionFactory都用一个SqlSessionTemplate，同时重写Mybatis提供的SqlSessionTemplate类，最后配置Mybatis自动扫描。

第二步，利用aop切面，拦截dao层所有方法，因为dao层方法命名的特点，比如所有查询sql都是select开头，或者get开头等等，拦截这些方法，并把当前数据源切换至从库。

spring中配置如下：

主库数据源配置：

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

![复制代码][copycode.gif]

从库数据源配置：

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

主库SqlSessionFactory配置：

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

从库SqlSessionFactory配置：

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

两个SqlSessionFactory使用同一个SqlSessionTemplate配置：

1 <bean id="MasterAndSlaveSqlSessionTemplate" class="com.sincetimes.slg.framework.core.DynamicSqlSessionTemplate">
    2     <constructor-arg index="0" ref="masterSqlSessionFactory" />
    3     <property name="targetSqlSessionFactorys">
    4         <map>  
    5             <entry value-ref="masterSqlSessionFactory" key="master"/>  
    6             <entry value-ref="slaveSqlSessionFactory" key="slave"/>  
    7         </map>  
    8     </property>
    9 </bean>

配置Mybatis自动扫描dao

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

自己重写了SqlSessionTemplate代码如下：

1 package com.sincetimes.slg.framework.core;
      2 
      3 import static java.lang.reflect.Proxy.newProxyInstance;
      4 import static org.apache.ibatis.reflection.ExceptionUtil.unwrapThrowable;
      5 import static org.mybatis.spring.SqlSessionUtils.closeSqlSession;
      6 import static org.mybatis.spring.SqlSessionUtils.getSqlSession;
      7 import static org.mybatis.spring.SqlSessionUtils.isSqlSessionTransactional;
      8  
      9 import java.lang.reflect.InvocationHandler;
     10 import java.lang.reflect.Method;
     11 import java.sql.Connection;
     12 import java.util.List;
     13 import java.util.Map;
     14 
     15 import org.apache.ibatis.exceptions.PersistenceException;
     16 import org.apache.ibatis.executor.BatchResult;
     17 import org.apache.ibatis.session.Configuration;
     18 import org.apache.ibatis.session.ExecutorType;
     19 import org.apache.ibatis.session.ResultHandler;
     20 import org.apache.ibatis.session.RowBounds;
     21 import org.apache.ibatis.session.SqlSession;
     22 import org.apache.ibatis.session.SqlSessionFactory;
     23 import org.mybatis.spring.MyBatisExceptionTranslator;
     24 import org.mybatis.spring.SqlSessionTemplate;
     25 import org.springframework.dao.support.PersistenceExceptionTranslator;
     26 import org.springframework.util.Assert;
     27 
     28 import com.sincetimes.slg.framework.util.SqlSessionContentHolder;
     29 
     30 
     31 /**
     32  * 
     33  * TODO         重写SqlSessionTemplate
     34  * @author      ccg
     35  * @version        1.0
     36  * Created        2017年4月21日 下午3:15:15
     37  */
     38 public class DynamicSqlSessionTemplate extends SqlSessionTemplate {
     39  
     40     private final SqlSessionFactory sqlSessionFactory;
     41     private final ExecutorType executorType;
     42     private final SqlSession sqlSessionProxy;
     43     private final PersistenceExceptionTranslator exceptionTranslator;
     44  
     45     private Map<Object, SqlSessionFactory> targetSqlSessionFactorys;
     46     private SqlSessionFactory defaultTargetSqlSessionFactory;
     47  
     48     public void setTargetSqlSessionFactorys(Map<Object, SqlSessionFactory> targetSqlSessionFactorys) {
     49         this.targetSqlSessionFactorys = targetSqlSessionFactorys;
     50     }
     51     
     52     public Map<Object, SqlSessionFactory> getTargetSqlSessionFactorys(){
     53         return targetSqlSessionFactorys;
     54     }
     55  
     56     public void setDefaultTargetSqlSessionFactory(SqlSessionFactory defaultTargetSqlSessionFactory) {
     57         this.defaultTargetSqlSessionFactory = defaultTargetSqlSessionFactory;
     58     }
     59  
     60     public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
     61         this(sqlSessionFactory, sqlSessionFactory.getConfiguration().getDefaultExecutorType());
     62     }
     63  
     64     public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType) {
     65         this(sqlSessionFactory, executorType, new MyBatisExceptionTranslator(sqlSessionFactory.getConfiguration()
     66                 .getEnvironment().getDataSource(), true));
     67     }
     68  
     69     public DynamicSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType,
     70             PersistenceExceptionTranslator exceptionTranslator) {
     71  
     72         super(sqlSessionFactory, executorType, exceptionTranslator);
     73  
     74         this.sqlSessionFactory = sqlSessionFactory;
     75         this.executorType = executorType;
     76         this.exceptionTranslator = exceptionTranslator;
     77         
     78         this.sqlSessionProxy = (SqlSession) newProxyInstance(
     79                 SqlSessionFactory.class.getClassLoader(),
     80                 new Class[] { SqlSession.class }, 
     81                 new SqlSessionInterceptor());
     82  
     83         this.defaultTargetSqlSessionFactory = sqlSessionFactory;
     84     }
     85  
     86     @Override
     87     public SqlSessionFactory getSqlSessionFactory() {
     88  
     89         SqlSessionFactory targetSqlSessionFactory = targetSqlSessionFactorys.get(SqlSessionContentHolder.getContextType());
     90         if (targetSqlSessionFactory != null) {
     91             return targetSqlSessionFactory;
     92         } else if (defaultTargetSqlSessionFactory != null) {
     93             return defaultTargetSqlSessionFactory;
     94         } else {
     95             Assert.notNull(targetSqlSessionFactorys, "Property 'targetSqlSessionFactorys' or 'defaultTargetSqlSessionFactory' are required");
     96             Assert.notNull(defaultTargetSqlSessionFactory, "Property 'defaultTargetSqlSessionFactory' or 'targetSqlSessionFactorys' are required");
     97         }
     98         return this.sqlSessionFactory;
     99     }
    100  
    101     @Override
    102     public Configuration getConfiguration() {
    103         return this.getSqlSessionFactory().getConfiguration();
    104     }
    105  
    106     public ExecutorType getExecutorType() {
    107         return this.executorType;
    108     }
    109  
    110     public PersistenceExceptionTranslator getPersistenceExceptionTranslator() {
    111         return this.exceptionTranslator;
    112     }
    113  
    114     /**
    115      * {
        @inheritDoc}
    116      */
    117     public <T> T selectOne(String statement) {
    118         return this.sqlSessionProxy.<T> selectOne(statement);
    119     }
    120  
    121     /**
    122      * {
        @inheritDoc}
    123      */
    124     public <T> T selectOne(String statement, Object parameter) {
    125         return this.sqlSessionProxy.<T> selectOne(statement, parameter);
    126     }
    127  
    128     /**
    129      * {
        @inheritDoc}
    130      */
    131     public <K, V> Map<K, V> selectMap(String statement, String mapKey) {
    132         return this.sqlSessionProxy.<K, V> selectMap(statement, mapKey);
    133     }
    134  
    135     /**
    136      * {
        @inheritDoc}
    137      */
    138     public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey) {
    139         return this.sqlSessionProxy.<K, V> selectMap(statement, parameter, mapKey);
    140     }
    141  
    142     /**
    143      * {
        @inheritDoc}
    144      */
    145     public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowBounds) {
    146         return this.sqlSessionProxy.<K, V> selectMap(statement, parameter, mapKey, rowBounds);
    147     }
    148  
    149     /**
    150      * {
        @inheritDoc}
    151      */
    152     public <E> List<E> selectList(String statement) {
    153         return this.sqlSessionProxy.<E> selectList(statement);
    154     }
    155  
    156     /**
    157      * {
        @inheritDoc}
    158      */
    159     public <E> List<E> selectList(String statement, Object parameter) {
    160         return this.sqlSessionProxy.<E> selectList(statement, parameter);
    161     }
    162  
    163     /**
    164      * {
        @inheritDoc}
    165      */
    166     public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) {
    167         return this.sqlSessionProxy.<E> selectList(statement, parameter, rowBounds);
    168     }
    169  
    170     /**
    171      * {
        @inheritDoc}
    172      */
    173     public void select(String statement, ResultHandler handler) {
    174         this.sqlSessionProxy.select(statement, handler);
    175     }
    176  
    177     /**
    178      * {
        @inheritDoc}
    179      */
    180     public void select(String statement, Object parameter, ResultHandler handler) {
    181         this.sqlSessionProxy.select(statement, parameter, handler);
    182     }
    183  
    184     /**
    185      * {
        @inheritDoc}
    186      */
    187     public void select(String statement, Object parameter, RowBounds rowBounds, ResultHandler handler) {
    188         this.sqlSessionProxy.select(statement, parameter, rowBounds, handler);
    189     }
    190  
    191     /**
    192      * {
        @inheritDoc}
    193      */
    194     public int insert(String statement) {
    195         return this.sqlSessionProxy.insert(statement);
    196     }
    197  
    198     /**
    199      * {
        @inheritDoc}
    200      */
    201     public int insert(String statement, Object parameter) {
    202         return this.sqlSessionProxy.insert(statement, parameter);
    203     }
    204  
    205     /**
    206      * {
        @inheritDoc}
    207      */
    208     public int update(String statement) {
    209         return this.sqlSessionProxy.update(statement);
    210     }
    211  
    212     /**
    213      * {
        @inheritDoc}
    214      */
    215     public int update(String statement, Object parameter) {
    216         return this.sqlSessionProxy.update(statement, parameter);
    217     }
    218  
    219     /**
    220      * {
        @inheritDoc}
    221      */
    222     public int delete(String statement) {
    223         return this.sqlSessionProxy.delete(statement);
    224     }
    225  
    226     /**
    227      * {
        @inheritDoc}
    228      */
    229     public int delete(String statement, Object parameter) {
    230         return this.sqlSessionProxy.delete(statement, parameter);
    231     }
    232  
    233     /**
    234      * {
        @inheritDoc}
    235      */
    236     public <T> T getMapper(Class<T> type) {
    237         return getConfiguration().getMapper(type, this);
    238     }
    239  
    240     /**
    241      * {
        @inheritDoc}
    242      */
    243     public void commit() {
    244         throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
    245     }
    246  
    247     /**
    248      * {
        @inheritDoc}
    249      */
    250     public void commit(boolean force) {
    251         throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
    252     }
    253  
    254     /**
    255      * {
        @inheritDoc}
    256      */
    257     public void rollback() {
    258         throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
    259     }
    260  
    261     /**
    262      * {
        @inheritDoc}
    263      */
    264     public void rollback(boolean force) {
    265         throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
    266     }
    267  
    268     /**
    269      * {
        @inheritDoc}
    270      */
    271     public void close() {
    272         throw new UnsupportedOperationException("Manual close is not allowed over a Spring managed SqlSession");
    273     }
    274  
    275     /**
    276      * {
        @inheritDoc}
    277      */
    278     public void clearCache() {
    279         this.sqlSessionProxy.clearCache();
    280     }
    281  
    282     /**
    283      * {
        @inheritDoc}
    284      */
    285     public Connection getConnection() {
    286         return this.sqlSessionProxy.getConnection();
    287     }
    288  
    289     /**
    290      * {
        @inheritDoc}
    291      * @since 1.0.2
    292      */
    293     public List<BatchResult> flushStatements() {
    294         return this.sqlSessionProxy.flushStatements();
    295     }
    296  
    297     /**
    298      * Proxy needed to route MyBatis method calls to the proper SqlSession got from Spring's Transaction Manager It also
    299      * unwraps exceptions thrown by {
        @code Method#invoke(Object, Object...)} to pass a {
        @code PersistenceException} to
    300      * the {
        @code PersistenceExceptionTranslator}.
    301      */
    302     private class SqlSessionInterceptor implements InvocationHandler {
    303         public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    304             final SqlSession sqlSession = getSqlSession(
    305                     DynamicSqlSessionTemplate.this.getSqlSessionFactory(),
    306                     DynamicSqlSessionTemplate.this.executorType, 
    307                     DynamicSqlSessionTemplate.this.exceptionTranslator);
    308             try {
    309                 Object result = method.invoke(sqlSession, args);
    310                 if (!isSqlSessionTransactional(sqlSession, DynamicSqlSessionTemplate.this.getSqlSessionFactory())) {
    311                     // force commit even on non-dirty sessions because some databases require
    312                     // a commit/rollback before calling close()
    313                     sqlSession.commit(true);
    314                 }
    315                 return result;
    316             } catch (Throwable t) {
    317                 Throwable unwrapped = unwrapThrowable(t);
    318                 if (DynamicSqlSessionTemplate.this.exceptionTranslator != null && unwrapped instanceof PersistenceException) {
    319                     Throwable translated = DynamicSqlSessionTemplate.this.exceptionTranslator
    320                         .translateExceptionIfPossible((PersistenceException) unwrapped);
    321                     if (translated != null) {
    322                         unwrapped = translated;
    323                     }
    324                 }
    325                 throw unwrapped;
    326             } finally {
    327                 closeSqlSession(sqlSession, DynamicSqlSessionTemplate.this.getSqlSessionFactory());
    328             }
    329         }
    330     }
    331  
    332 }

SqlSessionContentHolder类代码如下：

1 package com.sincetimes.slg.framework.util;
     2 
     3 public abstract class SqlSessionContentHolder {
     4 
     5     public final static String SESSION_FACTORY_MASTER = "master";
     6     public final static String SESSION_FACTORY_SLAVE = "slave";
     7     
     8     private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();  
     9     
    10     public static void setContextType(String contextType) {  
    11         contextHolder.set(contextType);  
    12     }  
    13       
    14     public static String getContextType() {  
    15         return contextHolder.get();  
    16     }  
    17       
    18     public static void clearContextType() {  
    19         contextHolder.remove();  
    20     } 
    21 }

最后就是写切面去对dao所有方法进行处理了，代码很简单如下：

1 package com.sincetimes.slg.framework.core;
     2 
     3 import org.aspectj.lang.JoinPoint;
     4 import org.aspectj.lang.annotation.Aspect;
     5 import org.aspectj.lang.annotation.Before;
     6 import org.aspectj.lang.annotation.Pointcut;
     7 
     8 import com.sincetimes.slg.framework.util.SqlSessionContentHolder;
     9 
    10 @Aspect
    11 public class DynamicDataSourceAspect {
    12 
    13     @Pointcut("execution( * com.sincetimes.slg.dao.*.*(..))")
    14     public void pointCut(){
    15         
    16     }
    17     @Before("pointCut()")
    18     public void before(JoinPoint jp){
    19         String methodName = jp.getSignature().getName();  
    20         //dao方法查询走从库
    21         if(methodName.startsWith("query") || methodName.startsWith("get") || methodName.startsWith("count") || methodName.startsWith("list")){
    22             SqlSessionContentHolder.setContextType(SqlSessionContentHolder.SESSION_FACTORY_SLAVE);
    23         }else{
    24             SqlSessionContentHolder.setContextType(SqlSessionContentHolder.SESSION_FACTORY_MASTER);
    25         }
    26     }
    27     
    28 }

[http_blog.csdn.net_zl3450341_article_details_20150687]: http://blog.csdn.net/zl3450341/article/details/20150687
[http_www.blogjava.net_hoojo_archive_2013_10_22_405488.html]: http://www.blogjava.net/hoojo/archive/2013/10/22/405488.html
[copycode.gif]: /images/20220531/791d3509a6d94bc884c926d0ce96731d.png