Java Reference
In-Depth Information
Chapter 13:
Using PreparedStatements and
CallableStatements
In This Chapter
All of the discussions and examples up to this point have been about how to use the JDBC API to
execute SQL statements. The subject of what actually happens when the SQL statement is passed to
the DBMS has not been considered. The purpose of this chapter is to address two significant ways in
which you can improve the performance of a Java database application by improving the execution
performance of your SQL statements.
One of the main drawbacks of using the basic
java.sql.Statement
is that every time the basic
Statement
object is executed, the SQL command is passed to the RDBMS, where it has to be parsed
and compiled before it can be executed. Most versions of SQL allow the user to define stored
procedures, which are, in effect, precompiled SQL statements or groups of statements. Stored
procedures, being precompiled, execute faster and more efficiently than statements that have to be
parsed and compiled each time they are used.
To eliminate the overhead of repeated parsing and compilation of the SQL command, JDBC provides
the user with two ways of using precompiled SQL statements: the
PreparedStatement
object and the
CallableStatement
object. Using
PreparedStatements
and
CallableStatements
greatly
increases the efficiency of an application when a specific SQL command is executed frequently or
repeatedly, as is the case when handling forms for a Web site.
The three different flavors of the
Statement
object are intended to be used in very different situations.
The first situation arises when you want to execute a statement just once. This is the ideal place to use
a basic java.sql.Statement. If you want to execute a SQL command repeatedly in a loop, and then
discard it, the best approach is to use a
PreparedStatement
, which is parsed, compiled and cached
temporarily by the RDBMS. Finally, if you have a statement or group of statements you want to execute
frequently, the
CallableStatement
is ideal, since it is compiled and stored permanently in the
RDBMS to be called by name when needed.
Creating and Using a PreparedStatement
The main difference between a basic
Statement
object and a
PreparedStatement
object is that
when the
PreparedStatement
is used, the SQL command is sent to the DBMS when the
PreparedStatement
is created, so that it can be precompiled and saved in a cache. This means that
when the
PreparedStatement
is executed, the database management system can run the
PreparedStatement
's SQL statement without having to compile it first.
Using
PreparedStatements
improves efficiency; when you execute the
PreparedStatement
, it is
once again parsed, but no recompile occurs. Instead, the precompiled statement is found in the cache
and is reused. For an application that requires the repeated execution of a SQL command in a loop, the
use of
PreparedStatements
can greatly improve the performance of the database.
PreparedStatement
objects can be used for SQL statements with no parameters or for SQL
statements that take parameters.
PreparedStatements
can contain placeholders for variables known
as IN parameters, which are set using setter methods. The JDBC
PreparedStatement
provides
setter
methods for all SQL data types.
Creating a PreparedStatement Object
PreparedStatements
, like
Statements,
are created using a
Connection
. For example, you can
easily replace the
Statement
object in the LoginBean developed in
Listing 12-9
with a
PreparedStatement,
as shown in
Listing 13-1
.
