Creating Dynamic SQL with Prepared Statements

A prepared statement is a SQL statement that is sent to the database prior to its execution. Unlike stored procedures, prepared statements won’t remain in the database after the resources associated with them are freed. They may be called a number of times with different parameter values. Like stored procedures, the SQL text of the prepared statement is optimized and precompiled by the database prior to its execution(s). In addition to the performance gain of using prepared statements compared to normal statements, the prepared statements can be used to execute SQL queries and updates whose parameters are known only at runtime. Figures 8-3 and 8-4 illustrate some differences between the execution of simple SQL statements and the execution of prepared statements.

In Figure 8-3, the SQL text is sent to the database along with specific values and literals. This example illustrates an INSERT prepared statement.

Figure 8-3: Sending a static statement.

Figure 8-4: Sending parameters to a prepared statement.

When executing a prepared statement (see Figure 8-4), the SQL statement is already at the database side. Only parameter values are passed.

As Figure 8-5 shows, five steps must be followed to use prepared statements with JDBC:

Figure 8-5: An overview of prepared statements.

1. Prepare the SQL statement.

2. Set IN parameters.

3. Execute the statement.

4. Get the results, if any.

5. If necessary, set new IN parameter values and reexecute the statement.

The last step is optional, but in many cases prepared statements are used for this facility.

A prepared statement is created from a Connection object as shown in the following. It usually doesn’t outlive the connection that created it.

Connection’s Method for Creating a Prepared Statement

PreparedStatement prepareStatement(String sql);

This method is used to get a PreparedStatement object for later execution. Parameters are symbolized by ? characters.

Passing IN parameters

As with callable statements, prepared statement IN parameters must be set one by one. The following methods are available for this purpose.

PreparedStatement’s Methods for Setting IN Parameters

void setNull(int parameterIndex, int sqlType); void setBoolean(int parameterIndex, boolean x); void setByte(int parameterIndex, byte x); void setShort(int parameterIndex, short x); void setInt(int parameterIndex, int x); void setLong(int parameterIndex, long x); void setFloat(int parameterIndex, float x); void setDouble(int parameterIndex, double x); void set BigDecimal(int parameterIndex); void setString(int parameterIndex, String x); void setBytes(int parameterIndex, byte x[]); void setDate(int parameterIndex, java.sql.Date x); void setDate(int parameterIndex, java.sql.Date x, java.util.Calendar cal); void setTime(int parameterIndex, java.sql.Time x); void setTime(int parameterIndex, java.sql.Time x, java.util.Calendar cal); void setTimestamp(int parameterIndex, java.sql.Timestamp x); void setTimestamp(int parameterIndex, java.sql.Timestamp x,  java.util.Calendar cal); void setAsciiStream(int parameterIndex, java.io.InputStream x, int length); void setBinaryStream(int parameterIndex, java.io.InputStream x, int length); void setCharacterStream(int parameterIndex, java.io.Reader r, int length); void setObject(int parameterIndex, Object x); void setObject(int parameterIndex, Object x, int targetSqlType); void setObject(int parameterIndex, Object x, int targetSqlType, int scale); void setRef(int parameterIndex, Ref r); void setBlob(int parameterIndex, Blob b); void setClob(int parameterIndex, Clob c); void setArray(int parameterIndex, Array a); void setNull(int parameterIndex, int sqlType, String typeName); void setURL(int parameterIndex, java.net.URL url); void clearParameters(); void setObject(...); 

Note that the setObject() and clearParameters() methods have the same meaning as for callable statements.

OUT parameters aren’t supported by prepared statements.

Executing the query and retrieving results

After all IN parameters are set, the execution of a prepared statement is performed as for normal statements. A prepared statement may return a count value as well as a ResultSet. Listing 8-2 shows the setting of parameters and the call to the executeUpdate() method.

Listing 8-2: Dealing with Prepared Statement Parameters

... ... Connection myConnection = DriverManager.getConnection(url,         "javauser", "hotjava"); PreparedStatement myStmt = myConnection.prepareStatement(         "UPDATE employees SET floor = ? WHERE department = ?"); myStmt.setInt(1, 1); myStmt.setString(2, "Operations"); int res = myStmt.executeUpdate(); myStmt.setInt(1, 2); myStmt.setString(2, "Engineering"); res = myStmt.executeUpdate(); myStmt.setInt(1, 3); myStmt.setString(2, "Management"); res = myStmt.executeUpdate(); myStmt.close(); myConnection.close(); ... ...

Sending BLOBs

With JDBC 1, there was no way to send binary large objects (BLOBs) to a database using normal SQL statements. This feature was simply not supported within normal SQL statements. Because prepared statements support the setAsciiStream(), setUnicode-Stream(), and setBinaryStream() methods of setting IN parameters, it was however possible to send multikilobyte and multimegabyte values to the database.

JDBC 2 introduced the setBlob() and getBlob() methods. These can be used to set and get BLOB content to table’s columns; however, I recommend using the techniques described in this chapter for accessing large BLOB values of several megabytes. Building several Blob objects and garbage-collecting them may be inefficient, and actually not needed if those objects are never used as such in the code. This applies to the CLOB types as well.

Very large binary data is sent using input/output (I/O) streams and setBinaryStream(). It is unnecessary to send the data in small chunks as is required for receiving BLOBs because the JDBC driver makes repeated calls on the I/O stream to read the stream’s content and send the content (that is, bytes) to the database as the actual parameter data. Listing 8-3 illustrates an insertion of BLOBs using input streams.

Listing 8-3: Sending Very Large Parameters to the Database

... ... Connection myConnection = DriverManager.getConnection(url,         "javauser", "hotjava"); java.io.File pictFile = new java.io.File("jones.jpeg"); java.io.File audioFile = new java.io.File("jones.au"); int pictFileLen = (int) pictFile.length(); int audioFileLen = (int) audioFile.length(); java.io.InputStream fPict =         new java.io.FileInputStream(pictFile); java.io.InputStream fAudio =         new java.io.FileInputStream(audioFile); PreparedStatement myStmt = myConnection.prepareStatement(         "UPDATE employees SET emp_pict = ?, emp_welcome = ?         WHERE id = ?"); myStmt.setBinaryStream(1, fPict, pictFileLen); myStmt.setBinaryStream(2, fAudio, audioFileLen); myStmt.setInt(3, 1); int res = myStmt.executeUpdate(); myStmt.close(); myConnection.close(); ... ...