Database Reference
In-Depth Information
With implicit statement caching, prepared statements are added to the cache when the
close
method is called.
Then, when a new prepared statement is instantiated through the
prepareStatement
method, the cache is checked to
find out whether a cursor with the same text is already present in it.
■
Only classes implementing the
java.sql.PreparedStatement
and
java.sql.CallableStatement
interfaces support implicit statement caching. in other words, plain statements (based on
java.sql.Statement
) don't
support implicit statement caching.
Note
The following lines of code show how implicit statement caching is enabled at the connection level. Be careful:
setting the size of the cache to a value greater than 0 is a requirement. The casts are necessary because both methods
are Oracle extensions.
((oracle.jdbc.OracleConnection)connection).setImplicitCachingEnabled(true);
((oracle.jdbc.OracleConnection)connection).setStatementCacheSize(50);
Another way to enable implicit statement caching is through the
setImplicitCachingEnabled
and
setMaxStatements
methods of the
OracleDataSource
class. Note that the
setMaxStatements
method is deprecated, though.
By default, all prepared statements are cached with implicit statement caching. When the cache is full, the least
recently used one is closed and replaced by a new one. If necessary, the caching of a specific statement can also be
disabled. The following line of code shows how to do it:
((oracle.jdbc.OraclePreparedStatement)statement).setDisableStmtCaching(true);
The Java code used for the examples in this section is an excerpt from the
ParsingTest1.java
,
ParsingTest2.java
, and
ParsingTest3.java
files that implement test case 1, 2, and 3, respectively.
ODP.NET
ODP.NET provides little control over the life cycle of a cursor. In the following code snippet that implements test case
1, the
ExecuteReader
method triggers parse, execute, and fetch calls at the same time:
sql = "SELECT pad FROM t WHERE val = ";
command = new OracleCommand(sql, connection);
for (int i = 0; i < 10000; i++)
{
command.CommandText = sql + i;
reader = command.
ExecuteReader
();
if (reader.Read())
{
pad = reader[0].ToString();
}
reader.Close();
}
To avoid all the hard parses performed by the previous code snippet, the
OracleParameter
class has to be used for
passing parameters (bind variables). The following code snippet shows how to use it to implement test case 2. Notice
how the value used for the lookup, instead of being concatenated to the
sql
variable (as in the previous example), is
