java 准备好的语句和连接池

Prepared Statements along with Connection Pooling

提问by Amit Verma

I have a question regarding general use of Prepared Statement along with connection pooling.

我有一个关于 Prepared Statement 和连接池的一般使用的问题。

Prepared Statements are generally tied to one connection only.In our application, a PreparedStatement is created at the start up and executed at a later point.

PreparedStatement 通常只绑定到一个连接。在我们的应用程序中，PreparedStatement 在启动时创建并在稍后执行。

If at the time of execution of some specific prepared statement, connection associated with the prepared statement is busy executing other statements than how this required statement will get executed.Does this statement will wait for connection to get free or this statement will be given preference in execution?

如果在执行某些特定的预处理语句时，与预处理语句关联的连接正忙于执行其他语句，而不是执行此所需语句的方式。此语句将等待连接释放还是优先执行此语句执行？

Update

更新

I have tested this by following SLEEP() function with Apache derby database which calls java function sleep in class TimeHandlingTest.

我已经通过在 Apache derby 数据库中跟踪 SLEEP() 函数来对此进行测试，该数据库在 TimeHandlingTest 类中调用 java 函数 sleep。

CREATE FUNCTION SLEEP() RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL EXTERNAL NAME 'com.derby.test.TimeHandlingTest.sleep';

创建函数 SLEEP() 返回整数语言 JAVA 参数样式 JAVA 无 SQL 外部名称 'com.derby.test.TimeHandlingTest.sleep';

And made two prepared statements from one connection and called Sleep() function from one prepared statement and simple sql select with other.Simple sql select took almost same time(10s) for which first prepared statement was sleeping.This means that one connection object cannot be used for execution by more than one prepared statement at a time.Please correct me if i am wrong.

并从一个连接生成两个准备好的语句，并从一个准备好的语句和简单的 sql select 调用 Sleep() 函数与另一个。简单的 sql select 花费的时间几乎与第一个准备好的语句休眠的时间相同（10 秒）。这意味着一个连接对象不能一次用于执行多个准备好的语句。如果我错了，请纠正我。

回答by Joachim Sauer

You can't return the Connectionto the pool if you plan on using the PreparedStatement.

你不能返回Connection，如果你打算使用的游泳池PreparedStatement。

In other words: you can only use a PreparedStatementconstructed from a Connectionthat you currently have.

换句话说：您只能使用PreparedStatement从Connection您当前拥有的a构造的 a 。

回答by Chris

The value of PreparedStatement lies in the ability of the database itself to create an execution plan for the statement that can be reused for arbitrary parameters and thus is generic in nature (of course that requires that you use parameters in your statetment, e.g.

PreparedStatement 的价值在于数据库本身能够为语句创建一个可以重用于任意参数的执行计划，因此本质上是通用的（当然这需要您在语句中使用参数，例如

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
                               SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00);
pstmt.setInt(2, 110592);

If on the other hand you would use string concatenation to paste the parameter values into the SQL code, the database would not be able to build a generic execution plan. Thus it would make no difference if you use a PreparedStatement or Statement, e.g.

另一方面，如果您使用字符串连接将参数值粘贴到 SQL 代码中，则数据库将无法构建通用执行计划。因此，如果您使用 PreparedStatement 或 Statement 没有区别，例如

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
                               SET SALARY = 1200 WHERE ID = 3");

would not be able to use the advantage of PreparedStatements.

将无法利用 PreparedStatements 的优势。

Your question implies, that you want to reuse the PreparedStatement object, which is not necessary. Of course if you can use a PreparedStatement object to update multiple values etc. it is a more efficient use of ressources. Nevertheless the lifespan (or at least usefull lifespan) of the PreparedStatement is tied to the Connection, thus if you call conn.close() the PreparedStatement is rendered useless. Nevertheless most good drivers in a pooling situation reuse the same PreparedStatement objects again. In short, don't cache the PreparedStatement independent of the connection.

您的问题暗示您要重用 PreparedStatement 对象，这是不必要的。当然，如果您可以使用 PreparedStatement 对象来更新多个值等，则可以更有效地利用资源。尽管如此， PreparedStatement 的生命周期（或至少有用的生命周期）与 Connection 相关联，因此如果您调用 conn.close() ， PreparedStatement 变得无用。尽管如此，池化情况下的大多数优秀驱动程序都会再次重用相同的 PreparedStatement 对象。简而言之，不要缓存独立于连接的 PreparedStatement。

回答by Vineet Reynolds

Assuming that this is a multi-threaded application, Connectionobjects are typically associated with a single thread at any instant of time. Connectionobjects acquired by a thread are not returned to the pool until they are closed. This applies both to the logical connection wrapper (that is typically returned by a DataSource managed by an application server) to the application, as well as to the physical connection. Also, physical connections can be shared across multiple logical connections as long as they are part of the same transaction.

假设这是一个多线程应用程序，Connection对象通常在任何时刻都与单个线程相关联。Connection线程获取的对象在关闭之前不会返回到池中。这既适用于应用程序的逻辑连接包装器（通常由应用程序服务器管理的数据源返回），也适用于物理连接。此外，物理连接可以在多个逻辑连接之间共享，只要它们是同一事务的一部分。

This means that if a logical connection handle is returned to your application, it is not necessary that the underlying physical connection is the same and is being contended for (unless it is part of the same transaction). If your application is expected to handle concurrent users without any hassle, a Connectionobject would be created in every thread starting a transaction, and this object would not be contended for, across threads. Under the hood, different physical connections in the pool would be executing the SQL queries associated with the prepared statements, across multiple threads, again without any contention.

这意味着如果逻辑连接句柄返回给您的应用程序，则底层物理连接不必相同并且正在竞争（除非它是同一事务的一部分）。如果希望您的应用程序能够轻松处理并发用户，Connection则将在启动事务的每个线程中创建一个对象，并且不会跨线程争用该对象。在幕后，池中的不同物理连接将跨多个线程执行与准备好的语句关联的 SQL 查询，同样没有任何争用。

回答by DaveH

This sounds like an unusual way to use your connection pool. Even though the connections are in a pool, they should only be used by one thread at a time. I tend to create the prepared statement and use it very close to the point of creation. Also, some JDBC drivers now support Statement caching which reduces the overhead of using it in this way.

这听起来像是使用连接池的一种不同寻常的方式。即使连接在一个池中，它们也应该一次只被一个线程使用。我倾向于创建准备好的语句并在非常接近创建点的地方使用它。此外，一些 JDBC 驱动程序现在支持语句缓存，这减少了以这种方式使用它的开销。

回答by Vijay Mathew

One way around this is to maintain a cache where connections are mapped to prepared statements. When you get a connection from the pool check if it is mapped to the prepared statement that is to be executed. If not, pass the prepared statement to the JDBC driver so that it is compiled. Then map it to the connection. The downside of this approach is that more than one connection might get copies of the same prepared statement. But it seems that this is what some J2EE servers do.

解决此问题的一种方法是维护一个缓存，其中将连接映射到准备好的语句。当您从池中获取连接时，请检查它是否映射到要执行的准备好的语句。如果没有，则将准备好的语句传递给 JDBC 驱动程序，以便对其进行编译。然后将其映射到连接。这种方法的缺点是多个连接可能会获得同一个准备好的语句的副本。但是，这似乎是某些 J2EE 服务器所做的。