|
|
Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
Sometimes it is more convenient or more efficient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement, that you already know.When to Use a PreparedStatement Object
If you want to execute a
Statementobject many times, it will normally reduce execution time to use aPreparedStatementobject instead.The main feature of a
PreparedStatementobject is that, unlike aStatementobject, it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, thePreparedStatementobject contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when thePreparedStatementis executed, the DBMS can just run thePreparedStatement's SQL statement without having to compile it first.Although
PreparedStatementobjects can be used for SQL statements with no parameters, you will 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. You will see an example of this in the following sections.Creating a PreparedStatement Object
As with
Statementobjects, you createPreparedStatementobjects with aConnectionmethod. Using our open connectionconfrom previous examples, you might write code such as the following to create aPreparedStatementobject that takes two input parameters:PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");The variable
updateSalesnow contains the SQL statement,"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?", which has also, in most cases, been sent to the DBMS and been precompiled.Supplying Values for PreparedStatement Parameters
You will need to supply values to be used in place of the question mark placeholders, if there are any, before you can execute a
PreparedStatementobject. You do this by calling one of thesetXXXmethods defined in the classPreparedStatement. If the value you want to substitute for a question mark is a Javaint, you call the methodsetInt.If the value you want to substitute for a question mark is a JavaString, you call the methodsetString, and so on. In general, there is asetXXXmethod for each type in the Java programming language.Using the
PreparedStatementobjectupdateSalesfrom the previous example, the following line of code sets the first question mark placeholder to a Javaintwith a value of 75:updateSales.setInt(1, 75);As you might surmise from the example, the first argument given to a
setXXXmethod 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
updateSaleswill be equivalent to the SQL statement in theStringobjectupdateStringthat we used in the previous update example. Therefore, the following two code fragments accomplish the same thing:String updateString = "UPDATE COFFEES SET SALES = 75 " + "WHERE COF_NAME LIKE 'Colombian'"; stmt.executeUpdate(updateString);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
executeUpdateto execute both theStatementstmtand thePreparedStatementupdateSales. Notice, however, that no argument is supplied toexecuteUpdatewhen it is used to executeupdateSales. This is true becauseupdateSalesalready contains the SQL statement to be executed.Looking at these examples, you might wonder why you would choose to use a
PreparedStatementobject with parameters instead of just a simple statement, since the simple statement involves fewer steps. If you were going to update theSALEScolumn only once or twice, then there would be no need to use an SQL statement with input parameters. If you will be updating often, on the other hand, it might be much easier to use aPreparedStatementobject, especially in situations where you can use a for loop or while loop to set a parameter to a succession of values. You will see an example of this later in this section.Once a parameter has been set with a value, it will retain that value until it is reset to another value or the method
clearParametersis called. Using thePreparedStatementobjectupdateSales, 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:updateSales.setInt(1, 100); updateSales.setString(2, "French_Roast"); updateSales.executeUpdate(); // changes SALES column of French Roast row to 100 updateSales.setString(2, "Espresso"); updateSales.executeUpdate(); // changes SALES column of Espresso row to 100 (the first // parameter stayed 100, and the second parameter was reset // to "Espresso")Using a Loop to Set Values
You can often make coding easier by using a
forloop or awhileloop to set values for input parameters.The code fragment that follows demonstrates using a
forloop to set values for parameters in thePreparedStatementobjectupdateSales. The arraysalesForWeekholds the weekly sales amounts. These sales amounts correspond to the coffee names listed in the arraycoffees, so that the first amount insalesForWeek(175) applies to the first coffee name incoffees("Colombian"), the second amount insalesForWeek(150) applies to the second coffee name incoffees("French_Roast"), and so on. This code fragment demonstrates updating theSALEScolumn for all the coffees in the tableCOFFEES:PreparedStatement updateSales; String updateString = "update COFFEES " + "set SALES = ? where COF_NAME like ?"; updateSales = con.prepareStatement(updateString); int [] salesForWeek = {175, 150, 60, 155, 90}; String [] coffees = {"Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf"}; int len = coffees.length; for(int i = 0; i < len; i++) { updateSales.setInt(1, salesForWeek[i]); updateSales.setString(2, coffees[i]); updateSales.executeUpdate(); }When the proprietor wants to update the sales amounts for the next week, he can use this same code as a template. All he has to do is enter the new sales amounts in the proper order in the array
salesForWeek. The coffee names in the arraycoffeesremain constant, so they do not need to be changed. (In a real application, the values would probably be input from the user rather than from an initialized Java array.)Return Values for the Method executeUpdate
Whereas
executeQueryreturns aResultSetobject containing the results of the query sent to the DBMS, the return value forexecuteUpdateis anintthat indicates how many rows of a table were updated. For instance, the following code shows the return value ofexecuteUpdatebeing assigned to the variablen:updateSales.setInt(1, 50); updateSales.setString(2, "Espresso"); int n = updateSales.executeUpdate(); // n = 1 because one row had a change in itThe table
COFFEESwas updated by having the value50replace the value in the columnSALESin the row forEspresso. That update affected one row in the table, sonis equal to1.When the method
executeUpdateis used to execute a DDL statement, such as in creating a table, it returns theint0. Consequently, in the following code fragment, which executes the DDL statement used to create the tableCOFFEES,nwill be assigned a value of0:int n = executeUpdate(createTableCoffees); // n = 0Note that when the return value for
executeUpdateis0, it can mean one of two things: (1) the statement executed was an update statement that affected zero rows, or (2) the statement executed was a DDL statement.![]()
![]()
![]()
Start of Tutorial > Start of Trail > Start of Lesson Search
Feedback FormCopyright 1995-2004 Sun Microsystems, Inc. All rights reserved.