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

最新推荐文章于 2025-07-07 00:22:25 发布

转载最新推荐文章于 2025-07-07 00:22:25 发布 · 835 阅读

文章标签：

#SSM #MySQL

SSM 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了如何通过Mysql主从配置实现数据的读写分离，包括创建DyncmicDataSourceHolder类、DynamicDataSource类、DyncmicDataSourceInterceptor拦截器，以及对mybatis-config、spring-dao.xml和jdbc.properties文件的修改。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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>
		<!-- 使用jdbc的getGeneratedKeys获取数据库自增主键值 -->
		<setting name="useGeneratedKeys" value="true" />

		<!-- 使用列别名替换列名 默认:true -->
		<setting name="useColumnLabel" value="true" />

		<!-- 开启驼峰命名转换:Table{create_time} -> Entity{createTime} -->
		<setting name="mapUnderscoreToCamelCase" value="true" />
		<!-- 打印查询语句 -->
		<setting name="logImpl" value="STDOUT_LOGGING" />
	</settings>

	<plugins>
		<!--自定义的mybatis拦截器-->
		<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">
    <!-- c3p0连接池的私有属性 -->
    <property name="maxPoolSize" value="30"/>
    <property name="minPoolSize" value="10"/>
    <!-- 关闭连接后不自动commit -->
    <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.youkuaiyun.com/jessDL/article/details/82725957