一、Druid连接池1、druid简介

Druid连接池是阿里巴巴开源的数据库连接池项目。Druid连接池为监控而生,内置强大的监控功能,监控特性不影响性能。功能强大,能防SQL注入,内置Loging能诊断Hack应用行为。
Druid连接池是阿里巴巴内部唯一使用的连接池,在内部数据库相关中间件TDDL/DRDS 都内置使用强依赖了Druid连接池,经过阿里内部数千上万的系统大规模验证,经过历年双十一超大规模并发验证。

2、druid特点

1)稳定性特性,阿里巴巴的业务验证2)完备的监控信息,快速诊断系统的瓶颈3)内置了WallFilter 提供防SQL注入功能二、整合SpringBoot2.0框架1、引入核心依赖

<!-- 数据库依赖 --><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.21</version></dependency><dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.13</version></dependency><!-- JDBC 依赖 --><dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId></dependency>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数据库连接池配置文件 */@Configurationpublic 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、简单测试类

@RestControllerpublic 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 root1、Druid监控首页

主要展示链接数据库的基础信息。

2、Druid监控数据源

连接池配置的各项详细属性,可以参考这里查看,无需再从网上查找。

3、Druid监控SQL执行

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

四、源代码

GitHub地址:知了一笑https://github.com/cicadasmile码云地址:知了一笑https://gitee.com/cicadasmile