Section 8.6. Prepared Statements

8.6. 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 8-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);

8.6.1. Batch Updates

The original JDBC standard was not very efficient at loading large amounts of information into a database. Even if you use a PreparedStatement, your program still executes a separate query for each piece of data inserted. If your software inserts 10,000 rows into the database, it can introduce a substantial performance bottleneck.

The addBatch( ) method of Statement allows you to lump multiple update statements into a unit and execute them all at once. Call addBatch( ) after you create the statement and before execution:

 con.setAutoCommit(false); // If some fail, we want to roll back the rest Statement stmt = con.createStatement(  );   stmt.addBatch(   "INSERT INTO CUSTOMERS VALUES (1, "J Smith", "781 555-1323"); stmt.addBatch(   "INSERT INTO CUSTOMERS VALUES (2, "A Smith", "617 555-1132"); stmt.addBatch(   "INSERT INTO CUSTOMERS VALUES (3, "C Smith", "617 555-1238"); stmt.addBatch(   "INSERT INTO CUSTOMERS VALUES (4, "K Smith", "617 555-7823");   int[] upCounts = stmt.executeBatch(  ); con.commit(  );

Notice that we turn transaction autocommit off before creating the batch. This is because we want to roll back all the SQL statements if one or more of them fail to execute properly (for a more detailed discussion of transaction handling, see "Transactions" later in this chapter). After calling addBatch( ) multiple times to create our batch, we call executeBatch( ) to send the SQL statements off to the database to be executed as a batch. Batch statements are executed in the order they are added to the batch. executeBatch( ) returns an array of update counts , in which each value in the array represents the number of rows affected by the corresponding batch statement. If you need to remove the statements from a pending batch job, you can call clearBatch( ), as long as you call it before calling executeBatch( ).

Note that you can use only SQL statements that return an update count (e.g., CREATE, DROP, INSERT, UPDATE, DELETE) as part of a batch. If you include a statement that returns a result set, such as SELECT, you get a SQLException when you execute the batch. If one of the statements in a batch can't be executed for some reason, executeBatch( ) throws a BatchUpdateException. This exception, derived from SQLException, contains an array of update counts for the batch statements that executed successfully before the exception was thrown. If we then call rollback( ), the components of the batch transaction that did execute successfully will be rolled back.

The addBatch( ) method works slightly differently for PreparedStatement and CallableStatement objects. To use batch updating with a PreparedStatement, create the statement normally, set the input parameters, and then call the addBatch( ) method with no arguments. Repeat as necessary and then call executeBatch( ) when you're finished:

 con.setAutoCommit(false); // If some fail, we want to roll back the rest PreparedStatement stmt = con.prepareStatement(  "INSERT INTO CUSTOMERS VALUES (?,?,?)");   stmt.setInt(1,1); stmt.setString(2, "J Smith"); stmt.setString(3, "781 555-1323"); stmt.addBatch(  );   stmt.setInt(1,2); stmt.setString(2, "A Smith"); stmt.setString(3, "617 555-1132"); stmt.addBatch(  );   int[] upCounts = stmt.executeBatch(  ); con.commit(  );

This batch functionality also works with CallableStatement objects for stored procedures. The catch is that each stored procedure must return an update count and may not take any OUT or INOUT parameters.

8.6.2. Auto-Generated Keys

Although it isn't part of the standard set of SQL capabilities, many databases, including the popular MySQL system, support autogenerated primary keys. This is a very useful capability, but in the past it's had a drawback: JDBC provided no way to retrieve the generated key values! So you were stuck running a second query to retrieve the new key or generating the key separately. But no more, as JDBC 3.0 provides methods for retrieving autogenerated key values, provided that the underlying database and driver support them.

The execute( ) and executeUpdate( ) methods of Statement now include additional versions that allow your program to register interest in generated key values by passing in the Statement.RETURN_GENERATED_KEYS constant. After executing the statement, you can call the getGeneratedKeys( ) method to return a ResultSet containing the generated keys. Keys are returned one to a row, so if your SQL statement generates multiple new rows, you'll get each key back, in order.

Example 8-2 shows a simple program that inserts a single row into a table, retrieves the key generated by the table, and prints the result.

Example 8-2. KeyGenerator

 import java.sql.*;   public class KeyGenerator {     public static void main(String[] args) throws Exception {       Class.forName("com.mysql.jdbc.Driver");       Connection conn =           DriverManager.getConnection("jdbc:mysql://localhost/test");         int newKey = -1;       Statement stmt = conn.createStatement(  );       stmt.executeUpdate(               "insert into people (fullname) values ('John Morgan')",               Statement.RETURN_GENERATED_KEYS);         ResultSet keys = stmt.getGeneratedKeys(  );       if (keys.next(  )) {           newKey = keys.getInt(1);       }       keys.close(  );         System.out.println("New Key: " + newKey);         stmt.close(  );       conn.close(  );   } }