JDBC 到 MySQL 的持久连接 [英] A persistent connection with JDBC to 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?

推荐答案

保持连接打开一段时间是不好的做法.当数据库打开时间过长时，它会强制关闭.您应该编写 JDBC 代码，以便它始终关闭您在其中获取它们的同一 try 块的 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 的持久连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！