Prepared Statements (Java Enterprise in a Nutshell)

2.8. Prepared Statements

The PreparedStatement object is a close relative of the Statement object. Both accomplish roughly the same thing: running SQL statements. PreparedStatement, however, allows you to precompile your SQL and run it repeatedly, adjusting specific parameters as necessary. Since processing SQL strings is a large part of a database's overhead, getting compilation out of the way at the start can significantly improve performance. With proper use, it can also simplify otherwise tedious database tasks.

As with Statement, you create a PreparedStatement object from a Connection object. In this case, though, the SQL is specified at creation instead of execution, using the prepareStatement() method of Connection:

PreparedStatement pstmt = con.prepareStatement(
      "INSERT INTO EMPLOYEES (NAME, PHONE) VALUES (?, ?)");

This SQL statement inserts a new row into the EMPLOYEES table, setting the NAME and PHONE columns to certain values. Since the whole point of a PreparedStatement is to be able to execute the statement repeatedly, we don't specify values in the call to prepareStatement(), but instead use question marks (?) to indicate parameters for the statement. To actually run the statement, we specify values for the parameters and then execute the statement:

pstmt.clearParameters();
pstmt.setString(1, "Jimmy Adelphi");
pstmt.setString(2, "201 555-7823");
pstmt.executeUpdate();

Before setting parameters, we clear out any previously specified parameters with the clearParameters() method. Then we can set the value for each parameter (indexed from 1 to the number of question marks) using the setString() method. PreparedStatement defines numerous setXXX() methods for specifying different types of parameters; see the java.sql reference material later in this book for a complete list. Finally, we use the executeUpdate() method to run the SQL.

The setObject() method can insert Java object types into the database, provided that those objects can be converted to standard SQL types. setObject() comes in three flavors:

setObject(int parameterIndex, Object x, int targetSqlType, int scale)
setObject(int parameterIndex, Object x, int targetSqlType)
setObject(int parameterIndex, Object x)

Calling setObject() with only a parameter index and an Object causes the method to try and automatically map the Object to a standard SQL type (see Table 2-1). Calling setObject() with a type specified allows you to control the mapping. The setXXX() methods work a little differently, in that they attempt to map Java primitive types to JDBC types.

You can use PreparedStatement to insert null values into a database, either by calling the setNull() method or by passing a null value to one of the setXXX() methods that take an Object. In either case, you must specify the target SQL type.

Let's clarify with an example. We want to set the first parameter of a prepared statement to the value of an Integer object, while the second parameter, which is a VARCHAR, should be null. Here's some code that does that:

Integer i = new Integer(32);
pstmt.setObject(1, i, Types.INTEGER);
pstmt.setObject(2, null, Types.VARCHAR);
// or pstmt.setNull(2, Types.VARCHAR);