java - 我是否应该在每次调用 execute() 后关闭并创建一个新语句？

Question

如果我使用 JDBC 创建一个语句并执行一个查询，我是否需要关闭该语句并在再次执行之前创建一个新语句？ Eclipse 不会提示第二种情况。

try {
        connection = dataSource.getConnection();

        try {
            statement = connection.createStatement();
            statement.execute("set search_path to '...'");
        } finally {
            Utils.tryClose(statement);
        }

        try {
            statement = connection.createStatement();
            statement.execute("SET statement_timeout TO " + (QUERY_TIMEOUT_SECONDS * 1000));
        } finally {
            Utils.tryClose(statement);
        }

        try {
            statement = connection.createStatement();
            statement.execute(query);
        } finally {
            Utils.tryClose(statement);
        }
} finally {
    Utils.tryClose(connection);
}

相对于:

try {
    connection = dataSource.getConnection();

    statement = connection.createStatement();
    statement.execute("set search_path to '...'");
    statement.execute("SET statement_timeout TO " + (QUERY_TIMEOUT_SECONDS * 1000));
    statement.execute(query);
} finally {
    Utils.tryClose(statement);
    Utils.tryClose(connection);
}

Answer 1

这不是必需的，您可以使用相同的语句多次查询数据库，唯一要记住的是，每个语句执行返回的结果集都将在创建新的 statemnet 后关闭。引自 java docs :-

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

因此你可以这样做:-

try {
     connection = dataSource.getConnection();

     statement = connection.createStatement();
     ResultSet rs1=statement.execute("....");

     //parse rs1
     //close rs1

     ResultSet rs2= statement.execute(....);
     //parse rs1
     //close rs1

  } finally {
    Utils.tryClose(statement);
    Utils.tryClose(connection);
  }

我不确定为什么 eclipse 在 PreparedStatements 的情况下会报错，PreparedStatements 的全部目的是定义一个查询结构并通过仅更改来多次执行查询参数。例如，当您要解析大型文本文件并将其插入数据库时​​。引自 javadocs

If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead.