Prepared statements with connection pool

I have a question regarding the general use of a prepared statement along with the connection pool.

Prepared statements are usually associated with only one connection. In our application, PreparedStatement is created at startup and executed later.

If, during the execution of any particular prepared statement, the connection associated with the prepared statement is busy executing other statements than how this required statement will be executed. Is it proved that this operator will wait for a connection in order to get a free one, or will this operator be a given preference in execution?

Update

I checked this by executing the SLEEP () function with the Apache derby database, which calls the java sleep function in the TimeHandlingTest class.

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

And he made two prepared statements from one connection and called the Sleep () function from one prepared statement and simply select sql with another. The approximate sql selection took almost the same time (10 seconds) for which the first prepared statement was asleep. This means that a single connection object cannot be used to be executed by more than one prepared statement at a time. Please correct me if I am wrong.

+7
source share
5 answers

You cannot return Connection to the pool if you plan to use PreparedStatement .

In other words: you can only use PreparedStatement built from Connection , which you have.

+8
source

The value of PreparedStatement lies in the ability of the database itself to create an execution plan for the operator, which can be reused for arbitrary parameters and, thus, general (of course, this requires you to use the parameters in their status, for example

 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 must use string concatenation to insert parameter values ​​into SQL code, the database cannot create a common execution plan. This way, it doesn't matter if you use PreparedStatement or Statement, for example.

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

will not be able to take advantage of PreparedStatements.

Your question implies that you want to reuse the PreparedStatement object, which is optional. Of course, if you can use the PreparedStatement object to update multiple values, etc., this is a more efficient use of ressources resources. However, the service life (or at least the useful life) of the PreparedStatement is bound to the connection, so if you call conn.close (), the PreparedStatement is useless. However, most good drivers in a merge situation reuse the same PreparedStatement objects. In short, do not cache PreparedStatement regardless of connection.

+4
source

Assuming this is a multi-threaded application, Connection objects are usually associated with a single thread at any given time. Connection objects received by the stream do not return to the pool until they are closed. This applies both to the wrapper of the logical connection (usually returned by this source, managed by the application server), and to the physical connection. In addition, physical connections can be split between multiple logical connections if they are part of the same transaction.

This means that if the logical connection operator is returned to your application, there is no need for the underlying physical connection to be the same and it is considered (if it is not part of the same transaction). If your application should handle concurrent users without any problems, a Connection object will be created in each thread that starts the transaction, and this object will not be discussed for threads. Under the hood, various physical connections in the pool will execute SQL queries related to prepared statements across multiple threads, without any disagreement.

+2
source

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

+1
source

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

0
source

All Articles