devbox
空白诗007
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

SpringBoot-MySql-MyBatis配置多数据源 读写分离_多数据源 validationquery

作者：空白诗007 | 2024-07-20 13:46:59

赞

踩

多数据源 validationquery

appliction.yml

druid.stat.mergeSql=true;druid.stat.slowSqlMillis=10000
        driver-class-name: com.mysql.jdbc.Driver
        filters: stat,wall,log4j
        initialSize: 10
        maxActive: 30
        maxPoolPreparedStatementPerConnectionSize: 30
        maxWait: 60000
        minEvictableIdleTimeMillis: 300000
        minIdle: 10
        password: Tzjj@2022
        poolPreparedStatements: true
        readSize: 1
        testOnBorrow: false
        testOnReturn: false
        testWhileIdle: true
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8
        useGlobalDataSourceStat: true
        username: admin
        validationQuery: SELECT 1 FROM DUAL
    slave:
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        driverClassName: com.mysql.jdbc.Driver
        filters: stat,wall,logback
        initialSize: 10
        maxActive: 30
        maxPoolPreparedStatementPerConnectionSize: 30
        maxWait: 60000
        minEvictableIdleTimeMillis: 300000
        minIdle: 10
        password: Tzjj@2022
        poolPreparedStatements: true
        testOnBorrow: false
        testOnReturn: false
        testWhileIdle: true
        timeBetweenEvictionRunsMillis: 60000
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8
        useGlobalDataSourceStat: true
        username: admin
        validationQuery: SELECT 1 FROM DUAL
    thymeleaf:
        cache: false
        enabled: false


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
package com.microvideo.ewcp.dataSourceConfig;


import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

/**
 * 数据源的切入面
 *
 */
@Aspect
@Component
@Order(-1)
public class DataSourceAOP {

    @Before("execution(* com.microvideo.ewcp.*.dao..*.select*(..)) || execution(* com.microvideo.ewcp.*.dao..*.get*(..))|| execution(* com.microvideo.ewcp.*.dao..*.query*(..))")
    public void setWriteDataSourceType() {
        DataSourceManager.setSlave();
        System.out.println("slave");
    }

    @Before("execution(* com.microvideo.ewcp.*.dao..*.add*(..)) || execution(* com.microvideo.ewcp.*.dao..*.insert*(..)) || execution(* com.microvideo.ewcp.*.dao..*.save*(..)) || execution(* com.microvideo.ewcp.*.dao..*.update*(..))")
    public void setReadDataSourceType() {
        DataSourceManager.setMaster();
        System.out.println("master");
    }

}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
package com.microvideo.ewcp.dataSourceConfig;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.lang.reflect.Method;

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

    private static final Logger LOG = LoggerFactory.getLogger(DataSourceAspect.class);

    @Pointcut("within(@org.springframework.stereotype.Service *)")
    public void chooseDataSource(){
    }

    @Around("chooseDataSource()")
    public Object chooseDs(ProceedingJoinPoint pjp) throws Throwable{
        DataSourceType dsType = DataSourceManager.getType();

        if(dsType == null || dsType == DataSourceType.SLAVE){
            Method method = ((MethodSignature)pjp.getSignature()).getMethod();
            if(isWriteRequired(method)){
                DataSourceManager.setMaster();
            }else{
                DataSourceManager.setSlave();
            }
        }

        LOG.debug("choose datasource = {}", DataSourceManager.getType());

        return pjp.proceed();
    }

    private boolean isWriteRequired(Method method){
        Transactional transAnnotation = AnnotationUtils.findAnnotation(method, Transactional.class);
        if(transAnnotation != null){
            if(!transAnnotation.readOnly()){
                LOG.info("write db required");
                return true;
            }
        }
        return false;
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
package com.microvideo.ewcp.dataSourceConfig;

public class DataSourceManager {
    private static ThreadLocal<DataSourceType> dsHolder = new ThreadLocal<>();
    public static void setMaster(){ dsHolder.set(DataSourceType.MASTER); }
    public static void setSlave(){
        dsHolder.set(DataSourceType.SLAVE);
    }
    public static void reset(){
        dsHolder.remove();
    }
    public static DataSourceType getType(){
        return dsHolder.get();
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
package com.microvideo.ewcp.dataSourceConfig;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;

public class DataSourceRoutingDataSource extends AbstractRoutingDataSource{

    private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceRoutingDataSource.class);


    /**
     * 获取数据源key
     * @return
     */
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {

        LOGGER.info(" 当前获取的key:{}", DataSourceManager.getType());
        if(DataSourceManager.getType().equals(DataSourceType.MASTER)){
            //主数据
            return DataSourceType.MASTER.getValue();
        }else{
            //从数据源
            return DataSourceType.SLAVE.getValue();
        }
    }

}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
package com.microvideo.ewcp.dataSourceConfig;

public enum DataSourceType {

    MASTER("RW"),
    SLAVE("R");
    private String value;

    DataSourceType(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
package com.microvideo.ewcp.dataSourceConfig;

import org.mybatis.spring.SqlSessionFactoryBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class MutiDataSouceConfig{

    private static final Logger LOGGER = LoggerFactory.getLogger(MutiDataSouceConfig.class);

    @Value("${spring.datasource.type}")
    private String dataSourceType;

    /**
     * @Author:wlj
     * @Description:获取写数据源基础配置
     * @Date:2019-05-27
     */
    @Bean(name="master")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource masterDataSource() {
        DataSource dataSource= null;
        try {
            dataSource = DataSourceBuilder.create().type((Class<DataSource>) Class.forName(dataSourceType)).build();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return dataSource;
    }
    /**
     * @Author:wlj
     * @Description:获取第一个读数据源配置信息
     * @Date:2019-05-27
     */
    @Bean(name = "slave-alpha")
    @ConfigurationProperties(prefix = "spring.slave")
    public DataSource slaveAlphaDataSource(){
        try {
            return DataSourceBuilder.create().type((Class<DataSource>) Class.forName(dataSourceType)).build();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }


    @Bean("dynamicDataSource")
    public DataSource dynamicDataSource() {
        DataSourceRoutingDataSource dynamicRoutingDataSource = new DataSourceRoutingDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<>(2);
        dataSourceMap.put(DataSourceType.MASTER.getValue(), masterDataSource());
        dataSourceMap.put(DataSourceType.SLAVE.getValue(), slaveAlphaDataSource());
        // 将 master 数据源作为默认指定的数据源
        dynamicRoutingDataSource.setDefaultTargetDataSource(masterDataSource());
        // 将 master 和 slave 数据源作为指定的数据源
        dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
        return dynamicRoutingDataSource;
    }

    @Bean
//    @ConfigurationProperties(prefix = "mybatis")
    public SqlSessionFactoryBean sqlSessionFactoryBean() {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        // 配置 MyBatis
        //该属性可以给包中的类注册别名，注册后可以直接使用类名，而不用使用全限定的类名（就是不用包含包名）。
//        sqlSessionFactoryBean.setTypeAliasesPackage(typeAliasesPackage);
        //mapper.xml
        try {
            //配置mapper 文件的位置。。。。
            sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/microvideo/ewcp/*/mapper/*.xml"));
        } catch (IOException e) {
            LOGGER.error("           mybatis 设置MapperLocations error",e);
        }

        // 配置数据源，此处配置为关键配置，如果没有将 dynamicDataSource 作为数据源则不能实现切换
        sqlSessionFactoryBean.setDataSource(dynamicDataSource());
        return sqlSessionFactoryBean;
    }

    /**
     * 注入 DataSourceTransactionManager 用于事务管理
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/空白诗007/article/detail/857116
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号