Prepared Statements

The java.sql.PreparedStatement interface allows binding parameters which are associating a parameter (indicated by a placeholder like ? in the prepared statement) with an actual Java value. For example, if you were updating many bank accounts at once: 

 SQL> CREATE TABLE accounts_tbl (
   2    account_id  INT  PRIMARY KEY,
   3    balance     REAL NOT NULL );
 Table created.

Balance (args[0]) =

            
Statement  stmt = conn.createStatement( );
int  bal = Integer.parseInt( args[0].trim( ) );
for ( int i = 1;  i <= 5;  i++ )
  stmt.executeUpdate( "UPDATE accounts_tbl " +
    "SET balance = " + Integer.toString( i*bal ) +
    " WHERE account_id = " + Integer.toString( i+1000 ) );

Instead of calling this same statement over and over with different inputs, you can instead use a PreparedStatement:

Balance (args[0]) =

            
PreparedStatement  stmt = conn.prepareStatement(
  "UPDATE accounts_tbl SET balance = ? WHERE account_id = ?" );

int  bal = Integer.parseInt( args[0].trim( ) );
for ( int i = 1;  i <= 5;  i++ ) {
  stmt.setFloat( 1, i*bal );
  stmt.setInt  ( 2, i+1000 );
  stmt.execute ( );
}

This program executes that prepared SQL statement multiple times inside the for loop, but builds the query plan only a single time. In order to bind the input parameters, PrepareStatement provides setXXX methods (such as setInt). The setXXX methods bind parameters from left to right in the order you placed them in the prepared statement. The above example binds parameter 1 as a float to the account balance that is retrieved from the account object. The first ? is thus associated with parameter 1.




      What did the pirate say when he turned 80?    
      Aye Matey.