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

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

3 个解决方案

#1

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.

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

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.

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

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.

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

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

您可以通过编程方式创建连接池，这是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) {}
}

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:

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

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

#2

There are libraries, such as Apache's DBCP which can do connection pooling. A part of this is they can be setup to automatically test the connection when you go to use it (such as "SELECT NOW() FROM DUAL", or something else harmless) and automatically re-establish the connection transparently if necessary, allowing your application to pretend that the connection is everlasting.

有些库，例如Apache的DBCP，可以进行连接池。其中一部分是它们可以设置为在您使用它时自动测试连接（例如“SELECT NOW（）FROM DUAL”或其他无害的东西）并在必要时自动重新建立连接，允许您申请假装连接是永恒的。

#3

Check here:

点击这里：

http://download.oracle.com/javase/tutorial/jdbc/basics/connecting.html
http://download.oracle.com/javase/tutorial/jdbc/basics/connecting.html

Basically, you should use DataSource and always do a getConnection() before using it. DataSource, unless there's something terribly wrong, will reconnect if necessary.

基本上，您应该使用DataSource并在使用之前始终执行getConnection（）。除非出现严重错误，否则DataSource会在必要时重新连接。

#1