Prepared Statements

As shown in the last example, it is common to execute the same statements repeatedly, with changes to only the value attributes. Each time you construct a statement string, it must be compiled and the driver must map the values in your string to the SQL understood by the underlying database. If you execute enough identical statements, this can become inefficient. JDBC provides an interface called a prepared statement that implements the Statement interface. A prepared statement represents a “compiled” statement that is fairly static. You can get a prepared statement from a connection by calling the prepareStatement() method. The only things that change in the PreparedStatement string are the parameters to the SQL string that the statement contains. These parameters are represented in the original string by question marks (?). To set the values for these parameters, PreparedStatement has setXXX methods that take two parameters. The first parameter is the index (starting at 1) of the parameter you want to modify; the second parameter is the value. Consider the previous example. Here is what the PreparedStatement version of that example might look like:

...
 Statement stmt = con.createStatement();
PreparedStatement pstmt = con.prepareStatement(
    “INSERT INTO Temp VALUES (?,?,?)”
);

stmt.executeUpdate(“CREATE TABLE Temp ” +
                   “(id int, name varchar(25), value int)”);

//First paramter is index, second is value of specified type
pstmt.setInt(1,1);
pstmt.setString(2,“test1”);
pstmt.setFloat(3,5.0);
int insCount = pstmt.executeUpdate();
System.out.println(“Inserted ” + insCount + “rows”);

pstmt.setInt(1,2);
pstmt.setString(2,“test2”);
pstmt.setFloat(3,10.0);
insCount = pstmt.executeUpdate();
System.out.println(“Inserted ” + insCount + “rows”);

int updateCount = stmt.executeUpdate(“UPDATE Temp SET value=1.0”);
System.out.println(“Updated ” + insCount + “rows”);

int deleteCount = stmt.executeUpdate(“DELETE FROM Temp”);
System.out.println(“Deleted ” + insCount + “rows”);

Stmt.executeUpdate(“DROP TABLE Temp”);

Stmt.close();

---

NOTE:  Stored procedures can be called from the Statement and PreparedStatement objects. However, a connection has a method named prepareCall() that returns an object that implements the CallableStatement interface. This interface extends the PreparedStatement interface by adding methods that allow you to get the values coming back from a store procedure. Typically, these values are marked as OUT parameters in the stored procedure declaration. Stored procedure handling is beyond the scope of this section, but the principle is similar to the topics discussed so far.

---

PreparedStatement is beneficial when you intend to call the same SQL multiple times. It saves on the time necessary to compile a query. For a one–time query, use the Statement object. In each case, you are handling one SQL statement at a time.

The next section looks at how to control multiple SQL calls in a single transaction.

Managing Transactions

A transaction is a collection of consecutively executed database commands that are considered linked as one business process. If any part of a transaction fails, the programmer has the option of retrying the transaction or saving the current state of the transaction as permanent. To retry a transaction, it must be rolled back. Rollback transaction is a common term for resetting the state of the database to the point before the transaction was executed. To save the transaction, execute a commit transaction function. This saves all the changes made to the database as permanent changes. The important point is that all statements executed as part of a transaction are considered a group. Whatever you do to the transaction affects all statements in that transaction. For example, if you deleted a row, changed the value in another row, and then rolled back the transaction, both the delete and the update would be undone.

By default, a connection is set to auto commit. This means that every statement executed is considered to be in its own transaction. So, if the statement is successful, it is committed. To have several statements executed as a single transaction, turn off auto commit by calling the Connection method setAutoCommit() and passing false. Then use the Connection methods commit() and rollback() to control the status of your transaction. Not all databases support transactions; even the ones that do support them at different levels.

Most JDBC drivers support transactions. In fact, a JDBC–compliant driver must support transactions. DatabaseMetaData supplies information describing the level of transaction support a DBMS provides. This level is called the transaction isolation level. The higher this level, the more careful the database will be in preventing multiple users from overwriting each other’s transactions. However, the higher the isolation level, the slower the database may be. This is due to the degree of locking the database will perform. To find out the isolation level of your database, call the Connection method getTransactionIsolation(). Calling setTransactionIsolation can set level(). Be careful changing the isolation level during a transaction, because this causes the method commit to be called, which will cause any changes up to that point to be made permanent.

The isolation levels are listed and explained in Table 3.12.

Most databases support transactions; however, some do not. In these cases, you may find that the driver ignores your transaction calls. You can usually find out how the driver will handle transactions by checking the metadata and checking the property supportsTransactions. In some situations, an exception may even be raised.

The next section details the error–handling mechanisms of JDBC.

Exception–Handling /SQL Warning

Notice the previous examples were catching SQLExceptions. This is the generic exception type for JDBC methods. JDBC provides three types of exception handling:

•  SQLException

•  SQLWarning

•  DataTruncation

Let’s take a look at each of these in more detail.