SpringBoot + druid 实现多数据源

二掌柜，酒来！

已于 2023-09-15 09:24:11 修改

阅读量566

点赞数 2

分类专栏： mybatis jdbc 文章标签： spring boot 后端 java

于 2023-09-14 17:42:37 首次发布

本文链接：https://blog.csdn.net/qzibidog/article/details/132885502

版权

jdbc 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

mybatis

4 篇文章 0 订阅

订阅专栏

前言

没啥需要说的，一个很普遍的需求，干就玩了！直接上代码

代码结构

在这里插入图片描述

配置文件

yml 配置

server:
  port: 8085
spring:
  datasource:
    type:  com.alibaba.druid.pool.DruidDataSource
    druid:
      # 配置连接池初始化大小/最小/最大
      initial-size: 5
      min-idle: 5
      max-active: 100
      #获取连接等待超时时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      time-between-eviction-runs-millis: 60000
      remove-abandoned: true
      remove-abandoned-timeout: 1800
      #一个连接在池中最小生存的时间
      min-evictable-idle-time-millis: 300000
      # 检查空闲连接是否有效
      validation-query: SELECT 'x'
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      #打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为false
      pool-prepared-statements: false
      max-pool-prepared-statement-per-connection-size: 20
      log-abandoned: true
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall

Adb:
  name: A_datasource1
  url: jdbc:mysql://192.168.xx.xx:3306/ADB?zeroDateTimeBehavior=convertToNull&autoReconnect=true&useSSL=false&failOverReadOnly=false&allowMultiQueries=true
  username: user
  password: password
  driver-class-name: com.mysql.jdbc.Driver
Bdb:
  name: B_datasource2
  url: jdbc:mysql://192.168.xx.xx:3306/BDB?zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&autoReconnect=true&useSSL=false&failOverReadOnly=false&allowMultiQueries=true
  username: user
  password: password
  driver-class-name: com.mysql.jdbc.Driver

pom文件

		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
		</dependency>
		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.41</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
         </dependency>

读取配置文件

DruidProperties 通用基础配置

@Data
@Component
// 这种简单点 ，就不用@Value了
//@ConfigurationProperties(prefix = "spring.datasource.druid")
public class DruidProperties {
    @Value("${spring.datasource.druid.filters}")
    private String filters;

    @Value("${spring.datasource.druid.initial-size}")
    private int initialSize;

    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;

    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;

    @Value("${spring.datasource.druid.max-wait}")
    private int maxWait;

    @Value("${spring.datasource.druid.validation-query}")
    private String validationQuery;

    @Value("${spring.datasource.druid.test-on-borrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.druid.test-on-return}")
    private boolean testOnReturn;

    @Value("${spring.datasource.druid.test-while-idle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.druid.remove-abandoned}")
    private boolean removeAbandoned;

    @Value("${spring.datasource.druid.remove-abandoned-timeout}")
    private int removeAbandonedTimeout;

    @Value("${spring.datasource.druid.log-abandoned}")
    private boolean logAbandoned;

    @Value("${spring.datasource.druid.pool-prepared-statements}")
    private boolean poolPreparedStatements;

    @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
    private int maxPoolPreparedStatementPerConnectionSize;

    @Value("${spring.datasource.druid.connection-properties}")
    private String connectionProperties;
}

读A 数据库的配置

@Component
@Data
@ConfigurationProperties(prefix = "Adb" )
public class AProperties {
    private String name;
//    @Value("${spring.datasource.druid.datasource1.url}")
    private String url;

//    @Value("${spring.datasource.druid.datasource1.username}")
    private String username;

//    @Value("${spring.datasource.druid.datasource1.password}")
    private String password;

//    @Value("${spring.datasource.druid.datasource1.driver-class-name}")
    private String driverClassName;

    
}

配置A数据源

package com.example.mutidatasource.dataSource;

import com.alibaba.druid.pool.DruidDataSource;
import com.example.mutidatasource.utils.NormalAES;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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 javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Properties;

@Configuration
@MapperScan(basePackages = ADataSourceConfig.PACKAGE, sqlSessionFactoryRef = "ASqlSessionFactory")
@Slf4j
public class ADataSourceConfig {
    /**
     * 配置多数据源 关键就在这里 这里配置了不同的数据源扫描不同mapper
     */
    static final String PACKAGE = "com.example.mutidatasource.A.mapper";
    static final String MAPPER_LOCATION = "classpath:mybatis/hb/mapper/*.xml";

    @Resource
    private TBProperties AProperties;

    @Resource
    private DruidProperties druidProperties;

    @Bean(name="ADataSource")
    public DataSource tbDataSource() {
        String password = "";
        if (AProperties.getPassword() != null && !"".equals(AProperties.getPassword())) {
            password = NormalAES.decryptPinfo(AProperties.getPassword());
        }

        DruidDataSource dataSource = new DruidDataSource();


        dataSource.setName(AProperties.getName());

        dataSource.setDriverClassName(AProperties.getDriverClassName());

        dataSource.setUrl(AProperties.getUrl());
        dataSource.setUsername(AProperties.getUsername());
        dataSource.setPassword(password);

        dataSource.setInitialSize(druidProperties.getInitialSize());
        dataSource.setMinIdle(druidProperties.getMinIdle());
        dataSource.setMaxActive(druidProperties.getMaxActive());
        dataSource.setMaxWait(druidProperties.getMaxWait());
        dataSource.setTimeBetweenEvictionRunsMillis(druidProperties.getTimeBetweenEvictionRunsMillis());
        dataSource.setMinEvictableIdleTimeMillis(druidProperties.getMinEvictableIdleTimeMillis());
        dataSource.setValidationQuery(druidProperties.getValidationQuery());
        dataSource.setTestWhileIdle(druidProperties.isTestWhileIdle());
        dataSource.setTestOnBorrow(druidProperties.isTestOnBorrow());
        dataSource.setTestOnReturn(druidProperties.isTestOnReturn());
        dataSource.setPoolPreparedStatements(druidProperties.isPoolPreparedStatements());
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(druidProperties.getMaxPoolPreparedStatementPerConnectionSize());
        dataSource.setConnectProperties(settingProperties(druidProperties.getConnectionProperties()));

        try {
            dataSource.setFilters(druidProperties.getFilters());
        } catch (SQLException e) {
            log.error("TBDB druid configuration initialization filter", e);
        }

        return dataSource;
    }

    @Bean
    public SqlSessionFactory ASqlSessionFactory(@Qualifier("ADataSource") DataSource ADataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(ADataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return bean.getObject();
    }

    @Bean
    public DataSourceTransactionManager ATransactionManager(@Qualifier("ADataSource") DataSource ADataSource) {
        return new DataSourceTransactionManager(ADataSource);
    }

    @Bean
    public SqlSessionTemplate ASqlSessionTemplate(@Qualifier("ASqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }


    /**
     * @Description 将属性connectionProperties解析到Properties对象中
     * @Author Mico
     * @Date 2018/7/19 17:09
     * @Param
     * @return
     */
    private Properties settingProperties(String connectionProperties) {
        Properties properties = new Properties();

        if (connectionProperties != null && !"".equals(connectionProperties.trim())) {
            String[] strArr = connectionProperties.split(";");
            if (strArr.length > 0) {
                for (int i = 0; i < strArr.length; i++) {
                    String str = strArr[i];
                    if (str == null || "".equals(str.trim())) {
                        continue;
                    }
                    else {
                        String[] keyValueArr = str.split("=");
                        if (keyValueArr.length == 2) {
                            properties.setProperty(keyValueArr[0], keyValueArr[1]);
                        }
                    }
                }
            }
        }

        return properties;
    }


}