SpringBoot整合Druid

最近一直在折腾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: 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应用等等。