Prepared Statements

Concatenated SQL strings should be replaced with Prepared Statements where SQL calls use data outside of ISO-Latin-1 (utf-8). The following example is derived from Sun's Java site and demonstrates the use of Prepared Statements:

As with Statement objects, you create PreparedStatement objects with a Connection method. Using an open connection con object, you might write code such as the following to create a PreparedStatement object that takes two input parameters:

PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

The variable updateSales now contains the SQL statement, "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?", which, in most cases, has been sent to the DBMS and precompiled.

Using the PreparedStatement object updateSales, the following line of code sets the first question mark placeholder to a Java int with a value of 75:

updateSales.setInt(1, 75);

As you might surmise from the example, the first argument given to a setXXX method indicates which question mark placeholder is to be set, and the second argument indicates the value to which it is to be set. The next example sets the second placeholder parameter to the string Colombian:

updateSales.setString(2, "Colombian");

After these values have been set for its two input parameters, the SQL statement in updateSales will be equivalent to the SQL statement in the String object updateString. Therefore, the following two code fragments accomplish the same thing:

Code Fragment 1:

String updateString = "UPDATE COFFEES SET SALES = 75 " +
"WHERE COF_NAME LIKE 'Colombian'";
stmt.executeUpdate(updateString);

Code Fragment 2:

PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate():

We used the method executeUpdate to execute both the Statement stmt and the PreparedStatement updateSales. Notice, however, that no argument is supplied to executeUpdate when it is used to execute updateSales. This is true because updateSales already contains the SQL statement to be executed.