springboot sqlServer aop多数据源读写分离+jta事务管理

秋小苒

已于 2023-06-29 19:03:04 修改

阅读量539

点赞数

分类专栏：数据库 Java 文章标签： spring boot sqlserver 后端

于 2023-06-29 16:57:21 首次发布

本文链接：https://blog.csdn.net/flower_CSDN/article/details/131457789

版权

Java 同时被 2 个专栏收录

57 篇文章 0 订阅

订阅专栏

数据库

37 篇文章 0 订阅

订阅专栏

背景

这里的表太多了，而且关联关系都很复杂，一个sql好长好长，部分表的数据量也很大，查询起来非常耗时，一个sql执行下来要十几秒，xx报表查询sql执行要17s，主管说读的时间太长了阻塞了写操作，所以想改成部分业务代码改成读写分离的模式。

思路历程

1，本来一开始想用shardingsphere-jdbc的，看着配置很简单，改改yml配置，即可自动区分读写操作实现主从切换。搞了两天，换了好多jar版本，一直报错，始终无法解决一遇到子查询，关联查询，表达式操作就报错的问题，遂放弃。
2，还看了一下mycat的方式，但是mycat的配置起来有些复杂，关键在这我连测试环境的服务器都不知道，盲人摸象。
最后还是参考了网上使用了自定义编码的方式，采用了自定义注解+aop的方式来实现主从切换，读写分离。

最终方案：自定义注解+aop的方式来实现主从切换

数据库：sqlServer，一主+两从
pom

        <!--分布式事务管理-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jta-atomikos</artifactId>
        </dependency>
        
      <!-- aop -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.5</version>
        </dependency>

yml配置：

spring:
  datasource:
    master:
      url: jdbc:sqlserver://xxxx:1433;DatabaseName=xxxx
      username: sa
      password: sql
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

    slave1:
      url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=slaveXxxx
      username: sa
      password: sql
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

    slave2:
      url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=slaveXxxx
      username: sa
      password: sql
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

    hikari:
      minimum-idle: 10
      idle-timeout: 600000
      maximum-pool-size: 10
      auto-commit: true
      pool-name: MyHakariCP
      max-lifetime: 1800000
      connection-timeout: 30000
      connection-test-query: SELECT 1

数据源名称枚举

public enum DataSourceType {
    MASTER,SLAVE1,SLAVE2
}

DataSourceConfig

package com.sub.pandora.business.db;

import com.microsoft.sqlserver.jdbc.SQLServerXADataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * @author: wangqiuran
 * @description: 多数据源配置文件，用来构建一个多数据源的DataSource
 * @date: 2023/6/27 14:48
 */
@Configuration
public class DataSourceConfig {
    @Resource
    private MasterParam masterParam;
    @Resource
    private Slave1Param slave1Param;
    @Resource
    private Slave2Param slave2Param;
    @Resource
    private HikariParam hikariParam;

    @Bean
    @DependsOn({ "transactionManager" })
    public DataSource masterDs() throws SQLException {
        // 设置数据库连接
        SQLServerXADataSource sqlServerDataSource = new SQLServerXADataSource();
        sqlServerDataSource.setURL(masterParam.getUrl());
        sqlServerDataSource.setUser(masterParam.getUsername());
        sqlServerDataSource.setPassword(masterParam.getPassword());
        // 事务管理器
        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setXaDataSource(sqlServerDataSource);
        atomikosDataSourceBean.setUniqueResourceName("masterTransactionManager");

        atomikosDataSourceBean.setMaxIdleTime(hikariParam.getIdleTimeout());
        atomikosDataSourceBean.setMaxPoolSize(hikariParam.getMaximumPoolSize());
        atomikosDataSourceBean.setLoginTimeout(hikariParam.getConnectionTimeout());
        atomikosDataSourceBean.setTestQuery(hikariParam.getConnectionTestQuery());
        return atomikosDataSourceBean;
    }

    @Bean
    @DependsOn({ "transactionManager" })
    public DataSource slaveDs1() throws SQLException {
        // 设置数据库连接
        SQLServerXADataSource sqlServerDataSource = new SQLServerXADataSource();
        sqlServerDataSource.setURL(slave1Param.getUrl());
        sqlServerDataSource.setUser(slave1Param.getUsername());
        sqlServerDataSource.setPassword(slave1Param.getPassword());
        // 事务管理器
        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setXaDataSource(sqlServerDataSource);
        atomikosDataSourceBean.setUniqueResourceName("slave1TransactionManager");

        atomikosDataSourceBean.setMaxIdleTime(hikariParam.getIdleTimeout());
        atomikosDataSourceBean.setMaxPoolSize(hikariParam.getMaximumPoolSize());
        atomikosDataSourceBean.setLoginTimeout(hikariParam.getConnectionTimeout());
        atomikosDataSourceBean.setTestQuery(hikariParam.getConnectionTestQuery());
        return atomikosDataSourceBean;
    }

    @Bean
    @DependsOn({ "transactionManager" })
    public DataSource slaveDs2() throws SQLException {
        // 设置数据库连接
        SQLServerXADataSource sqlServerDataSource = new SQLServerXADataSource();
        sqlServerDataSource.setURL(slave2Param.getUrl());
        sqlServerDataSource.setUser(slave2Param.getUsername());
        sqlServerDataSource.setPassword(slave2Param.getPassword());
        // 事务管理器
        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setXaDataSource(sqlServerDataSource);
        atomikosDataSourceBean.setUniqueResourceName("slave2TransactionManager");

        atomikosDataSourceBean.setMaxIdleTime(hikariParam.getIdleTimeout());
        atomikosDataSourceBean.setMaxPoolSize(hikariParam.getMaximumPoolSize());
        atomikosDataSourceBean.setLoginTimeout(hikariParam.getConnectionTimeout());
        atomikosDataSourceBean.setTestQuery(hikariParam.getConnectionTestQuery());
        return atomikosDataSourceBean;
    }

    @Bean
    public DataSource targetDs(@Qualifier("masterDs") DataSource masterDs,
                               @Qualifier("slaveDs1") DataSource slaveDs1,
                               @Qualifier("slaveDs2") DataSource slaveDs2){
        Map<Object,Object> targetDs = new HashMap<>();
        targetDs.put(DataSourceType.MASTER,masterDs);
        targetDs.put(DataSourceType.SLAVE1,slaveDs1);
        targetDs.put(DataSourceType.SLAVE2,slaveDs2);

        DefaultRoutingDataSource routingDs = new DefaultRoutingDataSource();
        //绑定所有的数据源
        routingDs.setTargetDataSources(targetDs);
        //绑定默认数据源
        routingDs.setDefaultTargetDataSource(masterDs);
        return routingDs;
    }

}

DefaultRoutingDataSource

public class DefaultRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceSwitcher.getCurrentDb();
    }
}

数据源切换类：

@Slf4j
public class DataSourceSwitcher {
    private static final ThreadLocal<DataSourceType> DB_TYPE_CONTAINER = new ThreadLocal<>();

    private static void switchDb(DataSourceType dbType){
        DB_TYPE_CONTAINER.set(dbType);
        log.info("切换数据源:{}",dbType);
    }

    public static void useMaster(){
        switchDb(DataSourceType.MASTER);
    }

    public static void useSlave(){
        DataSourceType[] slaves = new DataSourceType[]{DataSourceType.SLAVE1, DataSourceType.SLAVE2};
        int num = new Random().nextInt(2);
        switchDb(slaves[num]);
    }

    public static DataSourceType getCurrentDb(){
        return DB_TYPE_CONTAINER.get();
    }

    public static void removeDb(){
        DB_TYPE_CONTAINER.remove();
    }
}

aop

@Aspect
@Component
public class DataSourceAop {

    /**
     * 读切点
     */
    @Pointcut("@annotation(com.sub.pandora.business.db.Read)")
    public void readPointcut(){}

    /**
     * 写切点
     */
    @Pointcut("@annotation(com.sub.pandora.business.db.Write)")
    public void writePointcut(){}


    @Before("readPointcut()")
    public void beforeRead(){
        DataSourceSwitcher.useSlave();
    }

    @After("readPointcut()")
    public void afterRead(){
       // 读完之后还是切回master,不加read,write注解的时候默认还是回到master
        DataSourceSwitcher.useMaster();
    }
    @Before("writePointcut()")
    public void beforeWrite(){
        DataSourceSwitcher.useMaster();
    }
}

读注解

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Read {
}

写注解

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Write {
}

mybatis配置：

@MapperScan(basePackages = {"com.sub.pandora.business.mapper"})
@Configuration
@EnableTransactionManagement
public class MyBatisConfig {

    @Qualifier("targetDs")
    @Autowired
    private DataSource dataSource;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:/mapper/*.xml"));
        return bean.getObject();
    }
}

AtomikosConfig 事务管理

@Configuration
public class AtomikosConfig {

    @Bean(name = "userTransaction")
    public UserTransaction userTransaction() throws Throwable {
        UserTransactionImp userTransactionImp = new UserTransactionImp();
        userTransactionImp.setTransactionTimeout(10000);
        return userTransactionImp;
    }

    @Bean(name = "atomikosTransactionManager")
    public TransactionManager atomikosTransactionManager() throws Throwable {
        UserTransactionManager userTransactionManager = new UserTransactionManager();
        userTransactionManager.setForceShutdown(false);
        return userTransactionManager;
    }

    @Bean(name = "transactionManager")
    @DependsOn({ "userTransaction", "atomikosTransactionManager" })
    public PlatformTransactionManager transactionManager() throws Throwable {
        UserTransaction userTransaction = userTransaction();
        TransactionManager atomikosTransactionManager = atomikosTransactionManager();
        JtaTransactionManager jtaTransactionManager = new JtaTransactionManager(userTransaction, atomikosTransactionManager);
        jtaTransactionManager.setAllowCustomIsolationLevels(true);
        return jtaTransactionManager;
    }
}

jta.properties,如果多个项目同时使用atomikos，会导致日志名称冲突，需要增加这个配置

com.atomikos.icatch.log_base_name=scm-jta-tmlog

代码有些不全，上传到我的资源了：
https://upload.csdn.net/creation/uploadResources?spm=1011.2124.3001.5646

大部分代码是参考了，shusheng007
https://www.zhihu.com/question/381631883
自己做了一些改造

秋小苒

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
springboot sqlServer aop多数据源读写分离+jta事务管理

数据库 sqlserver springboot 读写分离
复制链接

扫一扫

专栏目录