java - 使用池连接时它不起作用。(Hibernate 和 c3p0)

Question

第一次查询数据库，成功了。但几分钟后，setAutoCommit 失败。

这是我的配置:

mysql服务器:5.6.21

mysql:mysql-connector-java-5.1.31.jar

驱动程序:com.mysql.fabric.jdbc.FabricMySQLDriver

hibernate :5.1.3.最终

数据源:com.mchange.v2.c3p0.ComboPooledDataSource

当我将数据源更改为 com.mchange.v2.c3p0.DriverManagerDataSource 时，它就可以工作。

@Bean
public DataSource dataSource() throws PropertyVetoException {
    ComboPooledDataSource dataSource = new ComboPooledDataSource();
    dataSource.setDriverClass(environment.getRequiredProperty("jdbc.driver"));
    dataSource.setJdbcUrl(environment.getRequiredProperty("jdbc.url"));
    dataSource.setUser(environment.getRequiredProperty("jdbc.username"));
    dataSource.setPassword(environment.getRequiredProperty("jdbc.password"));
    return dataSource;
}

private Properties hibernateProperties() {
    Properties properties = new Properties();
    properties.put("hibernate.dialect", environment.getRequiredProperty("hibernate.dialect"));
    properties.put("hibernate.show_sql", environment.getRequiredProperty("hibernate.show_sql"));
    properties.put("hibernate.format_sql", environment.getRequiredProperty("hibernate.format_sql"));
    return properties;
}

@Bean
public LocalSessionFactoryBean sessionFactory() throws PropertyVetoException {
    LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
    sessionFactory.setDataSource(dataSource());
    sessionFactory.setPackagesToScan("com.nd");
    sessionFactory.setHibernateProperties(hibernateProperties());
    return sessionFactory;
}

@Bean
@Autowired
public HibernateTemplate hibernateTemplate(SessionFactory sessionFactory) {
    HibernateTemplate hibernateTemplate = new HibernateTemplate();
    hibernateTemplate.setSessionFactory(sessionFactory);
    return hibernateTemplate;
}

@Bean
@Autowired
public HibernateTransactionManager transactionManager(SessionFactory sessionFactory) {
    HibernateTransactionManager txManager = new HibernateTransactionManager();
    txManager.setSessionFactory(sessionFactory);
    return txManager;
}

堆栈跟踪:

The last packet successfully received from the server was 7,226,009 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_101]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_101]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_101]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_101]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4149) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5368) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.LoadBalancedMySQLConnection.setAutoCommit(LoadBalancedMySQLConnection.java:2313) ~[mysql-connector-java-5.1.31.jar:?]
    at sun.reflect.GeneratedMethodAccessor78.invoke(Unknown Source) ~[?:?]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_101]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_101]
    at com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:643) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:548) ~[mysql-connector-java-5.1.31.jar:?]
    at com.sun.proxy.$Proxy62.setAutoCommit(Unknown Source) ~[?:?]
    at com.mysql.jdbc.ReplicationConnection.setAutoCommit(ReplicationConnection.java:692) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.fabric.jdbc.FabricMySQLConnectionProxy.setAutoCommit(FabricMySQLConnectionProxy.java:633) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:912) ~[c3p0-0.9.2.1.jar:0.9.2.1]
    at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:67) ~[hibernate-core-5.1.3.Final.jar:5.1.3.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:235) ~[hibernate-core-5.1.3.Final.jar:5.1.3.Final]
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:214) ~[hibernate-core-5.1.3.Final.jar:5.1.3.Final]
    at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:52) ~[hibernate-core-5.1.3.Final.jar:5.1.3.Final]
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1525) ~[hibernate-core-5.1.3.Final.jar:5.1.3.Final]
    at org.springframework.orm.hibernate5.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:500) ~[spring-orm-4.3.9.RELEASE.jar:4.3.9.RELEASE]
    ... 88 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3161) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3615) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4149) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5368) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.LoadBalancedMySQLConnection.setAutoCommit(LoadBalancedMySQLConnection.java:2313) ~[mysql-connector-java-5.1.31.jar:?]
    at sun.reflect.GeneratedMethodAccessor78.invoke(Unknown Source) ~[?:?]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_101]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_101]
    at com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:643) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:548) ~[mysql-connector-java-5.1.31.jar:?]
    at com.sun.proxy.$Proxy62.setAutoCommit(Unknown Source) ~[?:?]
    at com.mysql.jdbc.ReplicationConnection.setAutoCommit(ReplicationConnection.java:692) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mysql.fabric.jdbc.FabricMySQLConnectionProxy.setAutoCommit(FabricMySQLConnectionProxy.java:633) ~[mysql-connector-java-5.1.31.jar:?]
    at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:912) ~[c3p0-0.9.2.1.jar:0.9.2.1]
    at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:67) ~[hibernate-core-5.1.3.Final.jar:5.1.3.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:235) ~[hibernate-core-5.1.3.Final.jar:5.1.3.Final]
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:214) ~[hibernate-core-5.1.3.Final.jar:5.1.3.Final]
    at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:52) ~[hibernate-core-5.1.3.Final.jar:5.1.3.Final]
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1525) ~[hibernate-core-5.1.3.Final.jar:5.1.3.Final]
    at org.springframework.orm.hibernate5.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:500) ~[spring-orm-4.3.9.RELEASE.jar:4.3.9.RELEASE]
    ... 88 more

Answer 1

请配置连接测试，以便c3p 0 auto 使超时连接失效。

最简单的方法是添加

dataSource.setTestConnectionOnCheckout(true);

到您的dataSource()方法。