springboot多数据源和配置druid连接池

多数据源

1、pom



   com.alibaba
   druid-spring-boot-starter
   1.1.17

2、配置文件

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mytest?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8
    password: root
    username: root
    
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 10
    #maxIdle:15

    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 320000

    validationQuery: SELECT 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    #poolPreparedStatements:true
    maxOpenPreparedStatements: 20

 3、配置类

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;

// druid配置类，读取spring.datasource的相关配置
@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid() {
        return new DruidDataSource();
    }
}


参数说明：

- spring.datasource.druid.max-active  最大连接数 
- spring.datasource.druid.initial-size  初始化大小 
- spring.datasource.druid.min-idle  最小连接数 
- spring.datasource.druid.max-wait  获取连接等待超时时间 
- spring.datasource.druid.time-between-eviction-runs-millis  间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 
- spring.datasource.druid.min-evictable-idle-time-millis  一个连接在池中最小生存的时间，单位是毫秒 
- spring.datasource.druid.filters=config,stat,wall,log4j  配置监控统计拦截的filters，去掉后监控界面SQL无法进行统计，’wall’用于防火墙

多数据源

1、配置文件，增加 db2 数据源

查看代码 
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mytest?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8
    password: root
    username: root
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 10
    #maxIdle:15

    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 320000

    validationQuery: SELECT 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    #poolPreparedStatements:true
    maxOpenPreparedStatements: 20

    db2:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8
      password: root
      username: root
      filters: stat
      maxActive: 20
      initialSize: 1
      maxWait: 60000
      minIdle: 10
      #maxIdle:15

      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 320000

      validationQuery: SELECT 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      #poolPreparedStatements:true
      maxOpenPreparedStatements: 20

2、两个数据源配置类

主数据源配置类：

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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;

@Configuration
public class DruidConfig {

    // 读取配置文件中的 spring.datasource配置
    // 配置多数据源时要默认一个数据源，所以要加 @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean(name = "dataSource")
    @Primary
    public DataSource dataSource() {
        return new DruidDataSource();
    }

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

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("dataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:mapper/**/*.xml"));   // 对应的mapper.xml
        sessionFactory.setTypeAliasesPackage("com.xxx.**.domain");          // 对应的实体类,可写可不写
        //mybatis 数据库字段与实体类属性驼峰映射配置
        sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return sessionFactory.getObject();
    }
        
    @Bean(name = "jdbcTemplate1")
    @Primary
    public JdbcTemplate jdbcTemplate1() {
        return new JdbcTemplate(dataSource());
    }
}

 第二个数据源配置类：

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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 javax.sql.DataSource;

@Configuration
public class DruidConfig2 {

    // 读取配置文件中，spring.datasource.db2 的数据源配置，返加datasource对象
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    @Bean(name = "dataSource2")
    public DataSource dataSource2() {
        return new DruidDataSource();
    }

    @Bean(name = "clusterTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(dataSource2());
    }

    @Bean(name = "clusterSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("dataSource2") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:mapper/**/*.xml"));
        sessionFactory.setTypeAliasesPackage("com.subject.xxx.domain");
        //mybatis 数据库字段与实体类属性驼峰映射配置
        sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return sessionFactory.getObject();
    }
        
    @Bean(name = "jdbcTemplate2")
    public JdbcTemplate jdbcTemplate2() {
        return new JdbcTemplate(dataSource2());
    }
}

以上两个类主要区别是：是否加@Primary注解，读取配置前缀不同，bean的name不一样。

3、修改main方法上mapper接口扫描路径，将对应的mapper接口交给相应的数据源 sqlSessionFactoryRef

@MapperScan(basePackages = {"com.subject.mapper"},sqlSessionFactoryRef = "masterSqlSessionFactory")
@MapperScan(basePackages = {"com.subject.mapper2"},sqlSessionFactoryRef = "clusterSqlSessionFactory")

 在包 com.subject.mapper 下的所有mapper接口都走 masterSqlSessionFactory下的数据源。

 在包 com.subject.mapper2 下的所有mapper接口都走 clusterSqlSessionFactory下的数据源。

注：masterSqlSessionFactory、clusterSqlSessionFactory就是第2步配置类中对应 Factory的bean的name。

druid监控页面

在任一配置类加入以下方法，并访问 http://127.0.0.1:xxxx/druid/index.html

/**
     *  实现WEB监控的配置处理
     *  http://127.0.0.1:xxxx/druid/index.html
     */
    @Bean
    public ServletRegistrationBean druidServlet() {
        // 现在要进行druid监控的配置处理操作
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(
                new StatViewServlet(), "/druid/*");
        // 白名单,多个用逗号分割， 如果allow没有配置或者为空，则允许所有访问
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        // 黑名单,多个用逗号分割 (共同存在时，deny优先于allow)
        servletRegistrationBean.addInitParameter("deny", "192.168.1.110");
        // 控制台管理用户名
        servletRegistrationBean.addInitParameter("loginUsername", "root");
        // 控制台管理密码
        servletRegistrationBean.addInitParameter("loginPassword", "root");
        // 是否可以重置数据源，禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean ;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean() ;
        filterRegistrationBean.setFilter(new WebStatFilter());
        //所有请求进行监控处理
        filterRegistrationBean.addUrlPatterns("/*");
        //添加不需要忽略的格式信息
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.css,/druid/*");
        return filterRegistrationBean ;
    }

以上只是以两个数据源为例，如果有多个，就建多个类

代码Demo

相关

标签