Contents | Prev | Next | JDBCTM Guide: Getting Started

# 6 - PreparedStatement

This overview is excerpted from JDBCTM Database Access from JavaTM: 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:

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

Copyright © 1996, 1997 Sun Microsystems, Inc. All rights reserved.