转载连接池的配置问题spring+hibernate+c3p0

关键字: spring c3p0

项目遇到了一个瓶胫，当用户访问并发数不大的情况下，系统运行正常，但是当并发数达到一定量的时候，就会出现“无法获取连接，网络适配器断开”

 

首先确定网络是否连接正常。WEB服务器是不是开了防火Q，是否中了ARP之类的病毒。发现都是正常的。

网络和服务器没有问题后，就检查ORACLE数据库，发并连接正常，但是用户会话会有些异常，系统启动时会话量增大，运行中居然变小。

那确定应该是程序的问题。首先将问题锁定在数据库连接管理上。

 

项目中采用的是C3P0连接池来管理数据库连接，系统架构是采用Hibernate+Spring+Strtus实现，由Spring整合C3P0连接池来管理连接。下面是配置文件：

 

Java代码
  1. <?xml version="1.0" encoding="UTF-8"?>  
  2. <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">  
  3. <beans default-lazy-init="true">  
  4.     <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">  
  5.         <property name="location">  
  6.             <value>classpath:/database.properties</value>  
  7.         </property>  
  8.     </bean>   
  9.     <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
  10.         <property name="driverClassName">  
  11.             <value>${jdbc.driverClassName}</value>  
  12.         </property>  
  13.         <property name="url">  
  14.             <value>${jdbc.url}</value>  
  15.         </property>  
  16.         <property name="username">  
  17.             <value>${jdbc.username}</value>  
  18.         </property>  
  19.         <property name="password">  
  20.             <value>${jdbc.password}</value>  
  21.         </property>  
  22.     </bean>  
  23.     <!-- Hibernate SessionFactory -->  
  24.     <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">  
  25.         <property name="dataSource">  
  26.             <ref local="dataSource" />  
  27.         </property>  
  28.         <property name="hibernateProperties">  
  29.             <props>  
  30.                 <prop key="hibernate.dialect"> ${hibernate.dialect} </prop>  
  31.                 <prop key="hibernate.show_sql"> ${hibernate.show_sql} </prop>  
  32.                 <prop key="hibernate.c3p0.minPoolSize"> ${hibernate.c3p0.minPoolSize}</prop>  
  33.                 <prop key="hibernate.c3p0.maxPoolSize"> ${hibernate.c3p0.maxPoolSize} </prop>  
  34.                 <prop key="hibernate.c3p0.timeout"> ${hibernate.c3p0.timeout} </prop>  
  35.                 <prop key="hibernate.c3p0.max_statement"> ${hibernate.c3p0.max_statement} </prop>  
  36.                 <prop key="hibernate.cache.provider_class">cn.com.jody.common.framework.hibernate.MemcachedCacheProvider</prop>  
  37.                 <prop key="memcached.servers">192.168.1.0:8888</prop>  
  38.                 <prop key="memcached.server.weights">1</prop>  
  39.                 <prop key="memcached.failover">true</prop>  
  40.                 <prop key="memcached.initConn">10</prop>  
  41.                 <prop key="memcached.minConn">10</prop>  
  42.                 <prop key="memcached.maxConn">50</prop>  
  43.                 <prop key="memcached.maintSleep">30</prop>  
  44.                 <prop key="memcached.nagle">false</prop>  
  45.                 <prop key="memcached.socketTO">3000</prop>  
  46.                 <prop key="memcached.aliveCheck">true</prop>  
  47.                 <prop key="memcached.*">20m</prop>  
  48.                 <prop key="hibernate.cache.use_query_cache">false</prop>  
  49.                 <prop key="hibernate.cache.use_second_level_cache">true</prop>  
  50.                 <prop key="hibernate.jdbc.batch_size">100</prop>  
  51.                 <prop key="hibernate.jdbc.fetch_size">50</prop>  
  52.                 <prop key="hibernate.default_batch_fetch_size">50</prop>  
  53.                 <prop key="hibernate.cache.use_minimal_puts">true</prop>  
  54.                 <prop key="hibernate.connection.release_mode">after_transaction</prop>  
  55.             </props>  
  56.         </property>  
  57.         <property name="mappingDirectoryLocations">  
  58.             <list>  
  59.                 <value>classpath:/ormappings</value>  
  60.             </list>  
  61.         </property>  
  62.     </bean>  
  63.   
  64.     <!-- Transaction manager for a single Hibernate SessionFactory (alternative to JTA) -->  
  65.     <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">  
  66.         <property name="sessionFactory">  
  67.             <ref local="sessionFactory" />  
  68.         </property>  
  69.     </bean>  
  70. </beans>  
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans default-lazy-init="true">
    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location">
            <value>classpath:/database.properties</value>
        </property>
    </bean> 
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName">
            <value>${jdbc.driverClassName}</value>
        </property>
        <property name="url">
            <value>${jdbc.url}</value>
        </property>
        <property name="username">
            <value>${jdbc.username}</value>
        </property>
        <property name="password">
            <value>${jdbc.password}</value>
        </property>
    </bean>
	<!-- Hibernate SessionFactory -->
    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource">
            <ref local="dataSource" />
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect"> ${hibernate.dialect} </prop>
                <prop key="hibernate.show_sql"> ${hibernate.show_sql} </prop>
                <prop key="hibernate.c3p0.minPoolSize"> ${hibernate.c3p0.minPoolSize}</prop>
                <prop key="hibernate.c3p0.maxPoolSize"> ${hibernate.c3p0.maxPoolSize} </prop>
                <prop key="hibernate.c3p0.timeout"> ${hibernate.c3p0.timeout} </prop>
                <prop key="hibernate.c3p0.max_statement"> ${hibernate.c3p0.max_statement} </prop>
                <prop key="hibernate.cache.provider_class">cn.com.jody.common.framework.hibernate.MemcachedCacheProvider</prop>
                <prop key="memcached.servers">192.168.1.0:8888</prop>
                <prop key="memcached.server.weights">1</prop>
                <prop key="memcached.failover">true</prop>
                <prop key="memcached.initConn">10</prop>
                <prop key="memcached.minConn">10</prop>
                <prop key="memcached.maxConn">50</prop>
                <prop key="memcached.maintSleep">30</prop>
                <prop key="memcached.nagle">false</prop>
                <prop key="memcached.socketTO">3000</prop>
                <prop key="memcached.aliveCheck">true</prop>
                <prop key="memcached.*">20m</prop>
                <prop key="hibernate.cache.use_query_cache">false</prop>
                <prop key="hibernate.cache.use_second_level_cache">true</prop>
                <prop key="hibernate.jdbc.batch_size">100</prop>
                <prop key="hibernate.jdbc.fetch_size">50</prop>
                <prop key="hibernate.default_batch_fetch_size">50</prop>
                <prop key="hibernate.cache.use_minimal_puts">true</prop>
                <prop key="hibernate.connection.release_mode">after_transaction</prop>
            </props>
        </property>
        <property name="mappingDirectoryLocations">
            <list>
                <value>classpath:/ormappings</value>
            </list>
        </property>
    </bean>

	<!-- Transaction manager for a single Hibernate SessionFactory (alternative to JTA) -->
    <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory">
            <ref local="sessionFactory" />
        </property>
    </bean>
</beans>

 

 细细检查发现，原来是上面的配置存在问题。

 

数据源（dataSource）是通过Spring的“org.springframework.jdbc.datasource.DriverManagerDataSource”类通过连接JDBC驱动获取的数据库源。

 

Hibernate的（sessionFactory）是通过Spring的“org.springframework.orm.hibernate3.LocalSessionFactoryBean”来管理的。这个配置中虽然配置了C3P0连接池：

Xml代码
  1. <prop key="hibernate.c3p0.minPoolSize"> ${hibernate.c3p0.minPoolSize}</prop>  
  2.                 <prop key="hibernate.c3p0.maxPoolSize"> ${hibernate.c3p0.maxPoolSize} </prop>  
  3.                 <prop key="hibernate.c3p0.timeout"> ${hibernate.c3p0.timeout} </prop>  
  4.                 <prop key="hibernate.c3p0.max_statement"> ${hibernate.c3p0.max_statement} </prop>  
<prop key="hibernate.c3p0.minPoolSize"> ${hibernate.c3p0.minPoolSize}</prop>
                <prop key="hibernate.c3p0.maxPoolSize"> ${hibernate.c3p0.maxPoolSize} </prop>
                <prop key="hibernate.c3p0.timeout"> ${hibernate.c3p0.timeout} </prop>
                <prop key="hibernate.c3p0.max_statement"> ${hibernate.c3p0.max_statement} </prop>

 

但是却没有作用，对Spring 管理的数据源(dataSource)没有改变。当Hibernate中调用一个sessionFactory时，得到的数据源（dataSource） 只是在Spring中通过JDBC驱动获取和数据源。没有通过数据库连接池来管理数据库连接。所以当数据库连接达到一定的数量时，就出现无法获取数据库连 接的现像。

 

修改Application.xml配置文件：

 

Xml代码
  1. <?xml version="1.0" encoding="UTF-8"?>  
  2. <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">  
  3. <beans default-lazy-init="true">  
  4.     <!-- ========================= RESOURCE DEFINITIONS ========================= -->  
  5.     <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">  
  6.         <property name="location" value="classpath:/database.properties"/>  
  7.   
  8.   
  9.     </bean>   
  10.     <!-- Local DataSource that works in any environment -->  
  11.     <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">  
  12.         <property name="driverClass" value="${jdbc.driverClassName}"/>  
  13.         <property name="jdbcUrl" value="${jdbc.url}"/>  
  14.         <property name="maxPoolSize" value="${hibernate.c3p0.maxPoolSize}"/>  
  15.         <property name="minPoolSize" value="${hibernate.c3p0.minPoolSize}"/>  
  16.         <property name="idleConnectionTestPeriod" value="30"/>  
  17.         <property name="acquireIncrement" value="5"/>  
  18.         <property name="properties">  
  19.             <props>  
  20.                 <prop key="user">${jdbc.username}</prop>  
  21.                 <prop key="password">${jdbc.password}</prop>  
  22.             </props>  
  23.         </property>  
  24.     </bean>  
  25.     <!-- Hibernate SessionFactory -->  
  26.     <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">  
  27.         <property name="dataSource">  
  28.             <ref local="dataSource" />  
  29.         </property>  
  30.         <property name="hibernateProperties">  
  31.             <props>  
  32.                 <prop key="hibernate.dialect"> ${hibernate.dialect} </prop>  
  33.                 <prop key="hibernate.show_sql"> ${hibernate.show_sql} </prop>  
  34.   
  35.   
  36.   
  37.   
  38.                 <prop key="hibernate.cache.provider_class">cn.com.jody.common.framework.hibernate.MemcachedCacheProvider</prop>  
  39.                 <prop key="memcached.servers">192.168.1.0:8888</prop>  
  40.                 <prop key="memcached.server.weights">1</prop>  
  41.                 <prop key="memcached.failover">true</prop>  
  42.                 <prop key="memcached.initConn">10</prop>  
  43.                 <prop key="memcached.minConn">10</prop>  
  44.                 <prop key="memcached.maxConn">50</prop>  
  45.                 <prop key="memcached.maintSleep">30</prop>  
  46.                 <prop key="memcached.nagle">false</prop>  
  47.                 <prop key="memcached.socketTO">3000</prop>  
  48.                 <prop key="memcached.aliveCheck">true</prop>  
  49.                 <prop key="memcached.*">20m</prop>  
  50.                 <prop key="hibernate.cache.use_query_cache">false</prop>  
  51.                 <prop key="hibernate.cache.use_second_level_cache">true</prop>  
  52.                 <prop key="hibernate.jdbc.batch_size">100</prop>  
  53.                 <prop key="hibernate.jdbc.fetch_size">50</prop>  
  54.                 <prop key="hibernate.default_batch_fetch_size">50</prop>  
  55.                 <prop key="hibernate.cache.use_minimal_puts">true</prop>  
  56.                 <prop key="hibernate.connection.release_mode">after_transaction</prop>  
  57.             </props>  
  58.         </property>  
  59.                 <property name="mappingDirectoryLocations">  
  60.             <list>  
  61.                 <value>classpath:/orm</value>  
  62.             </list>  
  63.         </property>  
  64.     </bean>  
  65.   
  66.     <!-- Transaction manager for a single Hibernate SessionFactory (alternative to JTA) -->  
  67.     <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">  
  68.         <property name="sessionFactory">  
  69.             <ref local="sessionFactory" />  
  70.         </property>  
  71.     </bean>  
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans default-lazy-init="true">
	<!-- ========================= RESOURCE DEFINITIONS ========================= -->
    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location" value="classpath:/database.properties"/>


    </bean> 
	<!-- Local DataSource that works in any environment -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClassName}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="maxPoolSize" value="${hibernate.c3p0.maxPoolSize}"/>
        <property name="minPoolSize" value="${hibernate.c3p0.minPoolSize}"/>
        <property name="idleConnectionTestPeriod" value="30"/>
        <property name="acquireIncrement" value="5"/>
        <property name="properties">
            <props>
                <prop key="user">${jdbc.username}</prop>
                <prop key="password">${jdbc.password}</prop>
            </props>
        </property>
    </bean>
	<!-- Hibernate SessionFactory -->
    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource">
            <ref local="dataSource" />
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect"> ${hibernate.dialect} </prop>
                <prop key="hibernate.show_sql"> ${hibernate.show_sql} </prop>




                <prop key="hibernate.cache.provider_class">cn.com.jody.common.framework.hibernate.MemcachedCacheProvider</prop>
                <prop key="memcached.servers">192.168.1.0:8888</prop>
                <prop key="memcached.server.weights">1</prop>
                <prop key="memcached.failover">true</prop>
                <prop key="memcached.initConn">10</prop>
                <prop key="memcached.minConn">10</prop>
                <prop key="memcached.maxConn">50</prop>
                <prop key="memcached.maintSleep">30</prop>
                <prop key="memcached.nagle">false</prop>
                <prop key="memcached.socketTO">3000</prop>
                <prop key="memcached.aliveCheck">true</prop>
                <prop key="memcached.*">20m</prop>
                <prop key="hibernate.cache.use_query_cache">false</prop>
                <prop key="hibernate.cache.use_second_level_cache">true</prop>
                <prop key="hibernate.jdbc.batch_size">100</prop>
                <prop key="hibernate.jdbc.fetch_size">50</prop>
                <prop key="hibernate.default_batch_fetch_size">50</prop>
                <prop key="hibernate.cache.use_minimal_puts">true</prop>
                <prop key="hibernate.connection.release_mode">after_transaction</prop>
            </props>
        </property>
		        <property name="mappingDirectoryLocations">
            <list>
                <value>classpath:/orm</value>
            </list>
        </property>
    </bean>

	<!-- Transaction manager for a single Hibernate SessionFactory (alternative to JTA) -->
    <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory">
            <ref local="sessionFactory" />
        </property>
    </bean>
Xml代码
  1. </beans>  
</beans>

 这个配置中，

数据源（dataSource）是通过C3P0的“com.mchange.v2.c3p0.ComboPooledDataSource”类通过连接JDBC驱动获取的数据库源，并且有连接池的管理。

然后，

Hibernate的（sessionFactory）是通过Spring的“org.springframework.orm.hibernate3.LocalSessionFactoryBean”来管理的。这个配置中虽然配置了C3P0连接池：

这样，Spring管理的是C3P0连接池中的连接事务，Hibernate调用的是C3P0连接池中的连接，因为有了连接池的管理，所以当用户并发量大时，在处理上也可以胜任。当然，C3P0也是有一个并发量限制，单机处理并发量问题，还是不现实，集群才是最好的办法。

posted @ 2011-01-13 14:52  xupei  阅读(2096)  评论(0)  编辑  收藏  举报