SpringBoot配置多数据源 - 邢帅杰

公告

参考：https://blog.csdn.net/qq_37759895/article/details/135742006
pom.xml 我这里给出了我练习用的demo上的所有导入包，反正不会错。SpringBoot版本3.3.1

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.xcg</groupId>
    <artifactId>webapp</artifactId>
    <version>1.0.0</version>
    <name>webapp</name>
    <description>WebApp project for Spring Boot3</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>3.3.1</version>
        </dependency>
        <!--配置多数据源时，不需要此启动器，不需要单独配置此启动器的数据源。-->
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-3-starter -->
<!--        <dependency>-->
<!--            <groupId>com.alibaba</groupId>-->
<!--            <artifactId>druid-spring-boot-3-starter</artifactId>-->
<!--            <version>1.2.23</version>-->
<!--        </dependency>-->
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.23</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.4.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
<!--        <dependency>-->
<!--            <groupId>org.apache.logging.log4j</groupId>-->
<!--            <artifactId>log4j-core</artifactId>-->
<!--            <version>2.23.1</version>-->
<!--        </dependency>-->

<!--        &lt;!&ndash; https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-api &ndash;&gt;-->
<!--        <dependency>-->
<!--            <groupId>org.apache.logging.log4j</groupId>-->
<!--            <artifactId>log4j-api</artifactId>-->
<!--            <version>2.23.1</version>-->
<!--        </dependency>-->

        <!-- 使用SpringBoot自带的log组件 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
        </dependency>
        <!-- redis -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-aop -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
            <version>3.3.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.auth0/java-jwt -->
        <dependency>
            <groupId>com.auth0</groupId>
            <artifactId>java-jwt</artifactId>
            <version>4.4.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba.fastjson2/fastjson2 -->
        <dependency>
            <groupId>com.alibaba.fastjson2</groupId>
            <artifactId>fastjson2</artifactId>
            <version>2.0.51</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.34</version>
            <scope>provided</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.14.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>12.6.2.jre8</version>
        </dependency>
        <!--oracle驱动-->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.3.0.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.httpcomponents.client5/httpclient5 -->
        <dependency>
            <groupId>org.apache.httpcomponents.client5</groupId>
            <artifactId>httpclient5</artifactId>
            <version>5.1.3</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

application.yml

server:
  address: 192.168.9.2
  port: 8100
spring:
  profiles:
    active: dev #表示开发环境，nacos config
  application:
    name: webapp
  # 数据源类型
  datasource:
    master:
      db-type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/xcgdb?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&autoReconnect=true&failOverReadOnly=false
      username: root
      password: root
      validation-query: SELECT 1 FROM DUAL #测试连接是否可用的SQL语句
      initial-size: 10 #数据库连接池初始化连接数量
      min-idle: 10 #数据库连接池最小连接数量
      max-active: 30 #数据库连接池最大连接数量
      max-wait: 30000 # 配置获取连接等待超时的时间 30000毫秒(30秒)
    db01:
      db-type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/xcgdb?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&autoReconnect=true&failOverReadOnly=false
      username: root
      password: root
      validation-query: SELECT 1 FROM DUAL #测试连接是否可用的SQL语句
      initial-size: 10 #数据库连接池初始化连接数量
      min-idle: 10 #数据库连接池最小连接数量
      max-active: 30 #数据库连接池最大连接数量
      max-wait: 30000 # 配置获取连接等待超时的时间 30000毫秒(30秒)
    db02:
      db-type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      # 注意，连接字符串中加上encrypt=false;trustServerCertificate=true;不然他会使用SSL去连数据库。
      url: jdbc:sqlserver://localhost:1433;DatabaseName=testdb;encrypt=false;trustServerCertificate=true;loginTimeout=30;
      username: sa
      password: 123456
      validation-query: SELECT 1 #测试连接是否可用的SQL语句
      initial-size: 10 #数据库连接池初始化连接数量
      min-idle: 10 #数据库连接池最小连接数量
      max-active: 30 #数据库连接池最大连接数量
      max-wait: 30000 # 配置获取连接等待超时的时间 30000毫秒(30秒)
      testOnBorrow: true # 申请连接时执行validationQuery检测连接是否有效
      testWhileIdle: true # 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
      timeBetweenEvictionRunsMillis: 60000 # 默认60秒。Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。

# 日志相关配置
logging:
  level:
    root: ERROR
  file:
    name: sys.log
  logback:
    rollingpolicy:
      # gz
      file-name-pattern: ${LOG_FILE}.%d{yyyy-MM-dd}.%i.zip
      max-file-size: 1MB
  pattern:
    dateformat: yyyy-MM-dd HH:mm:ss

mybatis:
  mapper-locations: classpath:mapper/*.xml
  # 如果指定了mybatis的扫描包，那么在mapper/*.xml中就不好再指定别的目录了，所以这里先注释。
#  type-aliases-package: com.xcg.webapp.model.entity

配置文件绑定类，把yml中的配置转为类map绑定数据源

package com.xcg.webapp.config;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import java.util.Map;
@Data
@ConfigurationProperties(prefix = "spring")
@Component
public class DynamicDatasourceProperties {
    //绑定配置文件中的数据源
    private Map<String, DruidDataSource> datasource;
}

数据源切换类，使用ThreadLocal，每个线程使用自己的数据源副本。

public class DynamicDataSourceContextHolder {
    /**
     * 动态数据源名称上下文
     */
    private static final ThreadLocal<String> DATASOURCE_CONTEXT_KEY_HOLDER = new ThreadLocal<>();
    /**
     * 设置/切换数据源
     */
    public static void setContextKey(String key) {
        DATASOURCE_CONTEXT_KEY_HOLDER.set(key);
    }
    /**
     * 获取数据源名称
     */
    public static String getContextKey() {
        String key = DATASOURCE_CONTEXT_KEY_HOLDER.get();
        return key == null ? DataSourceConstants.DS_KEY_MASTER : key;
    }
    /**
     * 删除当前数据源名称
     */
    public static void removeContextKey() {
        DATASOURCE_CONTEXT_KEY_HOLDER.remove();
    }
}

继承AbstractRoutingDataSource，重写determineCurrentLookupKey()

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
 * 动态数据源
 * */
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getContextKey();
    }
}

配置数据源

@Configuration
public class DynamicDataSourceConfig {
    @Configuration
    @EnableConfigurationProperties(DynamicDatasourceProperties.class)
    @MapperScan(basePackages = "com.xcg.webapp.mapper", sqlSessionTemplateRef  = "dynamicSqlSessionTemplate")
    public static class DynamicDatasourceConfiguration {
        @Resource
        private DynamicDatasourceProperties dynamicDatasourceProperties;
        @Bean(name = "dynamicDataSource")
        public DynamicDataSource dynamicDataSource(){
            HashMap<Object, Object> dataSourceMap = new HashMap<>(dynamicDatasourceProperties.getDatasource());
            DynamicDataSource dynamicDatasource = new DynamicDataSource();
            dynamicDatasource.setTargetDataSources(dataSourceMap);
            dynamicDatasource.setDefaultTargetDataSource(dataSourceMap.get("master"));
            return dynamicDatasource;
        }

        @Bean(name = "dynamicTransactionManager")
        public DataSourceTransactionManager dynamicTransactionManager(@Qualifier("dynamicDataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }

        @Bean(name = "dynamicSqlSessionFactory")
        public SqlSessionFactory dynamicSqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
            bean.setTypeAliasesPackage("com.xcg.webapp.model");
            return bean.getObject();
        }

        @Bean(name = "dynamicSqlSessionTemplate")
        public SqlSessionTemplate dynamicSqlSessionTemplate(@Qualifier("dynamicSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }
}

做一个切面AOP，动态选定数据源

package com.xcg.webapp.aspect;

import com.xcg.webapp.annotation.DS;
import com.xcg.webapp.config.DynamicDataSourceContextHolder;
import com.xcg.webapp.config.DynamicDatasourceProperties;
import org.apache.commons.lang3.StringUtils;
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.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class DynamicDataSourceAspect {
    @Autowired
    private DynamicDatasourceProperties dynamicDatasourceProperties;

    //定义切入点
    //@Pointcut("execution(* com.xcg.mapper.*.*(..))")   com.railway.mapper 包下所有方法，不管什么返回值和参数。
    //@Pointcut("@annotation(com.xcg.annotation.DS)") 所有使用了 DS 注解的地方
    @Pointcut("@annotation(com.xcg.webapp.annotation.DS)")
    public void pointcut() {
    }

    //可以多个 @Around("pointcut() && @annotation(permissions) && @annotation(ds)")
    //@Around("pointcut()")
    @Around("pointcut() && @annotation(ds)")
    public Object around(ProceedingJoinPoint joinPoint, DS ds) throws Throwable {
        //数据源名称
        String dsKey = getDSAnnotation(joinPoint);
        DynamicDataSourceContextHolder.setContextKey(dsKey);
        try {
            return joinPoint.proceed();
        } finally {
            DynamicDataSourceContextHolder.removeContextKey();
        }
    }

    /**
     * 获取第一个参数作为数据源名称，如果名称在数据源map中不存在，
     * 则查找方法上面的注解。
     */
    private String getDSAnnotation(ProceedingJoinPoint joinPoint) {
        //DS设置到类上面没用，只能设置到mapper的方法上面，第一个参数指定数据源。
        String dsKey = null;
        Object[] args = joinPoint.getArgs();
        if (args != null && args.length > 0 && null != args[0]) {
            String key = args[0].toString();
            boolean has = dynamicDatasourceProperties.getDatasource().containsKey(key);
            if (has) {
                dsKey = key;
            }
        }
        if (StringUtils.isBlank(dsKey)) {
            //获取方法上面的注解
            MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
            dsKey = methodSignature.getMethod().getAnnotation(DS.class).value();
        }
        return dsKey;
    }
}

自定义注解

import java.lang.annotation.*;
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DS {
    String value() default "";
}

常量

package com.xcg.webapp.config;

public class DynamicDataSourceConstants {
    //主库
    public static final String DS_KEY_MASTER = "master";
}

使用，在mapper的方法上面增加注解，或者方法的第一个参数传数据源名称

@Mapper
@Repository
public interface DbSqlStuMapper {
    @DS("db02")
    List<DbSqlStu> getAll();
}

posted on 2024-07-11 10:08 邢帅杰阅读(2) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部