| Contents | Prev | Next | JDBCTM Guide: Getting Started |
PreparedStatement interface inherits from Statement and differs from it in
two ways:
PreparedStatement contain an SQL statement that has already
been compiled. This is what makes a statement "prepared."
PreparedStatement object may have one or
more IN parameters. An IN parameter is a parameter whose value is not specified when the SQL statement is created. Instead the statement has a question
mark ("?") as a placeholder for each IN parameter. A value for each question
mark must be supplied by the appropriate setXXX method before the statement
is executed.
PreparedStatement objects are precompiled, their execution can be faster
than that of Statement objects. Consequently, an SQL statement that is executed
many times is often created as a PreparedStatement object to increase efficiency.
Being a subclass of Statement, PreparedStatement inherits all the functionality of Statement. In addition, it adds a whole set of methods which are needed
for setting the values to be sent to the database in place of the placeholders for IN
parameters. Also, the three methods execute, executeQuery, and executeUpdate
are modified so that they take no argument. The Statement forms of these methods (the forms that take an SQL statement parameter) should never be used with a
PreparedStatement object.
con is a Connection object, creates a PreparedStatement object containing an SQL statement with two placeholders for IN
parameters:
PreparedStatement pstmt = con.prepareStatement(
"UPDATE table4 SET m = ? WHERE x = ?");
The object pstmt now contains the statement "UPDATE table4 SET m = ?
WHERE x = ?", which has already been sent to the DBMS and been prepared for
execution.
PreparedStatement object is executed, the value of each ? parameter
must be set. This is done by calling a setXXX method, where XXX is the appropriate type for the parameter. For example, if the parameter has a Java type of long,
the method to use is setLong. The first argument to the setXXX methods is the
ordinal position of the parameter to be set, and the second argument is the value to
which the parameter is to be set. For example, the following code sets the first
parameter to 123456789 and the second parameter to 100000000:
pstmt.setLong(1, 123456789);
pstmt.setLong(2, 100000000);
Once a parameter value has been set for a given statement, it can be used for
multiple executions of that statement until it is cleared by a call to the method
clearParameters.
In the default mode for a connection (auto-commit enabled), each statement is commited or rolled back automatically when it is completed.
The same PreparedStatement object may be executed multiple times if the
underlying database and driver will keep statements open after they have been
committed. Unless this is the case, however, there is no point in trying to improve
performance by using a PreparedStatement object in place of a Statement object.
Using pstmt, the PreparedStatement object created above, the following code
illustrates setting values for the two parameter placeholders and executing pstmt
10 times. As stated above, for this to work, the database must not close pstmt. In
this example, the first parameter is set to "Hi" and remains constant. The second
parameter is set to a different value each time around the for loop, starting with 0
and ending with 9.
pstmt.setString(1, "Hi");
for (int i = 0; i < 10; i++) {
pstmt.setInt(2, i);
int rowCount = pstmt.executeUpdate();
}
XXX in a setXXX method is a Java type. It is implicitly a JDBC type (a generic
SQL type) because the driver will map the Java type to its corresponding JDBC type
(following the mapping specified in the table in Section 8.6.2 of "Mapping Java and
JDBC Types" in this JDBC Guide) and send that JDBC type to the database. For
example, the following code fragment sets the second parameter of the PreparedStatement object pstmt to 44, with a Java type of short:
pstmt.setShort(2, 44);
The driver will send 44 to the database as a JDBC SMALLINT, which is the standard
mapping from a Java short.
It is the programmer's responsibility to make sure that the Java type of each
IN parameter maps to a JDBC type that is compatible with the JDBC data type
expected by the database. Consider the case where the database expects a JDBC
SMALLINT. If the method setByte is used, the driver will send a JDBC TINYINT to
the database. This will probably work because many databases convert from one
related type to another, and generally a TINYINT can be used anywhere a SMALLINT
is used. However, for an application to work with the most databases possible, it
is best to use Java types that correspond to the exact JDBC types expected by the
database. If the expected JDBC type is SMALLINT, using setShort instead of setByte will make an application more portable.
setObject. This method can take a third argument,
which specifies the target JDBC type. The driver will convert the Java Object to
the specified JDBC type before sending it to the database.
If no JDBC type is given, the driver will simply map the Java Object to its
default JDBC type (using the table in Section 8.6.4) and then send it to the database. This is similar to what happens with the regular setXXX methods; in both
cases, the driver maps the Java type of the value to the appropriate JDBC type
before sending it to the database. The difference is that the setXXX methods use
the standard mapping from Java types to JDBC types (see the table in Section
8.6.2), whereas the setObject method uses the mapping from Java Object types
to JDBC types (see the table in Section 8.6.4).
The capability of the method setObject to accept any Java object allows an
application to be generic and accept input for a parameter at run time. In this situation the type of the input is not known when the application is compiled. By
using setObject, the application can accept any Java object type as input and convert it to the JDBC type expected by the database. The table in Section 8.6.5
shows all the possible conversions that setObject can perform.
setNull method allows a programmer to send a JDBC NULL value to the database as an IN parameter. Note, however, that one must still specify the JDBC type
of the parameter.
A JDBC NULL will also be sent to the database when a Java null value is
passed to a setXXX method (if it takes Java objects as arguments). The method
setObject, however, can take a null value only if the JDBC type is specified.
setBytes and setString are capable of sending unlimited amounts of
data. Sometimes, however, programmers prefer to pass in large blobs of data in
smaller chunks. This can be accomplished by setting an IN parameter to a Java
input stream. When the statement is executed, the JDBC driver will make repeated
calls to this input stream, reading its contents and transmitting those contents as the
actual parameter data.
JDBC provides three methods for setting IN parameters to input streams:
setBinaryStream for streams containing uninterpreted bytes, setAsciiStream for
streams containing ASCII characters, and setUnicodeStream for streams containing Unicode characters. These methods take one more argument than the other
setXXX methods because the total length of the stream must be specified. This is
necessary because some databases need to know the total transfer size before any
data is sent.
The following code illustrates using a stream to send the contents of a file as an IN parameter:
java.io.File file = new java.io.File("/tmp/data");
int fileLength = file.length();
java.io.InputStream fin = new java.io.FileInputStream(file);
java.sql.PreparedStatement pstmt = con.prepareStatement(
"UPDATE Table5 SET stuff = ? WHERE index = 4");
pstmt.setBinaryStream (1, fin, fileLength);
pstmt.executeUpdate();
When the statement executes, the input stream fin will get called repeatedly
to deliver up its data.