Prepared Statements

Similar to their statement counterparts, prepared statements can be used to insert, update, delete, or select data. However, prepared statements are precompiled statements that can be reused to execute identical SQL statements with different values more efficiently. They make only one trip to the database for metadata, whereas statements make a round trip with each

execution. In addition, since bind variables are used, the database compiles and caches the prepared SQL statement and reuses it on subsequent executions to improve the database's performance. Prepared statements are also useful because some types of values, such as BLOBs, objects, collections, REFs, etc., are not representable as SQL text. To support this added functionality, you use a question mark as a placeholder within the text of a SQL statement for values that you wish to specify when you execute that statement. You can then replace that question mark with an appropriate value using one of the many available setXXX( ) accessor methods. setXXX( ) methods are available for setting every data type, just as getXXX( )

methods are available for getting the values for any data type from a result set.

In this chapter, we'll discuss the benefits of using a prepared statement versus a statement, how to format SQL statements for use with a PreparedStatement object, how to use the various

setXXX( ) methods, String data type limitations when using a PreparedStatement object, and batching. Let's start by discussing the pros and cons of using a prepared statement.

11.1 A Prepared Statement Versus a Statement

It's a popular belief that using a PreparedStatement object to execute a SQL statement is faster than using a Statement object. That's because a PreparedStatement object makes only one round trip to the database to get its data type information when it is first prepared, while a Statement object must make an extra round trip to the database to get its metadata each time it is executed. So the simple conclusion is that on the second and subsequent executions of a prepared statement, it is 50% faster than a statement. However, according to my tests in Chapter 19, due to the overhead of using a PreparedStatement object, it takes at least 65 executions before a PreparedStatement object is faster than a Statement object. For a small number of executions, a PreparedStatement object is not faster than a Statement object. However, that doesn't mean you shouldn't use a PreparedStatement. On the contrary, if you use the batch capabilities of a PreparedStatement object to execute the same SQL statement many times, it is significantly faster than a Statement object. Oracle's implementation of JDBC implements batching only for PreparedStatement objects, not for Statement objects. Prepared statements are less dynamic than their statement counterparts; you can build a SQL statement dynamically at runtime, but doing so using a prepared statement requires more coding, and the code required is fairly specific to the task. Prepared statements can, however, greatly simplify formulating your SQL statements, because you don't have to worry about date formats, number formats, or tick characters in strings. And prepared statements allow you to insert or update streaming data types.

The advantages of using prepared statements are that they allow you to improve efficiency by batching, utilize the SQL statement cache in the database to increase its efficiency, simplify your coding, and allow you to insert or update streaming data types, which we'll cover in Chapter 12.

11.2 Formulating SQL Statements

When you write a prepared statement, you use a question mark character (?) as a placeholder that will later be replaced by a value you specify using a setXXX( ) method. These

placeholders can be used only for values that need to be specified in a SQL statement and not in place of SQL keywords; they can't be used to implement a type of macro language. When building SQL statements, you must abide by certain rules. For an INSERT statement, you can use placeholders only in the VALUES list. For example:

insert into person_identifier_type ( code, description, inactive_date ) values

( ?, ?, ? )

In this example, the first placeholder, or question mark (?), represents the value for the code

column; the second represents the description column, and the third represents the

inactive_date column.

For an UPDATE statement, you can use placeholders only in the SET VALUES list and in the WHERE clause. For example:

update person_identifier_type set description = ?

In this example, the first placeholder represents the new value for the description column, while the second represents a value for the code column in the WHERE clause.

For a DELETE statement, you can use the placeholder only in the WHERE clause. For example:

delete person_identifier_type where code = ?

Finally, for a SELECT statement, you can use the placeholder in the SELECT list, WHERE clause, GROUP BY clause, and ORDER BY clause. For example:

select ?, code, description from person_identifier_type where code = ?

order by ?

Important! The question-mark placeholder used in the select