关于Springboot 配置多数据源 Druid 德鲁伊

关于Springboot 配置多数据源 Druid 德鲁伊

yml

server:
  port: 8080
  servlet:
    encoding:
      charset: UTF-8
spring:
  resources:
    static-locations: classpath:/static
  application:
    name:springboot_list
  aop:
    proxy-target-class: true
    auto: true
  mvc:
    static-path-pattern: /**
    thymeleaf:
      check-template-location: true  # 检查模板位置是否正确(默认值 :true )
      check-template: true  # 检查模板是否存在,然后再呈现
      content-type: text/html
      enabled: true
      encoding: UTF-8
      cache: false  # 开启模板缓存(默认值: true )
      mode: HTML5
      prefix: classpath:/templates/
      suffix: .html
  datasource:
    #使用druid连接池
    type: com.alibaba.druid.pool.DruidDataSource

primary:
  datasource:
    #druid相关配置
    druid:
      #监控统计拦截的filters
      filters: stat
      driverClassName: com.mysql.cj.jdbc.Driver
      #配置基本属性
      url: 
      username: 
      password: 
      #配置初始化大小/最小/最大
      initialSize: 1
      minIdle: 1
      maxActive: 20
      #获取连接等待超时时间
      maxWait: 60000
      #间隔多久进行一次检测,检测需要关闭的空闲连接
      timeBetweenEvictionRunsMillis: 60000
      #一个连接在池中最小生存的时间
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      #打开PSCache,并指定每个连接上PSCache的大小。oracle设为true,mysql设为false。分库分表较多推荐设置为false
      poolPreparedStatements: false
      maxPoolPreparedStatementPerConnectionSize: 20

# 自定义的从数据源配置信息
time:
  datasource:
    #druid相关配置
    druid:
      #监控统计拦截的filters
      filters: stat
      driverClassName: com.mysql.cj.jdbc.Driver
      #配置基本属性
      url: 
      username: 
      password: 
      #配置初始化大小/最小/最大
      initialSize: 1
      minIdle: 1
      maxActive: 20
      #获取连接等待超时时间
      maxWait: 60000
      #间隔多久进行一次检测,检测需要关闭的空闲连接
      timeBetweenEvictionRunsMillis: 60000
      #一个连接在池中最小生存的时间
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      #打开PSCache,并指定每个连接上PSCache的大小。oracle设为true,mysql设为false。分库分表较多推荐设置为false
      poolPreparedStatements: false
      maxPoolPreparedStatementPerConnectionSize: 20

sqlser:
  datasource:
    druid:
      #监控统计拦截的filters
      filters: stat
      username: 
      password: 
      url: 
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
      initialSize: 1
      minIdle: 1
      maxActive: 20
      #获取连接等待超时时间
      maxWait: 60000
      #间隔多久进行一次检测,检测需要关闭的空闲连接
      timeBetweenEvictionRunsMillis: 60000
      #一个连接在池中最小生存的时间
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      #打开PSCache,并指定每个连接上PSCache的大小。oracle设为true,mysql设为false。分库分表较多推荐设置为false
      poolPreparedStatements: false
      maxPoolPreparedStatementPerConnectionSize: 20


#      url: jdbc:mysql://192.168.1.66:3306/hnks_qzj?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT&useSSL=false
#      driver-class-name: com.mysql.cj.jdbc.Driver
#      username: hnks
#      password: hnksadmin
#      initial-size: 1
#      min-idle: 1
#      max-active: 20

mybatis-plus:
  mapper-locations: classpath:mapper/*/*.xml
  type-aliases-package: com.hnks.springboot_list.entity.*
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true #驼峰

  logging:
    level:
      com.hnks.springboot_list.mapper.*: debug


config

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * @Description: 后台数据源配置类
 */

@Data
@Configuration
@Component
@ConfigurationProperties(prefix = "time.datasource.druid")
@MapperScan(basePackages = BackDataBaseConfig.PACKAGE, sqlSessionFactoryRef = "backSqlSessionFactory")
public class BackDataBaseConfig {


    /**
     * dao层的包路径
     */
    static final String PACKAGE = "com.hnks.springboot_list.mapper.time";

    /**
     * mapper文件的相对路径
     */
    private static final String MAPPER_LOCATION = "classpath:mapper/time/*.xml";

    private String filters;
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private long maxWait;
    private long timeBetweenEvictionRunsMillis;
    private long minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;


    @Bean(name = "backDataSource")
    public DataSource backDataSource() throws SQLException {
        DruidDataSource druid = new DruidDataSource();
        // 监控统计拦截的filters
        druid.setFilters(filters);

        // 配置基本属性
        druid.setDriverClassName(driverClassName);
        druid.setUsername(username);
        druid.setPassword(password);
        druid.setUrl(url);

        //初始化时建立物理连接的个数
        druid.setInitialSize(initialSize);
        //最大连接池数量
        druid.setMaxActive(maxActive);
        //最小连接池数量
        druid.setMinIdle(minIdle);
        //获取连接时最大等待时间,单位毫秒。
        druid.setMaxWait(maxWait);
        //间隔多久进行一次检测,检测需要关闭的空闲连接
        druid.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        //一个连接在池中最小生存的时间
        druid.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        //用来检测连接是否有效的sql
        druid.setValidationQuery(validationQuery);
        //建议配置为true,不影响性能,并且保证安全性。
        druid.setTestWhileIdle(testWhileIdle);
        //申请连接时执行validationQuery检测连接是否有效
        druid.setTestOnBorrow(testOnBorrow);
        druid.setTestOnReturn(testOnReturn);
        //是否缓存preparedStatement,也就是PSCache,oracle设为true,mysql设为false。分库分表较多推荐设置为false
        druid.setPoolPreparedStatements(poolPreparedStatements);
        // 打开PSCache时,指定每个连接上PSCache的大小
        druid.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

        return druid;
    }

    @Bean(name = "backTransactionManager")
    public DataSourceTransactionManager backTransactionManager() throws SQLException {
        return new DataSourceTransactionManager(backDataSource());
    }



    @Bean(name = "backSqlSessionFactory")
    public SqlSessionFactory backSqlSessionFactory(@Qualifier("backDataSource") DataSource backDataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(backDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(BackDataBaseConfig.MAPPER_LOCATION));

        return sessionFactory.getObject();
    }
}


import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import lombok.Data;
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 org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.SQLException;

@Data
@Configuration
// 前缀为primary.datasource.druid的配置信息
@Component
@ConfigurationProperties(prefix = "primary.datasource.druid")
@MapperScan(basePackages = PrimaryDataBaseConfig.PACKAGE, sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataBaseConfig {

    /**
     * dao层的包路径
     */
    static final String PACKAGE = "com.hnks.springboot_list.mapper.primary";

    /**
     * mapper文件的相对路径
     */
    private static final String MAPPER_LOCATION = "classpath:mapper/primary/*.xml";

    private String filters;
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private long maxWait;
    private long timeBetweenEvictionRunsMillis;
    private long minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;

    // 主数据源使用@Primary注解进行标识
    @Primary
    @Bean(name = "primaryDataSource")
    public DataSource primaryDataSource() throws SQLException {
        DruidDataSource druid = new DruidDataSource();
        // 监控统计拦截的filters
        druid.setFilters(filters);

        // 配置基本属性
        druid.setDriverClassName(driverClassName);
        druid.setUsername(username);
        druid.setPassword(password);
        druid.setUrl(url);

        //初始化时建立物理连接的个数
        druid.setInitialSize(initialSize);
        //最大连接池数量
        druid.setMaxActive(maxActive);
        //最小连接池数量
        druid.setMinIdle(minIdle);
        //获取连接时最大等待时间,单位毫秒。
        druid.setMaxWait(maxWait);
        //间隔多久进行一次检测,检测需要关闭的空闲连接
        druid.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        //一个连接在池中最小生存的时间
        druid.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        //用来检测连接是否有效的sql
        druid.setValidationQuery(validationQuery);
        //建议配置为true,不影响性能,并且保证安全性。
        druid.setTestWhileIdle(testWhileIdle);
        //申请连接时执行validationQuery检测连接是否有效
        druid.setTestOnBorrow(testOnBorrow);
        druid.setTestOnReturn(testOnReturn);
        //是否缓存preparedStatement,也就是PSCache,oracle设为true,mysql设为false。分库分表较多推荐设置为false
        druid.setPoolPreparedStatements(poolPreparedStatements);
        // 打开PSCache时,指定每个连接上PSCache的大小
        druid.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

        return druid;
    }

    // 创建该数据源的事务管理
    @Primary
    @Bean(name = "primaryTransactionManager")
    public DataSourceTransactionManager primaryTransactionManager() throws SQLException {
        return new DataSourceTransactionManager(primaryDataSource());
    }

    // 创建Mybatis的连接会话工厂实例
    @Primary
    @Bean(name = "primarySqlSessionFactory")
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource primaryDataSource, MybatisPlusProperties mybatisPlusProperties) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(primaryDataSource);  // 设置数据源bean
        sessionFactory.setConfiguration(mybatisPlusProperties.getConfiguration());//配置mybatisPlus sql打印日志
//        sessionFactory.setConfiguration(mybatisProperties.getConfiguration());// mybatis 配置sql打印日志
//        MybatisProperties mybatisProperties
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(PrimaryDataBaseConfig.MAPPER_LOCATION));  // 设置mapper文件路径

        return sessionFactory.getObject();
    }
}


import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import lombok.Data;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * @Description: 后台数据源配置类
 */

@Data
@Configuration
@Component
@ConfigurationProperties(prefix = "sqlser.datasource.druid")
@MapperScan(basePackages = SqlServerDataBaseConfig.PACKAGE, sqlSessionFactoryRef = "sqlServerDataBase")
public class SqlServerDataBaseConfig {

    /**
     * dao层的包路径
     */
    static final String PACKAGE = "com.hnks.springboot_list.mapper.sqlser";

    /**
     * mapper文件的相对路径
     */
    private static final String MAPPER_LOCATION = "classpath:mapper/sqlser/*.xml";

    private String filters;
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private long maxWait;
    private long timeBetweenEvictionRunsMillis;
    private long minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;


    @Bean(name = "sqlBaseConfig")
    public DataSource sqlBaseConfig() throws SQLException {
        DruidDataSource druid = new DruidDataSource();
        // 监控统计拦截的filters
        druid.setFilters(filters);

        // 配置基本属性
        druid.setDriverClassName(driverClassName);
        druid.setUsername(username);
        druid.setPassword(password);
        druid.setUrl(url);

        //初始化时建立物理连接的个数
        druid.setInitialSize(initialSize);
        //最大连接池数量
        druid.setMaxActive(maxActive);
        //最小连接池数量
        druid.setMinIdle(minIdle);
        //获取连接时最大等待时间,单位毫秒。
        druid.setMaxWait(maxWait);
        //间隔多久进行一次检测,检测需要关闭的空闲连接
        druid.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        //一个连接在池中最小生存的时间
        druid.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        //用来检测连接是否有效的sql
        druid.setValidationQuery(validationQuery);
        //建议配置为true,不影响性能,并且保证安全性。
        druid.setTestWhileIdle(testWhileIdle);
        //申请连接时执行validationQuery检测连接是否有效
        druid.setTestOnBorrow(testOnBorrow);
        druid.setTestOnReturn(testOnReturn);
        //是否缓存preparedStatement,也就是PSCache,oracle设为true,mysql设为false。分库分表较多推荐设置为false
        druid.setPoolPreparedStatements(poolPreparedStatements);
        // 打开PSCache时,指定每个连接上PSCache的大小
        druid.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        return druid;
    }

    @Bean(name = "backManager")
    public DataSourceTransactionManager backManager() throws SQLException {
        return new DataSourceTransactionManager(sqlBaseConfig());
    }



    @Bean(name = "sqlServerDataBase")
    public SqlSessionFactory sqlServerDataBaseConfig(@Qualifier("sqlBaseConfig") DataSource backDataSource) throws Exception {
        final MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
        sessionFactory.setDataSource(backDataSource);

        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(SqlServerDataBaseConfig.MAPPER_LOCATION));

        return sessionFactory.getObject();
    }
}


特别注意

mybatis 和mybatisplus 是不一样的 mybatis->SqlSessionFactoryBean
mybatisplus->MybatisSqlSessionFactoryBean(曾经也是吃了我半天时间!!)

打印控制台sql:mybatis -》MybatisProperties sessionFactory.setConfiguration(mybatisProperties.getConfiguration());// mybatis 配置sql打印日志

打印控制台sql:mybatisplus-》MybatisPlusProperties
sessionFactory.setConfiguration(mybatisPlusProperties.getConfiguration());//配置mybatisPlus sql打印日志

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Spring Boot可以通过配置多个数据来实现多数据的使用,而Druid是一种数据库连接池,可以提供对多个数据的连接管理和监控功能。 首先,在`pom.xml`文件中添加Druid和对应数据库驱动的依赖: ```xml <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.2.6</version> </dependency> <!-- 添加其他数据库驱动的依赖 --> ``` 然后,在`application.properties`或`application.yml`中配置多个数据的相关信息,例如: ```properties # 主数据 spring.datasource.url=jdbc:mysql://localhost:3306/db1 spring.datasource.username=root spring.datasource.password=123456 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver # 第二个数据 spring.datasource.second.url=jdbc:mysql://localhost:3306/db2 spring.datasource.second.username=root spring.datasource.second.password=123456 spring.datasource.second.driver-class-name=com.mysql.cj.jdbc.Driver ``` 接下来,创建多个数据配置类,例如: ```java @Configuration public class DataSourceConfig { @Primary @Bean(name = "dataSource") @ConfigurationProperties(prefix = "spring.datasource") public DataSource dataSource() { return DruidDataSourceBuilder.create().build(); } @Bean(name = "secondDataSource") @ConfigurationProperties(prefix = "spring.datasource.second") public DataSource secondDataSource() { return DruidDataSourceBuilder.create().build(); } } ``` 注意,`@Primary`注解用于标识默认的主数据。 最后,在需要使用数据的地方,通过`@Qualifier`注解指定要使用的数据,例如: ```java @Service public class MyService { @Autowired @Qualifier("dataSource") private DataSource dataSource; // 使用dataSource进行数据库操作 @Autowired @Qualifier("secondDataSource") private DataSource secondDataSource; // 使用secondDataSource进行数据库操作 } ``` 这样就完成了Spring Boot中多数据Druid配置。在使用数据时,可以根据需要在不同的地方注入不同的数据

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值