Java Reference
In-Depth Information
Committing a Transaction to Delete Related Records
Deleting a record from the Users table is more involved because a user may
have one or more stock holdings; therefore, any records for the user in the
UserStocks table must be deleted prior to deleting the user record. Furthermore,
the user may be the only user holding a given stock. The program must verify if
this is the case and, if so, delete the record for that stock from the Stocks table.
When tables have related records, it is likely that a change, such as deleting a
record in one table, may require changes in one or more records from one or
more additional tables. Such changes are considered an individual transaction.
A
transaction
is a logical unit of work. If the entire transaction cannot be com-
pleted successfully, then no portion of it should be completed. For example,
given a group of related records, it is possible to delete some of the records and
then encounter an error condition that prevents deletion of the remaining
records. Such a situation would leave the database in an inconsistent state. To
avoid this, the deletions can be grouped as a transaction. In a transaction, if all
changes are applied successfully, then the program can
commit
the transaction,
meaning all of the changes are saved. If all the changes are not made successfully,
then the program can
rollback
the changes made, undoing them and leaving the
database without any of the changes applied.
By default, new connections automatically commit SQL statements as indi-
vidual transactions. Setting a connection's auto-commit mode to false allows
multiple SQL statements to be grouped into a single transaction. Line 215 calls
the setAutoCommit() method of a Connection object, disabling the auto-
commit mode. All subsequent SQL statements are treated as a single transaction
until either the
commit()
method or the
rollback()
method is called to commit
the statements or roll them back, respectively.
Line 217 gets the user ID from the User object passed to the method. Line
218 verifies that the user record exists in the database. Lines 220 and 221 query
the UserStocks table for all records associated with a given user. The loop begin-
ning on line 222 iterates through the ResultSet, obtaining the symbol for each
stock held by the user; then in line 227, it calls the method, delUserStocks(),
which deletes the entry from the UserStocks table and, if necessary, calls del-
Stocks() to delete the stock from the Stocks table. If a stock or stock holding
could not be deleted, an SQLException is thrown in line 231. An SQLException
is used for database access errors and distinguishes them from other exceptions,
in an analogous manner to the PasswordExceptions created earlier. Once all
stock holdings for the user are deleted, lines 238 and 239 delete the user
record from the Users table. Note that line 247 throws an IOException, not an
SQLException, if the user record is not found. This prevents attempting to exe-
cute either a commit or a rollback. Neither is valid, because no user record was
read, so no deletes were performed; therefore, no transactions exist to commit or
rollback.
If all the statements complete without error, line 251 commits the statements
as a single transaction. If an error occurs, line 262 rolls back the transaction,
undoing all deletions. If the rollback fails, line 266 throws a new SQLException.
If the rollback was successful, lines 271 and 272 throw an SQLException
indicating that the transaction failed and was rolled back. If no errors occur, line
275 closes the Statement.
Search WWH ::
Custom Search