java Hibernate 4.1.9 c3p0 设置，以及与数据库的连接太多

Hibernate 4.1.9 c3p0 settings, and way too many connections to database

提问by russellelbert

I'm using Hibernate 4.1.9 in a Java web application (that uses a Oracle 11g database) and seem to be getting some run-away connections even though i'm using c3p0 pooling.

我在 Java Web 应用程序（使用 Oracle 11g 数据库）中使用 Hibernate 4.1.9，尽管我使用的是 c3p0 池，但似乎连接失控。

it seems this should be managed in my config file with the appropriate properties, but I am struggling to get them set up correctly.

似乎这应该在我的配置文件中使用适当的属性进行管理，但我正在努力正确设置它们。

here is my persistence.xml file with the properties settings in it:

这是我的 persistence.xml 文件，其中包含属性设置：

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">

<persistence-unit name="RPRM_PERSISTENCE_UNIT" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence</provider>

<properties>

  <property name="hibernate.connection.username" value="username"/>
  <property name="hibernate.connection.password" value="********"/>        

  <property name="hibernate.connection.url" value="jdbc:oracle:thin:@xxxxx.xxxx.com:1771:xxxxxx"/>
  <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
  <property name="hibernate.connection.driver_class" value="oracle.jdbc.OracleDriver"/>

  <property name="hibernate.connection.provider_class" value="org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider" /> <!-- hibernate 4.1.9 -->
  <property name="hibernate.c3p0.acquireIncrement" value="3"/>
  <property name="hibernate.c3p0.maxIdleTime" value="3600"/>
  <property name="hibernate.c3p0.minPoolSize" value="6"/>
  <property name="hibernate.c3p0.maxPoolSize" value="20"/>
  <property name="hibernate.c3p0.maxStatements" value="20"/>
  <property name="hibernate.c3p0.idleConnectionTestPeriod" value="1800"/> <!-- seconds -->
  <property name="hibernate.c3p0.maxConnectionAge" value="100"/>
  <property name="hibernate.c3p0.maxIdleTimeExcessConnections" value="300"/>
  <property name="hibernate.c3p0.testConnectionOnCheckin" value="true"/>
  <property name="hibernate.c3p0.preferredTestQuery" value="select 1 from dual"/>

  <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>

  <property name="hibernate.show_sql" value="false"/>
  <property name="hibernate.format_sql" value="false" />

</properties>

and when i start up the app i see information that Hibernate is using the c3p0 settings:

当我启动应用程序时，我看到 Hibernate 正在使用 c3p0 设置的信息：

Mar 7, 2013 11:15:21 AM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.1 [built 16-January-2007 14:46:42; debug? true; trace: 10]
Mar 7, 2013 11:15:21 AM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@330d4ac9 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@87961d4a [ acquireIncrement -> 3, 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 -> 30huhj8tjhzyr1ovdu4t|6196fc, idleConnectionTestPeriod -> 1800, initialPoolSize -> 3, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 100, maxIdleTime -> 3600, maxIdleTimeExcessConnections -> 300, maxPoolSize -> 20, maxStatements -> 20, maxStatementsPerConnection -> 0, minPoolSize -> 6, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@8d40ef6e [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 30huhj8tjhzyr1ovdu4t|1e9c3f, jdbcUrl -> jdbc:oracle:thin:@xxxxx.xxxxx.com:1771:xxxxx, properties -> {user=******, password=******, autocommit=true, release_mode=auto} ], preferredTestQuery -> select 1 from dual, propertyCycle -> 0, testConnectionOnCheckin -> true, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> 30huhj8tjhzyr1ovdu4t|fa0094, numHelperThreads -> 3 ]
Mar 7, 2013 11:15:24 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.Oracle10gDialect
Mar 7, 2013 11:15:24 AM org.hibernate.engine.jdbc.internal.LobCreatorBuilder useContextualLobCreation
INFO: HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
Mar 7, 2013 11:15:24 AM org.hibernate.engine.transaction.internal.TransactionFactoryInitiator initiateService
INFO: HHH000268: Transaction strategy: org.hibernate.engine.transaction.internal.jdbc.JdbcTransactionFactory

unfortunately approximately every 2 minutes Oracle is registering a handful (usually 12 at a time) of new connections (even when no users are accessing the application)

不幸的是，大约每 2 分钟 Oracle 就会注册少量（通常一次 12 个）新连接（即使没有用户访问应用程序）

I apologize if i am missing something obvious in my settings - i've scoured thru documentation, and the web trying to understand all the properties, but may be missing something or have something set wrong.

如果我在设置中遗漏了一些明显的内容，我深表歉意 - 我已经浏览了文档和网络，试图了解所有属性，但可能遗漏了某些内容或设置错误。

note, i am using hibernate 4.1.9.Final, and Oracle 11g in a Tomcat 6.0.14 container.

请注意，我在 Tomcat 6.0.14 容器中使用 hibernate 4.1.9.Final 和 Oracle 11g。

thank you for your time!

感谢您的时间！

回答by Mark Rotteveel

You have set maxConnectionAgeto 100 seconds. This means that if a connection is older than 100 seconds it will be forcefully closed, this also means that if your pool is idle, it will create 6 new connections every 100 seconds.

您已设置maxConnectionAge为 100 秒。这意味着如果连接超过 100 秒，它将被强制关闭，这也意味着如果您的池空闲，它将每 100 秒创建 6 个新连接。

The documentation says:

文档说：

Seconds, effectively a time to live. A Connection older than maxConnectionAgewill be destroyed and purged from the pool. This differs from maxIdleTimein that it refers to absolute age. Even a Connection which has not been much idle will be purged from the pool if it exceeds maxConnectionAge. Zero means no maximum absolute age is enforced.

几秒钟，有效地生活的时间。一个较旧的连接maxConnectionAge将被销毁并从池中清除。这与maxIdleTime它指的是绝对年龄不同。如果连接超过maxConnectionAge. 零意味着不强制执行最大绝对年龄。

Either set maxConnectionAgeto 0 to disable this or set it to a much higher number.

设置maxConnectionAge为 0 以禁用此功能或将其设置为更高的数字。

回答by Derek

C3P0 is culling connections that are older than 100 seconds. Setting your maxConnectionAgeto something less aggressive than 100 seconds should solve your issue.

C3P0 正在剔除超过 100 秒的连接。将您设置maxConnectionAge为小于 100 秒的攻击性应该可以解决您的问题。