Java Programming with Oracle JDBC

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