| Contents | Prev | Next | JDBCTMGuide: 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 an SQL type because the
driver will map the Java type to its corresponding SQL type (following the mapping
specified in the table in Section 9.5.2 of "Mapping Java and SQL Types" in this
JDBC Guide) and send that SQL 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 an SQL
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 an SQL type that is compatible with the SQL data type expected by the database. Consider the case where the database expects an SQL SMALLINT. If the method setByte is used, the driver will send an SQL 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 SQL types expected by the database. If the expected SQL 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 SQL type. The driver will convert the Java Object to the specified SQL type before sending it to the database.
If no SQL type is given, the driver will simply map the Java Object to its default SQL type (using the table in Section 9.5.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 SQL type before sending it to the database. The difference is that the setXXX methods use the standard mapping from Java types to SQL types (see the table in Section 9.5.2), whereas the setObject method uses the mapping from Java Object types to SQL types (see the table in Section 9.5.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 SQL type expected by the database. The table in Section 9.5.5 shows all the possible conversions that setObject can perform.
setNull method allows a programmer to send an SQL NULL value to the database as an IN parameter. Note, however, that one must still specify the SQL type of
the parameter.
An SQL 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 SQL 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.