SpringBoot采用方法名和注解实现数据库的读写分离并且有事务回滚

最新推荐文章于 2022-12-28 17:57:44 发布

一颗知足的心

最新推荐文章于 2022-12-28 17:57:44 发布

阅读量844

点赞数 4

分类专栏： SpringBoot 数据库文章标签：数据库 aop

本文链接：https://blog.csdn.net/StreamlineWq/article/details/116999177

版权

SpringBoot 同时被 2 个专栏收录

15 篇文章 0 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

SpringBoot实现读写分离有两种方式
第一种是根据方法名，比如"select、get、query"开头的方法走从库，其余的走主库
第二种就是注解式，在方法上加上注解，里面指定走主库还是从库。
一、下面我先介绍第一种方式，直接上代码。
configure.properties

spring.datasource.server.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.datasource.server.username=root
spring.datasource.server.password=123456

spring.datasource.server.slave.url=jdbc:mysql://localhost:test-slave/?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.datasource.server.slave.username=root
spring.datasource.server.slave.password=123456

application.yml,master是主库，slave是从库

spring:
  datasource:
    master:
      username: ${spring.datasource.server.username}
      password: ${spring.datasource.server.password}
      url: ${spring.datasource.server.url}
    slave:
      username: ${spring.datasource.server.slave.username}
      password: ${spring.datasource.server.slave.password}
      url: ${spring.datasource.server.slave.url}

1.DataSourceConfig数据源加载类

@Configuration
@MapperScan(basePackages = "com.aaa.bbb.mapper", sqlSessionTemplateRef = "sqlTemplateTest")
public class DataSourceConfig {


    /**
     * 主库
     */
    @Bean(name="master")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource master() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 从库
     */
    @Bean(name="slave")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slave() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 实例化数据源路由
     */
    @Bean(name="dynamicDBTest")
    public DataSourceRouter dynamicDBTest(@Qualifier("master") DataSource masterDataSource,
                                      @Autowired(required = false) @Qualifier("slave") DataSource slaveDataSource) {
        DataSourceRouter dynamicDataSource = new DataSourceRouter();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource);
        if (slaveDataSource != null) {
            targetDataSources.put("slave", slaveDataSource);
        }
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
        return dynamicDataSource;
    }
    /**
     * 配置sessionFactory
     */
    @Bean
    public SqlSessionFactory sessionFactory(@Qualifier("dynamicDBTest") DataSource dynamicDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:com/aa/bb/cc/mapper/**/*.xml"));
        bean.setDataSource(dynamicDataSource);
        return bean.getObject();
    }


    /**
     * 创建sqlTemplate
     */
    @Bean
    public SqlSessionTemplate sqlTemplateTest(@Qualifier("sessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    /**
     * 事务配置
     */
    @Bean(name = "dataSourceTx")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dynamicDBTest") DataSource dynamicDataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dynamicDataSource);
        return dataSourceTransactionManager;
    }

}

2.DataSourceContextHolder.java

/**
 * 利用ThreadLocal封装的保存数据源上线的上下文context
 */
public class DataSourceContextHolder {

    private static final ThreadLocal<String> context = new ThreadLocal<>();

    /**
     * 赋值
     */
    public static void set(String datasourceType) {
        context.set(datasourceType);
    }

    /**
     * 获取值
     */
    public static String get() {
        return context.get();
    }

    public static void clear() {
        context.remove();
    }
}

DataSourceRouter.java 这个类继承了AbstractRoutingDataSource，重写了determineCurrentLookupKey方法，这是实现动态路由的关键代码

public class DataSourceRouter extends AbstractRoutingDataSource {

    /**
     * 最终的determineCurrentLookupKey返回的是从DataSourceContextHolder中拿到的,因此在动态切换数据源的时候注解
     * 应该给DataSourceContextHolder设值
     */
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();
    }
}

3.最后咱再写一个切面，去切mapper层的所有方法
DataSourceAspect.java

@Aspect
@Component
@Order(1)
public class DataSourceAspect {

    /**
     * 在进入Service方法之前执行
     * 读取的时候都走从库，增删改都走主库
     *这里只是做了简单的关键字匹配，可按需求通过自定义注解、正则等方式处理
     * @param point 切面对象
     */
    @Before("execution (* com.bb.cc.mapper..*(..))")
    public void before(JoinPoint point) {
        // 获取到当前执行的方法名
        String methodName = point.getSignature().getName();
        // 设置数据源
        if (isSlave(methodName)) {//从库
            DataSourceContextHolder.set("slave");
        }else{//主库
            DataSourceContextHolder.set("master");
        }
    }

    @After("execution (* com.aa.bb.cc.mapper..*(..))")
    public void afterSwitchDS(JoinPoint point){
        DataSourceContextHolder.clear();
    }

    /**
     * 判断是否为从库
     * @param methodName
     * @return
     */
    private Boolean isSlave(String methodName) {
        // 方法名以select,query、find、get开头的方法名走从库
        return StringUtils.startsWithAny(methodName, "select","query", "find", "get");
    }
}

最后再写一个测试的controller，我们两个方法都是同样的SQL语句，只是其中一个方法名前面是以select开头的，根据我们切面里的逻辑，我们应该走从库，代码亲测有效

    @RequestMapping(value = "/master")
    @ApiOperation(value = "主数据库读", httpMethod = "POST")
    public ResultMsg readMaster() {
        return ResultMsg.success(userInfoService.test());
    }

    @RequestMapping(value = "/slave")
    @ApiOperation(value = "从数据库读", httpMethod = "POST")
    public ResultMsg readSlave() {
        return ResultMsg.success(userInfoService.SelectTest());
    }

二、根据注解的实现
数据源那里基本不变，唯一的变化就是我们新建一个注解
value值我们可以用枚举来表示

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface DataSourceSwitcher {
    /**
     * 默认数据源
     * @return
     */
    DataSourceEnum value() default DataSourceEnum.MASTER;
    /**
     * 清除
     * @return
     */
    boolean clear() default true;

}

新建一个切面，做一下小小的修改,我们在这里就不是去切mapper层了，而是找到加了@DataSourceSwitcher注解的方法，去执行数据源的切换

@Slf4j
@Aspect
@Order(value = 1)
@Component
public class DataSourceContextAop {

    @Around("@annotation(com.bb.annotation.DataSourceSwitcher)")
    public Object setDynamicDataSource(ProceedingJoinPoint pjp) throws Throwable {
        boolean clear = false;
        try {
            Method method = this.getMethod(pjp);
            DataSourceSwitcher dataSourceSwitcher = method.getAnnotation(DataSourceSwitcher.class);
            clear = dataSourceSwitcher.clear();
            DataSourceContextHolder.set(dataSourceSwitcher.value().getDataSourceName());
            log.info("数据源切换至：{}", dataSourceSwitcher.value().getDataSourceName());
            return pjp.proceed();
        } finally {
            if (clear) {
                DataSourceContextHolder.clear();
            }

        }
    }

    private Method getMethod(JoinPoint pjp) {
        MethodSignature signature = (MethodSignature) pjp.getSignature();
        return signature.getMethod();
    }

}

以上代码亲测有效，有错误的欢迎评论区指出

补充修改：
经过测试，DataSourceConfig这个类里面的事务配置：

    /**
     * 事务配置
     */
    @Bean(name = "dataSourceTx")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dynamicDBTest") DataSource dynamicDataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dynamicDataSource);
        return dataSourceTransactionManager;
    }

这样配的话要实现事务回滚，只有加上@Transactionl注解才能成功，这样就导致每个方法都需要加注解，和我的初衷不一样，所以我略加改进，改进后的DataSourceConfig.java是这样的：

@Configuration
@MapperScan(basePackages = "com.aa.bb.cc.mapper", sqlSessionTemplateRef = "sqlTemplateTest")
public class DataSourceConfig {
    @Autowired
    private GeneralDataBasePropertiesConfig generalDataBasePropertiesConfigMaster;
    @Autowired
    private GeneralDataBasePropertiesConfig generalDataBasePropertiesConfigSlave;
    /**
     * 主库
     */
    @Bean(name="master")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource master() {
        DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
        generalDataBasePropertiesConfigMaster.buildDatasource(druidDataSource);
        return druidDataSource;
    }

    /**
     * 从库
     */
    @Bean(name="slave")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slave() {
        DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
        generalDataBasePropertiesConfigSlave.buildDatasource(druidDataSource);
        return druidDataSource;
    }

    /**
     * 实例化数据源路由
     */
    @Bean(name="dynamicDBTest")
    public DataSourceRouter dynamicDBTest(@Qualifier("master") DataSource masterDataSource,
                                      @Autowired(required = false) @Qualifier("slave") DataSource slaveDataSource) {
        DataSourceRouter dynamicDataSource = new DataSourceRouter();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource);
        if (slaveDataSource != null) {
            targetDataSources.put("slave", slaveDataSource);
        }
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
        return dynamicDataSource;
    }
    /**
     * 配置sessionFactory
     */
    @Bean
    public SqlSessionFactory sessionFactory(@Qualifier("dynamicDBTest") DataSource dynamicDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:com/aa/bb/cc/mapper/**/*.xml"));
        bean.setDataSource(dynamicDataSource);
        return bean.getObject();
    }


    /**
     * 创建sqlTemplate
     */
    @Bean
    public SqlSessionTemplate sqlTemplateTest(@Qualifier("sessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "serverTransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("dynamicDBTest") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "serverTransactionInterceptor")
    @Primary
    public TransactionInterceptor TxAdvice(@Qualifier("serverTransactionManager") DataSourceTransactionManager transactionManager) {
        String source = DataSourceContextHolder.get();
        NameMatchTransactionAttributeSource nameMatchTransactionAttributeSource;
        if("master".equals(source)){
            nameMatchTransactionAttributeSource = generalDataBasePropertiesConfigMaster.getPropagationSource();
        }else{
            nameMatchTransactionAttributeSource = generalDataBasePropertiesConfigSlave.getPropagationSource();
        }
        TransactionInterceptor transactionInterceptor = new TransactionInterceptor();
        transactionInterceptor.setTransactionManager(transactionManager);
        transactionInterceptor.setTransactionAttributeSource(nameMatchTransactionAttributeSource);
        return transactionInterceptor;
    }

    @Bean(name = "serverTxAdviceAdvisor")
    @Primary
    public Advisor txAdviceAdvisor(@Qualifier("serverTransactionInterceptor") TransactionInterceptor transactionInterceptor) {
        AspectJExpressionPointcut pointcut = new AspectJExpressionPointcut();
        pointcut.setExpression("execution (* com.aa.bb.cc.service..*(..))");
        return new DefaultPointcutAdvisor(pointcut, transactionInterceptor);
    }
}

我采用事务的Interceptor来作处理，用DataSourceContextHolder选择器来获取当前的数据源，这样就实现了不加@Transactionl也能实现事务的回滚，service调service是同一个事务，也能成功

最后贴上GeneralDataBasePropertiesConfig.java的代码：

@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class GeneralDataBasePropertiesConfig {

    private int maxActive;
    private int minIdle;
    private int initialSize;
    private boolean keepAlive;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private int maxWait;
    private int minEvictableIdleTimeMillis;
    private int maxPoolPreparedStatementPerConnectionSize;
    private List<String> connectionInitSqls;
    private boolean poolPreparedStatements;
    private boolean testOnReturn;
    private String driverClassName;
    private Properties connectProperties;

    public NameMatchTransactionAttributeSource getPropagationSource() {
        DefaultTransactionAttribute defaultAttribute = new DefaultTransactionAttribute(TransactionDefinition.PROPAGATION_REQUIRED);
        DefaultTransactionAttribute readOnlyAttribute = new DefaultTransactionAttribute(TransactionDefinition.PROPAGATION_REQUIRED);
        readOnlyAttribute.setReadOnly(true);
        NameMatchTransactionAttributeSource source = new NameMatchTransactionAttributeSource();
        source.addTransactionalMethod("*", defaultAttribute);
        source.addTransactionalMethod("save*", defaultAttribute);
        source.addTransactionalMethod("insert*", defaultAttribute);
        source.addTransactionalMethod("delete*", defaultAttribute);
        source.addTransactionalMethod("update*", defaultAttribute);
        source.addTransactionalMethod("exec*", defaultAttribute);
        source.addTransactionalMethod("set*", defaultAttribute);
        source.addTransactionalMethod("add*", defaultAttribute);
        source.addTransactionalMethod("get*", readOnlyAttribute);
        source.addTransactionalMethod("query*", readOnlyAttribute);
        source.addTransactionalMethod("find*", readOnlyAttribute);
        source.addTransactionalMethod("list*", readOnlyAttribute);
        source.addTransactionalMethod("count*", readOnlyAttribute);
        source.addTransactionalMethod("is*", readOnlyAttribute);
        return source;
    }

    public void buildDatasource(DruidDataSource druidDataSource,boolean exclude) {
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setKeepAlive(keepAlive);
        druidDataSource.setValidationQuery(validationQuery);
        druidDataSource.setTestWhileIdle(testWhileIdle);
        druidDataSource.setTestOnBorrow(testOnBorrow);
        druidDataSource.setMaxWait(maxWait);
        druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        druidDataSource.setConnectionInitSqls(connectionInitSqls);
        druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
        druidDataSource.setTestOnReturn(testOnReturn);
        druidDataSource.setDriverClassName(driverClassName);
        if (!exclude) druidDataSource.setConnectProperties(connectProperties);
    }

    public void buildDatasource(DruidDataSource druidDataSource) {
        buildDatasource(druidDataSource,false);
    }

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public void setKeepAlive(boolean keepAlive) {
        this.keepAlive = keepAlive;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public void setTestWhileIdle(boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testOnBorrow = testOnBorrow;
    }

    public void setMaxWait(int maxWait) {
        this.maxWait = maxWait;
    }

    public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    }

    public void setConnectionInitSqls(List<String> connectionInitSqls) {
        this.connectionInitSqls = connectionInitSqls;
    }

    public void setPoolPreparedStatements(boolean poolPreparedStatements) {
        this.poolPreparedStatements = poolPreparedStatements;
    }

    public void setTestOnReturn(boolean testOnReturn) {
        this.testOnReturn = testOnReturn;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    public void setConnectProperties(Properties connectProperties) {
        this.connectProperties = connectProperties;
    }

}