怎么在SpringBoot中使用Druid数据库连接池

tangjin

作者

这篇文章主要介绍怎么在SpringBoot中使用Druid数据库连接池，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

Druid是阿里开源的一款数据库连接池，除了常规的连接池功能外，它还提供了强大的监控和扩展功能。这对没有做数据库监控的小项目有很大的吸引力。

下列步骤可以让你无脑式的在SpringBoot2.x中使用Druid。

1.Maven中的pom文件

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.1.14</version>
</dependency>
<dependency>
	<groupId>log4j</groupId>
	<artifactId>log4j</artifactId>
	<version>1.2.17</version>
</dependency>

使用 Spring Boot-2.x时，如果未引入log4j，在配置 Druid 时，会遇到Caused by: java.lang.ClassNotFoundException: org.apache.log4j.Priority的报错。因为Spring Boot默认使用的是log4j2。

2.SpringBoot 配置文件

下面是一个完整的yml文件的，其中使用mybatis作为数据库访问框架

server:
servlet:
context-path:/
session:
timeout:60m
port:8080

spring:
datasource:
url:jdbc:mysql://127.0.0.1:9080/hospital?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
username:root
password:root
#环境dev|test|pro
profiles:
active:dev
driver-class-name:com.mysql.cj.jdbc.Driver

##########################druid配置##########################
type:com.alibaba.druid.pool.DruidDataSource
#初始化大小，最小，最大
initialSize:5
minIdle:1
maxActive:20
#配置获取连接等待超时的时间
maxWait:60000
#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
timeBetweenEvictionRunsMillis:60000
#配置一个连接在池中最小生存的时间，单位是毫秒
minEvictableIdleTimeMillis:300000
#校验SQL，Oracle配置validationQuery=SELECT1FROMDUAL，如果不配validationQuery项，则下面三项配置无用
validationQuery:SELECT1FROMDUAL
testWhileIdle:true
testOnBorrow:false
testOnReturn:false
#打开PSCache，并且指定每个连接上PSCache的大小
poolPreparedStatements:true
maxPoolPreparedStatementPerConnectionSize:20
#配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
filters:stat,wall,log4j
#通过connectProperties属性来打开mergeSql功能；慢SQL记录
connectionProperties:druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#合并多个DruidDataSource的监控数据
useGlobalDataSourceStat:true

#Mybatis配置
mybatis:
mapperLocations:classpath:mapper/*.xml,classpath:mapper/extend/*.xml
configuration:
map-underscore-to-camel-case:true
log-impl:org.apache.ibatis.logging.stdout.StdOutImpl

3.配置Druid数据源实例

由于Druid暂时不在Spring Boot中的直接支持，故需要进行配置信息的定制：

SpringBoot中的配置信息无法再Druid中直接生效，需要在Spring容器中实现一个DataSource实例。

importjava.sql.SQLException;
importjavax.sql.DataSource;
importorg.apache.log4j.Logger;
importorg.springframework.beans.factory.annotation.Value;
importorg.springframework.context.annotation.Bean;
importorg.springframework.context.annotation.Configuration;
importorg.springframework.context.annotation.Primary;

importcom.alibaba.druid.pool.DruidDataSource;

@Configuration
publicclassDruidDBConfig{
	privateLoggerlogger=Logger.getLogger(this.getClass());	//log4j日志

	@Value("${spring.datasource.url}")
	privateStringdbUrl;

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

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

	@Value("${spring.datasource.driver-class-name}")
	privateStringdriverClassName;

	@Value("${spring.datasource.initialSize}")
	privateintinitialSize;

	@Value("${spring.datasource.minIdle}")
	privateintminIdle;

	@Value("${spring.datasource.maxActive}")
	privateintmaxActive;

	@Value("${spring.datasource.maxWait}")
	privateintmaxWait;

	@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
	privateinttimeBetweenEvictionRunsMillis;

	@Value("${spring.datasource.minEvictableIdleTimeMillis}")
	privateintminEvictableIdleTimeMillis;

	@Value("${spring.datasource.validationQuery}")
	privateStringvalidationQuery;

	@Value("${spring.datasource.testWhileIdle}")
	privatebooleantestWhileIdle;

	@Value("${spring.datasource.testOnBorrow}")
	privatebooleantestOnBorrow;

	@Value("${spring.datasource.testOnReturn}")
	privatebooleantestOnReturn;

	@Value("${spring.datasource.poolPreparedStatements}")
	privatebooleanpoolPreparedStatements;

	@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
	privateintmaxPoolPreparedStatementPerConnectionSize;

	@Value("${spring.datasource.filters}")
	privateStringfilters;

	@Value("{spring.datasource.connectionProperties}")
	privateStringconnectionProperties;

	@Bean//声明其为Bean实例
	@Primary//在同样的DataSource中，首先使用被标注的DataSource
	publicDataSourcedataSource(){
		DruidDataSourcedatasource=newDruidDataSource();

		datasource.setUrl(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(SQLExceptione){
			//logger.error("druidconfigurationinitializationfilter",e);
			logger.error("druidconfigurationinitializationfilter",e);
			e.printStackTrace();
		}
		datasource.setConnectionProperties(connectionProperties);

		returndatasource;
	}

}

4.过滤器和Servlet

还需要实现一个过滤器和Servlet，用于访问统计页面。

过滤器

importjavax.servlet.annotation.WebFilter;
importjavax.servlet.annotation.WebInitParam;
importcom.alibaba.druid.support.http.WebStatFilter;
@WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
initParams={
@WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")//忽略资源
}
)
publicclassDruidStatFilterextendsWebStatFilter{
}

Servlet

importjavax.servlet.annotation.WebInitParam;
importjavax.servlet.annotation.WebServlet;

importcom.alibaba.druid.support.http.StatViewServlet;

@WebServlet(urlPatterns="/druid/*",
initParams={
@WebInitParam(name="allow",value="127.0.0.1,192.168.163.1"),//IP白名单(没有配置或者为空，则允许所有访问)
@WebInitParam(name="deny",value="192.168.1.73"),//IP黑名单(存在共同时，deny优先于allow)
@WebInitParam(name="loginUsername",value="admin"),//用户名
@WebInitParam(name="loginPassword",value="admin"),//密码
@WebInitParam(name="resetEnable",value="false")//禁用HTML页面上的“ResetAll”功能
})
publicclassDruidStatViewServletextendsStatViewServlet{
	privatestaticfinallongserialVersionUID=-2688872071445249539L;

}

5.使用@ServletComponentScan注解，

使得刚才创建的Servlet，Filter能被访问，SpringBoot扫描并注册。

@SpringBootApplication()
@MapperScan("cn.china.mytestproject.dao")
//添加servlet组件扫描，使得Spring能够扫描到我们编写的servlet和filter
@ServletComponentScan
publicclassMytestprojectApplication{
publicstaticvoidmain(String[]args){
SpringApplication.run(MytestprojectApplication.class,args);
}
}