SQL in a Nutshell, 2nd Edition

Binding Parameters with JDBC

The following Java code fragment executes a SQL INSERT statement that adds new sales to the sales table in the pubs database. The INSERT statement is parameterized to provide better performance.

// Create a Command object for the SQL statement
PreparedStatement statement = connection.prepareStatement(
    "INSERT INTO SALES(stor_id, 
                       ord_num, 
                       ord_date, 
                       qty, 
                       payterms, 
                       title_id) " +
    "VALUES(?, ?, ?, ?, ?, ?)" );

while( getNextSale(statement) )
{
    // Execute the statement
    int result = statement.executeUpdate( );
    if( result != 1 )
    {
        // If result isn't 1, then the insert failed.
        System.out.println( "The INSERT failed." );
        break;
    }
}

Use the following steps to execute statements with bound parameters in JDBC:

1. Create a JDBC PreparedStatement object and pass the parameterized SQL statement into its constructor. The difference for using bound parameters within the statement is in the VALUES clause of the INSERT statement. Contained within the VALUES clause are six placeholders (the question marks) for the parameters that will later be bound to the PreparedStatement object.

PreparedStatement statement = connection.prepareStatement(
    "INSERT INTO SALES(stor_id, 
                       ord_num, 
                       ord_date, 
                       qty, 
                       payterms, 
                       title_id) " +
    "VALUES(?, ?, ?, ?, ?, ?)" );

1. In this example, the parameters are assigned a value by the user-defined getNextSale function call, which could be implemented like this:

static boolean getNextSale( PreparedStatement statement ) throws SQLException { // Omitted is the code ...