Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Programming With JDBC


This chapter describes some programming aspects when using the Mimer JDBC Driver.

We recommend you to read JDBCBench, a Java Database Case Study available on our developer Web site, http://developer.mimer.com/features/feature_16.htm.

Examples in this Chapter

The examples are based on the sample schema that is provided as part of the Mimer SQL distribution. They assume that the example database environment has been created.

Transaction Processing

Mimer SQL uses a method for transaction management called Optimistic Concurrency Control. OCC does not involve any locking of rows as such, and therefore cannot cause a deadlock.

JDBC Transactions

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

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:

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:

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

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


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX