spring boot 多数据源配置 - zhangwuji的博客 - 91名师指路91mszl.com-让能力变成价值

原 spring boot 多数据源配置

版权声明：本文为博主原创文章，请尊重他人的劳动成果，转载请附上原文出处链接和本声明。
本文链接：https://www.91mszl.com/zhangwuji/article/details/1346

由于某些原因，现在不支持支付宝支付，如需要购买源码请加博主微信进行购买，微信号：13248254750

背景：有时候我们的项目需要从多个数据库中查询数据，这时就需要配置多数据源了。

一：引入pom

<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>

        <postgresql.version>42.2.23</postgresql.version>
        <druid.version>1.2.6</druid.version>
        <mybatisPlus.version>3.4.3</mybatisPlus.version>
        <lombok.version>1.18.20</lombok.version>
        <fastjson.version>1.2.77</fastjson.version>
        <hutool.version>5.7.13</hutool.version>
        <nacos.version>0.2.9</nacos.version>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.12.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>

        <!-- redis -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>

        <!-- nacos -->
        <dependency>
            <groupId>com.alibaba.boot</groupId>
            <artifactId>nacos-config-spring-boot-starter</artifactId>
            <version>${nacos.version}</version>
        </dependency>

        <!-- postgresql -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>${postgresql.version}</version>
        </dependency>

        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>

        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisPlus.version}</version>
        </dependency>
        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.9.0</version>
        </dependency>

        <!-- mybatis-plus生成代码 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.4.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.2</version>
        </dependency>

        <!-- 多数据源 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>

        <!-- start 常用工具jar -->
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <scope>provided</scope>
        </dependency>

        <!-- fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>${fastjson.version}</version>
        </dependency>

        <!-- hutool-all -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool.version}</version>
        </dependency>

        <!-- commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.11</version>
        </dependency>
        <!-- end 常用工具jar -->

二：配置 application.yml

server:
  port: 9001

spring:
  application:
    name: ms-shop
  datasource:
    master:
      driver-class-name: com.p6spy.engine.spy.P6SpyDriver
      jdbc-url: jdbc:p6spy:postgresql://127.0.0.1:5432/mszl_shop?characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=GMT%2B8
      username: postgres
      password: 123456
    slave:
      driver-class-name: com.p6spy.engine.spy.P6SpyDriver
      jdbc-url: jdbc:p6spy:postgresql://127.0.0.1:5432/mszl_cloud?characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=GMT%2B8
      username: postgres
      password: 123456

    druid:
      type: com.alibaba.druid.pool.DruidDataSource
      max-active: 100 # 最大连接数
      initial-size: 1 # 初始化大小
      max-wait: 60000 # 获取连接等待超时时间
      min-idle: 1 # 最小连接数
      time-between-eviction-runs-millis: 60000 # 间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      min-evictable-idle-time-millis: 300000 # 一个连接在池中最小生存的时间，单位是毫秒
      validation-query: select 'x'
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: true
      max-open-prepared-statements: 50
      max-pool-prepared-statement-per-connection-size: 20

  redis:
    host: 127.0.0.1
    port: 6379
    timeout: 5000 # 连接超时时间 单位ms(毫秒)
    database: 0

mybatis-plus:
  type-aliases-package: com.gaia.fm.entity # 所有Entity别名类所在包
  mapper-locations:
    - classpath:mapper/**/*.xml # mapper映射文件
  configuration:
    map-underscore-to-camel-case: true # 设置实体类中的属性驼峰转换为下划线(和config-location不能并存)
    call-setters-on-nulls: true # 解决map中返回的字段为空时不显示key的问题

三：master数据源配置文件

package com.gaia.fm.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.nacos.api.config.annotation.NacosValue;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;

/**
 * 功能：
 * @Author: zxb
 * @Date: 2021-10-12 16:10:27
 */
@Configuration
@MapperScan(basePackages = "com.gaia.fm.dao.master", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSourceMasterConfig {

    // 数据库连接配置
    @NacosValue(value = "${spring.datasource.master.driver-class-name}", autoRefreshed = true)
    private String driverClassName;

    @NacosValue(value = "${spring.datasource.master.jdbc-url}", autoRefreshed = true)
    private String jdbcUrl;

    @NacosValue(value = "${spring.datasource.master.username}", autoRefreshed = true)
    private String username;

    @NacosValue(value = "${spring.datasource.master.password}", autoRefreshed = true)
    private String password;

    // druid配置
    @NacosValue(value = "${spring.datasource.druid.max-active}", autoRefreshed = true)
    private int maxActive;

    @NacosValue(value = "${spring.datasource.druid.initial-size}", autoRefreshed = true)
    private int initialSize;

    @NacosValue(value = "${spring.datasource.druid.max-wait}", autoRefreshed = true)
    private int maxWait;

    @NacosValue(value = "${spring.datasource.druid.min-idle}", autoRefreshed = true)
    private int minIdle;

    @NacosValue(value = "${spring.datasource.druid.time-between-eviction-runs-millis}", autoRefreshed = true)
    private int timeBetweenEvictionRunsMillis;

    @NacosValue(value = "${spring.datasource.druid.min-evictable-idle-time-millis}", autoRefreshed = true)
    private int minEvictableIdleTimeMillis;

    @NacosValue(value = "${spring.datasource.druid.validation-query}", autoRefreshed = true)
    private String validationQuery;

    @NacosValue(value = "${spring.datasource.druid.test-while-idle}", autoRefreshed = true)
    private boolean testWhileIdle;

    @NacosValue(value = "${spring.datasource.druid.test-on-borrow}", autoRefreshed = true)
    private boolean testOnBorrow;

    @NacosValue(value = "${spring.datasource.druid.test-on-return}", autoRefreshed = true)
    private boolean testOnReturn;

    @NacosValue(value = "${spring.datasource.druid.pool-prepared-statements}", autoRefreshed = true)
    private boolean poolPreparedStatements;

    @NacosValue(value = "${spring.datasource.druid.max-open-prepared-statements}", autoRefreshed = true)
    private int maxOpenPreparedStatements;

    @NacosValue(value = "${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}", autoRefreshed = true)
    private int maxPoolPreparedStatementPerConnectionSize;


    @Primary // 表示这个数据源是默认数据源, 这个注解必须要加，因为不加的话spring将分不清楚那个为主数据源（默认数据源）
    @Bean("db1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master") //读取application.yml中的配置参数映射成为一个对象
    public DataSource getDb1DataSource(){
        // 数据库连接配置
        DruidDataSource dataSource=new DruidDataSource();
        dataSource.setUrl(jdbcUrl);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);

        // druid配置
        dataSource.setMaxActive(maxActive);
        dataSource.setInitialSize(initialSize);
        dataSource.setMaxWait(maxWait);
        dataSource.setMinIdle(minIdle);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);
        dataSource.setPoolPreparedStatements(poolPreparedStatements);
        dataSource.setMaxOpenPreparedStatements(maxOpenPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        return dataSource;
        // return DataSourceBuilder.create().build();
    }

    @Bean(name="masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(getDb1DataSource());
    }

    @Primary
    @Bean("db1SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/master/*.xml"));
        return bean.getObject();
    }

    /*@Primary
    @Bean("db1SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }*/

}

四：slave数据源配置文件

package com.gaia.fm.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.nacos.api.config.annotation.NacosValue;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;

/**
 * 功能：
 * @Author: zxb
 * @Date: 2021-10-12 16:10:18
 */
@Configuration
@MapperScan(basePackages = "com.gaia.fm.dao.slave", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSourceSlaveConfig {

    // 数据库连接配置
    @NacosValue(value = "${spring.datasource.slave.driver-class-name}", autoRefreshed = true)
    private String driverClassName;

    @NacosValue(value = "${spring.datasource.slave.jdbc-url}", autoRefreshed = true)
    private String jdbcUrl;

    @NacosValue(value = "${spring.datasource.slave.username}", autoRefreshed = true)
    private String username;

    @NacosValue(value = "${spring.datasource.slave.password}", autoRefreshed = true)
    private String password;

    // druid配置
    @NacosValue(value = "${spring.datasource.druid.max-active}", autoRefreshed = true)
    private int maxActive;

    @NacosValue(value = "${spring.datasource.druid.initial-size}", autoRefreshed = true)
    private int initialSize;

    @NacosValue(value = "${spring.datasource.druid.max-wait}", autoRefreshed = true)
    private int maxWait;

    @NacosValue(value = "${spring.datasource.druid.min-idle}", autoRefreshed = true)
    private int minIdle;

    @NacosValue(value = "${spring.datasource.druid.time-between-eviction-runs-millis}", autoRefreshed = true)
    private int timeBetweenEvictionRunsMillis;

    @NacosValue(value = "${spring.datasource.druid.min-evictable-idle-time-millis}", autoRefreshed = true)
    private int minEvictableIdleTimeMillis;

    @NacosValue(value = "${spring.datasource.druid.validation-query}", autoRefreshed = true)
    private String validationQuery;

    @NacosValue(value = "${spring.datasource.druid.test-while-idle}", autoRefreshed = true)
    private boolean testWhileIdle;

    @NacosValue(value = "${spring.datasource.druid.test-on-borrow}", autoRefreshed = true)
    private boolean testOnBorrow;

    @NacosValue(value = "${spring.datasource.druid.test-on-return}", autoRefreshed = true)
    private boolean testOnReturn;

    @NacosValue(value = "${spring.datasource.druid.pool-prepared-statements}", autoRefreshed = true)
    private boolean poolPreparedStatements;

    @NacosValue(value = "${spring.datasource.druid.max-open-prepared-statements}", autoRefreshed = true)
    private int maxOpenPreparedStatements;

    @NacosValue(value = "${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}", autoRefreshed = true)
    private int maxPoolPreparedStatementPerConnectionSize;


    @Bean("db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource getDb2DataSource(){
        // 数据库连接配置
        DruidDataSource dataSource=new DruidDataSource();
        dataSource.setUrl(jdbcUrl);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);

        // druid配置
        dataSource.setMaxActive(maxActive);
        dataSource.setInitialSize(initialSize);
        dataSource.setMaxWait(maxWait);
        dataSource.setMinIdle(minIdle);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);
        dataSource.setPoolPreparedStatements(poolPreparedStatements);
        dataSource.setMaxOpenPreparedStatements(maxOpenPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        return dataSource;
        // return DataSourceBuilder.create().build();
    }

    @Bean(name="slaveTransactionManager")
    @Primary
    public DataSourceTransactionManager slaveTransactionManager() {
        return new DataSourceTransactionManager(getDb2DataSource());
    }

    @Bean("db2SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/slave/*.xml"));
        return bean.getObject();
    }

    /*@Bean("db2SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }*/

}

五：在dao包下创建2个文件夹用来区分master和slave数据源，在mapper文件夹下创建2个文件夹用来区分master和slave数据源。如下图所示。

六：默认为master数据库，如果要用slave库，在dao层的类或方法上加上 @DS("slave") 即可，如果方法和类上都加上了@DS("slave") 则方法优先于类注解。

七：事务回滚。在service实现层加上如下的事务注解。

@Transactional(transactionManager = "masterTransactionManager", rollbackFor=Exception.class)
@Override
public void getUserInfo() {

}

注意：masterTransactionManager是DataSourceMasterConfig.java 类中的bean的名字

参考资料：

https://www.cnblogs.com/aizen-sousuke/p/11756279.html

https://www.cnblogs.com/qdhxhz/p/10192041.html

https://blog.csdn.net/wzy_168/article/details/110921043

2021-10-21 09:09:33 阅读(1085)

名师出品，必属精品 https://www.91mszl.com

联系博主

博主信息

昵称： zhangwuji
最新文章

1. idea自定义菜单栏

2. idea设置菜单栏快捷方式

3. word中打出上标和下标

4. idea 2023.3.7和idea 2024.1破解教程

5. idea过滤idea，iml，target等文件

6. uniapp中使用svg文件

7. spring-boot 3.2.3整合mybatis plus报错：java.lang.IllegalArgumentException: Invalid value type for attribu

8. spring-boot 3.2.3整合druid报错：Failed to configure a DataSource: 'url' attribute is not specified and no

9. 使用hutool实现姓名、身份证、手机号、地址、邮箱、车牌、银行卡脱敏

10. java集成ip2region实现离线IP地址定位