c3p0池中的连接检查

Question

我正在使用Java SE应用程序开发，使用Hibernate 4和c3p0与MariaDB数据库进行通信。 这是一个长时间运行的应用程序，等待来自外部的信号，因此有时在8小时不活动后，数据库会关闭我的连接。 我尝试配置c3p0连接进行验证，但它没有起作用。你能帮我吗？

错误日志（在命名查询执行期间抛出）：

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:

<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:

<?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&amp;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:

<?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>

编辑过的内容

在启动日志中，我看到了 c3p0 配置信息（通过 Maven 在 Netbeans 中执行）：

...
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 ]
...

编辑 2

在执行由maven-shade-plugin创建的jar文件时，我遇到了以下问题：

paź 27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.ConnectionProviderInitiator instantiateC3p0Provider WARN: HHH000022: 遇到c3p0属性，但类路径中未找到c3p0提供程序类；这些属性将被忽略。paź 27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure WARN: HHH000402: 使用Hibernate内置连接池（不适用于生产环境！）paź 27, 2014 10:56:22 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator

或者当我尝试在属性中指定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 instantiate

connection 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

但是如果从Maven开始，一切都很好。不幸的是，我需要从单个jar文件中运行它。有什么想法为什么在阴影jar文件中找不到org.hibernate.connection.C3P0ConnectionProvider？

- Radzikowski

Answer 1

好的，我已经解决了所有问题。以下是解决方案。

首先，正如Steve Waldman所建议的那样，c3p0实际上没有初始化，但在Hibernate 4.3中，hibernate.connection.provider_class参数应该是：org.hibernate.c3p0.internal.C3P0ConnectionProvider。在文档中可以看到：

使用C3P0连接池的连接提供程序。如果设置了hibernate.c3p0.*属性，则Hibernate将默认使用此选项。

但我认为最好自己设置这个参数，在使用c3p0配置文件而不是hibernate.c3p0.*属性时需要设置它。

---

第二个问题是在线程开始时通过调用SessionFactory.openSession()获取会话，然后始终使用同一个会话对象。我想，在数据库连接断开并重新在池中创建新连接后，使用旧会话导致使用旧的、损坏的连接。因此，可能的解决方案是在捕获连接错误后通过SessionFactory.openSession()获取新会话，或在每次通信开始时使用SessionFactory.getCurrentSession()。我决定使用第二个选项（在这种情况下，我的应用程序等待外部信号，有时需要等待数小时，因此我每次接收信号后都会获取当前会话）。

---

我的最终配置：

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&amp;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:

<?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>

使用以下c3p0配置：

• 如果连接5分钟内没有执行任何查询，则会进行测试，因此它不会被数据库作废（在标准配置中，MySQL在8小时不活动后作废连接）。
• 如果数据库重新启动或手动终止连接，我们有两个选择： a）在最多5分钟后重新建立连接， b）如果应用程序尝试在自动重新建立连接之前执行查询，则会抛出HibernateException，然后会重新建立连接，并且下一个查询将成功。

可选地，可以将testConnectionOnCheckout设置为true以防止异常，但这会导致性能问题（请参见文档）。

- Radzikowski

Answer 2

你确定c3p0已经初始化，并且它具有你期望的配置吗？

在INFO级别的日志中，你应该看到在池初始化时c3p0数据源配置的详细信息。验证它是否存在，并且它是你期望的配置。

如果它不存在，请考虑将以下行添加到你的hibernate.cfg.xml文件中：

<property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" /> 

- Steve Waldman

---

你说得对，当我从控制台执行我的fat jar时，c3p0没有被初始化 - 请参见更新的问题。我发现在hibernate-c3p0包v4.3.6中没有org.hibernate.connection.C3P0ConnectionProvider类，但是有org.hibernate.c3p0.internal.C3P0ConnectionProvider。我使用了它，看起来很好，c3p0已经初始化了，我只等待检查连接检查是否解决了问题。 - Radzikowski

Answer 3

尝试在您的c3p0-config中将testConnectionOnCheckout设置为true。

- pomkine