SpringBoot整合Druid

一.Druid连接池

关于Druid

Druid是阿里团队开源的高性能数据库连接池，国内使用广泛，特别是在监控sql和数据库性能方面非常强大。
连接池能有效节省数据库连接消耗，且对高写入、实时性要求高的业务非常合适。

SpringBoot搭建

1. 引入依赖

 		<!--alibaba数据源,池依赖,fastjson依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.1.41</version>
        </dependency>

        <!--druid连接池需要使用log4j依赖-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.15</version>
        </dependency>

2. application.yml配置

#Oracle配置
spring:
  datasource:
    druid: #连接池配置,监控数据库
      driver-class-name: oracle.jdbc.driver.OracleDriver
      url: jdbc:oracle:thin:@10.160.2.205:1521:ORA11G
      #driver-class-name: com.p6spy.engine.spy.P6SpyDriver
      #url: jdbc:p6spy:oracle:thin:@10.160.2.205:1521:ORA11G
      username: locomanager
      password: locomanager
      #初始化大小,最大,最小连接数
      initial-size: 5
      max-active: 20
      min-idle: 5
      #配置获取连接等待超时的时间
      max-wait: 60000
      #配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
      time-between-eviction-runs-millis: 60000
      #配置一个连接在池中最小生存的时间,单位是毫秒
      min-evictable-idle-time-millis: 300000
      #验证连接是否成功
      validation-query: select 1 from dual
      #在获取连接后,确定是否要进行连接空间时间的检查
      test-while-idle: true
      #获取连接检测
      test-on-borrow: false
      #归还连接检测
      test-on-return: false
      #打开PSCache,并指定每个连接上PSCache的大小
      pool-prepared-statements: true
      max-open-prepared-statements: 20
      #配置监控统计拦截的filters,去掉监控界面sql无法统计,"wall"用于防火墙
      filters: stat,wall,log4j
      #通过connectProperties属性打开mergeSql功能,慢sql记录
      connection-properties: druid.stat.mergeSql=true
      filter:
        wall:
          config:
            multi-statement-allow: true #允许多条sql带备注(带注释)

3. 编写配置类

编写druid过滤器

package com.byxx.yunan.config;

import com.alibaba.druid.support.http.WebStatFilter;

import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

/**
 * 配置druid过滤器
 * @Author: yww
 */
@WebFilter(filterName = "druidWebStatFilter",urlPatterns = "/*",
    initParams = {
        @WebInitParam(name = "exclusions",value = "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*") //忽略资源
    })
public class DruidStatFilter extends WebStatFilter {

}

编写druid服务

package com.byxx.yunan.config;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;

/**
 * 配置druid服务
 * @Author: yww
 */
@WebServlet(urlPatterns = "/druid/*",
    initParams = {
        @WebInitParam(name = "allow",value = ""), //IP白名单 (没有配置或者为空,则允许所有访问)
//        @WebInitParam(name = "deny",value = ""), //IP黑名单 (存在共同时,deny优先于allow)
        @WebInitParam(name = "loginUsername",value = "admin"), //用户名
        @WebInitParam(name = "loginPassword",value = "admin"), //密码
        @WebInitParam(name = "resetEnable",value = "false") //禁止html页面上的 "Reset All" 功能
    })
public class DruidStatViewServlet extends StatViewServlet{
    
}

4. springboot启动类加上@ServletComponentScan注解

访问地址如下 : http://localhost:8080/项目名/druid/index.html

package com.byxx.yunan;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;

/**
 * swagger访问地址 : http://localhost:8080/yunan/swagger-ui.html
 * druid监控地址 : http://localhost:8080/yunan/druid/index.html
 */
@SpringBootApplication
@MapperScan({"com.byxx.yunan.manager.*.mapper"})//扫描mapper
@ServletComponentScan//druid服务监控
public class YunanApplication {

    public static void main(String[] args) {
        SpringApplication.run(YunanApplication.class, args);
    }

}