Download the JDK
Search the Tutorials
Hide the TOC
Trail: JDBC(TM) Database Access
Lesson: JDBC Basics
JDBC Basics
Getting Started
Setting Up a Database
Establishing a Connection
Setting Up Tables
Retrieving Values from Result Sets
Updating Tables
Milestone: The Basics of JDBC
Using Prepared Statements
Using Joins
Using Transactions
Stored Procedures
SQL Statements for Creating a Stored Procedure
Creating Complete JDBC Applications
Running the Sample Applications
Creating an Applet from an Application
Home Page > JDBC(TM) Database Access > JDBC Basics
« Previous • Trail • Next »
Using Prepared Statements

> 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, `Statement`, that you already know.

> If you want to execute a `Statement` object many times, it normally reduces execution time to use a `PreparedStatement` object instead.

> The main feature of a `PreparedStatement` object is that, unlike a `Statement` object, it is given an 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 an SQL statement, but an 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 `PreparedStatement` objects can be used 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.

> ### Creating a `PreparedStatement` Object

> As with `Statement` objects, you create `PreparedStatement` objects with a `Connection` method. Using our open connection `con` from previous examples, 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 has also, in most cases, been sent to the DBMS and been precompiled.

> ### Supplying Values for PreparedStatement Parameters

> You need to supply values to be used in place of the question mark placeholders (if there are any) before you can execute a `PreparedStatement` object. You do this by calling one of the `setXXX` methods defined in the `PreparedStatement` class. If the value you want to substitute for a question mark is a Java `int`, you call the method `setInt.` If the value you want to substitute for a question mark is a Java `String`, you call the method `setString`, and so on. In general, there is a `setXXX` method for each primitive type declared in the Java programming language.

> setXXX Using the `PreparedStatement` object `updateSales` from the previous example, the following line of code sets the first question mark placeholder to a Java `int` with a value of 75:

> >     updateSales.setInt(1, 75);

> setXXX 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");

> setXXX After these values have been set for its two input parameters, the SQL statement in `updateSales` is the equivalent to the SQL statement in the `String` object `updateString` that was used in the previous update example. 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():

> The method `executeUpdate` was used 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.

> Looking at these examples, you might wonder why you would choose to use a `PreparedStatement` object with parameters instead of just a simple statement, since the simple statement involves fewer steps. If you were going to update the `SALES` column 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 a `PreparedStatement` object, 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 retains that value until it is reset to another value, or the method `clearParameters` is called. Using the `PreparedStatement` object `updateSales`, 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 `for` loop or a `while` loop to set values for input parameters.

> The code fragment that follows demonstrates using a `for` loop to set values for parameters in the `PreparedStatement` object `updateSales`. The array `salesForWeek` holds the weekly sales amounts. These sales amounts correspond to the coffee names listed in the array `coffees`, so that the first amount in `salesForWeek` (175) applies to the first coffee name in `coffees` ("`Colombian`"), the second amount in `salesForWeek` (150) applies to the second coffee name in `coffees` ("`French_Roast` "), and so on. This code fragment demonstrates updating the `SALES` column for all the coffees in the table `COFFEES`:

> > >     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 array `coffees` remain 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 `executeUpdate` Method

> > Whereas `executeQuery` returns a `ResultSet` object containing the results of the query sent to the DBMS, the return value for `executeUpdate` is an `int` that indicates how many rows of a table were updated. For instance, the following code shows the return value of `executeUpdate` being assigned to the variable `n`:

> > >     updateSales.setInt(1, 50);
> > >     updateSales.setString(2, "Espresso");
> > >     int n = updateSales.executeUpdate();
> > >     // n = 1 because one row had a change in it

> > The table `COFFEES` was updated by having the value `50` replace the value in the column `SALES` in the row for `Espresso`. That update affected one row in the table, so `n` is equal to `1`.

> > When the method `executeUpdate` is used to execute a DDL statement, such as in creating a table, it returns the `int` `0`. Consequently, in the following code fragment, which executes the DDL statement used to create the table `COFFEES`, `n` is assigned a value of `0`:

> > > >     int n = executeUpdate(createTableCoffees); // n = 0

> > > Note that when the return value for `executeUpdate` is `0`, it can mean one of two things:

> > > - the statement executed was an update statement that affected zero rows
> > > - the statement executed was a DDL statement.

> > « Previous • Trail • Next »

Previous page: Milestone: The Basics of JDBC
Next page: Using Joins