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.

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.

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.

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);

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

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.

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.

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.

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.