PreparedStatement in Java

Posted in: JDBC
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 aPreparedStatement 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.
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 aPreparedStatement 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 namedupdateSales:

        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, setIntspecifies the first placeholder and setString specifies the second placeholder.

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 oneResultSet object. Both PreparedStatement objects in CoffeesTable.updateCoffeeSales contain UPDATESQL 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();