SpringBoot整合Druid
- 2019 年 10 月 16 日
- 笔记
最近一直在折腾springboot，也根据需要整合了一些好用的库，mybatis作为持续层数据操作，也想用一下大名鼎鼎的Druid来做连接池和数据库监控服务。于是摸索了一下午，整理出这篇小文，希望能帮助到同道，也给自己做个记录反思。
- 1.关于Druid
Druid是阿里团队开源的高性能数据库连接池，国内使用广泛，特别是在监控sql和数据库性能方面非常强大。
连接池能有效节省数据库连接消耗，且对高写入、实时性要求高的业务非常合适。
- 2.引入依赖到pom.xml中。
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> - 3.然后在application.yml中设置相关配置
spring: datasource: url: jdbc:mysql://127.0.0.1:3306/test_go?characterEncoding=UTF-8&useSSL=false username: xxx password: xxxxxx driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource druid: initial-size: 5 min-idle: 5 max-active: 20 test-while-idle: true test-on-borrow: false test-on-return: false pool-prepared-statements: true max-pool-prepared-statement-per-connection-size: 20 max-wait: 60000 time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 30000 filters: stat async-init: true其中最重要的是
type: com.alibaba.druid.pool.DruidDataSource
这样就不会使用Springboot默认的连接池Hikari。
由于SpringBoot没法生效写在yml文件中的配置，需要单独编写Bean文件来加载。
- 4.定义Druid配置类。
package com.tony.testspringboot.config; import com.alibaba.druid.pool.DruidDataSource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; 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; import java.sql.SQLException; @Configuration public class DruidConfig { private Logger logger = LoggerFactory.getLogger(DruidConfig.class); @Value("${spring.datasource.url}") private String dbUrl; @Value("${spring.datasource.username}") private String username; @Value("${spring.datasource.password}") private String password; @Value("${spring.datasource.test-druid.driver-class-name}") private String driverClassName; @Value("${spring.datasource.druid.initial-size}") private int initialSize; @Value("${spring.datasource.druid.min-idle}") private int minIdle; @Value("${spring.datasource.druid.max-active}") private int maxActive; @Value("${spring.datasource.druid.max-wait}") private int maxWait; @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.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.pool-prepared-statements}") private boolean poolPreparedStatements; @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}") private int maxPoolPreparedStatementPerConnectionSize; @Value("${spring.datasource.druid.filters}") private String filters; @Bean @Primary public DataSource dataSource() { DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(this.dbUrl); datasource.setUsername(this.username); datasource.setPassword(this.password); datasource.setDriverClassName(this.driverClassName); // configuration datasource.setInitialSize(this.initialSize); datasource.setMinIdle(this.minIdle); datasource.setMaxActive(this.maxActive); datasource.setMaxWait(this.maxWait); datasource.setTimeBetweenEvictionRunsMillis(this.timeBetweenEvictionRunsMillis); datasource.setMinEvictableIdleTimeMillis(this.minEvictableIdleTimeMillis); datasource.setTestWhileIdle(this.testWhileIdle); datasource.setTestOnBorrow(this.testOnBorrow); datasource.setTestOnReturn(this.testOnReturn); datasource.setPoolPreparedStatements(this.poolPreparedStatements); datasource.setMaxPoolPreparedStatementPerConnectionSize(this.maxPoolPreparedStatementPerConnectionSize); try { datasource.setFilters(this.filters); } catch (SQLException e) { logger.error("druid configuration init fail!"); } return datasource; } } 如此即可让配置的参数生效并作为首选的DataSource进行使用。
5.在Controller中测试。
可以使用JdbcTemplate来进行查询。
测试代码如下所示：
@RequestMapping(value = "/hey", method = RequestMethod.GET) public ResultResponse testDruid() { String sql = "SELECT mobile FROM user WHERE id = ?"; String mobile = jdbcTemplate.queryForObject(sql, new Object[]{1}, String.class); return new ResultResponse(201, "hey" + mobile); } PS: ResultResponse是我项目中封装的通用response对象。
关于多数据源参数的设置。
只需要在yml(application.yml)中设置即可，格式如下：
datasource: one-source: url: jdbc:mysql://127.0.0.1:3306/test_go?characterEncoding=UTF-8&useSSL=false username: xxxx1 password: xxxx driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource two-source: url: jdbc:mysql://127.0.0.1:3306/demo2?characterEncoding=UTF-8&useSSL=false username: xxxx password: 1xxxxxx driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource ...使用的时候也遵循这个结构，如要获取第一个数据源的url配置则在DruidConfig.java文件中的相应项的@Value中这样写：
@Value("${spring.datasource.one-source.url}") private String dbUrl;- 6.配置数据库监控。
a) 先在application.yml中增加如下配置：
druid: ..... # 通过connectProperties属性来打开mergeSql功能；慢SQL记录 connection-properties: druid.stat.mergeSql=true;druid.stat.SlowSqlMills=5000 # 监控后台的配置，如登录账号和密码等 monitor: allow: 127.0.0.1 loginUsername: admin loginPassword: adminb)单独编写DruidMonitorConfiguration类。
public class DruidMonitorConfiguration { @Value("${spring.datasource.druid.monitor.allow}") private String allow; // @Value("${spring.datasource.druid.monitor.deny}") // private String deny; @Value("${spring.datasource.druid.monitor.loginUsername}") private String loginUsername; @Value("${spring.datasource.druid.monitor.loginPassword}") private String loginPassword; @Value("${spring.datasource.druid.monitor.resetEnable") private String resetEnable; @Bean public ServletRegistrationBean druidStatViewServlet() { ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); servletRegistrationBean.addInitParameter("allow", this.allow); // servletRegistrationBean.addInitParameter("deny", this.deny); servletRegistrationBean.addInitParameter("loginUsername", this.loginUsername); servletRegistrationBean.addInitParameter("loginPassword", this.loginPassword); servletRegistrationBean.addInitParameter("resetEnable", this.resetEnable); return servletRegistrationBean; } @Bean public FilterRegistrationBean druidStatFilter() { FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter()); filterRegistrationBean.addUrlPatterns("/*"); filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); return filterRegistrationBean; } } 访问方法就是:http://project-name.com/druid/login.html
验证登录即可。整个监控功能十分强大，有sql监控、URI监控、Session监控，Web应用等等。
