hibernate自带的连接池一般用在开发阶段或学习阶段,但是在部署到服务器后种种问题涌现
hibernate使用连接池c3p0有2中配置方式
1. 配置在文件applicationContext.xml中
注意配置在一个文件applicationContext.xml中,需要配置数据源2次(hibernate4需要2次)
applicationContext.xml
<?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:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">
<bean id="ConfigBean"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:jdbc.properties</value>
</list>
</property>
</bean>
<!--
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"></property>
<property name="url" value="${c3p0.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${jdbc.driverClassName}" />
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="user" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="sessionFactory"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<!-- connection -->
<property name="dataSource">
<ref local="dataSource" />
</property>
<!-- hibernate自身属性 -->
<property name="hibernateProperties">
<props>
<prop key="hibernate.autoReconnect">true</prop>
<prop key="hibernate.show_sql">false</prop>
<prop key="hibernate.format_sql">false</prop>
<prop key="hibernate.connection.autocommit">true</prop>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<!-- <prop key="hibernate.current_session_context_class">org.springframework.orm.hibernate4.SpringSessionContext</prop> -->
<!-- c3p0 -->
<!-- 连接池hibernate配置 <prop key="hibernate.connection.provider_class">org.hibernate.connection.ProxoolConnectionProvider</prop> -->
<!-- 需要再次配置数据源,c3p0才起作用 -->
<prop key="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</prop>
<prop key="hibernate.connection.driver_class">${jdbc.driverClassName}</prop>
<prop key="hibernate.connection.url">${jdbc.url}</prop>
<prop key="hibernate.connection.username">${jdbc.username}</prop>
<prop key="hibernate.connection.password">${jdbc.password}</prop>
<!-- 连接池中JDBC连接的最小数量。Hibernate默认为1 -->
<prop key="hibernate.c3p0.min_size">3</prop>
<!-- 连接池中JDBC连接的最大数量。Hibernate默认为100 -->
<prop key="hibernate.c3p0.max_size">56</prop>
<!-- 何时从连接池中移除一个空闲的连接(以秒为单位)时。Hibernate默认为0,永不过期 -->
<prop key="hibernate.c3p0.timeout">321</prop>
<!-- 被缓存的预编译语句数量。用来提高性能。Hibernate默认为0,缓存不可用 -->
<prop key="c3p0.max_statements">255</prop>
<!-- 一个连接被自动验证前的闲置时间(以秒为单位)。Hibernate默认为0 -->
<prop key="hibernate.c3p0.idle_test_period">300</prop>
<!-- 当连接池里面的连接用完的时候,C3P0一下获取的新的连接数 -->
<prop key="hibernate.c3p0.acquire_increment">2</prop>
<!-- 每次都验证连接是否可用 -->
<prop key="hibernate.c3p0.validate">true</prop>
</props>
</property>
<!-- 映射文件 -->
<property name="mappingDirectoryLocations">
<list>
<value>classpath:/com/hbm</value>
</list>
</property>
</bean>
<!-- 事务管理配置 -->
<bean id="transactionManager"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory"></property>
</bean>
<!-- 配置事务传播特性 -->
<tx:advice id="TransAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="save*" propagation="REQUIRED" />
<tx:method name="add*" propagation="REQUIRED" />
<tx:method name="create*" propagation="REQUIRED" />
<tx:method name="insert*" propagation="REQUIRED" />
<tx:method name="update*" propagation="REQUIRED" />
<tx:method name="merge*" propagation="REQUIRED" />
<tx:method name="del*" propagation="REQUIRED" />
<tx:method name="remove*" propagation="REQUIRED" />
<tx:method name="put*" propagation="REQUIRED" />
<tx:method name="use*" propagation="REQUIRED" />
<!--hibernate4必须配置为开启事务 否则 getCurrentSession()获取不到 -->
<tx:method name="get*" propagation="REQUIRED" read-only="true" />
<tx:method name="count*" propagation="REQUIRED" read-only="true" />
<tx:method name="find*" propagation="REQUIRED" read-only="true" />
<tx:method name="list*" propagation="REQUIRED" read-only="true" />
<tx:method name="*" propagation="REQUIRED" />
<tx:method name="*" timeout="30" />
</tx:attributes>
</tx:advice>
<!-- 配置参与事务的类 -->
<aop:config>
<aop:pointcut id="allTransServiceMethod" expression="execution(* com.dao.*.*(..))" />
<aop:advisor pointcut-ref="allTransServiceMethod"
advice-ref="TransAdvice" />
</aop:config>
<!--=============================导入DAO bean配置文件==================================== -->
<import resource="classpath:spring/*-spring.xml" />
</beans>
jdbc.properties
jdbc.name=mysqlJdbc
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc\:mysql\://xxx.xxx.xxx.xxx\:3306/dbname?useUnicode\=true&characterEncoding\=UTF8
jdbc.username=root
jdbc.password=root
2. 配置文件applicationContext.xml和hibernate.cfg.xml
applicationContext.xml
<?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:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">
<bean id="ConfigBean"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:jdbc.properties</value>
</list>
</property>
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="configLocation">
<value>classpath:hibernate.cfg.xml</value>
</property>
<!-- 映射文件 -->
<property name="mappingDirectoryLocations">
<list>
<value>classpath:/com/hbm</value>
</list>
</property>
</bean>
<!--?事务管理配置? -->
<bean id="transactionManager"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory"></property>
</bean>
<!-- 配置事务传播特性 -->
<tx:advice id="TransAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="save*" propagation="REQUIRED" />
<tx:method name="add*" propagation="REQUIRED" />
<tx:method name="create*" propagation="REQUIRED" />
<tx:method name="insert*" propagation="REQUIRED" />
<tx:method name="update*" propagation="REQUIRED" />
<tx:method name="merge*" propagation="REQUIRED" />
<tx:method name="del*" propagation="REQUIRED" />
<tx:method name="remove*" propagation="REQUIRED" />
<tx:method name="put*" propagation="REQUIRED" />
<tx:method name="use*" propagation="REQUIRED" />
<!--hibernate4必须配置为开启事务 否则 getCurrentSession()获取不到 -->
<tx:method name="get*" propagation="REQUIRED" read-only="true" />
<tx:method name="count*" propagation="REQUIRED" read-only="true" />
<tx:method name="find*" propagation="REQUIRED" read-only="true" />
<tx:method name="list*" propagation="REQUIRED" read-only="true" />
<tx:method name="*" propagation="REQUIRED" />
<tx:method name="*" timeout="30" />
</tx:attributes>
</tx:advice>
<!-- 配置参与事务的类 -->
<aop:config>
<aop:pointcut id="allTransServiceMethod" expression="execution(* com.dao.*.*(..))" />
<aop:advisor pointcut-ref="allTransServiceMethod"
advice-ref="TransAdvice" />
</aop:config>
<!--=============================导入DAO bean配置文件==================================== -->
<import resource="classpath:spring/*-spring.xml" />
</beans>
hibernate.cfg.xml
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<!-- Generated by MyEclipse Hibernate Tools. -->
<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">
org.hibernate.dialect.MySQLDialect
</property>
<!-- dialect for MySQL -->
<property name="hibernate.show_sql">false</property>
<property name="hibernate.format_sql">false</property>
<property name="hibernate.connection.autocommit">true</property>
<property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
<!-- local connection properties -->
<property name="connection.url">
jdbc:mysql://xxx.xxx.xxx.xxx:3306/xxx?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8
</property>
<property name="hibernate.connection.driver_class">
org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider
</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">root</property>
<!-- 最小连接数 -->
<property name="hibernate.c3p0.min_size">5</property>
<!-- 最大连接数 -->
<property name="hibernate.c3p0.max_size">20</property>
<!-- 获得连接的超时时间,如果超过这个时间,会抛出异常,单位秒 -->
<property name="hibernate.c3p0.timeout">120</property>
<!-- 最大的PreparedStatement的数量 -->
<property name="hibernate.c3p0.max_statements">100</property>
<!-- 每隔120秒检查连接池里的空闲连接 ,单位是秒 -->
<property name="hibernate.c3p0.idle_test_period">120</property>
<!-- 当连接池里面的连接用完的时候,C3P0一下获取的新的连接数 -->
<property name="hibernate.c3p0.acquire_increment">2</property>
<!-- 每次都验证连接是否可用 -->
<property name="hibernate.c3p0.validate">true</property>
<!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的 时候都将校验其有效性。
建议使用idleConnectionTestPeriod或automaticTestTable
等方法来提升连接测试的性能。Default: false -->
<property name="idleConnectionTestPeriod">600</property>
<property name="automaticTestTable" >c3p0Test</property>
<property name="preferredTestQuery">select * from c3p0Test</property>
</session-factory>
</hibernate-configuration>
3. 错误解析
错误代码
[INFO]2014-07-10 18:26:26|#HHH000130: Instantiating explicit connection provider: org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider|#
[INFO]2014-07-10 18:26:26|#HHH010002: C3P0 using driver: null at URL: null|#
[INFO]2014-07-10 18:26:26|#HHH000046: Connection properties: {}|#
[INFO]2014-07-10 18:26:26|#HHH000006: Autocommit mode: false|#
[WARN]2014-07-10 18:26:26|#HHH000148: No JDBC Driver class was specified by property hibernate.connection.driver_class|#
[INFO]2014-07-10 18:26:26|#Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@7c5fa7eb [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@7fbe289f [ acquireIncrement -> 2, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge0yx938tqlu478usms|1ad093c, idleConnectionTestPeriod -> 300, initialPoolSize -> 3, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 60, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 500, maxStatements -> 50, maxStatementsPerConnection -> 0, minPoolSize -> 1, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@d1cd0c92 [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 1hge0yx938tqlu478usms|11a9310, jdbcUrl -> null, properties -> {} ], preferredTestQuery -> null, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> 1hge0yx938tqlu478usms|1cc5d23, numHelperThreads -> 3 ]|#
[WARN]2014-07-10 18:26:55|#com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@133f6dd -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: |#
java.lang.NullPointerException
at sun.jdbc.odbc.JdbcOdbcDriver.getProtocol(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.knownURL(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.acceptsURL(Unknown Source)
at java.sql.DriverManager.getDriver(Unknown Source)
at com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:224)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:120)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:143)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:132)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
原因和解决方法
主要看下面这几行
[INFO]2014-07-10 18:26:26|#HHH010002: C3P0 using driver: null at URL: null|#
[INFO]2014-07-10 18:26:26|#HHH000046: Connection properties: {}|#
[INFO]2014-07-10 18:26:26|#HHH000006: Autocommit mode: false|#
[WARN]2014-07-10 18:26:26|#HHH000148: No JDBC Driver class was specified by property hibernate.connection.driver_class|#
C3P0提示driver 和URL是空,标识C3P0连接池没有开启成功,但是在网上查了大量的资料配置的dataSource都一样,还是找不到原因,最后使用方法“2 配置文件applicationContext.xml和hibernate.cfg.xml”就可以成功开启C3P0,经过对比和尝试,最终找到了原因,把hibernate配置信息配置在applicationContext.xml中,数据源信息需要配置2次,方法1就是成功开启C3P0后的配置,即如下2段
段1
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${jdbc.driverClassName}" />
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="user" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
段2
<!-- 需要再次配置数据源,c3p0才起作用 -->
<prop key="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</prop>
<prop key="hibernate.connection.driver_class">${jdbc.driverClassName}</prop>
<prop key="hibernate.connection.url">${jdbc.url}</prop>
<prop key="hibernate.connection.username">${jdbc.username}</prop>
<prop key="hibernate.connection.password">${jdbc.password}</prop>
配置成功后C3P0提示如下:
[INFO]2014-07-10 18:35:28|#HHH000130: Instantiating explicit connection provider: org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider|#
[INFO]2014-07-10 18:35:28|#HHH010002: C3P0 using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://114.215.181.133:3306/ipark_new?useUnicode=true&characterEncoding=UTF8|#
[INFO]2014-07-10 18:35:28|#HHH000046: Connection properties: {user=root, password=****, autocommit=true}|#
[INFO]2014-07-10 18:35:28|#HHH000006: Autocommit mode: true|#
[INFO]2014-07-10 18:35:28|#Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@9c865936 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@9bd6c87b [ acquireIncrement -> 2, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge0yx938u27td1x3abjl|165ab39, idleConnectionTestPeriod -> 300, initialPoolSize -> 3, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 321, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 56, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@f1eb5c2f [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 1hge0yx938u27td1x3abjl|1e937f, jdbcUrl -> jdbc:mysql://114.215.181.133:3306/ipark_new?useUnicode=true&characterEncoding=UTF8, properties -> {user=******, password=******, autocommit=true} ], preferredTestQuery -> null, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> 1hge0yx938u27td1x3abjl|ceb6dd, numHelperThreads -> 3 ]|#
[INFO]2014-07-10 18:35:35|#HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect|#
[INFO]2014-07-10 18:35:35|#HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException|#
[INFO]2014-07-10 18:35:35|#HHH000399: Using default transaction strategy (direct JDBC transactions)|#
[INFO]2014-07-10 18:35:35|#HHH000397: Using ASTQueryTranslatorFactory|#
[INFO]2014-07-10 18:35:35|#Using DataSource [com.mchange.v2.c3p0.PoolBackedDataSource@9c865936 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@9bd6c87b [ acquireIncrement -> 2, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge0yx938u27td1x3abjl|165ab39, idleConnectionTestPeriod -> 300, initialPoolSize -> 3, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 321, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 56, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@f1eb5c2f [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 1hge0yx938u27td1x3abjl|1e937f, jdbcUrl -> jdbc:mysql://114.215.181.133:3306/ipark_new?useUnicode=true&characterEncoding=UTF8, properties -> {user=******, password=******, autocommit=true} ], preferredTestQuery -> null, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> 1hge0yx938u27td1x3abjl|ceb6dd, numHelperThreads -> 3 ]] of Hibernate SessionFactory for HibernateTransactionManager|#
[INFO]2014-07-10 18:35:36|#Root WebApplicationContext: initialization completed in 9700 ms|#
转载请注明转自: BlackShadowWalker