Sometimes it is more convenient to use a PreparedStatement
object for sending SQL statements to the database. This special type of statement is derived from the more general class,
String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; // ... PreparedStatement updateSales = con.prepareStatement[updateString];0, that you already know.
If you want to execute a
String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; // ... PreparedStatement updateSales = con.prepareStatement[updateString];0 object many times, it usually reduces execution time to use a
PreparedStatement
object instead.The main feature of a PreparedStatement
object is that, unlike a
String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; // ... PreparedStatement updateSales = con.prepareStatement[updateString];0 object, it is given a SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the
PreparedStatement
object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the PreparedStatement
is executed, the DBMS can just run the PreparedStatement
SQL statement without having to compile it first.Although you can use PreparedStatement
objects for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. Examples of this are in the following sections.
However, the most important advantage of prepared statements is that they help prevent SQL injection attacks. SQL injection is a technique to maliciously exploit applications that use client-supplied data in SQL statements. Attackers trick the SQL engine into executing unintended commands by supplying specially crafted string input, thereby gaining unauthorized access to a database to view or manipulate restricted data. SQL injection techniques all exploit a single vulnerability in the application: Incorrectly validated or nonvalidated string literals are concatenated into a dynamically built SQL statement and interpreted as code by the SQL engine. Prepared statements always treat client-supplied data as content of a parameter and never as a part of an SQL statement. See the section in Database PL/SQL Language Reference, part of Oracle Database documentation, for more information.
The following method,
String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; // ... PreparedStatement updateSales = con.prepareStatement[updateString];9, stores the number of pounds of coffee sold in the current week in the
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];0 column for each type of coffee, and updates the total number of pounds of coffee sold in the
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];1 column for each type of coffee:
public void updateCoffeeSales[HashMap salesForWeek] throws SQLException { String updateString = "update COFFEES set SALES = ? where COF_NAME = ?"; String updateStatement = "update COFFEES set TOTAL = TOTAL + ? where COF_NAME = ?"; try [PreparedStatement updateSales = con.prepareStatement[updateString]; PreparedStatement updateTotal = con.prepareStatement[updateStatement]] { con.setAutoCommit[false]; for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; updateSales.executeUpdate[]; updateTotal.setInt[1, e.getValue[].intValue[]]; updateTotal.setString[2, e.getKey[]]; updateTotal.executeUpdate[]; con.commit[]; } } catch [SQLException e] { JDBCTutorialUtilities.printSQLException[e]; if [con != null] { try { System.err.print["Transaction is being rolled back"]; con.rollback[]; } catch [SQLException excep] { JDBCTutorialUtilities.printSQLException[excep]; } } } }
The following creates a PreparedStatement
object that takes two input parameters:
String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; // ... PreparedStatement updateSales = con.prepareStatement[updateString];
You must supply values in place of the question mark placeholders [if there are any] before you can execute a PreparedStatement
object. Do this by calling one of the setter methods defined in the PreparedStatement
class. The following statements supply the two question mark placeholders in the PreparedStatement
named
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];6:
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];
The first argument for each of these setter methods specifies the question mark placeholder. In this example,
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];7 specifies the first placeholder and
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];8 specifies the second placeholder.
After a parameter has been set with a value, it retains that value until it is reset to another value, or the method
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];9 is called. Using the
PreparedStatement
object updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];6, the following code fragment illustrates reusing a prepared statement after resetting the value of one of its parameters and leaving the other one the same:
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];
Using Loops to Set Values
You can often make coding easier by using a
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];2 loop or a
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];3 loop to set values for input parameters.
The
String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; // ... PreparedStatement updateSales = con.prepareStatement[updateString];9 method uses a for-each loop to repeatedly set values in the
PreparedStatement
objects updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];6 and
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];7:
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }
The method
String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; // ... PreparedStatement updateSales = con.prepareStatement[updateString];9 takes one argument,
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];9. Each element in the
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];9 argument contains the name of one type of coffee and the number of pounds of that type of coffee sold during the current week. The for-each loop iterates through each element of the
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];9 argument and sets the appropriate question mark placeholders in
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];6 and
// changes SALES column of French Roast //row to 100 updateSales.setInt[1, 100]; updateSales.setString[2, "French_Roast"]; updateSales.executeUpdate[]; // changes SALES column of Espresso row to 100 // [the first parameter stayed 100, and the second // parameter was reset to "Espresso"] updateSales.setString[2, "Espresso"]; updateSales.executeUpdate[];7.
As with
String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; // ... PreparedStatement updateSales = con.prepareStatement[updateString];0 objects, to execute a
PreparedStatement
object, call an execute statement: for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }6 if the query returns only one
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }7 [such as a
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }8 SQL statement],
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }9 if the query does not return a
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }7 [such as an
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
1 SQL statement], or updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
2 if the query might return more than one for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }7 object. Both
PreparedStatement
objects in updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
5 contain updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
1 SQL statements, so both are executed by calling for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }9:
updateSales.setInt[1, e.getValue[].intValue[]];
updateSales.setString[2, e.getKey[]];
updateSales.executeUpdate[];
updateTotal.setInt[1, e.getValue[].intValue[]];
updateTotal.setString[2, e.getKey[]];
updateTotal.executeUpdate[];
con.commit[];
No arguments are supplied to
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }9 when they are used to execute
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];6 and
con.setAutoCommit[false];0; both
PreparedStatement
objects already contain the SQL statement to be executed.Note: At the beginning of
String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; // ... PreparedStatement updateSales = con.prepareStatement[updateString];9, the auto-commit mode is set to false:
con.setAutoCommit[false];
Consequently, no SQL statements are committed until the method
con.setAutoCommit[false];3 is called. For more information about the auto-commit mode, see Transactions.
Return Values for the executeUpdate Method
Whereas
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }6 returns a
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }7 object containing the results of the query sent to the DBMS, the return value for
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }9 is an
con.setAutoCommit[false];7 value that indicates how many rows of a table were updated. For instance, the following code shows the return value of
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }9 being assigned to the variable
con.setAutoCommit[false];9:
updateSales.setInt[1, 50]; updateSales.setString[2, "Espresso"]; int n = updateSales.executeUpdate[]; // n = 1 because one row had a change in it
The table
updateSales.setInt[1, 50]; updateSales.setString[2, "Espresso"]; int n = updateSales.executeUpdate[]; // n = 1 because one row had a change in it0 is updated; the value 50 replaces the value in the column
updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]];0 in the row for
updateSales.setInt[1, 50]; updateSales.setString[2, "Espresso"]; int n = updateSales.executeUpdate[]; // n = 1 because one row had a change in it2. That update affects one row in the table, so
con.setAutoCommit[false];9 is equal to 1.
When the method
for [Map.Entry e : salesForWeek.entrySet[]] { updateSales.setInt[1, e.getValue[].intValue[]]; updateSales.setString[2, e.getKey[]]; // ... }9 is used to execute a DDL [data definition language] statement, such as in creating a table, it returns the
con.setAutoCommit[false];7 value of 0. Consequently, in the following code fragment, which executes the DDL statement used to create the table
updateSales.setInt[1, 50]; updateSales.setString[2, "Espresso"]; int n = updateSales.executeUpdate[]; // n = 1 because one row had a change in it0,
con.setAutoCommit[false];9 is assigned a value of 0: