连续PreparedStatement良好做法 [英] Consecutive PreparedStatement good practice

问题描述

我正在连续执行一些 SELECT ，我想知道我应该如何处理 PreparedStatement s。

I'm executing a few SELECTs in a row and I'm wondering how I should handle the PreparedStatements.

示例代码：

//Connection conn is already declared
PreparedStatement pstmt = null;
ResultSet rset = null;
try {
  String sql = "SELECT ...";
  pstmt = conn.prepareStatement(sql);
  pstmt.setString(1, someVar);

  rset = pstmt.executeQuery();
  // Use ResultSet

  // A different query
  sql = "SELECT ...";
  pstmt = conn.prepareStatement(sql);
  pstmt.setString(1, someVar);

  rset = pstmt.executeQuery();
  // Use ResultSet
} catch (SQLException e) {
  // Handle
} finally {
  if (rset != null)
    rset.close();
  if (pstmt != null)
    pstmt.close();
  if (conn != null)
    conn.close();
}

现在的问题是，关闭<$ c $会更好吗？ c> PreparedStatement s在每次使用/使用不同的语句之后还是没有区别？

Now the question is, would it be better to close the PreparedStatements after each usage/use different statements or would it make absolutely no difference?

我发现了一些关于重用a的信息 PreparedStatement 总是有相同的查询，但我不确定使用不同的查询。

I've found some information about reusing a PreparedStatement that always has the same query but I'm not sure about using different queries.

推荐答案

您没有使用相同的 PreparedStatement ，工厂方法 Connection.prepareStatement 正在返回给您每次调用它时都有一个新实例。 PreparedStatement.executeQuery 对 ResultSet 执行相同的操作。你只是使用相同的变量。

You're not using the same PreparedStatement, the factory method Connection.prepareStatement is returning you a new instance each time you call it. PreparedStatement.executeQuery is doing the same with ResultSet. You are just using the same variables.

这意味着你正在泄漏资源 - 第一个 PreparedStatement 和 ResultSet - 每次调用此方法时都不会被关闭。

This means you're leaking resources - the first PreparedStatement and ResultSet - every time this method is called, which are never being closed.

我的建议是使用Spring的 JdbcTemplate 它将正确处理这些数据库资源，并将代码分解为两种方法。

My recommendation would be to use Spring's JdbcTemplate which will handle these database resources correctly for you and you break your code into two methods.

这篇关于连续PreparedStatement良好做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！