最近一直在折腾 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.YAML 中设置相关配置
- 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 没法生效写在 YAML 文件中的配置, 需要单独编写 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 对象.
关于多数据源参数的设置.
只需要在 YAML(application.YAML) 中设置即可, 格式如下:
- 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.YAML 中增加如下配置:
- druid:
- .....
- # 通过 connectProperties 属性来打开 mergeSql 功能; 慢 SQL 记录
- connection-properties: druid.stat.mergeSql=true;druid.stat.SlowSqlMills=5000
- # 监控后台的配置, 如登录账号和密码等
- monitor:
- allow: 127.0.0.1
- loginUsername: admin
- loginPassword: admin
b) 单独编写 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 应用等等.
来源: https://www.cnblogs.com/freephp/p/11688387.html