springboot，druid动态数据源切换

GJQ-JAVA

已于 2024-03-01 17:39:26 修改

阅读量807

点赞数 8

文章标签： spring boot 后端 java

于 2024-03-01 17:33:18 首次发布

本文链接：https://blog.csdn.net/weixin_46519031/article/details/136402230

版权

关键字：springboot，druid数据库连接池，两个数据源（可以切换成多个），事务管理

关于druid简介传送门：https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98
在这里插入图片描述

具体分为四步：

1，ThreadLocal维护分支信息，

/**
 * @ClassName DataSourceContextHolder
 * @Description: 设置threadlocal数据元信息
 * @Author gjq
 * @Date 2024/2/29
 * @Version V1.0
 **/
public class DataSourceContextHolder {

    /**使用Threadlocal维护变量，threadlocal为每个使用该变量的现成提供独立的变量副本，
        所以每一个线程可以独立的改变自己的副本，而不会影响其他线程使用对应的副本
     **/
    private static final ThreadLocal<String> HOLDER = new InheritableThreadLocal<>();

    public static void setDataSource(String key) {
        HOLDER.set(key);
    }

    public static String getDataSource() {
        return HOLDER.get();
    }

    public static void clearDataSource() {
        HOLDER.remove();
    }

}

2，创建数据源设置方法，初始化数据源信息

package com.pg.ga.datasource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.Map;

/**
 * @ClassName DynamicDataSource
 * @Description:
 * @Author gjq
 * @Date 2024/2/29
 * @Version V1.0
 **/
public class DynamicDataSource extends AbstractRoutingDataSource {

    public DynamicDataSource(DataSource dataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(dataSource);
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }

}

3，多数据源配置，及枚举值定义

枚举值定义：

package com.pg.ga.datasource;

public enum DataSourceType {
    MASTER,
    SLAVE
}

package com.pg.ga.conf;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.pg.ga.datasource.DataSourceType;
import com.pg.ga.datasource.DynamicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.*;

/**
 * @ClassName PgConfig
 * @Description:类描述
 * @Author gjq
 * @Date 2024/2/29
 * @Version V1.0
 **/
@Configuration
@MapperScan({"com.pg.ga.dao"})
public class PgConfig {


    @Bean
    @ConfigurationProperties("spring.datasource.druid.master")
    public DruidDataSource masterDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.druid.slave")
    // @ConditionalOnProperty(prefix = "spring.datasource.druid.slave", name = "enable", havingValue = "true")
    public DataSource slaveDataSource() {
        return DruidDataSourceBuilder.create().build();
    }


    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource(DataSource masterDataSource, DataSource slaveDataSource) {
        Map<Object, Object> targetDataSource = new HashMap<>();
        targetDataSource.put(DataSourceType.MASTER.name(), masterDataSource);
        targetDataSource.put(DataSourceType.SLAVE.name(), slaveDataSource);
        return new DynamicDataSource(masterDataSource(), targetDataSource);
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(DynamicDataSource dynamicDataSource) throws Exception {
        //核心就是这个，替换原始的SqlSessionFactoryBean 用mybatisSqlSessionFactoryBean即可
        MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
        factoryBean.setDataSource(dynamicDataSource);
        //设置mapper.xml位置路径
        factoryBean.setMapperLocations(resolveMapperLocations());
        return factoryBean.getObject();
    }

    public Resource[] resolveMapperLocations() {
        ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        List<String> mapperLocations = new ArrayList<>();
        mapperLocations.add("classpath*:mapper/*.xml");
        List<Resource> resourceList = new ArrayList<>();
        if (null != mapperLocations) {
            for (String mapperLocation : mapperLocations) {
                try {
                    Resource[] mappers = resourcePatternResolver.getResources(mapperLocation);
                    resourceList.addAll(Arrays.asList(mappers));
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }
        }
        return resourceList.toArray(new Resource[resourceList.size()]);
    }


    /**
     * @MethodName:
     * @Description: 配置@Transactional注解事务
     * @Param:
     * @Return:
     * @Author: gjq
     * @Date: 2024/2/29
     **/
    @Bean
    public PlatformTransactionManager transactionManager(DynamicDataSource dynamicDataSource) {
        return new DataSourceTransactionManager(dynamicDataSource);
    }

}

4，动态切换注解定义

package com.pg.ga.datasource;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

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

    /**
     @MethodName:
     @Description: 切换数据源名称
     @Param:
     @Return:
     @Author: gjq
     @Date: 2024/2/29
     **/
    DataSourceType value() default DataSourceType.MASTER;
}

切面实现

package com.pg.ga.datasource;

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.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

/**
 * @ClassName DataSourceAspect
 * @Description:类描述
 * @Author gjq
 * @Date 2024/2/29
 * @Version V1.0
 **/
@Aspect
@Component
@Order(1)
public class DataSourceAspect {
    //先声明切点
    @Pointcut("@annotation(com.pg.ga.datasource.DataSource)")
    public void dsPointCut() {
        System.out.println(11111);
    }

    @Around("dsPointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable{
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();
        DataSource dataSource = method.getAnnotation(DataSource.class);
        if(null!=dataSource){
            DataSourceContextHolder.setDataSource(dataSource.value().name());
        }
        try{
            return point.proceed();
        }finally {
            DataSourceContextHolder.clearDataSource();
        }
    }
}

数据库配置信息.yaml文件配置

server :
  port : 8090
  ##项目名字配置
  #servlet :
  #  context-path : /demo
  tomcat :
    uri-encoding : UTF-8
    #xx 报错修改的地方
    max-connections: 200000
    max-http-form-post-size: 9000000
    threads:
      max: 128
      min-spare: 5
spring :
  # 环境 dev|test|prod
  profiles :
    active : dev
    #引入其他配置文件，例如ftpHX 未配置文件application-ftpHX.yml
    #include: ftpHX,ftpCloud
  servlet:
    multipart:
      #设置总上传的数据大小
      max-request-size: 100MB
      #单个文件大小
      maxFileSize : 30MB
        #xx 报错修改的地方
    max-connections: 200000
    max-http-post-size: 9000000
  #热部署模块
  devtools:
    restart:
      #热部署开关
      enabled: true
      #指定热部署的目录
      additional-paths: src/main/java
      #指定目录不更新
      exclude: test/**
  mvc:   #静态文件
    static-path-pattern : /static/**
    pathmatch:
      matching-strategy: ant_path_matcher
  #模板引擎
  thymeleaf:
    model: HTML5
    prefix: classpath:/templates/
    suffix: .html
    #指定编码
    encoding: utf-8
    #禁用缓存 默认false
    cache: false
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss
  redis:
    ssl: false
    database: 0
    host: 127.0.0.1
    port: 6379
    password: 
    timeout: 1000
    lettuce:
      pool:
        max-active: 200
        max-wait: -1
        max-idle: 10
        min-idle: 0

# 控制台输出sql、下划线转驼峰
mybatis-plus:
  mapper-locations: classpath:/mapper/*.xml
  typeAliasesPackage: com.pg.ga.model
  # 控制台输出sql、下划线转驼峰
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true

#pagehelper分页插件
pagehelper:
    helperDialect: mysql
    reasonable: true
    supportMethodsArguments: true
    params: count=countSql

application-dev.yaml配置信息

#dev环境  mysql7.0
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: org.postgresql.Driver
    #druid连接池配置
    druid:
     #主库数据源
     master:
        url: jdbc:postgresql://X.X.X.X:30811/ticket
        username: postgres
        password: postgres
     #备数据源 #关闭
     slave:
        enabled: false
        url: jdbc:postgresql://X.X.X.X:30811/ticket
        username: postgres
        password: postgres
        #配置初始化连接数大小
     initial-size: 10
     # 最大连接数
     maxActive: 50
     #最小连接数
     minIdle: 10
     #获取连接等待超时时间
     maxWait: 5000
     poolPreparedStatements: true #是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
     maxPoolPreparedStatementPerConnection-size: 20
     validationQuery: SELECT 1 FROM DUAL
     validationQueryTimeout: 20000
     testOnBorrow: false #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
     testOnReturn: false #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
     testWhileIdle: true #建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
     timeBetweenEvictionRunsMillis: 60000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
     minEvictableIdleTimeMillis: 300000  #一个连接在池中最小生存的时间，单位是毫秒
     maxEvictableIdleTimeMillis: 900000  # 配置一个连接在池中最大生存的时间，单位是毫秒
     #StatViewServlet配置。（因为暴露的监控信息比较敏感，支持密码加密和访问ip限定）
     webStatFilter:
      enabled: true
     statViewServlet:
      enabled: true
      urlPattern: /druid/*
      #可以增加访问账号密码【去掉注释就可以】
      #login-username: admin
      #login-password: admin
     filter:
      stat:
        enabled: true
        # 慢SQL记录
        log-slow-sql: true
        slow-sql-millis: 1000
        merge-sql: true
      wall:
        config:
          multi-statement-allow: true

测试controller

package com.pg.ga.controller;

import com.alibaba.fastjson.JSONObject;
import com.pg.ga.model.TicketBall;
import com.pg.ga.service.TickService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


@RestController
@RequestMapping("/crud")
public class Crud {
    @Autowired
    TickService tickService;

    @GetMapping("/master")
    public String master() {
        TicketBall ticketBall = tickService.selectMaster();
        System.out.println(JSONObject.toJSON(ticketBall));
        return null;
    }

    @GetMapping("/slave")
    public String create() {
        TicketBall ticketBall = tickService.selectSlave();
        System.out.println(JSONObject.toJSON(ticketBall));
        return null;
    }
}