数据库连接池Druid的配置使用，开启sql监控

前言

博主这里项目采用maven构建，使用spring容器， 关于Druid数据库连接池是什么等问题可移步我的这篇博文，数据库连接池Druid一览 

下面分步骤来看看，在项目中如何使用Druid

1.首先配置pom.xml,添加Druid依赖

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.18</version>
</dependency>

2.数据源的配置，如下

我的连接信息：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/webside?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&autoReconnect=true
jdbc.username=root
jdbc.password=sasa
#jdbc.password=oNF4ELsLxPYSYcuq4e6dihnGZhInVdUcHsSjA9RFoT
druid.initialSize=10
druid.minIdle=10
druid.maxActive=50
druid.maxWait=60000
druid.timeBetweenEvictionRunsMillis=60000
druid.minEvictableIdleTimeMillis=300000
druid.validationQuery=SELECT 'x'
druid.testWhileIdle=true
druid.testOnBorrow=false
druid.testOnReturn=false
druid.poolPreparedStatements=true
druid.maxPoolPreparedStatementPerConnectionSize=20
druid.maxOpenPreparedStatements=100
druid.timeBetweenLogStatsMillis=300000
druid.filters=config,wall,log4j
#加上如下配置可以加解密 数据库连接密码
#druid.connectionProperties==true;.key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAKZN4rjl
druid.useGlobalDataSourceStat=true
druid.removeAbandoned=true
druid.removeAbandonedTimeout=1800
druid.logAbandoned=false
我的datasource bean配置

	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
		init-method="init" destroy-method="close">
		<!-- 基本属性 url、user、password -->
		<property name="driverClassName" value="${jdbc.driver}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<!-- 配置初始化大小、最小、最大 -->
		<property name="initialSize" value="${druid.initialSize}" />
		<property name="minIdle" value="${druid.minIdle}" />
		<property name="maxActive" value="${druid.maxActive}" />
		<!-- 配置获取连接等待超时的时间 -->
		<property name="maxWait" value="${druid.maxWait}" />
		<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
		<property name="timeBetweenEvictionRunsMillis" value="${druid.timeBetweenEvictionRunsMillis}" />
		<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="${druid.minEvictableIdleTimeMillis}" />

		<property name="validationQuery" value="${druid.validationQuery}" />
		<property name="testWhileIdle" value="${druid.testWhileIdle}" />
		<property name="testOnBorrow" value="${druid.testOnBorrow}" />
		<property name="testOnReturn" value="${druid.testOnReturn}" />
		<!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->
		<property name="poolPreparedStatements" value="${druid.poolPreparedStatements}" />
		<property name="maxPoolPreparedStatementPerConnectionSize"
			value="${druid.maxPoolPreparedStatementPerConnectionSize}" />

		<property name="maxOpenPreparedStatements" value="${druid.maxOpenPreparedStatements}" />

		<!-- 打开removeAbandoned功能 -->
		<property name="removeAbandoned" value="${druid.removeAbandoned}" />
		<!-- 1800秒，也就是30分钟,超过30分钟,连接会自动关闭 -->
		<property name="removeAbandonedTimeout" value="${druid.removeAbandonedTimeout}" />
		<!-- 关闭abanded连接时输出错误日志 -->
		<property name="logAbandoned" value="${druid.logAbandoned}" />

		<!-- 配置监控统计拦截的filters -->
		<property name="filters" value="${druid.filters}" />
		<property name="proxyFilters">
	        <list>
	            <ref bean="stat-filter" />
	        </list>
	    </property>

		<!--使用此配置数据库连接密码将采用 密文模式<property name="connectionProperties" value="${druid.connectionProperties}" />-->
		<property name="useGlobalDataSourceStat" value="${druid.useGlobalDataSourceStat}" />
	</bean>

3.配置web.xml开启sql监控功能

     <filter>
		<filter-name>DruidWebStatFilter</filter-name>
		<filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class>
		<init-param>
			<param-name>exclusions</param-name>
			<param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</param-value>
		</init-param>
		
		<init-param>
	        <param-name>principalCookieName</param-name>
	        <param-value>webside.cookieid</param-value>
	    </init-param>
	    
	    <!-- 设置session的名称，因为和shiro的logout有冲突，暂时注掉
	    <init-param>
	        <param-name>principalSessionName</param-name>
	        <param-value>accountName</param-value>
	    </init-param>
	     -->
		<init-param>
			<param-name>sessionStatMaxCount</param-name>
			<param-value>1000</param-value>
		</init-param>
		<init-param>
			<param-name>sessionStatEnable</param-name>
			<param-value>true</param-value>
		</init-param>
		<init-param>
			<param-name>profileEnable</param-name>
			<param-value>true</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>DruidWebStatFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>
	<servlet>
		<servlet-name>DruidStatView</servlet-name>
		<servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
		<init-param>
			<param-name>resetEnable</param-name>
			<param-value>true</param-value>
		</init-param>
		<init-param>
			<param-name>loginUsername</param-name><!--druid监控后台用户名-->
			<param-value>druid</param-value>
		</init-param>
		<init-param>
			<param-name>loginPassword</param-name><!--druid监控后台密码-->
			<param-value>druid</param-value>
		</init-param>
		<init-param>
			<param-name>allow</param-name><!--许可地址-->
			<param-value>127.0.0.1/24,127.0.0.1</param-value>
		</init-param>
		<init-param>
			<param-name>deny</param-name><!--屏蔽地址-->
			<param-value>172.101.106.13</param-value>
		</init-param>
	</servlet>
	<servlet-mapping>
		<servlet-name>DruidStatView</servlet-name>
		<url-pattern>/druid/*</url-pattern>
	</servlet-mapping>

sql监控界面如下：