Jdbc Prepare Statement

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 usually 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 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 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.

The following method, CoffeesTable.updateCoffeeSales, stores the number of pounds of coffee sold in the current week in the SALES column for each type of coffee, and updates the total number of pounds of coffee sold in the TOTAL column for each type of coffee:

public void updateCoffeeSales(HashMap<String, Integer> salesForWeek)
    throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString =
        "update " + dbName + ".COFFEES " +
        "set SALES = ? where COF_NAME = ?";

    String updateStatement =
        "update " + dbName + ".COFFEES " +
        "set TOTAL = TOTAL + ? " +
        "where COF_NAME = ?";

    try {
        con.setAutoCommit(false);
        updateSales = con.prepareStatement(updateString);
        updateTotal = con.prepareStatement(updateStatement);

        for (Map.Entry<String, Integer> 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);
            }
        }
    } finally {
        if (updateSales != null) {
            updateSales.close();
        }
        if (updateTotal != null) {
            updateTotal.close();
        }
        con.setAutoCommit(true);
    }
}

Creating a PreparedStatement Object

The following creates a PreparedStatement object that takes two input parameters:

String updateString =
    "update " + dbName + ".COFFEES " +
    "set SALES = ? where COF_NAME = ?";
updateSales = con.prepareStatement(updateString);

Supplying Values for PreparedStatement Parameters

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:

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, setInt specifies the first placeholder and setString 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 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:

// 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 for loop or a while loop to set values for input parameters.

The CoffeesTable.updateCoffeeSales method uses a for-each loop to repeatedly set values in the PreparedStatement objects updateSales and updateTotal:

for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {

    updateSales.setInt(1, e.getValue().intValue());
    updateSales.setString(2, e.getKey());

    // ...
}

The method CoffeesTable.updateCoffeeSales takes one argument, HashMap. Each element in the HashMap 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 HashMap argument and sets the appropriate question mark placeholders in updateSales and updateTotal.

Executing PreparedStatement Objects

As with Statement objects, to execute a PreparedStatement object, call an execute statement: executeQuery if the query returns only one ResultSet (such as a SELECT SQL statement), executeUpdate if the query does not return a ResultSet (such as an UPDATE SQL statement), or execute if the query might return more than one ResultSet object. Both PreparedStatement objects in CoffeesTable.updateCoffeeSales contain UPDATE SQL statements, so both are executed by calling executeUpdate:

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 executeUpdate when they are used to execute updateSales and updateTotals; both PreparedStatement objects already contain the SQL statement to be executed.

Note: At the beginning of CoffeesTable.updateCoffeeSales, the auto-commit mode is set to false:

con.setAutoCommit(false);

Consequently, no SQL statements are committed until the method commit is called. For more information about the auto-commit mode, see Transactions.

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 value 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 is updated; the value 50 replaces the value in the column SALES in the row for Espresso. That update affects one row in the table, so n is equal to 1.

When the method executeUpdate is used to execute a DDL (data definition language) statement, such as in creating a table, it returns the int value of 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:

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

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.

Modifier and Type	Method and Description
void	addBatch() Adds a set of parameters to this PreparedStatement object's batch of commands.
void	clearParameters() Clears the current parameter values immediately.
boolean	execute() Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.
ResultSet	executeQuery() Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
int	executeUpdate() Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
ResultSetMetaData	getMetaData() Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed.
ParameterMetaData	getParameterMetaData()Retrieves the number, types and properties of this PreparedStatement object's parameters.
void	setArray(int parameterIndex, Array x) Sets the designated parameter to the given java.sql.Array object.
void	setAsciiStream(int parameterIndex, InputStream x) Sets the designated parameter to the given input stream.
void	setAsciiStream(int parameterIndex, InputStream x, int length) Sets the designated parameter to the given input stream, which will have the specified number of bytes.
void	setAsciiStream(int parameterIndex, InputStream x, long length) Sets the designated parameter to the given input stream, which will have the specified number of bytes.
void	setBigDecimal(int parameterIndex, BigDecimal x) Sets the designated parameter to the given java.math.BigDecimal value.
void	setBinaryStream(int parameterIndex, InputStream x) Sets the designated parameter to the given input stream.
void	setBinaryStream(int parameterIndex, InputStream x, int length) Sets the designated parameter to the given input stream, which will have the specified number of bytes.
void	setBinaryStream(int parameterIndex, InputStream x, long length) Sets the designated parameter to the given input stream, which will have the specified number of bytes.
void	setBlob(int parameterIndex, Blob x) Sets the designated parameter to the given java.sql.Blob object.
void	setBlob(int parameterIndex, InputStream inputStream) Sets the designated parameter to a InputStream object.
void	setBlob(int parameterIndex, InputStream inputStream, long length) Sets the designated parameter to a InputStream object.
void	setBoolean(int parameterIndex, boolean x) Sets the designated parameter to the given Java boolean value.
void	setByte(int parameterIndex, byte x) Sets the designated parameter to the given Java byte value.
void	setBytes(int parameterIndex, byte[] x) Sets the designated parameter to the given Java array of bytes.
void	setCharacterStream(int parameterIndex, Reader reader) Sets the designated parameter to the given Reader object.
void	setCharacterStream(int parameterIndex, Reader reader, int length) Sets the designated parameter to the given Reader object, which is the given number of characters long.
void	setCharacterStream(int parameterIndex, Reader reader, long length) Sets the designated parameter to the given Reader object, which is the given number of characters long.
void	setClob(int parameterIndex, Clob x) Sets the designated parameter to the given java.sql.Clob object.
void	setClob(int parameterIndex, Reader reader) Sets the designated parameter to a Reader object.
void	setClob(int parameterIndex, Reader reader, long length) Sets the designated parameter to a Reader object.
void	setDate(int parameterIndex, Date x) Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application.
void	setDate(int parameterIndex, Date x, Calendar cal) Sets the designated parameter to the given java.sql.Date value, using the given Calendar object.
void	setDouble(int parameterIndex, double x) Sets the designated parameter to the given Java double value.
void	setFloat(int parameterIndex, float x) Sets the designated parameter to the given Java float value.
void	setInt(int parameterIndex, int x) Sets the designated parameter to the given Java int value.
void	setLong(int parameterIndex, long x) Sets the designated parameter to the given Java long value.
void	setNCharacterStream(int parameterIndex, Reader value) Sets the designated parameter to a Reader object.
void	setNCharacterStream(int parameterIndex, Reader value, long length) Sets the designated parameter to a Reader object.
void	setNClob(int parameterIndex, NClob value) Sets the designated parameter to a java.sql.NClob object.
void	setNClob(int parameterIndex, Reader reader) Sets the designated parameter to a Reader object.
void	setNClob(int parameterIndex, Reader reader, long length) Sets the designated parameter to a Reader object.
void	setNString(int parameterIndex, String value) Sets the designated paramter to the given String object.
void	setNull(int parameterIndex, int sqlType) Sets the designated parameter to SQL NULL.
void	setNull(int parameterIndex, int sqlType, String typeName) Sets the designated parameter to SQL NULL
void	setObject(int parameterIndex, Object x) Sets the value of the designated parameter using the given object.
void	setObject(int parameterIndex, Object x, int targetSqlType) Sets the value of the designated parameter with the given object.
void	setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength) Sets the value of the designated parameter with the given object.
void	setRef(int parameterIndex, Ref x) Sets the designated parameter to the given REF(<structured-type>) value.
void	setRowId(int parameterIndex, RowId x) Sets the designated parameter to the given java.sql.RowId object.
void	setShort(int parameterIndex, short x) Sets the designated parameter to the given Java short value.
void	setSQLXML(int parameterIndex, SQLXML xmlObject) Sets the designated parameter to the given java.sql.SQLXML object.
void	setString(int parameterIndex, String x) Sets the designated parameter to the given Java String value.
void	setTime(int parameterIndex, Time x) Sets the designated parameter to the given java.sql.Time value.
void	setTime(int parameterIndex, Time x, Calendar cal) Sets the designated parameter to the given java.sql.Time value, using the given Calendar object.
void	setTimestamp(int parameterIndex, Timestamp x) Sets the designated parameter to the given java.sql.Timestamp value.
void	setTimestamp(int parameterIndex, Timestamp x, Calendar cal) Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object.
void	setUnicodeStream(int parameterIndex, InputStream x, int length) Deprecated.
void	setURL(int parameterIndex, URL x) Sets the designated parameter to the given java.net.URL value.