java c3p0 池中的连接检查
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26583120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connections checking in c3p0 pool
提问by Radzikowski
I'm developing with Java SE application using Hibernate 4 and c3p0 for communication with MariaDB database. It's long running application, waiting for signals from outside, so sometimes database closes my connections after 8 hours of inactivity. I tried to configure c3p0 connections validating but it's not working. Could you help me?
我正在开发 Java SE 应用程序，使用 Hibernate 4 和 c3p0 与 MariaDB 数据库进行通信。它是长时间运行的应用程序，等待来自外部的信号，因此有时数据库会在 8 小时不活动后关闭我的连接。我试图配置 c3p0 连接验证，但它不起作用。你可以帮帮我吗？
Error log (thrown during named query execution):
错误日志（在命名查询执行期间抛出）：
2014-10-27 08:10:19.062 ERROR [trans] com.example.runnable.T1 - Exception thrown during event processing, rollbacking transaction: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91) at org.hibernate.loader.Loader.getResultSet(Loader.java:2065) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838) at org.hibernate.loader.Loader.doQuery(Loader.java:909) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354) at org.hibernate.loader.Loader.doList(Loader.java:2553) at org.hibernate.loader.Loader.doList(Loader.java:2539) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369) at org.hibernate.loader.Loader.list(Loader.java:2364) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:496) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:231) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103) at com.example.runnable.T1.find(EventsTransmitter.java:140) at com.example.runnable.T1.run(EventsTransmitter.java:86) at java.lang.Thread.run(Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 108,132,692 milliseconds ago. The last packet sent successfully to the server was 108,132,692 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. at sun.reflect.GeneratedConstructorAccessor30.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:408) at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3661) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2030) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ... 17 more Caused by: java.net.SocketException: Broken pipe at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) at java.net.SocketOutputStream.write(SocketOutputStream.java:153) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3643) ... 23 more
2014-10-27 08:10:19.062 ERROR [trans] com.example.runnable.T1 - Exception thrown during event processing, rollbacking transaction: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91) at org.hibernate.loader.Loader.getResultSet(Loader.java:2065) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838) at org.hibernate.loader.Loader.doQuery(Loader.java:909) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354) at org.hibernate.loader.Loader.doList(Loader.java:2553) at org.hibernate.loader.Loader.doList(Loader.java:2539) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369) at org.hibernate.loader.Loader.list(Loader.java:2364) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:496) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:231) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103) at com.example.runnable.T1.find(EventsTransmitter.java:140) at com.example.runnable.T1.run(EventsTransmitter.java:86) at java.lang.Thread.run(Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 108,132,692 milliseconds ago. The last packet sent successfully to the server was 108,132,692 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. at sun.reflect.GeneratedConstructorAccessor30.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:408) at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3661) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2030) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) ... 17 more Caused by: java.net.SocketException: Broken pipe at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) at java.net.SocketOutputStream.write(SocketOutputStream.java:153) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3643) ... 23 more
pom.xml:
pom.xml:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.33</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>4.3.6.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.1.2.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>4.3.1.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-ehcache</artifactId>
<version>4.3.6.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-c3p0</artifactId>
<version>4.3.6.Final</version>
</dependency>
src/main/resources/hibernate.cfg.xml:
src/main/resources/hibernate.cfg.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/MyBase?zeroDateTimeBehavior=convertToNull&autoReconnect=true</property>
<property name="hibernate.connection.username">user</property>
<property name="hibernate.connection.password">pass</property>
<property name="hibernate.current_session_context_class">thread</property>
<property name="show_sql">true</property>
<property name="use_sql_comments">true</property>
<property name="hibernate.cache.use_second_level_cache">true</property>
<property name="hibernate.cache.use_query_cache">true</property>
<property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">10</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.max_statements">30</property>
<mapping class="com.example.domain.E" />
</session-factory>
</hibernate-configuration>
src/main/resources/c3p0-config.xml:
src/main/resources/c3p0-config.xml：
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="preferredTestQuery">SELECT 1 FROM DUAL</property>
<property name="testConnectionOnCheckin">true</property>
<property name="idleConnectionTestPeriod">1800</property> <!-- 30 minutes -->
</default-config>
</c3p0-config>
Edited
已编辑
In logs on startup I'm getting c3p0 configuration (executing by Maven from Netbeans):
在启动日志中，我得到了 c3p0 配置（由 Netbeans 的 Maven 执行）：
...
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@81187ff9
[ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@e1820e10 [ 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 -> z8kflt95n558v5xddgsj|2cf3d63b, idleConnectionTestPeriod -> 1800, initialPoolSize -> 5,
maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 300, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 30,
maxStatementsPerConnection -> 0, minPoolSize -> 5, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@2f0d7eae [ description -> null, driverClass -> null,
factoryClassLocation -> null, identityToken -> z8kflt95n558v5xddgsj|1e6a3214, jdbcUrl -> jdbc:mysql://localhost:3306/MyBase?zeroDateTimeBehavior=convertToNull&autoReconnect=true,
properties -> {user=******, password=******} ], preferredTestQuery -> SELECT 1 FROM DUAL, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0,
testConnectionOnCheckin -> true, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ],
dataSourceName -> null, factoryClassLocation -> null, identityToken -> z8kflt95n558v5xddgsj|7161d8d1, numHelperThreads -> 3 ]
...
Edit 2
编辑 2
When executing jar created by maven-shade-plugin I'm getting:
当执行由 maven-shade-plugin 创建的 jar 时，我得到：
pa? 27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator instantiateC3p0Provider WARN: HHH000022: c3p0 properties were encountered, but the c3p0 provider class was not found on the classpath; these properties are going to be ignored. pa? 27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure WARN: HHH000402: Using Hibernate built-in connection pool (not for production use!) pa? 27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
爸？27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator instantiateC3p0Provider WARN：HHH000022：遇到c3p0属性，但是在类路径上没有找到c3p0提供者类；这些属性将被忽略。爸？27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl 配置警告：HHH000402：使用 Hibernate 内置连接池（不用于生产！）pa？27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
or when I tried to indicate org.hibernate.connection.C3P0ConnectionProviderin properties:
或者当我试图org.hibernate.connection.C3P0ConnectionProvider在属性中指出时：
INFO: HHH000130: Instantiating explicit connection provider: org.hibernate.connection.C3P0ConnectionProvider Initial SessionFactory creation failed.org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.connections.spi.ConnectionProvider] Exception in thread "main" java.lang.ExceptionInInitializerError at com.example.util.HibernateUtil.(HibernateUtil.java:27) at com.example.App.run(App.java:31) at com.example.App.main(App.java:25)
Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.connections.spi.ConnectionProvider]at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:261) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:225) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206) at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:260) at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:94) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206) at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1885) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1843) at com.example.util.HibernateUtil.<clinit>(HibernateUtil.java:24) ... 2 more Caused by: org.hibernate.HibernateException: Could not instantiateconnection provider [org.hibernate.connection.C3P0ConnectionProvider]
at org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.instantiateExplicitConnectionProvider(ConnectionProviderInitiator.java:197) at org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.initiateService(ConnectionProviderInitiator.java:120) at org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.initiateService(ConnectionProviderInitiator.java:55) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:105) at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:251) ... 12 more Caused by: org.hibernate.boot.registry.selector.spi.StrategySelectionException:Unable to resolve name [org.hibernate.connection.C3P0ConnectionProvider] as strategy [org.hibernate.engine.jdbc.connections.spi.ConnectionProvider] at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplementor(StrategySelectorImpl.java:128) at org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.instantiateExplicitConnectionProvider(ConnectionProviderInitiator.java:194) ... 16 more
信息：HHH000130：实例化显式连接提供者：org.hibernate.connection.C3P0ConnectionProvider 初始会话工厂创建失败.org.hibernate.service.spi.ServiceException：无法创建请求的服务 [org.hibernate.engine.jdbc.connections.spi.ConnectionProvider ] com.example.util.HibernateUtil.(HibernateUtil.java:27) 处 com.example.App.run(App.java:31) 处 com.example.App 处的线程“main”java.lang.ExceptionInInitializerError 中的异常。 main(App.java:25)
引起：org.hibernate.service.spi.ServiceException：无法创建请求的服务 [org.hibernate.engine.jdbc.connections.spi.ConnectionProvider]at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:261) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:225) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206) at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:260) at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:94) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206) at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1885) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1843) at com.example.util.HibernateUtil.<clinit>(HibernateUtil.java:24) ... 2 more Caused by: org.hibernate.HibernateException: Could not instantiate连接提供者 [org.hibernate.connection.C3P0ConnectionProvider]
at org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.instantiateExplicitConnectionProvider(ConnectionProviderInitiator.java:197) at org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.initiateService(ConnectionProviderInitiator.java:120) at org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.initiateService(ConnectionProviderInitiator.java:55) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:105) at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:251) ... 12 more Caused by: org.hibernate.boot.registry.selector.spi.StrategySelectionException:无法将名称 [org.hibernate.connection.C3P0ConnectionProvider] 解析为 org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplementor(StrategySelectorImpl.java) 处的策略 [org.hibernate.engine.jdbc.connections.spi.ConnectionProvider] :128) 在 org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator.instantiateExplicitConnectionProvider(ConnectionProviderInitiator.java:194) ... 16 更多
But everything is good when starting by Maven. Unfortunately I need this to work from single jar. Any ideas why org.hibernate.connection.C3P0ConnectionProvider cannot be found in shaded jar?
但是当从 Maven 开始时，一切都很好。不幸的是，我需要它从单个 jar 中工作。为什么在阴影 jar 中找不到 org.hibernate.connection.C3P0ConnectionProvider 的任何想法？
回答by Radzikowski
Ok, i managed to fix all problems. Here is soultion.
好的，我设法解决了所有问题。这里是灵魂。
First of all, as Steve Waldman suggested, c3p0 wasn't actually initialized, but in Hibernate 4.3 hibernate.connection.provider_classparameter should be: org.hibernate.c3p0.internal.C3P0ConnectionProvider. In documentationyou can read:
首先，正如史蒂夫沃尔德曼所建议的那样， c3p0 实际上并未初始化，但在 Hibernate 4.3 中hibernate.connection.provider_class参数应该是：org.hibernate.c3p0.internal.C3P0ConnectionProvider。在文档中，您可以阅读：
A connection provider that uses a C3P0 connection pool. Hibernate will use this by default if the hibernate.c3p0.* properties are set.
使用 C3P0 连接池的连接提供程序。如果设置了 hibernate.c3p0.* 属性，Hibernate 将默认使用它。
But in my opinion it's good to put this parameter by yourself, and it's needed if you are using c3p0 config file, not hibernate.c3p0.*properties.
但在我看来，这个参数最好自己放，如果您使用的是 c3p0 配置文件，而不是hibernate.c3p0.*属性，则需要它。
Second problem was obtaining session by calling SessionFactory.openSession()at the begining of the thread and later using always the same session object. I suppose after database connection was broken and there was new one recreated in pool, using old session caused using old, broken connection. So possible solutions were obtaining new session by SessionFactory.openSession()after catching connection error or using SessionFactory.getCurrentSession()at the begining of every communication. I've decided to use the second option (in this case my application waits for external signal, sometimes for hours, so I'm obtaining current session after each signal).
第二个问题是通过SessionFactory.openSession()在线程开始时调用然后始终使用相同的会话对象来获取会话。我想在数据库连接断开并且池中重新创建了一个新的连接后，使用旧的、断开的连接引起的旧会话。因此，可能的解决方案是通过SessionFactory.openSession()捕获连接错误或SessionFactory.getCurrentSession()在每次通信开始时使用来获取新会话。我决定使用第二个选项（在这种情况下，我的应用程序等待外部信号，有时需要几个小时，所以我在每个信号后获取当前会话）。
My final configuration:
我的最终配置：
hibernate.cfg.xml:
hibernate.cfg.xml：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/MyBase?zeroDateTimeBehavior=convertToNull&autoReconnect=true</property>
<property name="hibernate.connection.username">user</property>
<property name="hibernate.connection.password">pass</property>
<property name="hibernate.connection.provider_class">org.hibernate.c3p0.internal.C3P0ConnectionProvider</property>
<property name="hibernate.current_session_context_class">thread</property>
<property name="show_sql">true</property>
<property name="use_sql_comments">true</property>
<property name="hibernate.cache.use_second_level_cache">true</property>
<property name="hibernate.cache.use_query_cache">true</property>
<property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</property>
</session-factory>
</hibernate-configuration>
c3p0-config.xml:
c3p0-config.xml：
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="initialPoolSize">5</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">3000</property>
<property name="maxStatementsPerConnection">30</property>
<property name="preferredTestQuery">SELECT 1 FROM DUAL</property>
<property name="testConnectionOnCheckin">true</property>
<property name="testConnectionOnCheckout">false</property>
<property name="idleConnectionTestPeriod">300</property> <!-- 5 minutes -->
</default-config>
</c3p0-config>
With this c3p0 configuration:
使用此 c3p0 配置：
- Every 5 minutes of not performing any queries on connection it will be tested, so it will never be invalidated by DB (in standard configuration MySQL invalidates connection after 8 hours of inactivity),
- If DB will be restarted or connection(s) will be manually killed we have two options:
a) after max 5 minutes connections will be reestablished,
b) if application tries to perform query before automatic connection reestablishing
HibernateExceptionwill be thrown, then connection will be reestablished and next query will succeed.
- 每 5 分钟不对连接执行任何查询，它就会被测试一次，因此它永远不会被 DB 失效（在标准配置中，MySQL 在 8 小时不活动后使连接失效），
- 如果数据库将重新启动或连接将被手动终止，我们有两种选择：a) 最多 5 分钟后重新建立连接，b) 如果应用程序尝试在自动重新建立连接之前执行查询
HibernateException，则连接将被重新建立，下一个查询将成功。
Optionally testConnectionOnCheckoutcould be set to true to prevent exceptions, but it will cause performance troubles (see documentation).
可以选择testConnectionOnCheckout将其设置为 true 以防止出现异常，但这会导致性能问题（请参阅文档）。
回答by pomkine
Try setting testConnectionOnCheckout to true in your c3p0-config.
尝试在您的 c3p0-config 中将 testConnectionOnCheckout 设置为 true。
回答by Steve Waldman
are you sure c3p0 is actually initialized, and that it has the config you expect it to have?
您确定 c3p0 实际上已初始化，并且它具有您期望的配置吗？
in your logs, at INFO level, you should see a dump of your c3p0 DataSource's config upon pool initialization. verify that it is there, and that it is the configuration you expect.
在您的日志中，在 INFO 级别，您应该在池初始化时看到您的 c3p0 数据源配置的转储。验证它在那里，并且它是您期望的配置。
if it is not there, please consider adding the following line to your hibernate.cfg.xml:
如果它不存在，请考虑将以下行添加到您的 hibernate.cfg.xml：
<property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" />
