JDK 5 Documentation v1.2.2, Java 2 SDK 英文文档

JDBC^TM Guide: Getting Started

6 - PreparedStatement

This overview is excerpted from JDBC^TM Database Access from Java^TM: A Tutorial and Annotated Reference, currently in progress at JavaSoft. This book, both a tutorial and the definitive reference manual for JDBC, will be published in the spring of 1997 by Addison-Wesley Publishing Company as part of the Java series.

6.1 Overview

The PreparedStatement interface inherits from Statement and differs from it in two ways:

Instances of PreparedStatement contain an SQL statement that has already been compiled. This is what makes a statement "prepared."
The SQL statement contained in a 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.

Because 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.

6.1.1 Creating PreparedStatement Objects

The following code fragment, where 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.

6.1.2 Passing IN Parameters

Before a 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();
    }

6.1.3 Data Type Conformance on IN Parameters

The 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.

6.1.4 Using setObject

A programmer can explicitly convert an input parameter to a particular JDBC type by using the method 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.

6.1.5 Sending JDBC NULL as an IN parameter

The 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.

6.1.6 Sending Very Large IN Parameters

The methods 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.

Contents | Prev | Next

jdbc@wombat.eng.sun.com or jdbc-odbc@wombat.eng.sun.com