一、Druid连线池

Druid连线池是阿里巴巴开源的数据库连线池专案。Druid连线池为监控而生，内建强大的监控功能，监控特性不影响效能。功能强大，能防SQL注入，内建Loging能诊断Hack应用行为。

Druid连线池是阿里巴巴内部唯一使用的连线池，在内部数据库相关中介软件TDDL/DRDS 都内建使用强依赖了Druid连线池，经过阿里内部数千上万的系统大规模验证，经过历年双十一超大规模并发验证。

2、druid特点

1）稳定性特性，阿里巴巴的业务验证

2）完备的监控资讯，快速诊断系统的瓶颈

3）内建了WallFilter 提供防SQL注入功能

二、整合SpringBoot2.0框架

mysql

mysql-connector-java

5.1.21

com.alibaba

druid-spring-boot-starter

1.1.13

org.springframework.boot

spring-boot-starter-jdbc

2、资料来源配置档案

spring:

application:

# 应用名称

name: node07-boot-druid

datasource:

type: com.alibaba.druid.pool.DruidDataSource

druid:

driverClassName: com.mysql.jdbc.Driver

url: jdbc:mysql://localhost:3306/data_one?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false

username: root

password: 123

initial-size: 10

max-active: 100

min-idle: 10

max-wait: 60000

pool-prepared-statements: true

max-pool-prepared-statement-per-connection-size: 20

time-between-eviction-runs-millis: 60000

min-evictable-idle-time-millis: 300000

max-evictable-idle-time-millis: 60000

validation-query: SELECT 1 FROM DUAL

# validation-query-timeout: 5000

test-on-borrow: false

test-on-return: false

test-while-idle: true

connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

#filters: #配置多个英文逗号分隔(统计，sql注入，log4j过滤)

filters: stat,wall

stat-view-servlet:

enabled: true

url-pattern: /druid/*

3、核心配置类

import com.alibaba.druid.pool.DruidDataSource;

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

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

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.boot.web.servlet.FilterRegistrationBean;

import org.springframework.boot.web.servlet.ServletRegistrationBean;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.jdbc.core.JdbcTemplate;

/**

* Druid数据库连线池配置档案

*/

@Configuration

public class DruidConfig {

private static final Logger logger = LoggerFactory.getLogger(DruidConfig.class);

@Value("${spring.datasource.druid.url}")

private String dbUrl;

@Value("${spring.datasource.druid.username}")

private String username;

@Value("${spring.datasource.druid.password}")

private String password;

@Value("${spring.datasource.druid.driverClassName}")

private String driverClassName;

@Value("${spring.datasource.druid.initial-size}")

private int initialSize;

@Value("${spring.datasource.druid.max-active}")

private int maxActive;

@Value("${spring.datasource.druid.min-idle}")

private int minIdle;

@Value("${spring.datasource.druid.max-wait}")

private int maxWait;

@Value("${spring.datasource.druid.pool-prepared-statements}")

private boolean poolPreparedStatements;

@Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")

private int maxPoolPreparedStatementPerConnectionSize;

@Value("${spring.datasource.druid.time-between-eviction-runs-millis}")

private int timeBetweenEvictionRunsMillis;

@Value("${spring.datasource.druid.min-evictable-idle-time-millis}")

private int minEvictableIdleTimeMillis;

@Value("${spring.datasource.druid.max-evictable-idle-time-millis}")

private int maxEvictableIdleTimeMillis;

@Value("${spring.datasource.druid.validation-query}")

private String validationQuery;

@Value("${spring.datasource.druid.test-while-idle}")

private boolean testWhileIdle;

@Value("${spring.datasource.druid.test-on-borrow}")

private boolean testOnBorrow;

@Value("${spring.datasource.druid.test-on-return}")

private boolean testOnReturn;

@Value("${spring.datasource.druid.filters}")

private String filters;

@Value("{spring.datasource.druid.connection-properties}")

private String connectionProperties;

/**

* Druid 连线池配置

*/

@Bean //宣告其为Bean例项

public DruidDataSource dataSource() {

DruidDataSource datasource = new DruidDataSource();

datasource.setUrl(dbUrl);

datasource.setUsername(username);

datasource.setPassword(password);

datasource.setDriverClassName(driverClassName);

datasource.setInitialSize(initialSize);

datasource.setMinIdle(minIdle);

datasource.setMaxActive(maxActive);

datasource.setMaxWait(maxWait);

datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);

datasource.setMaxEvictableIdleTimeMillis(minEvictableIdleTimeMillis);

datasource.setValidationQuery(validationQuery);

datasource.setTestWhileIdle(testWhileIdle);

datasource.setTestOnBorrow(testOnBorrow);

datasource.setTestOnReturn(testOnReturn);

datasource.setPoolPreparedStatements(poolPreparedStatements);

datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

try {

datasource.setFilters(filters);

} catch (Exception e) {

logger.error("druid configuration initialization filter", e);

}

datasource.setConnectionProperties(connectionProperties);

return datasource;

}

/**

* JDBC操作配置

*/

@Bean(name = "dataOneTemplate")

public JdbcTemplate jdbcTemplate (@Autowired DruidDataSource dataSource){

return new JdbcTemplate(dataSource) ;

}

/**

* 配置 Druid 监控界面

*/

@Bean

public ServletRegistrationBean statViewServlet(){

ServletRegistrationBean srb =

new ServletRegistrationBean(new StatViewServlet(),"/druid/*");

//设定控制台管理使用者

srb.addInitParameter("loginUsername","root");

srb.addInitParameter("loginPassword","root");

//是否可以重置资料

srb.addInitParameter("resetEnable","false");

return srb;

}

@Bean

public FilterRegistrationBean statFilter(){

//建立过滤器

FilterRegistrationBean frb =

new FilterRegistrationBean(new WebStatFilter());

//设定过滤器过滤路径

frb.addUrlPatterns("/*");

//忽略过滤的形式

frb.addInitParameter("exclusions",

"*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");

return frb;

}

}

4、简单测试类

@RestController

public class DruidController {

private static final Logger LOG = LoggerFactory.getLogger(DruidController.class);

@Resource

private JdbcTemplate jdbcTemplate ;

@RequestMapping("/druidData")

public String druidData (){

String sql = "SELECT COUNT(1) FROM d_phone" ;

Integer countOne = jdbcTemplate.queryForObject(sql,Integer.class) ;

// countOne==2

LOG.info("countOne=="+countOne);

return "success" ;

}

}

三、测试效果

http://localhost:8007/druid

输入配置的使用者名称和密码：

root root

1、Druid监控首页

主要展示连结数据库的基础资讯。

2、Druid监控资料来源

连线池配置的各项详细属性，可以参考这里检视，无需再从网上查询。

3、Druid监控SQL执行

所有执行的SQL，都会在这里被监控到，且会有SQL执行的详细计划。

四、源代码

https://github.com/cicadasmile/spring-boot-base

码云地址：知了一笑

https://gitee.com/cicadasmile/spring-boot-base