In my Javafx application I connect to remote server on Hetzner.de using sftp. To manage connections, I use cp30 library connection pooling with the following parameters:
在Javafx应用程序中，我使用sftp连接到远程服务器上的Hetzner.de。为了管理连接，我使用cp30库连接池，使用以下参数:
public Connection dbConnectSite() throws SQLException, PropertyVetoException {
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://" + mySQLHost + ":" + mySQLPort + "/" + mySQLDBName + "?characterEncoding=UTF-8&autoReconnect=true"); // 192.168.100.100 v seti.
cpds.setUser(mySQLUser);
cpds.setPassword(mySQLPassword);
cpds.setMinPoolSize(3);
cpds.setMaxPoolSize(20); // Maximum number of Connections a pool will maintain at any given time.
cpds.setAcquireIncrement(1);
cpds.setTestConnectionOnCheckin(true); // If true, an operation will be performed asynchronously at every connection checkin to verify that the connection is valid.
cpds.setTestConnectionOnCheckout(true); // If true, an operation will be performed at every connection checkout to verify that the connection is valid.
cpds.setIdleConnectionTestPeriod(300); // If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.
cpds.setMaxIdleTimeExcessConnections(240);
cpds.setMaxIdleTime(3600); // Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.
cpds.setMaxStatements(100);
cpds.setCheckoutTimeout(0); // The number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted. Zero means wait indefinitely.
cpds.setMaxAdministrativeTaskTime(0); // Seconds before c3p0's thread pool will try to interrupt an apparently hung task.
cpds.setMaxConnectionAge(saytPort); // Seconds, effectively a time to live. A Connection older than maxConnectionAge will be destroyed and purged from the pool. Zero means no maximum absolute age is enforced.
cpds.setPreferredTestQuery("SELECT 1");
dsSite = cpds;
conSayt = dsSite.getConnection();
return conSayt;
}
Connection goes fine. The console log for connection is:
连接就好了。连接的控制台日志是:
ноя 10, 2013 9:59:47 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
ноя 10, 2013 9:59:47 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
ноя 10, 2013 9:59:47 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kfsx8yiing6p1lp8nb2|3ba701c9, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kfsx8yiing6p1lp8nb2|3ba701c9, idleConnectionTestPeriod -> 300, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://sql249.your-server.de:3306/kombinezonik?characterEncoding=UTF-8&autoReconnect=true, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 22, maxIdleTime -> 3600, maxIdleTimeExcessConnections -> 240, maxPoolSize -> 20, maxStatements -> 100, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> SELECT 1, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> true, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
nov 10, 2013 9:59:49 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kfsx8yiing6p1lp8nb2|2bc60511, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kfsx8yiing6p1lp8nb2|2bc60511, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/kombadmin?characterEncoding=UTF-8&autoReconnect=true, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
But if the GUI stands by for some different periods of time, I get an error:
但是，如果GUI在不同的时间段内存在，我就会得到一个错误:
nov 10, 2013 10:07:50 PM MyClass
SEVERE: null
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 344 144 milliseconds ago. The last packet sent successfully to the server was 2 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3603)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3492)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4043)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2738)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1617)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
at MyClass.java:158
at MyClass$1$1$1.run(MyClass.java:128)
at com.sun.javafx.application.PlatformImpl$4$1.run(PlatformImpl.java:179)
at com.sun.javafx.application.PlatformImpl$4$1.run(PlatformImpl.java:176)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl$4.run(PlatformImpl.java:176)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:76)
at com.sun.glass.ui.gtk.GtkApplication._runLoop(Native Method)
at com.sun.glass.ui.gtk.GtkApplication$3$1.run(GtkApplication.java:82)
at java.lang.Thread.run(Thread.java:722)
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:3052)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3503)
... 18 more
Of course I close Statements and ResultSets, using try with resources:
当然，我使用资源的尝试，关闭语句和结果集。
try (Statement stmt = m.conSayt.createStatement(); ResultSet rs = stmt.executeQuery(SQL);) {
// Contents.
} catch (SQLException ex){
// Contents.
}
1 个解决方案
#1
5
The issue here is the way you are using (misusing, i'm afraid) c3p0.
这里的问题是你使用的方法(我害怕)c3p0。
Each time you get a Connection, you are creating a new Connection pool, then starting up the Connection pool, then letting it go out of scope. Note that in your logs you are initializing three different c3p0 pools in three seconds, once each time you try to acquire a Connection. Not good.
每当您获得一个连接时，您都在创建一个新的连接池，然后启动连接池，然后让它超出范围。请注意，在您的日志中，您将在三秒内初始化三个不同的c3p0池，每次您尝试获取一个连接。不好的。
You never clean-up your Connection. (You don't actually clean up ResultSets and Statements in the code you provide here.) As samlewis says, it looks like you intend to just hold the Connection open.
你从来没有清理过你的连接。(实际上，您在这里提供的代码中并没有清理结果集和语句)。正如samlewis所说，看起来你只是想保持连接打开。
All of that is not at all how you ought to use a Connection pool. If you want to fetch one Connection and hold it open, just fetch the Connection directly using DriverManager.getConnection( ... ) and avoid a whole lot of complexity.
所有这些并不是您应该如何使用连接池。如果您想要获取一个连接并保持打开，只需使用DriverManager直接获取连接。getConnection(…)避免了太多的复杂性。
Except you'll find, as you have found already, that holding open a JDBC Connection for long periods of time and expecting to reuse it is a fragile way to run an application. That is the problem Connection pools exist to solve.
但是，正如您已经发现的，您会发现，在长时间内保持打开JDBC连接并期望重用它是运行应用程序的一种脆弱方式。这就是要解决的问题连接池。
With a Connection pool, stuff has to be different:
有了连接池，事情就不同了:
1) That ComboPooledDataSource you create? Don't let it disappear. Hold on to a reference to it somewhere, perhaps as a static field, perhaps as a member of some Object you use. You should create one DataSource one time for a typical application deployment.
1)你创建的那个好斗的数据源?不要让它消失。保持对它的引用，也许作为一个静态字段，或者作为某个对象的一个成员。您应该一次为典型的应用程序部署创建一个数据源。
2) Each time you need a Connection i) acquire a "new" Connection by calling getConnection() on the DataSource; ii) do whatever work you need to do; iii) close() that Connection immediately after use.
2)每次您需要一个连接i)通过在数据源上调用getConnection()获得一个“新的”连接;做你需要做的任何工作;使用后立即关闭连接。
c3p0 will maintain a pool of Connections, so all of this will be fast. cpds.getConnection() won't make network connections to the dbms to establish a new Connection, it will simply hand you a Connection from the pool. When you close() the Connection, the connection to the dbms won't actually be destroyed, just recycled back into the pool. This is JDBC-standard transparent Connection pooling.
c3p0将保持一个连接池，所以所有这些都将是快速的。getconnection()不会使网络连接到dbms来建立一个新的连接，它只会将连接从池中传递给您。当您关闭()连接时，与dbms的连接实际上不会被破坏，只是回收到池中。这是jdbc标准的透明连接池。
[Oops! I didn't notice, questioner uses java 7 try-with-resources, which should be fina, as long as Connections are among the resources!] Note that calling functions that create ResultSets, Statements, or Connnections in a try{} block is not at all sufficient to ensure that those resources are close()ed. You have to actually close them, in a finally block:
[哦!我没有注意到，提问者使用java 7试用资源，这应该是fina，只要连接在资源中!注意，在try{}块中调用创建resultset、语句或Connnections的函数并不足以确保这些资源是close()ed。你必须关闭它们，在最后一个块中:
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try
{
con = cpds.getConnection();
stmt = cpds.createStatement();
rs = stmt.executeQuery("SELECT something FROM somewhere");
while ( rs.next() )
{
// do some stuff with results
}
}
finally
{
try { if (rs != null) rs.close() } catch (SQLException e) { e.printStackTrace(); }
try { if (stmt != null) stmt.close() } catch (SQLException e) { e.printStackTrace(); }
try { if (con != null) con.close() } catch (SQLException e) { e.printStackTrace(); }
}
Again, cpds should not be a local variable. Your Connection, on the other hand, should be closed (generally) within the scope of the method that opened it, in a finally block.
同样，cpds不应该是一个局部变量。另一方面，您的连接应该在打开它的方法的范围内(通常)关闭(在最后一个块中)。
[Note that each resource close() inside the finally block is wrapped in a nested try, to ensure that an Exception within one close() doesn't prevent other resources from a best-attempt close(). You can make this cleaner by wrapping the nested tries in helper methods.]
注意，finally块中的每个资源关闭()都封装在一个嵌套的try中，以确保在一个close()内的异常不会阻止其他资源的最佳尝试()。您可以使用辅助方法来包装嵌套的尝试，从而使这个更干净。
Some other comments: acquireIncrement of 1 is generally a bad idea, there's little reason to set testConnectionOnCheckin to true if you are already testing Connections on checkout. But for the moment, none of that matters, the only config your app is actually exercising is the jdbcUrl, user, and password, because you are only ever getting a single Connection and then discarding (well, trying to discard) the pool.
一些其他的注释:1的默认增量通常是一个坏主意，如果您已经在checkout上测试连接，那么就没有理由将testConnectionOnCheckin设置为true。但是现在，这些都无关紧要，你的应用程序唯一的配置就是jdbcUrl、用户和密码，因为你只获得一个连接，然后丢弃(嗯，尝试丢弃)池。
It's worth explaining that "trying to discard". c3p0 pools spawn their own maintenance Threads. As you construct and initialize new pools and then let them go out of scope without close()ing them, you are creating Threads and memory leak. Whenever you are done with a c3p0 ComboPooledDataSource, you must call close() [or more rarely the static method DataSources.destroy( ... )] to shut the DataSource down. Typically this is done once, as an application is shutting down or resetting itself.
值得解释的是“试着丢弃”。c3p0池生成它们自己的维护线程。当您构造和初始化新池，然后让它们在没有关闭的情况下超出范围时，您正在创建线程和内存泄漏。无论何时，当您使用c3p0与datasource一起完成时，您必须调用close()(或更少的静态方法数据源)。关闭数据源。通常，这是一次完成的，因为应用程序正在关闭或重新设置自己。
#1
5
The issue here is the way you are using (misusing, i'm afraid) c3p0.
这里的问题是你使用的方法(我害怕)c3p0。
Each time you get a Connection, you are creating a new Connection pool, then starting up the Connection pool, then letting it go out of scope. Note that in your logs you are initializing three different c3p0 pools in three seconds, once each time you try to acquire a Connection. Not good.
每当您获得一个连接时，您都在创建一个新的连接池，然后启动连接池，然后让它超出范围。请注意，在您的日志中，您将在三秒内初始化三个不同的c3p0池，每次您尝试获取一个连接。不好的。
You never clean-up your Connection. (You don't actually clean up ResultSets and Statements in the code you provide here.) As samlewis says, it looks like you intend to just hold the Connection open.
你从来没有清理过你的连接。(实际上，您在这里提供的代码中并没有清理结果集和语句)。正如samlewis所说，看起来你只是想保持连接打开。
All of that is not at all how you ought to use a Connection pool. If you want to fetch one Connection and hold it open, just fetch the Connection directly using DriverManager.getConnection( ... ) and avoid a whole lot of complexity.
所有这些并不是您应该如何使用连接池。如果您想要获取一个连接并保持打开，只需使用DriverManager直接获取连接。getConnection(…)避免了太多的复杂性。
Except you'll find, as you have found already, that holding open a JDBC Connection for long periods of time and expecting to reuse it is a fragile way to run an application. That is the problem Connection pools exist to solve.
但是，正如您已经发现的，您会发现，在长时间内保持打开JDBC连接并期望重用它是运行应用程序的一种脆弱方式。这就是要解决的问题连接池。
With a Connection pool, stuff has to be different:
有了连接池，事情就不同了:
1) That ComboPooledDataSource you create? Don't let it disappear. Hold on to a reference to it somewhere, perhaps as a static field, perhaps as a member of some Object you use. You should create one DataSource one time for a typical application deployment.
1)你创建的那个好斗的数据源?不要让它消失。保持对它的引用，也许作为一个静态字段，或者作为某个对象的一个成员。您应该一次为典型的应用程序部署创建一个数据源。
2) Each time you need a Connection i) acquire a "new" Connection by calling getConnection() on the DataSource; ii) do whatever work you need to do; iii) close() that Connection immediately after use.
2)每次您需要一个连接i)通过在数据源上调用getConnection()获得一个“新的”连接;做你需要做的任何工作;使用后立即关闭连接。
c3p0 will maintain a pool of Connections, so all of this will be fast. cpds.getConnection() won't make network connections to the dbms to establish a new Connection, it will simply hand you a Connection from the pool. When you close() the Connection, the connection to the dbms won't actually be destroyed, just recycled back into the pool. This is JDBC-standard transparent Connection pooling.
c3p0将保持一个连接池，所以所有这些都将是快速的。getconnection()不会使网络连接到dbms来建立一个新的连接，它只会将连接从池中传递给您。当您关闭()连接时，与dbms的连接实际上不会被破坏，只是回收到池中。这是jdbc标准的透明连接池。
[Oops! I didn't notice, questioner uses java 7 try-with-resources, which should be fina, as long as Connections are among the resources!] Note that calling functions that create ResultSets, Statements, or Connnections in a try{} block is not at all sufficient to ensure that those resources are close()ed. You have to actually close them, in a finally block:
[哦!我没有注意到，提问者使用java 7试用资源，这应该是fina，只要连接在资源中!注意，在try{}块中调用创建resultset、语句或Connnections的函数并不足以确保这些资源是close()ed。你必须关闭它们，在最后一个块中:
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try
{
con = cpds.getConnection();
stmt = cpds.createStatement();
rs = stmt.executeQuery("SELECT something FROM somewhere");
while ( rs.next() )
{
// do some stuff with results
}
}
finally
{
try { if (rs != null) rs.close() } catch (SQLException e) { e.printStackTrace(); }
try { if (stmt != null) stmt.close() } catch (SQLException e) { e.printStackTrace(); }
try { if (con != null) con.close() } catch (SQLException e) { e.printStackTrace(); }
}
Again, cpds should not be a local variable. Your Connection, on the other hand, should be closed (generally) within the scope of the method that opened it, in a finally block.
同样，cpds不应该是一个局部变量。另一方面，您的连接应该在打开它的方法的范围内(通常)关闭(在最后一个块中)。
[Note that each resource close() inside the finally block is wrapped in a nested try, to ensure that an Exception within one close() doesn't prevent other resources from a best-attempt close(). You can make this cleaner by wrapping the nested tries in helper methods.]
注意，finally块中的每个资源关闭()都封装在一个嵌套的try中，以确保在一个close()内的异常不会阻止其他资源的最佳尝试()。您可以使用辅助方法来包装嵌套的尝试，从而使这个更干净。
Some other comments: acquireIncrement of 1 is generally a bad idea, there's little reason to set testConnectionOnCheckin to true if you are already testing Connections on checkout. But for the moment, none of that matters, the only config your app is actually exercising is the jdbcUrl, user, and password, because you are only ever getting a single Connection and then discarding (well, trying to discard) the pool.
一些其他的注释:1的默认增量通常是一个坏主意，如果您已经在checkout上测试连接，那么就没有理由将testConnectionOnCheckin设置为true。但是现在，这些都无关紧要，你的应用程序唯一的配置就是jdbcUrl、用户和密码，因为你只获得一个连接，然后丢弃(嗯，尝试丢弃)池。
It's worth explaining that "trying to discard". c3p0 pools spawn their own maintenance Threads. As you construct and initialize new pools and then let them go out of scope without close()ing them, you are creating Threads and memory leak. Whenever you are done with a c3p0 ComboPooledDataSource, you must call close() [or more rarely the static method DataSources.destroy( ... )] to shut the DataSource down. Typically this is done once, as an application is shutting down or resetting itself.
值得解释的是“试着丢弃”。c3p0池生成它们自己的维护线程。当您构造和初始化新池，然后让它们在没有关闭的情况下超出范围时，您正在创建线程和内存泄漏。无论何时，当您使用c3p0与datasource一起完成时，您必须调用close()(或更少的静态方法数据源)。关闭数据源。通常，这是一次完成的，因为应用程序正在关闭或重新设置自己。