springboot中单/多数据源下druid的使用

作者: 墨色尘埃 | 来源:发表于2018-06-04 19:41 被阅读87次

参考另一篇文章springboot中单数据源下druid的使用
1、对于单数据源，pom.xml中的配置如下：

server:
  port: 10003

spring:
  cache:
    type: redis      #指定缓存的类型
  redis:
    host: 127.0.0.1  # 服务器地址
    port: 6379       # 服务器连接端口
    password: jihy   # 链接密码
    database: 0      # 数据库索引
    pool:            # 链接池
      max-active: 8  # 最大连接数（负值表示没有限制）
      max-wait: -1   # 最大阻塞等待时间（负值表示没有限制）
      max-idle: 8    # 最大空闲链接
      min-idle: 0    # 最小空闲链接
    timeout: 0       # 链接超时时间（毫秒）
  resource:
    static-locations: file:/public/
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://172.16.11.66:3306/guian?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: *****
    driver-class-name: com.mysql.jdbc.Driver
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    #自动检测关闭和空闲连接的间隔
    timeBetweenEvictionRunsMillis: 30000
    #最小生存时间
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    #这个参数设置为true，可以保证每次的连接都是正常的，但是。。 性能会降低， 建议使用空闲下检测30s一次， 这样服务器最多30s出现问题后恢复
    testOnBorrow: false
    testOnReturn: false
    #通过connectionProperties属性来打开mergeSql功能：慢SQL记录
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=3000
    #配置监控统计拦截的filters，去掉后监控界面sql无法统计
    filters: stat  #,wall,log4j
    #打开PSCatch，并且指定每个连接上PSCatch的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20

配置druid的时候，DruidConfig中druidDataSource()方法
①如果使用@Bean则登陆http://localhost:10003/druid/login.html后数据源显示(*) property for user to setup，如图1所示，需要请求一个接口才能显示正常。
②如果使用@Bean(destroyMethod = "close", initMethod = "init")则数据源显示正常，如图2。
方法中的spring.datasource对应于application-dev.yml中spring下的datasource

图1.png

图2.png
单数据源情况下，DruidConfig类中一定要有druidDataSource()方法，
返回new DruidDataSource();
DruidConfig

package com.jsptpd.portal.common.config.druid;

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;

/**
 * Created by JIHY on 2017/6/20.
 * 以上配置的监控方式是使用了原生的servlet，filter方式，然后通过@ServletComponentScan进行启动扫描包的方式进行处理的，你会发现我们的servlet，filter根本没有任何的编码。
 * 在这里我们将使用另外一种方式进行处理：使用代码注册Servlet：
 * 编写类：com.kfit.base.servlet.DruidConfiguration ：
 */
@Configuration
public class DruidConfig {

//    @Bean("druid-stat-interceptor")
//    public DruidStatInterceptor interceptor() {
//        return new DruidStatInterceptor();
//    }
//
//
//    @Bean
//    public SpringIbatisBeanTypeAutoProxyCreator proxy() {
//        SpringIbatisBeanTypeAutoProxyCreator beanTypeAutoProxyCreator = new SpringIbatisBeanTypeAutoProxyCreator();
//        beanTypeAutoProxyCreator.setInterceptorNames("druid-stat-interceptor");
//        return beanTypeAutoProxyCreator;
//    }

    //    @Bean
    @Bean(destroyMethod = "close", initMethod = "init")
    @ConfigurationProperties(prefix = "spring.datasource") //对应于application-dev.yml中spring下的datasource
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }
}

2、对于多数据源，参考贵安云谷gayg项目。
对于多数据源而言，因为已经配置了数据源类QYYDataSourceConfig、WYDataSourceConfig和YYDataSourceConfig，所以DruidConfig类中就不需要再返回druidDataSource()方法。为了避免出现单数据源(*) property for user to setup情况，需要将@Bean(destroyMethod = "close", initMethod = "init")写入到多数据源的配置中，如下@Bean(name = "yyDataSource", destroyMethod = "close", initMethod = "init")

DruidConfig

package com.jsptpd.gayg.common.config.druid;

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;

/**
 * Created by LOG on 2017/6/20.
 */
@Configuration
public class DruidConfig {

//    @Bean("druid-stat-interceptor")
//    public DruidStatInterceptor interceptor() {
//        return new DruidStatInterceptor();
//    }
//
//
//    @Bean
//    public SpringIbatisBeanTypeAutoProxyCreator proxy() {
//        SpringIbatisBeanTypeAutoProxyCreator beanTypeAutoProxyCreator = new SpringIbatisBeanTypeAutoProxyCreator();
//        beanTypeAutoProxyCreator.setInterceptorNames("druid-stat-interceptor");
//        return beanTypeAutoProxyCreator;
//    }

    /**
     * spring boot整合druid出现(*) property for user to setup
     * 如果引入jpa就不会出现这个问题了
     * 如果不引人jpa引入mybatis的话，需要进行如下配置
     * 对于多数据源而言，因为已经配置了数据源类QYYDataSourceConfig、WYDataSourceConfig和YYDataSourceConfig，所以这里就不需要再返回了
     * @return
     */
//    @Bean(destroyMethod = "close", initMethod = "init")
////    @Bean
//    @ConfigurationProperties(prefix = "spring.datasource_wy")
//    public DataSource druidDataSource() {
//        return new DruidDataSource();
//    }
}

多数据源配置类如下，qyyDataSource()、wyDataSource()、yyDataSource()方法不能返回return DataSourceBuilder.create().build()，而是返回return new DruidDataSource();这是因为返回的是两个不同的源：

org.apache.tomcat.jdbc.pool.DataSource //无效

com.alibaba.druid.pool //有效

QYYDataSourceConfig

package com.jsptpd.gayg.common.config.druid;

import com.alibaba.druid.pool.DruidDataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
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
@MapperScan(basePackages = QYYDataSourceConfig.PACKAGE_CLOUD, sqlSessionTemplateRef = "qyySqlSessionTemplate")
public class QYYDataSourceConfig {
    static final String PACKAGE_CLOUD = "com.jsptpd.gayg.modules.cloud.dao";
    static final String MAPPER_LOCATION = "classpath:com/jsptpd/gayg/**/*.xml";

    @Autowired
    private MybatisProperties mybatisProperties;

    @Bean(name = "qyyDataSource", destroyMethod = "close", initMethod = "init")
//    @ConfigurationProperties(prefix = "spring.datasource.qyy")
    @ConfigurationProperties(prefix = "spring.datasource_qyy")
    public DataSource yyDataSource() {
        return new DruidDataSource();
//        return DataSourceBuilder.create().build();
    }

    @Bean(name = "qyyTransactionManager")
    public DataSourceTransactionManager yyTransactionManager() {
        return new DataSourceTransactionManager(yyDataSource());
    }

    @Bean(name = "qyySqlSessionFactory")
    public SqlSessionFactory yySqlSessionFactory(@Qualifier("qyyDataSource") DataSource yyDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setConfiguration(mybatisProperties.getConfiguration());
        sessionFactory.setDataSource(yyDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    @Bean(name = "qyySqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("qyySqlSessionFactory") SqlSessionFactory
                                                                 sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

WYDataSourceConfig

package com.jsptpd.gayg.common.config.druid;

import com.alibaba.druid.pool.DruidDataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
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
@MapperScan(basePackages = {WYDataSourceConfig.PACKAGE_COMMON, WYDataSourceConfig.PACKAGE_OTHER, WYDataSourceConfig.PACKAGE_PROPERTY,
        WYDataSourceConfig.PACKAGE_SECU, WYDataSourceConfig.PACKAGE_SYS},
        sqlSessionTemplateRef = "wySqlSessionTemplate")
public class WYDataSourceConfig {

    static final String PACKAGE_COMMON = "com.jsptpd.gayg.common.dao";
    static final String PACKAGE_OTHER = "com.jsptpd.gayg.modules.other.dao";
    static final String PACKAGE_PROPERTY = "com.jsptpd.gayg.modules.property.dao";
    static final String PACKAGE_SECU = "com.jsptpd.gayg.modules.secu.dao";
    static final String PACKAGE_SYS = "com.jsptpd.gayg.modules.sys.dao";

    static final String MAPPER_LOCATION = "classpath:com/jsptpd/gayg/**/*.xml";

    @Autowired
    private MybatisProperties mybatisProperties;

    @Bean(name = "wyDataSource",destroyMethod = "close", initMethod = "init")
    @ConfigurationProperties(prefix = "spring.datasource_wy")
//    @ConfigurationProperties(prefix = "spring.datasource.wy")
    @Primary
    public DataSource wyDataSource() {
        return new DruidDataSource(); //com.alibaba.druid.pool
        //使用这个不报错，但是druid监控不到sql执行，因为这个是属于org.apache.tomcat.jdbc.pool
        // .DataSource@45f280a6{ConnectionPool[defaultAutoCommit=null; defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=null; driverClassName=null; maxActive=100; maxIdle=100; minIdle=10; initialSize=10; maxWait=30000; testOnBorrow=false; testOnReturn=false; timeBetweenEvictionRunsMillis=5000; numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=60000; testWhileIdle=false; testOnConnect=false; password=********; url=null; username=null; validationQuery=null; validationQueryTimeout=-1; validatorClassName=null; validationInterval=3000; accessToUnderlyingConnectionAllowed=true; removeAbandoned=false; removeAbandonedTimeout=60; logAbandoned=false; connectionProperties=null; initSQL=null; jdbcInterceptors=null; jmxEnabled=true; fairQueue=true; useEquals=true; abandonWhenPercentageFull=0; maxAge=0; useLock=false; dataSource=null; dataSourceJNDI=null; suspectTimeout=0; alternateUsernameAllowed=false; commitOnReturn=false; rollbackOnReturn=false; useDisposableConnectionFacade=true; logValidationErrors=false; propagateInterruptState=false; ignoreExceptionOnPreLoad=false; }
//        return DataSourceBuilder.create().build();
    }

    @Bean(name = "wyTransactionManager")
    @Primary
    public DataSourceTransactionManager wyTransactionManager() {
        return new DataSourceTransactionManager(wyDataSource());
    }

    @Bean(name = "wySqlSessionFactory")
    @Primary
    public SqlSessionFactory wySqlSessionFactory(@Qualifier("wyDataSource") DataSource dataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setConfiguration(mybatisProperties.getConfiguration());
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    @Bean(name = "wySqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("wySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

YYDataSourceConfig

package com.jsptpd.gayg.common.config.druid;

import com.alibaba.druid.pool.DruidDataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
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
@MapperScan(basePackages = YYDataSourceConfig.PACKAGE_OPERATIVE, sqlSessionTemplateRef = "yySqlSessionTemplate")
public class YYDataSourceConfig {
    static final String PACKAGE_OPERATIVE = "com.jsptpd.gayg.modules.operative.dao";
    static final String MAPPER_LOCATION = "classpath:com/jsptpd/gayg/**/*.xml";

    @Autowired
    private MybatisProperties mybatisProperties;

    @Bean(name = "yyDataSource", destroyMethod = "close", initMethod = "init")
    @ConfigurationProperties(prefix = "spring.datasource_yy")
//    @ConfigurationProperties(prefix = "spring.datasource.yy")
    public DataSource yyDataSource() {
        return new DruidDataSource();
//        return DataSourceBuilder.create().build();
    }

    @Bean(name = "yyTransactionManager")
    public DataSourceTransactionManager yyTransactionManager() {
        return new DataSourceTransactionManager(yyDataSource());
    }

    @Bean(name = "yySqlSessionFactory")
    public SqlSessionFactory yySqlSessionFactory(@Qualifier("yyDataSource") DataSource yyDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setConfiguration(mybatisProperties.getConfiguration());
        sessionFactory.setDataSource(yyDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    @Bean(name = "yySqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("yySqlSessionFactory") SqlSessionFactory
                                                                 sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

pom.xml配置
①如果是datasource_yy、datasource_wy和datasource_qyy这种方式，则三个对应的数据源配置QYYDataSourceConfig、WYDataSourceConfig和YYDataSourceConfig中的@ConfigurationProperties注解应该为@ConfigurationProperties(prefix = "spring.datasource_yy")，其他两个同理。
②如果是datasource下的yy、wy和qyy这种方式，则三个对应的数据源配置QYYDataSourceConfig、WYDataSourceConfig和YYDataSourceConfig中的@ConfigurationProperties注解应该为@ConfigurationProperties(prefix = "spring.datasource.yy")，其他两个同理。

pom.xml配置方式①

server:
  port: 10002

spring:
  cache:
    type: redis
  redis:
    host: 127.0.0.1
    port: 6379
    password: jihy
  resource: 
    static-locations: file:/public/
  datasource_yy:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://172.16.11.66:3306/guian?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: jsptpd
    driver-class-name: com.mysql.jdbc.Driver
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    #自动检测关闭和空闲连接的间隔
    timeBetweenEvictionRunsMillis: 30000
    #最小生存时间
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    #这个参数设置为true，可以保证每次的连接都是正常的，但是。。 性能会降低， 建议使用空闲下检测30s一次， 这样服务器最多30s出现问题后恢复
    testOnBorrow: false
    testOnReturn: false
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=3000
    filters: stat  #,wall,log4j
    #PSCatch
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
  datasource_wy:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://172.16.11.66:3306/guian?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: jsptpd
    driver-class-name: com.mysql.jdbc.Driver
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    #自动检测关闭和空闲连接的间隔
    timeBetweenEvictionRunsMillis: 30000
    #最小生存时间
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    #这个参数设置为true，可以保证每次的连接都是正常的，但是。。 性能会降低， 建议使用空闲下检测30s一次， 这样服务器最多30s出现问题后恢复
    testOnBorrow: false
    testOnReturn: false
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=3000
    filters: stat
    #PSCatch
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
  datasource_qyy:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://172.16.11.66:3306/guian?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: jsptpd
    driver-class-name: com.mysql.jdbc.Driver
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    #自动检测关闭和空闲连接的间隔
    timeBetweenEvictionRunsMillis: 30000
    #最小生存时间
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    #这个参数设置为true，可以保证每次的连接都是正常的，但是。。 性能会降低， 建议使用空闲下检测30s一次， 这样服务器最多30s出现问题后恢复
    testOnBorrow: false
    testOnReturn: false
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=3000
    filters: stat
    #PSCatch
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20

pom.xml配置方式①


server:
  port: 10002

spring:
  cache:
    type: redis
  redis:
    host: 127.0.0.1
    port: 6379
    password: jihy
  resource: 
    static-locations: file:/public/
  datasource:
    yy:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://172.16.11.66:3306/guian?useUnicode=true&characterEncoding=UTF-8
      username: root
      password: jsptpd
      driver-class-name: com.mysql.jdbc.Driver
      initialSize: 5
      minIdle: 5
      maxActive: 20
      maxWait: 60000
      #自动检测关闭和空闲连接的间隔
      timeBetweenEvictionRunsMillis: 30000
      #最小生存时间
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      #这个参数设置为true，可以保证每次的连接都是正常的，但是。。 性能会降低， 建议使用空闲下检测30s一次， 这样服务器最多30s出现问题后恢复
      testOnBorrow: false
      testOnReturn: false
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=3000
      filters: stat
      #PSCatch
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
    wy:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://172.16.11.66:3306/guian?useUnicode=true&characterEncoding=UTF-8
      username: root
      password: jsptpd
      driver-class-name: com.mysql.jdbc.Driver
      initialSize: 5
      minIdle: 5
      maxActive: 20
      maxWait: 60000
      #自动检测关闭和空闲连接的间隔
      timeBetweenEvictionRunsMillis: 30000
      #最小生存时间
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      #这个参数设置为true，可以保证每次的连接都是正常的，但是。。 性能会降低， 建议使用空闲下检测30s一次， 这样服务器最多30s出现问题后恢复
      testOnBorrow: false
      testOnReturn: false
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=3000
      filters: stat
      #PSCatch
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
    qyy:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://172.16.11.66:3306/guian?useUnicode=true&characterEncoding=UTF-8
      username: root
      password: jsptpd
      driver-class-name: com.mysql.jdbc.Driver
      initialSize: 5
      minIdle: 5
      maxActive: 20
      maxWait: 60000
      #自动检测关闭和空闲连接的间隔
      timeBetweenEvictionRunsMillis: 30000
      #最小生存时间
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      #这个参数设置为true，可以保证每次的连接都是正常的，但是。。 性能会降低， 建议使用空闲下检测30s一次， 这样服务器最多30s出现问题后恢复
      testOnBorrow: false
      testOnReturn: false
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=3000
      filters: stat
      #PSCatch
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20

多数据源也有两种用法：
① application-prod生产环境+@ConfigurationProperties(prefix = "spring.datasource_wy")
② application-test测试环境+@ConfigurationProperties(prefix = "spring.datasource.wy")

springboot中单/多数据源下druid的使用

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读