Reuse statement objects

The Pooled Statement feature of OceanBase Connector/J allows applications to reuse PreparedStatement objects in the same way as using Connection objects.

Multiple logical connections can reuse PreparedStatement objects in a transparent manner.

Use pooled statements

An application can call the isPoolable method from the Statement API to check whether the data source supports pooled statements. If true is returned, the application learns that PreparedStatement objects are under preparation. The application can also call the setPoolable method from the Statement API to request statements to be or not to be pooled.

Reusing of a pooled statement must be completely transparent to the application. In other words, regardless of whether the PreparedStatement objects participate in statement pooling, the application code remains the same. If the application closes the PreparedStatement objects, you can call the Connection.prepareStatement method to reuse them.

Close pooled statements

An application can close pooled statements and non-pooled statements in the same way. If a statement is closed, it is no longer available for the application, regardless of whether it has been pooled or not. An error will occur if you attempt to reuse the statement. The only difference between closing a pooled statement and a non-pooled statement lies in that the application cannot directly close a physical statement that is being pooled, which can be closed only by the pool manager. The PooledConnection.closeAll method closes all statements that run on a given physical connection to release the resources associated with the statements.

You can close a pooled statement by using the following methods:

• close

  This java.sql.Statement method can be called by an application. If the statement is being pooled, this method closes the logical statement used by the application, but not the physical statement that is being pooled.

• close

  This java.sql.Connection method can be called by an application. The behavior of this method varies depending on whether the connection using the statement is pooled.

  • Non-pooled connection

    This method closes the physical connection and all statements created by the connection. This is necessary because the garbage collection mechanism cannot detect when externally managed resources can be released.

  • Pooled connection

    This method closes the logical connection and the logical statements it returned, but opens the underlying PooledConnection object and all associated cache statements.

• PooledConnection.closeAll

  The connection pool manager calls this method to close all physical statements that are being pooled for the PooledConnection object.