Java 使用单个 JDBC Statement 对象执行多个查询

Execute multiple queries using a single JDBC Statement object

提问by Syed Aqeel Ashiq

In JDBC, can I use single Statementobject to call executeQuery("")multiple times? Is it safe? Or should I close the statement object after each query, and create new object for executing another query.

在JDBC中，我可以使用单个Statement对象executeQuery("")多次调用吗？安全吗？或者我应该在每次查询后关闭语句对象，并创建新对象来执行另一个查询。

E.G:

例如：

Connection con;
Statement s;
ResultSet rs;
ResultSet rs2;
try
{
    con = getConnection();
    s = con.prepareStatement();

    try
    {
        rs = s.executeQuery(".......................");

        // process the result set rs
    }
    finally
    {
        close(rs);
    }

    // I know what to do to rs here
    // But I am asking, should I close the Statement s here? Or can I use it again for the next query?

    try
    {
        rs2 = s.executeQuery(".......................");

        // process the result set rs2
    }
    finally
    {
        close(rs2);
    }
}
finally
{
    close(s);
    close(con);
}

回答by Durandal

Yes you can re-use a Statement(specifically a PreparedStatement) and should do so in general with JDBC. It would be inefficient & bad style if you didn't re-use your statement and immediately created another identical Statementobject. As far as closing it, it would be appropriate to close it in a finally block, just as you are in this snippet.

是的，您可以重用 a Statement（特别是 a PreparedStatement）并且通常应该使用 JDBC 这样做。如果您不重复使用您的语句并立即创建另一个相同的Statement对象，那将是低效和糟糕的风格。至于关闭它，最好在 finally 块中关闭它，就像您在此代码段中一样。

For an example of what you're asking check out this link: jOOq Docs

有关您要问的内容的示例，请查看此链接：jOOq Docs

回答by Grmpfhmbl

I can't find anything in the API docsthat would state, that you shouldn't call executeQuery()on a given PreparedStatementinstance more than once.

我在API 文档中找不到任何说明您不应多次调用executeQuery()给定PreparedStatement实例的内容。

However your code does not close the PreparedStatement- a call to executeQuery()would throw a SQLExceptionin that case - but the ResultSetthat is returned by executeQuery(). A ResultSet is automatically closed, when you reexecute a PreparedStatement. Depending on your circumstances you should close it, when you don't need it anymore. I would close it, because i think it's bad style not to do so.

但是，您的代码不会关闭PreparedStatement-在这种情况下调用executeQuery()将抛出 a SQLException- 但ResultSet由executeQuery(). 当您重新执行 .ResultSet 时，ResultSet 会自动关闭PreparedStatement。根据您的情况，您应该在不再需要它时关闭它。我会关闭它，因为我认为不这样做是不好的风格。

UPDATEUpps, I missed your comment between the two try blocks. If you close your PreparedStatement at this point, you shouldn't be able to call executeQuery() again without getting a SQLException.

更新Upps，我错过了您在两个 try 块之间的评论。如果此时关闭 PreparedStatement，则不应该在没有得到 SQLException 的情况下再次调用 executeQuery()。

回答by SnakeDoc

A Prepared Statementtells the database to remember your query and to be prepared to accept parameterized variables to execute in that query. It's a lot like a stored procedure.

APrepared Statement告诉数据库记住您的查询并准备接受参数化变量以在该查询中执行。它很像一个存储过程。

Prepared Statementaccomplishes two main things:

Prepared Statement主要完成两件事：

1. It automatically escapes your query variables to help guard against SQL Injection.

2. It tells the database to remember the query and be ready to take variables.

1. 它会自动转义您的查询变量以帮助防止 SQL 注入。

2. 它告诉数据库记住查询并准备好接受变量。

Number 2 is important because it means the database only has to interpret your query once, and then it has the procedure ready to go. So it improves performance.

数字 2 很重要，因为它意味着数据库只需要解释一次您的查询，然后它就可以准备执行该过程。所以它提高了性能。

You should not close a prepared statement and/or the database connection in between execute calls. Doing so is incredibly in-efficient and it will cause more overhead than using a plain old Statementsince you instruct the database each time to create a procedure and remember it. Even if the database is configured for "hot spots" and remembers your query anyways even if you close the PreparedStatement, you still incur network overhead as well as small processing time.

您不应在执行调用之间关闭准备好的语句和/或数据库连接。这样做是非常低效的，并且会比使用普通的旧方法造成更多的开销，Statement因为您每次都指示数据库创建一个过程并记住它。即使数据库针对“热点”进行了配置，并且即使关闭PreparedStatement.

In short, keep the Connectionand PreparedStatementopen until you are done with them.

简而言之，保持Connection和PreparedStatement打开直到你完成它们。

Edit: To comment on not returning a ResultSetfrom the execution, this is fine. executeQuerywill return the ResultSetfor whatever query just executed.

编辑：要评论不ResultSet从执行中返回 a ，这很好。executeQuery将返回ResultSet刚刚执行的任何查询。

回答by mojiayi

Firstly I am confused about your code

首先我对你的代码感到困惑

s = con.prepareStatement();

Is it work well?I can't find such function in JAVA API,at least one parameter is needed.Maybe you want to invoke this function

效果好吗

s = con.createStatement();

I just ran my code to access DB2 for twice with one single Statement instance without close it between two operation.It's work well.I think you can try it yourself too.

我只是用一个 Statement 实例运行了两次访问 DB2 的代码，而没有在两次操作之间关闭它。它工作得很好。我想你也可以自己尝试一下。

    String sql = "";
    String sql2 = "";
    String driver = "com.ibm.db2.jcc.DB2Driver";
    String url = "jdbc:db2://ip:port/DBNAME";
    String user = "user";
    String password = "password";
    Class.forName(driver).newInstance();
    Connection conn = DriverManager.getConnection(url, user, password);
    Statement statement = conn.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);
    int count = 0;
    while (resultSet.next()) {
        count++;
    }
    System.out.println("Result row count of query number one is: " + count);
    count = 0;
    resultSet = statement.executeQuery(sql2);
    while (resultSet.next()) {
        count++;
    }
    System.out.println("Result row count of query number two is: " + count);

回答by Mark Rotteveel

I am not sure why you are asking. The API design and documentation show it is perfectly fine (and even intended) to reuse a Statementobject for multiple execute, executeUpdateand executeQuerycalls. If it wouldn't be allowed that would be explicitly documented in the Java doc (and likely the API would be different).

我不确定你为什么要问。API 设计和文档表明，Statement为多个execute,executeUpdate和executeQuery调用重用一个对象是完全正确的（甚至有意）。如果不允许，则会在 Java 文档中明确记录（并且 API 可能会有所不同）。

Furthermore the apidoc of Statementsays:

此外，apidocStatement说：

All execution methods in the Statementinterface implicitly close a statment's [sic] current ResultSetobject if an open one exists.

如果存在打开的对象，则Statement接口中的所有执行方法都会隐式关闭语句的 [原文如此] 当前ResultSet对象。

This is an indication that you can use it multiple times.

这表明您可以多次使用它。

TL;DR: Yes, you can call executeon single Statementobject multiple times, as long as you realize that any previously opened ResultSetwill be closed.

TL;DR：是的，您可以多次调用execute单个Statement对象，只要您意识到之前打开的任何对象ResultSet都将关闭。

Your example incorrectly uses PreparedStatement, and you cannot (or: should not) be able to call any of the execute...methods accepting a Stringon a PreparedStatement:

你举的例子错误地使用PreparedStatement，并且你不能（或：不应该）能够调用任何的execute...接受方法String上PreparedStatement：

SQLException- if [...] the method is called on a PreparedStatementor CallableStatement

SQLException- 如果 [...] 方法在一个PreparedStatement或CallableStatement

But to answer for PreparedStatementas well: the whole purpose of a PreparedStatementis to precompile a statement with parameter placeholders and reuse it for multiple executions with different parameter values.

但也要回答PreparedStatement： a 的全部目的PreparedStatement是预编译带有参数占位符的语句，并将其重用于具有不同参数值的多次执行。