SSM+Mysql8 实现读写分离

开发准备

centos7 安装mysql 8 https://blog.csdn.net/jessDL/article/details/82714155

centos7 下 mysql8 的主从配置 https://blog.csdn.net/jessDL/article/details/82720091

一个可以正常运行的SSM 项目，我的ssm项目如下：

dataSource.spilt 是我们做读写分离要添加的内容

前面两篇文件已经搭建好了mysql的主从配置，下面实现Spring4.2 +SpringMvc+Mybatis3 的读写分离

1.创建 DyncmicDataSourceHolder 类，

public class DyncmicDataSourceHolder {
    private static Logger logger=LoggerFactory.getLogger(DyncmicDataSourceHolder.class);

    //线程安全，用来保存所用数据源的key
    private static ThreadLocal<String> contextHolder=new ThreadLocal<String>();

    public static final String DB_MASTER="master";

    public static final String DB_SLAVE="slave";

    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

public class DynamicDataSource extends AbstractRoutingDataSource{

    @Override
    protected Object determineCurrentLookupKey() {
        return DyncmicDataSourceHolder.getDbType();
    }

}

3 创建DyncmicDataSourceInterceptor

@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 DyncmicDataSourceInterceptor implements Interceptor{
    private static Logger logger=LoggerFactory.getLogger(DyncmicDataSourceInterceptor.class);

    private static final String REGEX=".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*|";
    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        //表示是否有事务
        Boolean synchronizationActive=TransactionSynchronizationManager.isActualTransactionActive();
        //获取sql 中的参数
        Object[] objects=invocation.getArgs();
        //获取第一个参数，可判断是增删改查的哪一种操作
        MappedStatement ms=(MappedStatement)objects[0];
        //数据源key
        String lookupKey = DyncmicDataSourceHolder.DB_MASTER;


        if (synchronizationActive!=true) {//没有事务
            //读方法
            if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
                //selectKey为自增查询主键，这里表示插入数据后立即查询，所以使用主库数据源
                if (ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
                    lookupKey=DyncmicDataSourceHolder.DB_MASTER;
                }else {
                    //获取sql 语句
                    BoundSql boundSql=ms.getSqlSource().getBoundSql(objects[1]);
                    String sql=boundSql.getSql().toLowerCase(Locale.CHINA)
                            .replaceAll("\\t\\n\\r"," ");
                    if (sql.matches(REGEX)) {
                        lookupKey=DyncmicDataSourceHolder.DB_MASTER;
                    }else {
                        lookupKey=DyncmicDataSourceHolder.DB_SLAVE;
                    }
                }
            }
        }else{
            lookupKey=DyncmicDataSourceHolder.DB_MASTER;
        }
        logger.debug("设置方法[{}] use [{}] SqlCommandType[{}]",ms.getId(),lookupKey,ms.getSqlCommandType().name());
        DyncmicDataSourceHolder.setDbType(lookupKey);
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        //Executor表示含有增删改查的操作 的对象
        if (target instanceof Executor) {
            //有增删改查的操作,就调用拦截方法
            return Plugin.wrap(target, this);
        }
        //无增删改查的操作。不做处理
        return target;
    }

    @Override
    public void setProperties(Properties arg0) {
        // TODO Auto-generated method stub

    }

}

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="defaultExecutorType" value="REUSE" />
  </settings>

  <typeAliases>

  </typeAliases>

  <!-- 配置分页插件4.x的版本 -->
 <!--  <plugins>
    <plugin interceptor="com.github.pagehelper.PageHelper">
      设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库
      <property name="dialect" value="mysql"/>
    </plugin>
  </plugins> -->


 <plugins>
    <plugin interceptor="com.dataSource.spilt.DyncmicDataSourceInterceptor">
    </plugin>
  </plugins>
</configuration>

5.修改spring-mybatis.xml

<!-- 配置数据源 -->
    <bean name="abstartDataSource" abstract="true" class="com.alibaba.druid.pool.DruidDataSource" 
         destroy-method="close">

        <!-- 初始化连接大小 -->
        <property name="initialSize" value="0" />
        <!-- 连接池最大使用连接数量 -->
        <property name="maxActive" value="20" />
        <!-- 连接池最大空闲 -->
        <!--<property name="maxIdle" value="20" />-->
        <!-- 连接池最小空闲 -->
        <property name="minIdle" value="0" />
        <!-- 获取连接最大等待时间 -->
        <property name="maxWait" value="60000" />

        <!-- <property name="poolPreparedStatements" value="true" /> <property name="maxPoolPreparedStatementPerConnectionSize" value="33" /> -->

     <property name="validationQuery" value="${validationQuery}" /> 
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
        <property name="testWhileIdle" value="true" />

        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />
        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="25200000" />

        <!-- 打开removeAbandoned功能 -->
        <property name="removeAbandoned" value="true" />
        <!-- 1800秒，也就是30分钟 -->
        <property name="removeAbandonedTimeout" value="1800" />
        <!-- 关闭abanded连接时输出错误日志 -->
        <property name="logAbandoned" value="true" />

        <!-- 监控数据库 -->
        <!-- <property name="filters" value="stat" /> -->
        <property name="filters" value="mergeStat" />
    </bean>


    <!-- 主数据源 -->
    <bean id="master" parent="abstartDataSource">
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.master.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

    <!-- 从数据源 -->
    <bean id="slave" parent="abstartDataSource">
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.slave.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

    <!-- 配置动态数据源，这里的targetDataSource就是路由数据源所对应的名称 -->
    <bean id="dynamicDataSource" class="com.dataSource.spilt.DynamicDataSource">
        <property name="targetDataSources">
            <map>
                <entry value-ref="master" key="master"></entry>
                <entry value-ref="slave" key="slave"></entry>
            </map>
        </property>
    </bean>

    <bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <property name="targetDataSource">
            <ref bean="dynamicDataSource"/>
        </property>
    </bean>

6 修改jdbc.properties

#com.mysql.jdbc.Driver  mysql 5.x
#com.mysql.cj.jdbc.Driver mysql 8.0
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.master.url=jdbc:mysql://192.168.1.126:3306/shiro?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false
jdbc.slave.url=jdbc:mysql://192.168.1.127:3306/shiro?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false

jdbc.username=root
jdbc.password=jessDl2018@
validationQuery=SELECT 1