JDBC 中的 Connection、Statement 和 ResultSet 应该多久关闭一次? [英] How often should Connection, Statement and ResultSet be closed in JDBC?

问题描述

是否需要在每次查询后关闭并在每次查询开始时进行初始化?

Do they need to be closed after each query and initialized at the beginning of each query?

推荐答案

始终.您需要在尽可能短的范围内获取和关闭它们，以避免资源泄漏、事务问题和耗尽的连接池.不这样做会导致数据库迟早会耗尽资源，从而导致诸如连接过多"之类的异常.

Always. You need to acquire and close them in the shortest possible scope to avoid resource leaking, transactional problems and exhausted connection pools. Not doing so would cause the DB to run out of resources sooner or later, resulting in exceptions like "Too many connections".

正常的 JDBC 习惯用法如下，其中所有资源都在相同的 try-with-resources 块:

The normal JDBC idiom is the following, whereby all resources are opened and closed in the very same try-with-resources block:

public List<Entity> list() throws SQLException {
    List<Entity> entities = new ArrayList<Entity>();

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_LIST);
        ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            entities.add(map(resultSet));
        }
    }

    return entities;
}

或者当您尚未使用 Java 7 时:

Or when you're not on Java 7 yet:

public List<Entity> list() throws SQLException {
    List<Entity> entities = new ArrayList<Entity>();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement(SQL_LIST);
        resultSet = statement.executeQuery();

        while (resultSet.next()) {
            entities.add(map(resultSet));
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return entities;
}

使用 PreparedStatement 将使您受益于语句的数据库缓存(如果使用得当，可以防止 SQL 注入).获取和关闭连接是最昂贵的任务，但是连接池就是为此而发明的.如果您想重复使用相同的语句进行批量插入/更新，那么您可以使用批处理.

Using PreparedStatement will give you the benefit of the DB caching of the statements (next to SQL injection prevention when used properly). Acquiring and closing the connection is the most expensive task, but there the connection pools are invented for. If you want to reuse the same statement to do bulk inserts/updates, then you can use batches.

• 当我的应用失去连接时，我应该如何处理恢复了吗?
• 在多线程系统中使用静态 java.sql.Connection 实例是否安全?

这篇关于JDBC 中的 Connection、Statement 和 ResultSet 应该多久关闭一次?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！