Nemo

Nemo 关注TA

路漫漫其修远兮,吾将上下而求索。

Nemo

Nemo

关注TA

路漫漫其修远兮,吾将上下而求索。

  •  普罗旺斯
  • 负责帅就完事了
  • 写了1,493,291字

该文章投稿至Nemo社区   Java  板块 复制链接


spring + mybatis 操作AbstractRoutingDataSource 实现数据库读写分离

发布于 2017/11/07 17:30 2,897浏览 0回复 12,677

设想需要读写分离的场景:

    随着系统业务的复杂度 + 同时操作人数的日益增加,通过sql调优,数据源调优,网站分布式等等手段已经不足以应对,因为单台数据库服务器受CPU,磁盘IO,内存等等限制,很容易会成为整个系统的性能瓶颈。

    目前应对大并发的处理中,集群是比较流行的处理方式。运用可以做集群,数据库也同样可以。

   mysql 读写分离则是应对数据库瓶颈的解决方案之一:通过单台主节点数据库服务器处理数据更新及实时数据,多台从节点数据库服务器处理一些不需要实时的数据查询,从而分担来自数据库方面的压力、提高数据库性能。


mysql主从配置的方式这里就不贴出来了,网络上比比皆是。

这里光贴一些之前项目中的代码实现。


推荐使用通过spring的AbstractRoutingDataSource和mybatis Plugin拦截器实现非常友好的读写分离。

实现数据读写分离的方式有很多,使用这种方式的原因有:

1、这种方式不需要在开发过程或者对原有代码不需要任何改变,原有代码不会有任何的影响。

2、对于事务使用同一个数据库能保证读写的一致性。

3、不需要人为去判断使用哪一个数据库,不用担心会出现人物问题。

需要注意的是:

扩展性上面,当有多个从库的时候,不要想着配置多个从库数据源解决问题,而是应该配置数据库负载均衡然后实现多个从数据库的访问。


下面开始贴细节代码:

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import java.util.HashMap; import java.util.Map; /** * Created by Nemo * 动态数据源实现读写分离 */ public class DynamicDataSource extends AbstractRoutingDataSource {

    //写数据源
private Object writeDataSource;
    //读数据源 private Object readDataSource; @Override public void afterPropertiesSet() { 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() { 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() { return writeDataSource; } public Object getReadDataSource() { return readDataSource; } public void setReadDataSource(Object readDataSource) { this.readDataSource = readDataSource; } }

/**
 * 读/写 操作定义枚举
 * Created by Nemo
 */
public enum DynamicDataSourceGlobal {
    READ, WRITE;
}


/**
* 数据源操作
* Created by Nemo
*/
public final class DynamicDataSourceHolder {

   
    //
 private static final ThreadLocal<DynamicDataSourceGlobal> holder = new ThreadLocal<DynamicDataSourceGlobal>(); private DynamicDataSourceHolder() { // }
   
    // 设置数据源
 public static void putDataSource(DynamicDataSourceGlobal dataSource){ holder.set(dataSource); }

    //获取数据源
    public static DynamicDataSourceGlobal getDataSource(){ return holder.get(); }

    // 清除数据源
    public static void clearDataSource() { holder.remove(); } }


import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.transaction.TransactionDefinition; /**
* 动态数据源管理器 * Created by Nemo */ public class DynamicDataSourceTransactionManager extends DataSourceTransactionManager { /** * 只读事务到读库,读写事务到写库 * @param transaction * @param definition */ @Override protected void doBegin(Object transaction, TransactionDefinition definition) { //设置数据源 boolean readOnly = definition.isReadOnly(); if(readOnly) { DynamicDataSourceHolder.putDataSource(DynamicDataSourceGlobal.READ); } else { DynamicDataSourceHolder.putDataSource(DynamicDataSourceGlobal.WRITE); } super.doBegin(transaction, definition); } /** * 清理本地线程的数据源 * @param transaction */ @Override protected void doCleanupAfterCompletion(Object transaction) { super.doCleanupAfterCompletion(transaction); DynamicDataSourceHolder.clearDataSource(); } }


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; /**
* 重写mybatis 插件执行数据源动态切换 * Created by Nemo */ @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);

  // insert delete update结尾的操作作为写操作,其余,都当作读操作
 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) { //读方法 if(ms.getSqlCommandType().equals(SqlCommandType.SELECT)) { //!selectKey 为自增id查询主键(SELECT LAST_INSERT_ID() )方法,使用主库 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("设置方法[{}] 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 { return target; } } @Override public void setProperties(Properties properties) { // } }


配置文件:

<?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:tx="http://www.springframework.org/schema/tx"
    xmlns:context="http://www.springframework.org/schema/context"
    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.xsd
           http://www.springframework.org/schema/tx
           http://www.springframework.org/schema/tx/spring-tx.xsd">
 
    <context:property-placeholder location="classpath:resources/jdbc.properties"/>
    
    <bean id="abstractDataSource" abstract="true" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="minIdle" value="${jdbc.minIdle}"></property>
        <property name="maxIdle" value="${jdbc.maxIdle}"></property>
        <property name="maxWait" value="${jdbc.maxWait}"></property>
        <property name="maxActive" value="${jdbc.maxActive}"></property>
        <property name="initialSize" value="${jdbc.initialSize}"></property>
        <property name="testWhileIdle"><value>true</value></property>
        <property name="testOnBorrow"><value>true</value></property>
        <property name="testOnReturn"><value>false</value></property>
        <property name="validationQuery"><value>SELECT 1 FROM DUAL</value></property>
        <property name="validationQueryTimeout"><value>1</value></property>
        <property name="timeBetweenEvictionRunsMillis"><value>3000</value></property>
        <property name="numTestsPerEvictionRun"><value>2</value></property>
    </bean>
    
    <bean id="dataSourceRead"  parent="abstractDataSource">
        <property name="url" value="${jdbc.url.read}" />
        <property name="username" value="${jdbc.username.read}"/>
        <property name="password" value="${jdbc.password.read}"/>
    </bean>
    
    <bean id="dataSourceWrite"  parent="abstractDataSource">
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    
    <bean id="dataSource"  class="com.ssm.dao.data.DynamicDataSource">
         <property name="writeDataSource"  ref="dataSourceWrite"></property>
         <property name="readDataSource" ref="dataSourceRead"></property>
    </bean>

    <!--配置基于注解的声明式事务,默认使用注解来管理事务行为-->
    <tx:annotation-driven transaction-manager="transactionManager"/>

    <!--配置事务管理器(mybatis采用的是JDBC的事务管理器)-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--注入数据库连接池-->
        <property name="dataSource" ref="dataSource" />
        <!--扫描entity包,使用别名,多个用;隔开-->
        <property name="typeAliasesPackage" value="com/ssm/entity" />
        <!--扫描sql配置文件:mapper需要的xml文件-->
        <property name="mapperLocations" value="classpath*:com/ssm/dao/sqlxml/*.xml"></property>
        <property name="plugins">
            <array>
                <bean class="com.ssm.dao.data.DynamicPlugin" />
            </array>
        </property>
    </bean>
    
    <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory" />
    </bean>

    <!--配置扫描Dao接口包,动态实现DAO接口,注入到spring容器-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--注入SqlSessionFactory-->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        <!-- 给出需要扫描的Dao接口-->
        <property name="basePackage" value="com.ssm.dao"/>
    </bean>

</beans>
点赞(0)
点了个评