使用Druid数据库连接池

使用Druid数据库连接池

1.为什么使用Druid数据库连接池？

因为它是java语言最好的数据库连接池，提供了很多实时监控功能。

2.正式学习

2.1 引入相关依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.21</version>
</dependency>

2.2 Druid项目配置文件

#数据库配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456abc
#使用Druid需要多配置一个属性spring.datasource.type   就是要多配置一个连接池
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

#连接池的配置信息
#初始化大小，最小或最大
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20
#配置获取连接等待超时的时间
spring.datasource.druid.max-wait=60000
#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位毫秒
spring.datasource.druid.time-between-eviction-runs-millis=60000
#配置一个连接在池中最小生存的时间，单位毫秒
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.validation-query=select 1 from dual
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
#打开PSCache,并且制定每个连接上PSCache连接的大小
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
#配置监控统计拦截的filters，去掉后监控界面sql无法统计，wall用于防火墙
spring.datasource.druid.filters=stat,wall,log4j

2.3 创建配置文件引用相关配置

package com.example.demo.config;

import com.alibaba.druid.pool.DruidDataSource;
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 {

    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.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.validation-query}")
    private String validationQuery;

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

    @Value("{spring.datasource.druid.connection-properties}")
    private String connectionProperties;

    @Bean
    @Primary   //主数据源
    public DataSource dataSource(){
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(this.dbUrl);
        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) {
        }
        datasource.setConnectionProperties(connectionProperties);

        return datasource;
    }
}

2.4 新建Druid过滤器，过滤一些静态文件

//用于过滤一些静态文件
@WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
initParams={
        @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg, *.bmp, *.png,*.css,*.ico,/druid/*")//忽略资源
}
)
public class DruidFilter extends WebStatFilter{

}

2.5 新建DruidServlet，用于管理页面IP等信息

//用于过滤管理IP等信息  需要在启动类上加上@ServletComponentScan 否则无法扫描到此类
@WebServlet(urlPatterns="/druid/*",
initParams={
        @WebInitParam(name="allow",value=""),// IP白名单(没有配置或者为空，则允许所有访问)
        @WebInitParam(name="deny",value=""),// IP黑名单 (deny优先于allow)
        @WebInitParam(name="loginUsername",value="admin"),// 登录druid管理页面用户名
        @WebInitParam(name="loginPassword",value="admin")// 登录druid管理页面密码
})
public class DruidServlet extends StatViewServlet{
    
}

2.6 新建实体类，repository，controller一样的，不多言。

2.7 访问localhost:8080/druid,登录后就可以查看各项监控信息。