知识点③：Spring Boot 中的 druid 配置及使用

为什么80%的码农都做不了架构师？>>>   

Spring Boot druid 配置

依赖

1. 在 Spring Boot 项目中加入 druid 依赖

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>${druid.version}</version>
</dependency>

配置

spring.datasource.url= 
spring.datasource.username=
spring.datasource.password=
# ...其他配置（可选，不是必须的，使用内嵌数据库的话上述三项也可省略不填）

数据源配置参数详解

参考 → DRUID连接池的使用

• #druid-jdbc 配置

#数据库连接:这里是 mysql
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/group-pool?useUnicode=true&characterEncoding=UTF-8&useSSL=false
#数据库用户名
spring.datasource.username=root
#数据库密码
spring.datasource.password=sansicn
#数据库驱动类
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

• #druid-cp 配置

#数据源名称，当多数据源时，可通过该名称来区分
#默认生成名称为："DataSource-" + System.identityHashCode(this)
spring.datasource.name=
#数据源类型
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
spring.datasource.initialSize=10
#最小连接池数量
spring.datasource.minIdle=10
#最大连接池数量
spring.datasource.maxActive=200
#获取连接时最大等待时间，单位毫秒。
#配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
spring.datasource.maxWait=60000
#有两个含义： 
#1) Destroy线程会检测连接的间隔时间
#2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明
spring.datasource.timeBetweenEvictionRunsMillis=60000
#一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
#验证连接有效与否的SQL，不同的数据库配置不同
spring.datasource.validationQuery=SELECT 1 FROM DUAL
#建议配置为true，不影响性能，并且保证安全性
#申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
spring.datasource.testWhileIdle=true
#申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
spring.datasource.testOnBorrow=false
#归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
spring.datasource.testOnReturn=false
#是否缓存preparedStatement，也就是PSCache
#PSCache对支持游标的数据库性能提升巨大，比如说oracle
#在mysql下建议关闭???
spring.datasource.poolPreparedStatements=true
#每个连接上PSCache的大小
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j

数据源配置类

package com.shuchuanggroup.group_pool.config;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;

/**
 * DRUID数据源配置
 */
@Configuration
public class DruidConfig {

    @Value("${spring.datasource.url}")
    private String dbUrl;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.initialSize}")
    private Integer initialSize;
    @Value("${spring.datasource.minIdle}")
    private Integer minIdle;
    @Value("${spring.datasource.maxActive}")
    private Integer maxActive;
    @Value("${spring.datasource.maxWait}")
    private Integer maxWait;
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private Integer timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private Integer minEvictableIdleTimeMillis;
    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;
    @Value("${spring.datasource.testWhileIdle}")
    private Boolean testWhileIdle;
    @Value("${spring.datasource.testOnBorrow}")
    private Boolean testOnBorrow;
    @Value("${spring.datasource.testOnReturn}")
    private Boolean testOnReturn;
    @Value("${spring.datasource.poolPreparedStatements}")
    private Boolean poolPreparedStatements;
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private Integer maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.filters}")
    private String filters;
    @Value("{spring.datasource.connectionProperties}")
    private String connectionProperties;

    @Bean
    @Primary
    public DataSource dataSource(){
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        //configuration
        if(initialSize != null) {
            datasource.setInitialSize(initialSize);
        }
        if(minIdle != null) {
            datasource.setMinIdle(minIdle);
        }
        if(maxActive != null) {
            datasource.setMaxActive(maxActive);
        }
        if(maxWait != null) {
            datasource.setMaxWait(maxWait);
        }
        if(timeBetweenEvictionRunsMillis != null) {
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        }
        if(minEvictableIdleTimeMillis != null) {
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        }
        if(validationQuery!=null) {
            datasource.setValidationQuery(validationQuery);
        }
        if(testWhileIdle != null) {
            datasource.setTestWhileIdle(testWhileIdle);
        }
        if(testOnBorrow != null) {
            datasource.setTestOnBorrow(testOnBorrow);
        }
        if(testOnReturn != null) {
            datasource.setTestOnReturn(testOnReturn);
        }
        if(poolPreparedStatements != null) {
            datasource.setPoolPreparedStatements(poolPreparedStatements);
        }
        if(maxPoolPreparedStatementPerConnectionSize != null) {
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        }

        if(connectionProperties != null) {
            datasource.setConnectionProperties(connectionProperties);
        }

        List<Filter> filters = new ArrayList<>();
        filters.add(statFilter());
        filters.add(wallFilter());
        datasource.setProxyFilters(filters);

        return datasource;
    }

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
        servletRegistrationBean.setServlet(new StatViewServlet());
        servletRegistrationBean.addUrlMappings("/druid/*");
        return servletRegistrationBean;
    }

    @Bean
    public StatFilter statFilter(){
        StatFilter statFilter = new StatFilter();
        statFilter.setLogSlowSql(true);
        statFilter.setMergeSql(true);
        statFilter.setSlowSqlMillis(1000);

        return statFilter;
    }

    @Bean
    public WallFilter wallFilter(){
        WallFilter wallFilter = new WallFilter();

        //允许执行多条SQL
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);
        wallFilter.setConfig(config);

        return wallFilter;
    }

}

浏览器监控配置

• 监控支持

#配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j

• StatViewServlet servlet配置

import com.alibaba.druid.support.http.StatViewServlet;

import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;

@WebServlet(urlPatterns="/druid/*",initParams={
        // @WebInitParam(name="allow",value="192.168.1.72,127.0.0.1"),// IP白名单
        // @WebInitParam(name="deny",value="192.168.1.73"),// IP黑名单
        @WebInitParam(name="loginUsername",value="admin"),// 用户名
        @WebInitParam(name="loginPassword",value="123456"),// 密码
        @WebInitParam(name="resetEnable",value="false")//是否显示重置统计数据按钮
    }
)
public class DruidStatViewServlet extends StatViewServlet {
    private static final long serialVersionUID = 1L;
}

• WebStatFilter 静态资源过滤器

import com.alibaba.druid.support.http.WebStatFilter;

import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

@WebFilter(filterName="druidWebStatFilter",
    urlPatterns="/*",
    initParams={
        @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")//忽略资源
    }
)
public class DruidStatFilter extends WebStatFilter {}

多数据源配置：mybatis

参考 http://www.mybatis.org/spring/index.html 1.spring boot 默认启用了 单数据源的自动配置，所以第一步我们需要关闭数据源的自动配置功能：

//只需要在spring boot app注解增加排除参数，排除掉数据源的自动配置配置功能即可
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class OnlyswordApplication {
	public static void main(String[] args) {
		SpringApplication.run(OnlyswordApplication.class, args);
	}
}

2.分别配置数据源配置，并指定数据源名称：

• properties文件

#db1
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/only_sword?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root

#db2
spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db2.url=jdbc:mysql://127.0.0.1:3306/only_sword?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.db2.username=root
spring.datasource.db2.password=root

• datasource 配置

1. ds1配置

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;

@Configuration
public class Datasource1Config {

    @Value("${spring.datasource.url}")
    private String dbUrl;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    //生成数据源1
    @Bean(name = "ds1")
    @Primary
    public DataSource dataSource(){
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        return datasource;
    }
}

2. ds2配置

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;

@Configuration
public class Datasource2Config {

    @Value("${spring.datasource.db2.url}")
    private String dbUrl;
    @Value("${spring.datasource.db2.username}")
    private String username;
    @Value("${spring.datasource.db2.password}")
    private String password;
    @Value("${spring.datasource.db2.driver-class-name}")
    private String driverClassName;

    //生成数据源2
    @Bean(name = "ds2")
    @Primary
    public DataSource dataSource(){
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        return datasource;
    }
}

• mybatis 配置

1. properties 配置

# mybatis_plus_config
mybatis-plus.m1.mapper-locations=classpath*:mapper1/*Mapper.xml
mybatis-plus.m1.typeAliasesPackage=com.sword.onlysword.entity


mybatis-plus.m2.mapper-locations=classpath*:mapper2/*Mapper.xml
mybatis-plus.m2.typeAliasesPackage=com.sword.onlysword.entity

2. 引用 ds1 并扫描映射器，将它们链接到指定的SqlSessionTemplate并将它们注册到Spring上下文，以便将它们注入到bean中。

import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.sword.onlysword.mapper1*", sqlSessionTemplateRef = "template1")
public class MybatisConfig {

    @Value("${mybatis-plus.m1.mapper-locations}")
    private String mapperLocations;
    @Value("${mybatis-plus.m1.typeAliasesPackage}")
    private String aliasesPackage;

    @Autowired
    @Qualifier("db1")
    private DataSource db1;

    @Bean
    public SqlSessionTemplate template1() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(db1);
        //配置实体映射
        factoryBean.setTypeAliasesPackage(aliasesPackage);
        //配置 mapper.xml 所在包
        factoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return new SqlSessionTemplate(factoryBean.getObject());
    }

}

2. 引用 ds2 并扫描映射器，将它们链接到指定的SqlSessionTemplate并将它们注册到Spring上下文，以便将它们注入到bean中。

import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.sword.onlysword.mapper2*", sqlSessionTemplateRef = "template2")
public class MybatisConfig {

    @Value("${mybatis-plus.m2.mapper-locations}")
    private String mapperLocations;
    @Value("${mybatis-plus.m2.typeAliasesPackage}")
    private String aliasesPackage;

    @Autowired
    @Qualifier("db2")
    private DataSource db2;

    @Bean
    public SqlSessionTemplate template2() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(db2);
        factoryBean.setTypeAliasesPackage(aliasesPackage);
        factoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return new SqlSessionTemplate(factoryBean.getObject());
    }

}

这样只需要调用对应的 mapper 接口就能操作不同的数据源了，不同的 sqlsessiontemple 所对应的 mapper.xml 分别来自不同的文件夹，降低复杂度，方便维护，增强可读性

多数据源动态配置

待续

Druid Spring Boot Starter

• 更简洁、更简单的druid配置

Git 地址 → Druid Spring Boot Starter

Druid

• 源码地址

Git 地址 → Druid

转载于:https://my.oschina.net/ss6/blog/1634014