jGuru: Using Transactions

Print-friendly Version

by

[Help | API Docs | Short Course| Exercises]

Duane and Chrissie are competent and confident developers, but realize that problems ( and bugs ) occur, even in the best written programs. To ensure database integrity, avoid having to build in restart capability in all their code and to avoid having to manually "fix" problems in the database when a program loading a set of data dies, they decide to set up their loader program for transaction handling. The program should only permanently store data when every statement is successful and otherwise roll back previously performed operations.

4J management expects to have some vendor comparison data soon, so Chrissie sets up the necessary tables by running EURun2 ( from Handling SQLExceptions and SQLWarnings ) against the properties file CreateCoffeeTables.properties. This creates the tables CoffeeValT, CoffeeComp and CoffeeVend. She uses java EURun2 CreateCoffeeTables to accomplish the task. Duane has created some data for the CoffeeVend table in LoadCoffeeVend.properties. Now all they need is a program to handle the transactions. See the demonstration section for details on creating the tables and running the transaction program against LoadCoffeeVend.properties.

Prerequisites

• Handling SQLExceptions and SQLWarnings

Skeleton Code

• ConnectU.properties
• CreateCoffeeTables.properties
• DropCoffeeTables.properties
• LoadCoffeeVend.properties
• EUTrans.java

Tasks

1. To enable transaction handling, in doUpdate(), add code to turn off autocommit.

2. In doUpdate(), if the number of rows affected is zero,

   • Print a message to System.err that the previous operations will be rolled back.
   • Set the rows processed total iProcessedCount to zero.
   • Set the error flag bError to true and exit the processing loop.

3. In doUpdate(), in the last two catch blocks, add code to set the rows processed total iProcessedCount to zero and set the error flag bError to true.

4. In doUpdate(), in the finally block, check the error flag bError. If it is true, issue a rollback and print a message. If false, commit the statements.

Where help exists, the task numbers above are linked to the step-by-step help page.

Solution Source

• ConnectU.properties
• CreateCoffeeTables.properties
• DropCoffeeTables.properties
• LoadCoffeeVend.properties
• EUTrans.java

Demonstration

When this program has been compiled and run, all DML statements from the ResourceBundle will be committed or all will be rolled back, depending on error conditions. The program will rollback any DDL statements because support for DDL in transactions is dependent on the DBMS used.

After

• 1) Running java EURun2 CreateCoffeeTables to create the tables CoffeeValT, CoffeeComp and CoffeeVend,
• 2) Running the transaction program EUTrans, created in this section, with the vendor load file, as java EUTrans LoadCoffeeVend,

Here is sample output from a successful run:

EUTrans LoadCoffeeVend
10 rows processed.

This output resulted from an intentional error:

EUTrans LoadCoffeeVend
INSERT aINTO CoffeeVend VALUES ('ExoticaBeans', 40 )
problems with executeUpdate:
[SQL0104] Token AINTO was not valid. Valid tokens: INTO.
SQL State: 42601
Vendor Error Code: -104
0 rows processed.
ALL Statements for LoadCoffeeVend were rolled back.

Next Exercise

Exercises

Short Course

Copyright 1996-2000 jGuru.com. All Rights Reserved.