与JDBC到MySQL的持久连接

问题描述：

我有一个使用JDBC连接MySQL的应用程序。有些情况下，JDBC连接闲置数小时（甚至几天），并且它失去了与MySQL的连接，然后在尝试执行查询时排除。对此最好的解决方案是什么？

I have an application that connects to MySQL using JDBC. There are cases where the JDBC connection lies idle for hours (maybe even days) and its loosing its connection to MySQL and then excepts when it tries to execute a query. What is the best solution for this?

答

保持连接打开一段时间是不好的做法。当DB打开很长时间时，DB会强制关闭。您应该编写JDBC代码，以便它始终关闭 finally 块中的连接（以及语句和结果集），同时尝试阻止您获取它们的位置，以防止资源泄漏。

Keeping the connection open for an undertemined time is a bad practice. The DB will force a close when it's been open for a too long time. You should write your JDBC code so that it always closes the connection (and statement and resultset) in the finally block of the very same try block where you've acquired them in order to prevent resource leaking like this.

然而，在每次打嗝时获取连接确实是一项非常昂贵的任务，因此您希望使用连接池。体面的连接池将自行管理打开，测试，重用和关闭连接。但这并不意味着您可以将JDBC代码更改为永不关闭它们。您仍然需要关闭它们，因为这实际上会将底层连接释放回池以供将来重用。

However, acquiring the connection on every hiccup is indeed a pretty expensive task, so you'd like to use a connection pool. Decent connection pools will manage the opening, testing, reusing and closing the connections themselves. This does however not imply that you can change your JDBC code to never close them. You still need to close them since that would actually release the underlying connection back to the pool for future reuse.

有几个连接池，如 Apache DBCP ，它是单线程的，因此性能很差， C3P0 ，它是多线程的并且表现更好，并且 Tomcat JDBC ，用于您使用Tomcat但由于性能不佳而不想使用内置DBCP的情况。

There are several connection pools, like Apache DBCP which is singlethreaded and thus poor in performance, C3P0 which is multithreaded and performs better, and Tomcat JDBC for the case that you're using Tomcat and wouldn't like to use the builtin DBCP due to bad performance.

你可以通过编程方式创建连接池，这是C3P0的示例：

You can create connection pools programmatically, here's an example with C3P0:

ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/dbname");
dataSource.setUser("username");
dataSource.setPassword("password");

在应用程序启动期间执行一次，然后您可以按如下方式使用它：

Do it once during application's startup, then you can use it as follows:

Connection connection = null;
// ...

try {
    connection = dataSource.getConnection();
    // ...
} finally {
    // ...
    if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
}

当你在JNDI中运行时 - 像servletcontainer一样的容器（例如Tomcat），然后你也可以将它声明为 java.sql.DataSource （Tomcat特定手册这里）。然后它将使用servletcontainer提供的连接池设施。然后，您可以按如下方式获取数据源：

When you're running inside a JNDI-capable container like a servletcontainer (e.g. Tomcat), then you can also declare it as a java.sql.DataSource (Tomcat specific manual here). It will then use the servletcontainer-provided connection pooling facilities. You can then acquire the datasource as follows:

DataSource dataSource = (DataSource) new InitialContext().lookup("java:comp/env/jdbc/YourDataSourceName");

与JDBC到MySQL的持久连接

相关推荐