[Top] [Prev] [Next]

## PreparedStatement

NOTE: The material in this chapter is based on JDBCtm API Tutorial and Reference, Second Edition: Universal Data Access for the Javatm 2 Platform, published by Addison Wesley as part of the Java series, ISBN 0-201-43328-1.

### 6.1 PreparedStatement 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. The "?" is also known as a parameter marker. An application must set a value for each question mark in a prepared statement before executing the prepared statement.

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 set of methods that 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 update 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.

As with `Statement` objects, it is possible to create a `PreparedStatement` object that contains a query rather than an update statement; in fact, this is often done to improve efficiency for SQL statements that are executed many times. Using the new version of the method `prepareStatement` included in the JDBC 2.0 core API, the `PreparedStatement` object can produce `ResultSet` objects that are scrollable and updatable. For example, the following code fragment creates a `PreparedStatement` object such that each time it is executed, it will produce a `ResultSet` object that is scrollable and updatable.

>     PreparedStatement pstmt2 = con.prepareStatement(
>                 "SELECT a, b, c FROM Table1", ResultSet.TYPE_SCROLL_SENSITIVE,
>                 ResultSet.CONCUR_UPDATABLE);
>     ResultSet rs = pstmt2.executeQuery();

The object that rs represents is a result set with all the values stored in columns `a`, `b`, and `c` of `Table1`, and rs is scrollable and can be updated. Each time pstmt2 is executed, it will produce a result set that is scrollable and updatable.

#### 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 is of type `long` in the Java programming language, the method to use is `setLong`. The first argument to the `setXXX` methods is the ordinal position of the parameter to be set, with numbering starting at 1. 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` or until a new value is set.

When a connection has its auto-commit mode enabled, each statement is committed automatically when it is completed. Some database systems do not retain prepared statements across commits, so for them, the driver will have to recompile the prepared statement after each commit. This means that for these DBMSs, it may actually be less efficient to use a `PreparedStatement` object in place of a `Statement` object that is executed many times.

Using pstmt, the `PreparedStatement` object created above, the following code illustrates setting values for the two parameter placeholders and executing pstmt 10 times. 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();
>     }

New features in the JDBC 2.0 API make it possible to set a parameter placeholder with an SQL3 data type, as shown in the following example, where statistics is a `Blob` object representing an SQL `BLOB` value, and departments is an `Array` object representing an SQL `ARRAY` value.

>     PreparedStatement pstmt = con.prepareStatement(
>           "UPDATE Table3 SET Stats = ? WHERE Depts = ?");
>     pstmt.setBlob(1, statistics);
>     pstmt.setArray(2, departments);

#### 6.1.3 Data Type Conformance on IN Parameters

The `XXX` in a `setXXX` method is a type in the Java programming language. It also implicitly specifies a JDBC type because the driver will map the Java type to its corresponding JDBC type (following the mapping specified in "Java Types Mapped to JDBC Types" on page 106) 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 type in the Java programming language for 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 database systems 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 database systems possible, it is best to use types in the Java programming language 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. The table "Java Types Mapped to JDBC Types" in the chapter "Mapping SQL and Java Types" can be used to determine which `setXXX` method to use.

#### 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 `Object` in the Java programming language 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 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, whereas the `setObject` method uses the mapping to object types.

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 JDBC 2.0 core API includes a new implementation of the method `setObject` that applies to a user-defined type (UDT) that has been custom mapped to a class in the Java programming language. The custom mapping of an SQL UDT is specified in a class that implements the `SQLData` interface. When a UDT instance is retrieved from the database via the method `getObject`, it will be mapped to an instance of the Java class that implemented `SQLData` for it. When that custom mapped instance is passed to the method `setObject`, `setObject` will call the `SQLOutput.writeObject` method that is defined in the appropriate `SQLData` implementation, thereby converting the instance of a Java class back to an SQL UDT.

The details of custom mapping are hidden from the user. When an application invokes the method `setObject`, the value being stored will automatically be custom mapped if there is a custom mapping for it. As a result, code in which the method `setObject` performs a custom mapping looks identical to code in which `setObject` uses the standard mapping. UDTs can only be stored using the `setObject` method, which is a way of ensuring that UDTs with a custom mapping are mapped appropriately.

In all of the cases discussed so far, the value passed to the method `setObject` was originally an SQL data type that was retrieved from a table column. Before returning it to the database, the driver needed to convert it back to its SQL data type. If a database is one of the new generation of Java-aware DBMSs, called a Java relational DBMS, it can store an instance of a class defined in the Java programming language as well as values defined in SQL. A class instance may be stored as a serialized Java object or in some other format defined by the DBMS.

The following example shows the use of the method `setObject` to store emp, an instance of the class `Employee`. After the salary field of emp is increased by 50 per cent, emp is sent back to the database. The column `EMPLOYEE` in the table `PERSONNEL` stores instances of `Employee`.

>     emp.salary = emp.salary * 1.5;
>     PreparedStatement pstmt = con.prepareStatement(
>     	"UPDATE PERSONNEL SET EMPLOYEE = ? WHERE EMPLOYEE_NO = 300485");
>     pstmt.setObject(1, emp);
>     pstmt.executeUpdate();

Note that the syntax in this example is the same as that in the JDBC 1.0 API and is also the same as that used to store instances of UDTs that have been custom mapped.

#### 6.1.5 Sending JDBC NULL as an IN parameter

The `setNull` method allows a programmer to send a JDBC `NULL` (a generic SQL `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.

The JDBC 1.0 API provides two methods for setting IN parameters to input streams: `setBinaryStream` for streams containing uninterpreted bytes and `setAsciiStream` for streams containing ASCII characters. A third method, `set-UnicodeStream` for streams containing Unicode characters, has been deprecated; the new JDBC 2.0 core API method `setCharacterStream` should be used in its place. These stream 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 database systems 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.

Another way to send large IN parameters to the database is to use SQL3 types like `BLOB` and `CLOB`. This is different from using streams in that `BLOB` and `CLOB` values are originally retrieved from the database, where they were created as SQL types. Using streams makes it possible to send the contents of a file written in the Java programming language to the database.

#### 6.1.7 Using PreparedStatement Objects in Batch Updates

The JDBC 2.0 core API provides the ability to send multiple updates to the database for execution as a batch. The `Statement` method `addBatch` is given an SQL update statement as a parameter, and the SQL statement is added to the `Statement` object's list of commands to be executed in the next batch. The interface `PreparedStatement` has its own version of the method `addBatch`, which adds a set of parameters to the batch, as shown in the following code fragment.

>     PreparedStatement pstmt = con.prepareStatement(
>              "UPDATE Table4 SET History = ? WHERE ID = ?");
>     pstmt.setClob(1, clob1);
>     pstmt.setLong(2, 350985839);
>     pstmt.addBatch();

>     pstmt.setClob(1, clob2);
>     pstmt.setLong(2, 350985840);
>     pstmt.addBatch();

>     int [] updateCounts = pstmt.executeBatch();

When the `PreparedStatement` object in pstmt is executed, it will be executed twice, once with the parameters `clob1` and 350985839, and a second time with the parameters `clob2` and 350985840. If either update command returns anything other than a single update count, the method `executeBatch` will throw an exception.

[Top] [Prev] [Next]

Copyright © 1999, Sun Microsystems, Inc. All rights reserved.