springboot集成druid连接池
相对于dbcp、c3p0等连接池,druid性能更优越,提供可视化的sql监控页面,简直是目前最好用的数据库连接池没有之一。
那么如何快速集成springboot+druid呢?
- 导包
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.9</version> </dependency>
- 编写Druid配置文件
package com.xp.pay.gateway.config; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; 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.context.annotation.Primary; import javax.sql.DataSource; import java.sql.SQLException; /** * Created By xp. on 2019/1/28 */ @Configuration public class DruidConfiguration { @Value("${spring.datasource.url}") private String url; @Value("${spring.datasource.username}") private String username; @Value("${spring.datasource.password}") private String password; @Value("${spring.datasource.driverClassName}") private String driverClassName; @Value("${spring.datasource.initialSize}") private int initialSize; @Value("${spring.datasource.minIdle}") private int minIdle; @Value("${spring.datasource.maxActive}") private int maxActive; @Value("${spring.datasource.maxWait}") private int maxWait; @Value("${spring.datasource.timeBetweenEvictionRunsMillis}") private int timeBetweenEvictionRunsMillis; @Value("${spring.datasource.minEvictableIdleTimeMillis}") private int 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 int maxPoolPreparedStatementPerConnectionSize; @Value("${spring.datasource.filters}") private String filters; @Value("{spring.datasource.connectionProperties}") private String connectionProperties; @Value("${spring.datasource.logSlowSql}") private String logSlowSql; @Bean public ServletRegistrationBean druidServlet() { ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); // IP白名单 servletRegistrationBean.addInitParameter("allow", "192.168.36.1"); // IP黑名单(共同存在时,deny优先于allow) servletRegistrationBean.addInitParameter("deny", "192.168.1.100"); //控制台管理用户 servletRegistrationBean.addInitParameter("loginUsername", "admin"); servletRegistrationBean.addInitParameter("loginPassword", "admin"); //是否能够重置数据 禁用HTML页面上的“Reset All”功能 servletRegistrationBean.addInitParameter("resetEnable", "false"); servletRegistrationBean.addInitParameter("logSlowSql", logSlowSql); return servletRegistrationBean; } @Bean public FilterRegistrationBean filterRegistrationBean() { FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter()); filterRegistrationBean.addUrlPatterns("/*"); filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); return filterRegistrationBean; } @Bean //声明其为Bean实例 @Primary //在同样的DataSource中,首先使用被标注的DataSource public DataSource dataSource() { DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(url); datasource.setUsername(username); datasource.setPassword(password); datasource.setDriverClassName(driverClassName); //configuration datasource.setInitialSize(initialSize); datasource.setMinIdle(minIdle); datasource.setMaxActive(maxActive); datasource.setMaxWait(maxWait); datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); datasource.setValidationQuery(validationQuery); datasource.setTestWhileIdle(testWhileIdle); datasource.setTestOnBorrow(testOnBorrow); datasource.setTestOnReturn(testOnReturn); datasource.setPoolPreparedStatements(poolPreparedStatements); datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); try { datasource.setFilters(filters); } catch (SQLException e) { System.err.println("druid configuration initialization filter: " + e); } datasource.setConnectionProperties(connectionProperties); return datasource; } }
- 编写application.yml
spring : datasource : type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver driver-class-name: com.mysql.jdbc.Driver platform: mysql url: jdbc:mysql://127.0.0.1:3306/x_test?useUnicode=true&characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull username: root password: yourPassword initialSize: 5 #初始化连接大小 minIdle: 5 #最小连接池数量 maxActive: 20 #最大连接池数量 maxWait: 60000 #获取连接时最大等待时间,单位毫秒 maxPoolPreparedStatementPerConnectionSize: 20 timeBetweenEvictionRunsMillis: 60000 #配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 minEvictableIdleTimeMillis: 300000 #配置一个连接在池中最小生存的时间,单位是毫秒 validationQuery: SELECT 1 FROM DUAL #测试连接 testWhileIdle: true #申请连接的时候检测,建议配置为true,不影响性能,并且保证安全性 testOnBorrow: false #获取连接时执行检测,建议关闭,影响性能 testOnReturn: false #归还连接时执行检测,建议关闭,影响性能 poolPreparedStatements: false #是否开启PSCache,PSCache对支持游标的数据库性能提升巨大,mysql下建议关闭 filters: stat,wall,log4j2 #配置扩展插件,常用的插件有=>stat:监控统计 log4j:日志 wall:防御sql注入 connectionProperties: 'druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000' #慢SQL记录 logSlowSql: true #是否打印sql
- 访问http://localhost:8080/druid/login.html