Using Transactions to Ensure Data Integrity

Chapter 13 included an example that created a database to maintain inventory for a bookstore. Here, you will extend the bookstore example to add the capability to sell books. This example will provide an introduction to transactions against a single database. A few modifications to the database schema are required to add the price and inventory count to each book. The Books table needs two additional columns , the price as a REAL SQL data type and the number of copies in stock as an INTEGER . The customers will be identified with a name and ID. Each customer will have a balance to be used as the funds for purchasing books. The Customers table will therefore include the customer_name as a VARCHAR , customer_id as an INTEGER , and balance as a FLOAT . Selling books from the inventory requires a new many-to-many relationship between customers and books. Each customer can purchase many books, and each book can be purchased by many customers. The database now requires the Books table and two new tables, the Customers table and an association table called the ShoppingCart table, as shown in Figure 16.1.

Figure 16.1. The ShoppingCart table allows a many-to-many relationship between Customer and Books.

The scenario for a customer to purchase a book is as follows :

1. Customer identifies himself by customer_name .

2. Customer requests to purchase a book by title .

3. Verify there is at least one copy of the book.

4. Verify customer has enough money in his balance to cover the price of the book.

5. Decrement the copies of the book by one.

6. Decrement the customer balance by the price of the book.

7. Insert a new ShoppingCart entry to store the relationship.

Steps 5, 6, and 7 must be performed as a transaction. In other words, a transaction must be established to complete or fail steps 5 through 7 as a group . It is clear that if the transaction only partially completes, the database would be in an invalid state. More than likely, the customer would not be pleased either. This example requires three database updates to be completed together. When all the statements complete successfully, they will be committed to the database. If any operation fails, a rollback will be performed. JDBC throws an SQLException to indicate errors. The rollback is therefore performed in the catch block for SQLException . If no exceptions are thrown, the commit will be executed. By default, JDBC is in auto-commit mode, meaning each statement is automatically committed to the database. You need to disable auto-commit to be able to control transaction management. Assuming a Connection object named con has already been obtained from either the DriverManager or a DataSource , Listing 16.1 shows the try block that performs the sample transaction.

Listing 16.1 The Transaction Must Be Rolled Back if an Error Occurs While Updating the Database

 try {     con.setAutoCommit(false);    // disable auto-commit     Statement s = con.createStatement();     s.executeUpdate("UPDATE books SET copies = " + (copies  1) +         "WHERE title = " + title);     s.close();     s = con.createStatement();     s.executeUpdate("UPDATE customers SET balance = " + (balance  price) +         "WHERE customer_name = " + name);     s.close();     s = con.createStatement();     s.executeUpdate("INSERT INTO ShoppingCart VALUES (" +         customerID + ", " + bookID + ")");     s.close();     con.commit(); } catch(SQLException x) {     System.err.println("Update Failed, rolling back");     try { con.rollback() }     catch(SQLException x) { } }