Database Reference
In-Depth Information
{
pad = resultset.getString("pad");
}
resultset.close();
statement.
close
();
}
To avoid all the hard parses performed by the previous code snippet, you must use the
java.sql.
PreparedStatement
class (or one of its subclasses), which is a subclass of
java.sql.Statement
. 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 defined through a bind variable (defined with a
question mark in Java and called
placeholder
).
sql = "SELECT pad FROM t WHERE val =
?
";
for (int i=0 ; i<10000; i++)
{
statement = connection.
prepareStatement
(sql);
statement.
setInt
(1, i);
resultset = statement.
executeQuery
();
if (resultset.
next
())
{
pad = resultset.getString("pad");
}
resultset.close();
statement.
close
();
}
The next improvement is to avoid the soft parses as well, that is to say, to implement test case 3. As the following
code snippet shows, you can achieve this by moving the code for creating and closing the prepared statement outside
the loop:
sql = "SELECT pad FROM t WHERE val = ?";
statement = connection.
prepareStatement
(sql);
for
(int i=0 ; i<10000; i++)
{
statement.setInt(1, i);
resultset = statement.executeQuery();
if (resultset.next())
{
pad = resultset.getString("pad");
}
resultset.close();
}
statement.
close
();
The Oracle JDBC drivers provide two extensions to support client-side statement caching: implicit and explicit
statement caching. As the names suggest, while the former requires almost no code change, the latter must be
explicitly implemented.
With explicit statement caching, statements are opened and closed by means of Oracle-defined methods. Since
this has a huge impact on the code and, compared to implicit statement caching, it's more difficult to write faster
