java 多次查询数据库的首选方法？

Preferred way to query a database multiple times?

提问by FThompson

When using JDBC in Java, the generally accepted method of querying a database is to acquire a connection, create a statement from that connection, and then execute a query from that statement.

在 Java 中使用 JDBC 时，普遍接受的查询数据库的方法是获取连接，从该连接创建语句，然后从该语句执行查询。

// load driver
Connection con = DriverManager.getConnection(..);
Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery("SELECT..");
// ...

However, I am unsure of how to treat a second query to the same database.

但是，我不确定如何处理对同一数据库的第二个查询。

1. Can another query be executed safely on the same Statementobject, or must another statement be created from the Connectionobject in order to execute another query?

2. If the same Statementobject can be used for multiple queries, what is the purpose of the Statementclass (since it would then make more sense for a Connection.executeQuery()method to exist)?

1. 是否可以在同一个Statement对象上安全地执行另一个查询，或者必须从该Connection对象创建另一个语句才能执行另一个查询？

2. 如果同一个Statement对象可以用于多个查询，那么Statement类的目的是什么（因为如果Connection.executeQuery()存在一个方法会更有意义）？

采纳答案by Alex

Yes you can reuse the Statementobject, but the ResultSetobjects returned by the executeQuerycloses already opened resultsets.

是的，您可以重用该Statement对象，但关闭已打开的结果集ResultSet返回的对象executeQuery。

See the javadocfor the explanation

有关解释，请参阅javadoc

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.

默认情况下，每个 Statement 对象只能同时打开一个 ResultSet 对象。因此，如果一个 ResultSet 对象的读取与另一个的读取交错，则每个对象都必须由不同的 Statement 对象生成。如果存在打开的对象，则 Statement 接口中的所有执行方法都会隐式关闭语句的当前 ResultSet 对象。

So the following occurs:

所以会发生以下情况：

// load driver
Connection con = DriverManager.getConnection(..);
Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery("select ..");
// do something with result ... or not
ResultSet result2 = stmt.executeQuery("select ...");
// result is now closed, you cannot read from it anymore
// do something with result2
stmt.close(); // will close the resultset bound to it

For example you can find an open source implementation of Statement in the jTDS project. In the Statement.executeQuery() methodyou can see a call to initialize()that closes all the resultsetsalready opened

例如，您可以在 jTDS 项目中找到 Statement 的开源实现。在Statement.executeQuery的（）方法，你可以看到一个电话initialize()是关闭所有的结果集已经打开

protected void initialize() throws SQLException {
    updateCount = -1;
    resultQueue.clear();
    genKeyResultSet = null;
    tds.clearResponseQueue();
    // FIXME Should old exceptions found now be thrown instead of lost?
    messages.exceptions = null;
    messages.clearWarnings();
    closeAllResultSets();
}

回答by srini.venigalla

Programmatically, you can reuse the same connection and the same statement for more than one query and close the statement and the connection at the end.

以编程方式，您可以对多个查询重复使用相同的连接和相同的语句，并在最后关闭语句和连接。

However, this is not a good practice. Application performance is very sensitive to the way database is accessed. Ideally, each connection should be open for the least amount of time possible. Then, the connections must be pooled. Going by that, you would enclose each query in a block of {open connection, create a prepared statement, run query, close statement, close connection}. This is also the way most SQL Templates are implemented. If concurrency permits, you can fire several such queries at the same time using a thread pool.

然而，这不是一个好的做法。应用程序性能对访问数据库的方式非常敏感。理想情况下，每个连接都应该在尽可能短的时间内打开。然后，必须汇集连接。这样做，您会将每个查询包含在{open connection, create a prepared statement, run query, close statement, close connection}. 这也是大多数 SQL 模板的实现方式。如果并发允许，您可以使用线程池同时触发多个此类查询。

回答by Sebastian

I have one thing to add should you use Connection and Statement in a threaded environment. My experience shows that stmt.executeQuery(..) is save to use in a parallel environment but with the consequence that each query is serialized and thus processed sequencially, not yielding any speed-ups. So it es better to use a new Connection (not Statement) for every thread.

如果您在线程环境中使用 Connection 和 Statement，我要添加一件事。我的经验表明 stmt.executeQuery(..) 可以保存在并行环境中使用，但结果是每个查询都被序列化并按顺序处理，不会产生任何加速。所以最好为每个线程使用一个新的 Connection（而不是 Statement）。

For a standard sequential environment my experience has shown that reusing Statements is no problem at all and ResultSets need not be closed manually.

对于标准的顺序环境，我的经验表明重用 Statements 完全没有问题，并且不需要手动关闭 ResultSets。

回答by Bhesh Gurung

Usually, it's one statement for one query. It might not be necessary to do that but when writing real application, you don't want to repeat those same steps again and again. That's against the DRY principal, plus it also will get more complicated as the application grows.

通常，它是一个查询的一个语句。可能没有必要这样做，但是在编写实际应用程序时，您不想一次又一次地重复相同的步骤。这违背了 DRY 原则，而且随着应用程序的增长，它也会变得更加复杂。

It's good to write objects that will handle that kind of low level (repetitive) stuffs, and provide different methods to access db by providing the queries.

编写对象来处理那种低级（重复）的东西，并通过提供查询来提供不同的方法来访问数据库是很好的。

回答by Udo Held

I wouldn't worry about creating new statements. However opening up a database connection may be resource intensive and opening and closing connections does impact performance.

我不会担心创建新语句。但是，打开数据库连接可能会占用大量资源，并且打开和关闭连接确实会影响性能。

Leaving up connections in some self management way usually is pretty bad.

以某种自我管理的方式放弃连接通常是非常糟糕的。

You should consider using connection pooling. You usually issue a close commando however you are only giving that connection back to the pool. When you request a new connection then it will reuse the connection you gave back earlier.

您应该考虑使用连接池。您通常会发出一个关闭的突击队，但是您只是将该连接返回到池中。当您请求新连接时，它将重用您之前返回的连接。

You may want to have different statements for one connection. Statement is an implementation and an interface. Depending on what you need you sometimes want a use a CallableStatment. Some some logic may be reused when required.

您可能希望对一个连接使用不同的语句。语句是一个实现和一个接口。根据您的需要，您有时需要使用CallableStatment。一些逻辑可以在需要时重用。

回答by Bhanu Kaushik

Well that's why we have the concept of classes in object oriented programming . A class defines constituent members which enable its instances to have state and behavior. Here statement deals with everything related to an sql statement. There are so many more function that one might perform like batch queries etc.

嗯，这就是为什么我们在面向对象编程中有类的概念。类定义了组成成员，使其实例具有状态和行为。Here 语句处理与 sql 语句相关的所有内容。还有很多功能可以执行，例如批量查询等。