Transaction Processing

JDBC Transactions

JDBC transactions are controlled through the Connection object. There are two modes for managing transactions within JDBC:

• auto-commit
• manual-commit.

The setAutoCommit method is used to switch between the two modes.

Auto-commit Mode

Auto-commit mode is the default transaction mode for JDBC. When a connection is made, it is in auto-commit mode until setAutoCommit is used to disable auto-commit.

In auto-commit mode each individual statement is automatically committed when it completes successfully, no explicit transaction management is necessary. However, the return code must still be checked, as it is possible for the implicit transaction to fail.

Manual-commit Mode

When auto-commit is disabled, i.e. manual-commit is set, all executed statements are included in the same transaction until it is explicitly completed.

When an application turns auto-commit off, the next statement against the database starts a transaction. The transaction continues either the commit or the rollback method is called. The next command sent to the database after that starts a new transaction.

Calling the commit method ends the transaction. At that stage, Mimer SQL checks whether the transaction is valid and raises an exception if a conflict is identified.

If a conflict is encountered, the application determines how to continue, for example whether to automatically retry the transaction or inform the user of the failure. The application is notified about the conflict by an exception that must be caught and evaluated.

A request to rollback a transaction causes Mimer SQL to discard any changes made since the start of the transaction and to end the transaction.

Use the commit or rollback methods, rather than using the SQL COMMIT or ROLLBACK statements to complete transactions, for example:

 Statement stmt;
 int transactionAttempts;
 
 final int MAX_ATTEMPTS = 5; // Maximum transaction attempts
 
 // Open a connection
 url = "jdbc:mimer:/ExampleDB";
 con = DriverManager.getConnection(url, "MIMER_ADM", "admin");
 
 con.setAutoCommit(false); // Explicit transaction handling
 
 stmt = con.createStatement();
 
 // Loop until transaction successful (or max attempts exceeded)
 for (transactionAttempts = 1; ; transactionAttempts++) {
     // Perform an operation under transaction control
     stmt.executeUpdate("UPDATE mimer_store.currencies"
                      + "   SET exchange_rate = exchange_rate * 1.05"
                      + "   WHERE code = 'USD'");
 
     try {
         con.commit(); // Commit transaction
 
         System.out.println("Transaction successful");
         break;
 
     } catch(SQLException sqe) {
         // Check commit error - allow serialization failure
         if (sqe.getSQLState().equals("40001")) {
             // Check number of times the transaction has been attempted
             if (transactionAttempts >= MAX_ATTEMPTS) {
                 // Raise exception with application defined SQL state
                 throw new SQLException("Transaction failure", "UET01");
             }
         }
         else {
             // Raise all other exceptions to outer handler
             throw sqe;
         }
     } finally {
         con.close();
     }
 }

Setting the Transaction Isolation Level

The setTransactionIsolation method sets the transaction isolation level. The default isolation level for Mimer SQL is TRANSACTION_REPEATABLE_READ.

Note: With Enterprise Java Beans, the EJB environment provides the transaction management and therefore explicit transaction management is not required.

Executing an SQL Statement

The Connection object supports three types of Statement objects that can be used to execute an SQL statement or stored procedure:

• a Statement object is used to send SQL statements to the database
• the PreparedStatement interface inherits from Statement
• the CallableStatement object inherits both Statement and PreparedStatement methods.

Using a Statement Object

The Connection method createStatement is used to create a Statement object that can be used to execute SQL statements without parameters.

The executeUpdate method of the Statement object is used to execute an SQL DELETE, INSERT, or UPDATE statement, i.e. a statement that does not return a result set, it returns an int indicating the number of rows affected by the statement, for example:

 int rowCount;
 
 stmt = con.createStatement();
 
 rowCount = stmt.executeUpdate(
                    "UPDATE mimer_store.currencies"
                  + "   SET exchange_rate = exchange_rate * 1.05"
                  + "   WHERE code = 'USD'");
 
 System.out.println(rowCount + " rows have been updated");

Using a PreparedStatement Object

Where an SQL statement is being repeatedly executed, a PreparedStatement object is more efficient than repeated use of the executeUpdate method against a Statement object.

In this case the values for the parameters in the SQL statement (indicated by ?) are supplied with the setXXX method, where XXX is the appropriate type for the parameter.

For example:

 PreparedStatement pstmt;
 int rowCount;
 
 pstmt = con.prepareStatement(
                    "UPDATE mimer_store.currencies"
                  + "   SET exchange_rate = exchange_rate * ?"
                  + "   WHERE code = ?");
 
 pstmt.setFloat(1, 1.05f);
 pstmt.setString(2, "USD");
 rowCount = pstmt.executeUpdate();
 
 pstmt.setFloat(1, 1.08f);
 pstmt.setString(2, "GBP");
 rowCount = pstmt.executeUpdate();

Using a CallableStatement Object

Similarly, when using stored procedures, a CallableStatement object allows parameter values to be supplied, for example:

 CallableStatement cstmt;
 
 cstmt = con.prepareCall("CALL mimer_store.order_item( ?, ?, ? )");
 
 cstmt.setInt(1, 700001);
 cstmt.setInt(2, 60158);
 cstmt.setInt(3, 2);
 cstmt.executeUpdate();
 

The setNull method allows a JDBC null value to be specified as an IN parameter. Alternatively, use a Java null value with a setXXX method.

For example:

 pstmt.setString(4, null);
 

A more complicated example illustrates how to handle an output parameter:

 CallableStatement cstmt;
 
 cstmt = con.prepareCall("CALL mimer_store.age_of_adult( ?, ? )");
 
 cstmt.setString(1, "US");
 cstmt.registerOutParameter(2, Types.CHAR);
 
 cstmt.executeUpdate();
 System.out.println(cstmt.getString(2) + " years");

Batch Update Operations

JDBC provides support for batch update operations. The BatchUpdateException class provides information about errors that occur during a batch update using the Statement method executeBatch.

The class inherits all the method from the class SQLException and also the method getUpdateCounts which returns an array of update counts for those commands in the batch that were executed successfully before the error was encountered.

For example:

 try {
 
 ...
 
 } catch(BatchUpdateException bue) {
     System.err.println("\n*** BatchUpdateException:\n");
 
     int [] affectedCount = bue.getUpdateCounts();
     for (int i = 0; i < affectedCount.length; i++) {
         System.err.print(affectedCount[i] + "  ");
     }
     System.err.println();
 
     System.err.println("Message:     " + bue.getMessage());
     System.err.println("SQLState:    " + bue.getSQLState());
     System.err.println("NativeError: " + bue.getErrorCode());
     System.err.println();
 
     SQLException sqe = bue.getNextException();
     while (sqe != null) {
         System.err.println("Message:     " + sqe.getMessage());
         System.err.println("SQLState:    " + sqe.getSQLState());
         System.err.println("NativeError: " + sqe.getErrorCode());
         System.err.println();
 
         sqe = sqe.getNextException();
     }
 }
 

Note: The BatchUpdateException object points to a chain of SQLException objects.

Enhancing Performance

The batch update functionality allows the statement objects to support the submission of a number of update commands as a single batch.

The ability to batch a number of commands together can have significant performance benefits. The methods addBatch, clearBatch and executeBatch are used in processing batch updates.

The PreparedStatement example above could be simply rewritten to batch the commands.

For example:

 PreparedStatement pstmt;
 int [] affectedCount;
 
 pstmt = con.prepareStatement(
                    "UPDATE mimer_store.currencies"
                  + "   SET exchange_rate = exchange_rate * ?"
                  + "   WHERE code = ?");
 
 pstmt.setFloat(1, 1.05f);
 pstmt.setString(2, "USD");
 pstmt.addBatch();
 
 pstmt.setFloat(1, 1.08f);
 pstmt.setString(2, "GBP");
 pstmt.addBatch();
 
 affectedCount = pstmt.executeBatch();
 

The Mimer SQL database server executes each command in the order it was added to the batch and returns an update count for each completed command.

If an error is encountered while a command in the batch is being processed then a BatchUpdateException is thrown (see Error Handling) and the unprocessed commands in the batch are ignored.

In general it may be advisable to treat all the commands in the batch as a single transaction, allowing the application to have control over whether those commands that succeeded are committed or not.

Set the Connection object's auto-commit mode to off to group the statements together in a single transaction. The application can then commit or rollback the transaction as required.

Calling the method clearBatch clears a Statement object's list of commands.

Using the Close method to close any of the Statement objects releases the database and JDBC resources immediately. It is recommended that Statement objects be explicitly closed as soon as they are no longer required.

Result Set Processing

There are a number of ways of returning a result set. Perhaps the simplest is as the result of executing an SQL statement using the executeQuery method, for example:

 Statement stmt;
 ResultSet rs;
 
 stmt = con.createStatement();
 
 rs = stmt.executeQuery("SELECT *"
                      + "   FROM mimer_store.currencies");
 
 while (rs.next()) {
     System.out.println(rs.getString("CURRENCY"));
 

A ResultSet can be thought of as an array of rows. The 'current row' is the row being examined and manipulated at any given time, and the location in the ResultSet is the 'current row position'.

Information about the columns in a result set can be retrieved from the metadata, for example:

 Statement stmt;
 ResultSet rs;
 ResultSetMetaData rsmd;
 
 stmt = con.createStatement();
 
 rs = stmt.executeQuery("SELECT *"
                      + "   FROM mimer_store.currencies");
 
 rsmd = rs.getMetaData();
 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
     System.out.println(rsmd.getColumnName(i));
     System.out.println(" Type: " + rsmd.getColumnTypeName(i));
     System.out.println(" Size: " + rsmd.getColumnDisplaySize(i));
 }

Scrolling in Result Sets

The previous examples used forward-only cursors (TYPE_FORWARD_ONLY), which means that they only support fetching rows serially from the start to the end of the cursor, this is the default cursor type.

In modern, screen-based applications, the user expects to be able to scroll backwards and forwards through the data. While it is possible to cache small result sets in memory on the client, this is not feasible when dealing with large result sets. Support for scrollable cursors provide the answer.

Scrollable cursors allow you to move forward and back as well as to a particular row within the ResultSet. With scrollable cursors it is possible to iterate through the result set many times.

The Mimer drivers' scrollable cursors are of type TYPE_SCROLL_INSENSITIVE, which means that the result set is scrollable but also that the result set does not show changes that have been made to the underlying database by other users, i.e. the view of the database is consistent. To allow changes to be reflected may cause logically inconsistent results.

Positioning the Cursor

There are a number of methods provided to position the cursor:

• absolute
• afterLast
• beforeFirst
• first
• last
• next
• previous
• relative

There are also methods to determine the current position of the cursor:

• isAfterLast
• isBeforeFirst
• isFirst
• isLast

The getRow method returns the current cursor position, starting from 1. This provides a simple means of finding the number of rows in a result set.

For example:

 Statement stmt;
 ResultSet rs;
 
 stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
 
 rs = stmt.executeQuery("SELECT code, currency"
                      + "   FROM mimer_store.currencies"
                      + "   WHERE code LIKE 'A%'");
 
 System.out.println("\nOriginal sort order");
 while (rs.next()) {
     System.out.println(rs.getString(1) + "  " + rs.getString(2));
 }
 
 System.out.println("\nReverse order");
 while (rs.previous()) {
     System.out.println(rs.getString(1) + "  " + rs.getString(2));
 }
 
 rs.last();
 System.out.println("\nThere are " + rs.getRow() + " rows");
 

The Mimer JDBC Driver will automatically perform a pre-fetch whenever a result set is created. This means that a number of rows are transferred to the client in a single communication across the network. If only a small number of rows are actually required use setMaxRows to limit the number of rows returned in the result set.

Result Set Capabilities

A instance of the ResultSet class is created when a query is executed. The capabilities of the result set depend on the arguments used with the createStatement (or prepareStatement or prepareCall) method.

The first argument defines the type of the ResultSet, whether it is scrollable or non-scrollable, and the second argument defines the concurrency option, i.e. the update capabilities.

A ResultSet should only be made updatable if the functionality is going to be used, otherwise the option CONCUR_READ_ONLY should be used. If used, both the type and the concurrency option must be specified.

The following example creates a scrollable result set cursor that is also updatable:

 stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
 

Even if the options used specify that the result set will be scrollable and updatable, it is possible that the actual SQL query will return a ResultSet that is non-scrollable or non-updatable.

Holdable cursors

The mimjdbc3.jar driver supports the JDBC 3 specification. As such it provides an opportunity for application developers to create holdable cursors. The difference between a holdable cursor and a regular cursor is that regular cursors are closed at the end of the transaction. The holdable cursor can (theoretically) stay opened for an unlimited period of time. However, leaving a cursor open for a long period of time may have serious performance implications for the same reason long lasting transactions may impair server performance.

Updating Data

Applications can update data by executing the UPDATE, DELETE, and INSERT statements. An alternative method is to position the cursor on a particular row and then use DELETE CURRENT, or UPDATE CURRENT statements.

The following example illustrates how this can be done:

 Statement select;
 PreparedStatement update;
 ResultSet rs;
 
 select = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                              ResultSet.CONCUR_UPDATABLE);
 
 select.setCursorName("CRN"); /* Name the cursor */
 
 rs = select.executeQuery("SELECT currency"
                        + "   FROM mimer_store.currencies"
                        + "   WHERE code = 'ALL'"
                        + "   FOR UPDATE OF currency");
 
 update = con.prepareStatement("UPDATE mimer_store.currencies"
                             + "   SET currency = ?"
                             + "   WHERE CURRENT OF crn");
 
 while (rs.next()) {
     if (rs.getString("CURRENCY").startsWith("Leke")) {
         update.setString(1, "Albanian Leke");
     }
     else {
          update.setString(1, "Leke");
     }
     update.executeUpdate();
 }

User-Defined Types

Whenever the application working with user-defined types, a type mapping is used. For DISTINCT types, the default type mapping is given by the core SQL type which makes up the DISTINCT type. A structured type is mapped by default to a predefined JDBC interface which provides a basic functionality to work with its attributes.

Applications may alter the type mapping to integrate its own type classes with the database types. The custom type mapping will allow JDBC getter and setter methods to work directly with the classes in the application.

Default Type Mapping

By default, when fetching a user defined type from the database or supplying one to the database, the generic class java.sql.Struct is being used to hold the type attributes. Objects of this class simply holds an array of objects, each one corresponding the attribute in question.

For example, consider the following SQL type:

 create type NAME as (TITLE nvarchar(20),
                      GIVEN_NAME nvarchar(50),
                      FAMILY_NAME nvarchar(50));
 
   When retrieving columns of this type, the method ResultSet.getObject is used, which returns a java.sql.Struct object. For example:
 ResultSet rs = stmt.executeQuery("select EMPLOYEE_NAME from EMPLOYEES");
 while (rs.next()) {
    Struct employee_name = rs.getObject(1);
    Object[] employee_name_attributes = employee_name.getAttributes();
    String title = (String)employee_name_attributes[0];
    String given_name = (String)employee_name_attributes[1];
    String family_name = (String)employee_name_attributes[2];
 
    /* At this point the respective attributes are available in the
       above String objects for further processing. */
 }
 rs.close();

Custom Java Classes With Type Mapping

A more involved way is to map the SQL type against a Java class which implements the java.sql.SQLData interface. When mapping the SQL type name, the Java class might look like this:

 import java.sql.*;
 
 public class Name implements SQLData {
    String title,given_name,family_name;
    String type_name;
 
    /* SQLData interface routines. */
 
    public String getSQLTypeName() {
        return type_name;
    }
 
    public void readSQL(java.sql.SQLInput stream,String typeName) throws 
java.sql.SQLException
    {
        type_name = typeName;
        title = stream.readString();
        given_name = stream.readString();
        family_name = stream.readString();
    }
 
    public void writeSQL(java.sql.SQLOutput stream) throws 
java.sql.SQLException
    {
        stream.writeString(title);
        stream.writeString(given_name);
        stream.writeString(family_name);
    }
 
    /* Here follows additional methods to define the characteristics of
       this class.
     * This might be ordinary setter/getter methods for applications to use,
       for example the following
     */
 
    public String getCombinedName()
    {
        if (title!=null && title.length()>0)
            return title+" "+given_name+" "+family_name;
        return given_name+" "+family_name;
    }
 
    public String getTitle()
    {
        return title;
    }
 
    public void setTitle(String title)
    {
        this.title = title;
    }
 
    public String toString()
    {
        return getCombinedName();
    }
 }
 

The application must register its own type mapping with the connection to make the JDBC driver aware of the custom class. Whenever the custom type map is activated, the JDBC methods getObject and setObject will return and accept parameters of the specified class, for example the following will create a map between the SQL type MYSCHEMA.NAME and the Java class Name.

 java.util.Map map = con.getTypeMap();
 map.put("MYSCHEMA.NAME", Class.forName("Name"));
 con.setTypeMap(map);

Programming Considerations

Below is a summary of issues to be considered when programming with Mimer JDBC.

Interval Data

Both the JDBC specification and the Java language lack support for INTERVAL data types.

You can use the getString and setString methods for values accessed by a driver from database columns containing INTERVAL data.

Closing Objects

Although Java has automatic garbage collection, it is essential that you close JDBC objects, such as ResultSets, Statements and Connections, when done with them.

Closing objects gives your application better control over resources.

If you don't close objects, resources are kept allocated in the database server until garbage collection is triggered, this can exhaust server resources.

Increasing Performance

• Use Stored Procedures
  One of the main issues when trying to increase performance is to reduce network traffic. For example, you can increase performance by using the database server to return more accurate data instead of returning a large amount of unqualified data which your application must handle. You can achieve this by using more sophisticated SQL statements or by using stored procedures (PSM).
• Use More Than One Connection
  Mimer JDBC drivers are thread-safe and use one lock per connection. So, to achieve higher concurrency, an application should use several connections.
• Prefetching Data
  The drivers are implemented to perform automatic prefetch, i.e. whenever a resultSet is created, a buffer is filled with successive rows. This is an optimization for throughput, allowing more data to be fetched to the client in the single communication made.
  The flip side of the coin is that response time, i.e. the time it takes to receive the first record, may be increased (see Use setMaxRows below.)
• Use setMaxRows
  If you know that only a small number of records are to be fetched, then you can use the setMaxRows method to optimize the response time, i.e. to avoid an array fetch.
• Use PreparedStatements
  Another way of increasing performance is to avoid recompiling SQL statements unnecessarily. Whenever you use Statement.executeXXX methods, statements are compiled. Instead, use parameterized precompiled statements, i.e. PreparedStatement, whenever possible.
• Use Batched Statements
  Using the Mimer JDBC Driver version 2 or later, you can reduce the number of network requests by using batched statements.
  If, for example, you replace 20 calls to Statement.execute() with 20 calls to Statement.addBatch() followed by a Statement.executeBatch() call, 20 server network requests are replaced by a single network request.
  If response time is an issue, this simple change may give a twenty-fold performance improvement!
  Note that batched statements for PreparedStatement and CallableStatement differ from the implementation for the Statement class. When using PreparedStatement or CallableStatement, only a single SQL statement can be used in a batch. The addBatch() call (without argument) adds a set of parameters to the batch. When you use batches, the same SQL statement is called repeatedly with different parameters in a single network request.
  In versions 2 and later, you can use the setFetchSize method to control the amount of data fetched.