Transactions in Java Database Programming

You can group a set of statements to form a transaction. The transaction can be committed when all has gone well—or, if an error has occurred in one of them, it can be rolled back as if none of the statements had been issued.

The major reason for grouping statements into transactions is database integrity. For example, suppose we want to transfer money from one bank account to another. Then, it is important that we simultaneously debit one account and credit another. If the system fails after debiting the first account but before crediting the other account, the debit needs to be undone.

If you group update statements into a transaction, the transaction either suc­ceeds in its entirety and can be committed, or it fails somewhere in the middle. In that case, you can carry out a rollback and the database automatically undoes the effect of all updates that occurred since the last committed transaction.

1. Programming Transactions with JDBC

By default, a database connection is in autocommit mode, and each SQL state­ment is committed to the database as soon as it is executed. Once a statement is committed, you cannot roll it back. Turn off this default so you can use transactions:

conn.setAutoCommit(false);

Create a statement object in the normal way:

Statement stat = conn.createStatement();

Call executeUpdate any number of times:

stat.executeUpdate(command₁);

stat.executeUpdate(command₂);

stat.executeUpdate( command₃);

…

If all statements have been executed without error, call the commit method:

conn.commit();

However, if an error occurred, call

conn.rollback();

Then, all statements since the last commit are automatically reversed. You typically issue a rollback when your transaction was interrupted by a SQLException.

2. Save Points

With some databases and drivers, you can gain finer-grained control over the rollback process by using save points. Creating a save point marks a point to which you can later return without having to abandon the entire transaction. For example,

Statement stat = conn.createStatement(); // start transaction;

rottback() goes here stat.executeUpdate(command1);

Savepoint svpt = conn.setSavepoint(); // set savepoint; rottback(svpt) goes here

stat.executeUpdate(command2);

if (. . .) conn.rottback(svpt); // undo effect of command2

…

conn.commit();

When you no longer need a save point, you should release it:

conn.reteaseSavepoint(svpt);

3. Batch Updates

Suppose a program needs to execute many INSERT statements to populate a database table. You can improve the performance of the program by using a batch update. In a batch update, a sequence of statements is collected and submitted as a batch.

The statements in a batch can be actions such as INSERT, UPDATE, or DELETE as well as data definition statements such as CREATE TABLE or DROP TABLE. An exception is thrown if you add a SELECT statement to a batch. (Conceptually, a SELECT state­ment makes no sense in a batch because it returns a result set without updating the database.)

To execute a batch, first create a Statement object in the usual way:

Statement stat = conn.createStatement();

Now, instead of calling executeUpdate, call the addBatch method:

String command = “CREATE TABLE . . .”

stat.addBatch(command);

white (. . .)

{

command = “INSERT INTO . . . VALUES (” + . . . + “)”;

stat.addBatch(command);

}

Finally, submit the entire batch:

int[] counts = stat.executeBatch();

The call to executeBatch returns an array of the row counts for all submitted statements.

For proper error handling in batch mode, treat the batch execution as a single transaction. If a batch fails in the middle, you want to roll back to the state before the beginning of the batch.

First, turn the autocommit mode off, then collect the batch, execute it, commit it, and finally restore the original autocommit mode:

boolean autoCommit = conn.getAutoCommit();

conn.setAutoCommit(false);

Statement stat = conn.getStatement();

…

// keep catting stat.addBatch(. . .);

…

stat.executeBatch();

conn.commit();

conn.setAutoCommit(autoCommit);

4. Advanced SQL Types

Table 5.8 lists the SQL data types supported by JDBC and their equivalents in the Java programming language.

A SQL ARRAY is a sequence of values. For example, in a Student table, you can have a Scores column that is an ARRAY OF INTEGER. The getArray method returns an object of the interface type java.sqt.Array. That interface has methods to fetch the array values.

When you get a LOB or an array from a database, the actual contents are fetched from the database only when you request individual values. This is a useful performance enhancement, as the data can be quite voluminous.

Some databases support ROWID values that describe the location of a row so that it can be retrieved very rapidly. JDBC 4 introduced an interface java.sqt.RowId and the methods to supply the row ID in queries and retrieve it from results.

A national character string (NCHAR and its variants) stores strings in a local char­acter encoding and sorts them using a local sorting convention. JDBC 4 pro­vided methods for converting between Java String objects and national character strings in queries and results.

Some databases can store user-defined structured types. JDBC 3 provides a mechanism for automatically mapping structured SQL types to Java objects.

Some databases provide native storage for XML data. JDBC 4 introduced a SQLXML interface that can mediate between the internal XML representation and the DOM Source/Resutt interfaces, as well as binary streams. See the API documentation for the SQLXML class for details.

We do not discuss these advanced SQL types any further. You can find more information on these topics in the JDBC API Tutorial and Reference and the JDBC specification.

Source: Horstmann Cay S. (2019), Core Java. Volume II – Advanced Features, Pearson; 11th edition.