spring整合常用连接池

程序员文章站 2022-05-24 12:28:49

...

一整合阿里巴巴Druid

测试环境: mysql5.5 druid-1.0.9.jar

编写druid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/studentdb
theUser=root
password=test123
#配置监控统计拦截的filters，去掉后监控界面sql无法统计
filters=stat
#配置初始化大小 
initialSize=6
#配置初始化最大连接数 
maxActive=20
#配置初始化最小连接数 
minIdle=3
#配置获取连接等待超时的时间,1分钟 
maxWait=60000
#检测连接是否有效的SQL 
validationQuery=SELECT 'x'
#空闲的连接是否进行有效性检查
testWhileIdle=true
#申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能 
testOnBorrow=false
#归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能 
testOnReturn=false
#启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true 
maxPoolPreparedStatementPerConnectionSize=20
#对于长时间不使用的连接强制关闭 
removeAbandoned=true
#超过60秒的空闲连接就可以被关闭了,单位是秒 
removeAbandonedTimeout=60
#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 
timeBetweenEvictionRunsMillis=10000
#配置一个连接在池中最小生存的时间，单位是毫秒 
minEvictableIdleTimeMillis=30000

在spring中集成

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	                    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
	                    http://www.springframework.org/schema/aop
	                    http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
	                    http://www.springframework.org/schema/tx
	                    http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
	<!-- 导入其它sping配置文件 -->
	<import resource="classpath:configs/spring/context-*.xml"/>
	<!-- durid连接池配置start -->
	<bean id="duridConfig" class="org.springframework.beans.factory.config.PreferencesPlaceholderConfigurer">
		<property name="locations">
		    <list>
				<value>classpath:configs/druid.properties</value>
			</list>
		</property>
    </bean>
   <!--druid数据源-->
    <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource" 
		init-method="init" destroy-method="close">
		<property name="driverClassName" value="${driverClassName}" />
		<property name="url" value="${url}" />
		<property name="username" value="${theUser}" />
		<property name="password" value="${password}" />
		<property name="filters" value="${filters}" />
		<property name="initialSize" value="${initialSize}" />
		<property name="maxActive" value="${maxActive}" />
		<property name="minIdle" value="${minIdle}" />
		<property name="maxWait" value="${maxWait}" />
		<property name="validationQuery" value="${validationQuery}" />
		<property name="testWhileIdle" value="${testWhileIdle}" />
		<property name="testOnBorrow" value="${testOnBorrow}" />
		<property name="testOnReturn" value="${testOnReturn}" />
		<property name="maxPoolPreparedStatementPerConnectionSize" value="${maxPoolPreparedStatementPerConnectionSize}" />
		<property name="removeAbandoned" value="${removeAbandoned}" />
		<property name="removeAbandonedTimeout" value="${removeAbandonedTimeout}" />
		<property name="timeBetweenEvictionRunsMillis" value="${timeBetweenEvictionRunsMillis}" />
		<property name="minEvictableIdleTimeMillis" value="${minEvictableIdleTimeMillis}" />
	</bean>
    <!-- durid连接池配置end -->
    
	<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource" ref="druidDataSource"></property>
		<property name="configLocation">
			<value>classpath:configs/hibernate.cfg.xml</value>
		</property>
		<property name="mappingLocations">
			<list>
				<value>classpath:configs/mappers/*.hbm.xml</value>
			</list>
		</property>
	</bean>
	
</beans>

前台监控配置, 在web.xml中

<!-- druid pool -->
  <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>
  </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>
  </servlet>
  <servlet-mapping>
        <servlet-name>DruidStatView</servlet-name>
        <url-pattern>/druid/*</url-pattern>
  </servlet-mapping>
  <!--http://localhost:8080/SSHFinal/druid/index.html  -->

二整合C3P0

测试环境: mysql5.5 c3p0-0.9.5.2.jar mchange-commons-java-0.2.11.jar

编写c3p0.properties

jdbcUrl=jdbc:mysql://localhost:3306/schooldb?useUnicode=true&characterEncoding=utf-8
driverClass=com.mysql.jdbc.Driver
theuser=root
password=test123
#初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3
initialPoolSize=6
#连接池中保留的最小连接数。Default: 3
minPoolSize=3
#连接池中保留的最大连接数。Default: 15
maxPoolSize=5
#当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 
acquireIncrement=3
#控制数据源内加载的PreparedStatements数量。如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0
maxStatements=8
#maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0
maxStatementsPerConnection=5
#最大空闲时间,1800秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0
maxIdleTime=1800

在spring里面整合

<?xml version="1.0" encoding="UTF-8"?>
<beans
	xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	 http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
	 http://www.springframework.org/schema/aop
	 http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
	 http://www.springframework.org/schema/tx
	 http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
	 http://www.springframework.org/schema/context
	 http://www.springframework.org/schema/context/spring-context-3.0.xsd">
	 
   <import resource="classpath:configs/spring/applicationContext-*.xml"/>
    
   <context:property-placeholder location="classpath:configs/c3p0.properties" />
<!--	<bean id="config" class="org.springframework.beans.factory.config.PreferencesPlaceholderConfigurer">-->
<!--		<property name="location">-->
<!--			<value>classpath:configs/c3p0.properties</value>-->
<!--		</property>-->
<!--	</bean>-->
    <!-- 配置数据库连接池（c3p0） -->
    <bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!-- 基本信息 -->
        <property name="jdbcUrl" value="${jdbcUrl}"></property>
        <property name="driverClass" value="${driverClass}"></property>
        <property name="user" value="${theuser}"></property>
        <property name="password" value="${password}"></property>
        <!-- 其他配置 -->
        <!--初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
        <property name="initialPoolSize" value="${initialPoolSize}"></property>
        <!--连接池中保留的最小连接数。Default: 3 -->
        <property name="minPoolSize" value="${minPoolSize}"></property>
        <!--连接池中保留的最大连接数。Default: 15 -->
        <property name="maxPoolSize" value="${maxPoolSize}"></property>
        <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
        <property name="acquireIncrement" value="${acquireIncrement}"></property>
        <!-- 控制数据源内加载的PreparedStatements数量。
                            如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0 -->
        <property name="maxStatements" value="${maxStatements}"></property>
        <!-- maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 -->
        <property name="maxStatementsPerConnection" value="${maxStatementsPerConnection}"></property>
        <!--最大空闲时间,1800秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
        <property name="maxIdleTime" value="${maxIdleTime}"></property>
    </bean>

	<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource" ref="c3p0DataSource"></property>
		<property name="configLocation">
			<value>classpath:configs/hibernate.cfg.xml</value>
		</property>
		<property name="mappingLocations">
			<list>
				<value>classpath:com/obtk/entitys/*.hbm.xml</value>
			</list>
		</property>
	</bean>
	
	
</beans>

三整合proxool

环境 mysql5.5 proxool-0.9.1.jar proxool-cglib.jar

编写proxool.properties属性文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/studentdb?autoReconnect=true
theuser=root
password=test123
#最大的数据库连接数.默认是15
maximumConnectionCount=100
#最小的数据库连接数，默认是5
minimumConnectioncount=7
#最大活跃时间,如果一个线程活动时间超过这个数值，线程会被杀死 
maximumActiveTime=300000
#连接池使用状况统计。 参数“10s,1m,1d”
statistics=1m,15m,1d
#检查连接的有效性sql
houseKeepingTestSql=select CURRENT_DATE

和spring整合

<?xml version="1.0" encoding="UTF-8"?>
<beans
	xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	 http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
	 http://www.springframework.org/schema/aop
	 http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
	 http://www.springframework.org/schema/tx
	 http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
	 http://www.springframework.org/schema/context
	 http://www.springframework.org/schema/context/spring-context-3.0.xsd">
	 
   <import resource="classpath:configs/spring/applicationContext-*.xml"/>
    
   <context:property-placeholder location="classpath:configs/proxool.properties" />
   <!-- 配置数据库连接池（c3p0） -->
    <bean id="proxoolDataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
        <!-- 基本信息 -->
        <property name="driverUrl" value="${url}"></property>
        <property name="driver" value="${driverClassName}"></property>
        <property name="user" value="${theuser}"></property>
        <property name="password" value="${password}"></property>
        <!-- 最大的数据库连接数.默认是15 -->
        <property name="maximumConnectionCount" value="${maximumConnectionCount}"></property>
        <!-- 最小的数据库连接数，默认是5 -->
        <property name="minimumConnectionCount" value="${minimumConnectioncount}"></property>
        <!-- 最大活跃时间,如果一个线程活动时间超过这个数值，线程会被杀死 -->
        <property name="maximumActiveTime" value="${maximumActiveTime}"></property>
        <!-- 连接池使用状况统计。 参数“10s,1m,1d” -->
        <property name="statistics" value="${statistics}"></property>
        <!-- 检查连接的有效性 -->
        <property name="houseKeepingTestSql" value="${houseKeepingTestSql}"></property>
    </bean>

	<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource" ref="proxoolDataSource"></property>
		<property name="configLocation">
			<value>classpath:configs/hibernate.cfg.xml</value>
		</property>
		<property name="mappingLocations">
			<list>
				<value>classpath:com/obtk/entitys/*.hbm.xml</value>
			</list>
		</property>
	</bean>
</beans>

前台监控配置

<!-- proxool连接池的配置start -->
  <servlet>
  	<servlet-name>proxool</servlet-name>
  	<servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>proxool</servlet-name>
  	<url-pattern>/proxool.wps</url-pattern>
  </servlet-mapping>
   <!-- proxool连接池的配置end -->

上一篇： SpringMVC配置文件及数据库连接池配置

下一篇： Spring-Mybatis整合学习笔记1

spring整合常用连接池

Spring Security整合CAS的示例代码

浅谈Spring Boot 整合ActiveMQ的过程

Spring整合redis（jedis）实现Session共享的过程

Spring Boot2.0整合ES5实现文章内容搜索实战

Spring Boot 整合mybatis 与 swagger2

Mybatis整合Spring 由于版本引起的BUG问题

浅谈spring 常用注解

spring springMVC中常用注解解析

Spring Boot使用Druid连接池的示例代码

Spring Boot整合elasticsearch的详细步骤