SSM实现Mysql数据库的读写分离

灰太狼 2022-04-08 12:23 260阅读 0赞

### 目录 ###

*   *   *   *  搭建Mysql的主从配置
             *  代码层的读写分离
             *   *  1.创建 DyncmicDataSourceHolder 类
                 *  2.创建 DynamicDataSource，继承AbstractRoutingDataSource
                 *  3 创建DyncmicDataSourceInterceptor，mybatis拦截器
                 *  4.修改mybatis-config
                 *  5.修改spring-dao.xml
                 *  6 修改jdbc.properties

#### 搭建Mysql的主从配置 ####

前面已经介绍过，这里就偷个懒不再多说，不懂的可以查看[MySQL主从同步][MySQL]

#### 代码层的读写分离 ####

> 在读写分离的实现上，Spring为我们提供了路由数据源的AbstractRoutingDataSource抽象类，我们只需编写一个新的类继承它，即可在不改变原有的代码的基础上实现读写分离。

##### 1.创建 DyncmicDataSourceHolder 类 #####

public class DyncmicDataSourceHolder {
        private static Logger logger=LoggerFactory.getLogger(DyncmicDataSourceHolder.class);
    
       /**
       * 保证线程安全，使用线程threadlocal
       */
        private static ThreadLocal<String> contextHolder=new ThreadLocal<String>();
    
        public static final String DB_MASTER="master";
    
        public static final String DB_SLAVE="slave";
    
       /**
       * @return java.lang.String
       * @description 获取连接类型
       */
        public static String getDbType(){
            String db=contextHolder.get();
            if (StringUtils.isBlank(db)) {
                db=DB_MASTER;
            }
            return db;
        }
    
        /**
         * 设置数据源类型
         * @param str
         */
        public static void setDbType(String str){
            logger.debug("使用数据源类型---"+str);
            contextHolder.set(str);
        }
    
        /**
         * 清理连接类型
         */
        public static void clearDaType(){
            contextHolder.remove();
        }
    
    }

##### 2.创建 DynamicDataSource，继承AbstractRoutingDataSource #####

public class DynamicDataSource extends AbstractRoutingDataSource {
    
      @Override
      protected Object determineCurrentLookupKey() {
        return DynamicDataSouceHolder.getDbType();
      }
    }

##### 3 创建DyncmicDataSourceInterceptor，mybatis拦截器 #####

@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class,
        Object.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class,
            RowBounds.class, ResultHandler.class})})
    public class DynamicDataSourceInterceptor implements Interceptor {
    
      private static Logger logger = LoggerFactory.getLogger(DynamicDataSourceInterceptor.class);
      // 写操作的正则表达式
      private static final String REGEX = ".*insert\\u0020.*||.*delete\\u0020|.*update\\u0020.*";
    
      @Override
      public Object intercept(Invocation invocation) throws Throwable {
        // 判断方法是否是被事务管理的
        boolean synchronizationAction = TransactionSynchronizationManager.isActualTransactionActive();
        //获取sql 中的参数
        Object[] objects = invocation.getArgs();
        MappedStatement mappedStatement = (MappedStatement) objects[0];
        //数据源key
        String lookupKey = DynamicDataSouceHolder.DB_MASTER;
        if (!synchronizationAction) {
          // 读方法
          if (mappedStatement.getSqlCommandType().equals(SqlCommandType.SELECT)) {
            //selectKey为自增id查询主键（SELECT LAST_INSERT_ID）方法
            if (mappedStatement.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
              lookupKey = DynamicDataSouceHolder.DB_MASTER;
            } else {
              //获取sql 语句
              BoundSql boundSql = mappedStatement.getSqlSource().getBoundSql(objects[1]);
              String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replace("\\t\\n\\r", " ");
              if (sql.matches(REGEX)) {
                lookupKey = DynamicDataSouceHolder.DB_MASTER;
              } else {
                lookupKey = DynamicDataSouceHolder.DB_SLAVE;
              }
            }
          }
        } else {
          lookupKey = DynamicDataSouceHolder.DB_MASTER;
        }
        logger.debug("设置方法[{}] use [{}] Strategy,SqlCommandType [{}]...", mappedStatement.getId(),
            lookupKey, mappedStatement.getSqlCommandType().name());
        DynamicDataSouceHolder.setDbType(lookupKey);
        return invocation.proceed();
      }
    
      @Override
      public Object plugin(Object target) {
        //Executor表示含有增删改查的操作 的对象
        if (target instanceof Executor) {
          //有增删改查的操作,就调用拦截方法
          return Plugin.wrap(target, this);
        } else {
          //无增删改查的操作。不做处理
          return target;
        }
      }
    
      @Override
      public void setProperties(Properties properties) {
    
      }
    }

##### 4.修改mybatis-config #####

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
      PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
      "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
    	
    	<settings>
    		
    		<setting name="useGeneratedKeys" value="true" />
    
    		
    		<setting name="useColumnLabel" value="true" />
    
    		
    		<setting name="mapUnderscoreToCamelCase" value="true" />
    		
    		<setting name="logImpl" value="STDOUT_LOGGING" />
    	</settings>
    
    	<plugins>
    		
    		<plugin interceptor="com.learn.interceptor.DynamicDataSourceInterceptor"></plugin>
    		
    		
    		<plugin interceptor="com.github.pagehelper.PageHelper">
    			
    			<property name="dialect" value="mysql"/>
    		</plugin>
    	</plugins>
    </configuration>

##### 5.修改spring-dao.xml #####

<bean id="abstractDataSource" abstract="true" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        
        <property name="maxPoolSize" value="30"/>
        <property name="minPoolSize" value="10"/>
        
        <property name="autoCommitOnClose" value="false"/>
        
        <property name="checkoutTimeout" value="10000"/>
        
        <property name="acquireRetryAttempts" value="2"/>
      </bean>
      
      <bean id="masterDataSource" parent="abstractDataSource">
        
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.master.url}"/>
        <property name="user" value="${jdbc.master.username}"/>
        <property name="password" value="${jdbc.master.password}"/>
      </bean>
      
      <bean id="slaveDataSource" parent="abstractDataSource">
        
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.slave.url}"/>
        <property name="user" value="${jdbc.slave.username}"/>
        <property name="password" value="${jdbc.slave.password}"/>
      </bean>
      
      <bean id="dynamicDataSource" class="com.learn.dao.split.DynamicDataSource">
        <property name="targetDataSources">
          <map>
            <entry key="master" value-ref="masterDataSource"/>
            <entry key="slave" value-ref="slaveDataSource"/>
          </map>
        </property>
      </bean>
      
      <bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <property name="targetDataSource">
          <ref bean="dynamicDataSource"/>
        </property>
      </bean>

##### 6 修改jdbc.properties #####

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.master.url=jdbc:mysql://172.17.0.2:3306/o2o?useUnicode=true&characterEncoding=utf8
    jdbc.slave.url=jdbc:mysql://172.17.0.3:3306/o2o?useUnicode=true&characterEncoding=utf8
    jdbc.master.username=******
    jdb.master.password=******
    jdbc.slave.username=******
    jdb.slave.password=******
    ---------------------

参考文章：[https://blog.csdn.net/jessDL/article/details/82725957][https_blog.csdn.net_jessDL_article_details_82725957]

[MySQL]: https://blog.csdn.net/weixin_43882034/article/details/84994174
[https_blog.csdn.net_jessDL_article_details_82725957]: https://blog.csdn.net/jessDL/article/details/82725957