Spring Boot [使用 Druid 数据库连接池]

导读

最近一段时间比较忙，以至于很久没有更新Spring Boot系列文章，恰好最近用到Druid，就将Spring Boot 使用 Druid作为数据源做一个简单的介绍。

Druid介绍：

Druid是阿里巴巴开源的数据库连接池，Druid号称是Java语言中最好的数据库连接池，并且能够提供强大的监控和扩展功能，Druid的官方地址

了解更多：

JDBC连接池、监控组件 Druid （oschina）

快速上手：

下面来说明如何在 spring Boot 中配置使用Druid ，本例使用的持久层框为Spring Boot JPA。

第一步：添加pom依赖:

<dependency>

    <groupId>org.springframework.boot</groupId>

    <artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

<dependency>

    <groupId>com.alibaba</groupId>

    <artifactId>druid</artifactId>

    <version>1.0.20</version>

</dependency>

第二步：配置yml文件数据源相关信息

spring:

  datasource:

      url: jdbc:mysql://localhost:3306/sanji-boot?useUnicode=true&characterEncoding=utf-8&useSSL=false

      username: root

      password: 123456

      driver-class-name: com.mysql.jdbc.Driver

      platform: mysql

      type: com.alibaba.druid.pool.DruidDataSource

      # 下面为连接池的补充设置，应用到上面所有数据源中

      # 初始化大小，最小，最大

      initialSize: 1

      minIdle: 3

      maxActive: 20

      # 配置获取连接等待超时的时间

      maxWait: 60000

      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒

      timeBetweenEvictionRunsMillis: 60000

      # 配置一个连接在池中最小生存的时间，单位是毫秒

      minEvictableIdleTimeMillis: 30000

      validationQuery: select 'x'

      testWhileIdle: true

      testOnBorrow: false

      testOnReturn: false

      # 打开PSCache，并且指定每个连接上PSCache的大小

      poolPreparedStatements: true

      maxPoolPreparedStatementPerConnectionSize: 20

      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙

      filters: stat,wall,slf4j

      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录

      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

      # 合并多个DruidDataSource的监控数据

      #useGlobalDataSourceStat: true

  jpa:

    show-sql: true

    properties:

      hibernate:

        hbm2ddl:

          auto: create

第三步：配置Druid的监控统计功能

这里有两种方式：
第一种方式@Confing注解配置类:

@Configuration

public class DruidConfiguration {

    private static final Logger logger = LoggerFactory.getLogger(DruidConfiguration.class);

    private static final String DB_PREFIX = "spring.datasource";

    @Bean

    public ServletRegistrationBean druidServlet() {

        logger.info("init Druid Servlet Configuration ");

        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        // IP白名单

        servletRegistrationBean.addInitParameter("allow", "192.168.2.25,127.0.0.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");

        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;

    }

    //解决 spring.datasource.filters=stat,wall,log4j 无法正常注册进去

    @ConfigurationProperties(prefix = DB_PREFIX)

    class IDataSourceProperties {

        private String url;

        private String username;

        private String password;

        private String driverClassName;

        private int initialSize;

        private int minIdle;

        private int maxActive;

        private int maxWait;

        private int timeBetweenEvictionRunsMillis;

        private int minEvictableIdleTimeMillis;

        private String validationQuery;

        private boolean testWhileIdle;

        private boolean testOnBorrow;

        private boolean testOnReturn;

        private boolean poolPreparedStatements;

        private int maxPoolPreparedStatementPerConnectionSize;

        private String filters;

        private String connectionProperties;

        @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;

        }

        public String getUrl() {

            return url;

        }

        public void setUrl(String url) {

            this.url = url;

        }

        public String getUsername() {

            return username;

        }

        public void setUsername(String username) {

            this.username = username;

        }

        public String getPassword() {

            return password;

        }

        public void setPassword(String password) {

            this.password = password;

        }

        public String getDriverClassName() {

            return driverClassName;

        }

        public void setDriverClassName(String driverClassName) {

            this.driverClassName = driverClassName;

        }

        public int getInitialSize() {

            return initialSize;

        }

        public void setInitialSize(int initialSize) {

            this.initialSize = initialSize;

        }

        public int getMinIdle() {

            return minIdle;

        }

        public void setMinIdle(int minIdle) {

            this.minIdle = minIdle;

        }

        public int getMaxActive() {

            return maxActive;

        }

        public void setMaxActive(int maxActive) {

            this.maxActive = maxActive;

        }

        public int getMaxWait() {

            return maxWait;

        }

        public void setMaxWait(int maxWait) {

            this.maxWait = maxWait;

        }

        public int getTimeBetweenEvictionRunsMillis() {

            return timeBetweenEvictionRunsMillis;

        }

        public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {

            this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;

        }

        public int getMinEvictableIdleTimeMillis() {

            return minEvictableIdleTimeMillis;

        }

        public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {

            this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;

        }

        public String getValidationQuery() {

            return validationQuery;

        }

        public void setValidationQuery(String validationQuery) {

            this.validationQuery = validationQuery;

        }

        public boolean isTestWhileIdle() {

            return testWhileIdle;

        }

        public void setTestWhileIdle(boolean testWhileIdle) {

            this.testWhileIdle = testWhileIdle;

        }

        public boolean isTestOnBorrow() {

            return testOnBorrow;

        }

        public void setTestOnBorrow(boolean testOnBorrow) {

            this.testOnBorrow = testOnBorrow;

        }

        public boolean isTestOnReturn() {

            return testOnReturn;

        }

        public void setTestOnReturn(boolean testOnReturn) {

            this.testOnReturn = testOnReturn;

        }

        public boolean isPoolPreparedStatements() {

            return poolPreparedStatements;

        }

        public void setPoolPreparedStatements(boolean poolPreparedStatements) {

            this.poolPreparedStatements = poolPreparedStatements;

        }

        public int getMaxPoolPreparedStatementPerConnectionSize() {

            return maxPoolPreparedStatementPerConnectionSize;

        }

        public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {

            this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;

        }

        public String getFilters() {

            return filters;

        }

        public void setFilters(String filters) {

            this.filters = filters;

        }

        public String getConnectionProperties() {

            return connectionProperties;

        }

        public void setConnectionProperties(String connectionProperties) {

            this.connectionProperties = connectionProperties;

        }

    }

}

第二种方式基于注解的配置:

首先在启动类上加上注解：@ServletComponentScan使Spring能够扫描到我们自己编写的servlet和filter。

//druid数据源状态监控.

@WebServlet(urlPatterns = "/druid/*",

    initParams = {

        // IP白名单 (没有配置或者为空，则允许所有访问)

        @WebInitParam(name = "allow", value = "192.168.1.72,127.0.0.1"),

        // IP黑名单 (存在共同时，deny优先于allow)

        @WebInitParam(name = "deny", value = "192.168.1.73"),

        // 用户名

        @WebInitParam(name = "loginUsername", value = "admin"),

        // 密码

        @WebInitParam(name = "loginPassword", value = "admin"),

        // 禁用HTML页面上的“Reset All”功能

        @WebInitParam(name = "resetEnable", value = "false")

    }

)

public class DruidStatViewServlet extends StatViewServlet {

}

// druid过滤器.

@WebFilter(filterName = "druidWebStatFilter", urlPatterns = "/*",

    initParams = {

        // 忽略资源

        @WebInitParam(name = "exclusions", value = "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")

    }

)

public class DruidStatFilter extends WebStatFilter {

}

等应用启动后，可以访问地址：http://localhost:8080/druid/，登录之后，即可查看数据源及SQL统计等。
效果图如下：

参考资料：

官方地址
 Spring Boot 使用 Druid
Spring Boot集成Druid数据库连接池
 SPRING BOOT使用DRUID和监控配置方法

巴特西

Spring Boot [使用 Druid 数据库连接池]

导读

Druid介绍：

快速上手：

第一步：添加pom依赖:

第二步：配置yml文件数据源相关信息

第三步：配置Druid的监控统计功能

参考资料：

最新文章

热门文章

巴特西

Spring Boot [使用 Druid 数据库连接池]

导读

Druid介绍：

快速上手：

第一步：添加pom依赖:

第二步：配置yml文件 数据源相关信息

第三步：配置Druid的监控统计功能

参考资料：

最新文章

热门文章

第二步：配置yml文件数据源相关信息