在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习惯用法如下，所有资源都以和 docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.htmlrel =nofollow noreferrer> 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实例是否安全？

• When my app loses connection, how should I recover it?
• Is it safe to use a static java.sql.Connection instance in a multithreaded system?

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