Introduction
JDBC is the de-facto standard for accessing relational database systems from the Java programming language. It defines a framework that provides a uniform interface to a number of different database connectivity modules.
Mimer SQL supports JDBC as one of its native application programming interfaces (API). The Mimer JDBC Driver is a Type 4 – Native Protocol All-Java Driver, also known as a Java thin driver. The Type 4 architecture uses a message protocol that is specific to Mimer SQL; as this means that there is no need for any intervening processes or translation, this architecture is extremely efficient.
A Type 4 driver is written completely in Java so that it can run on any client that supports the Java Virtual Machine (JVM). This makes the Mimer JDBC Driver an ideal solution for both Internet and intranet access and for use in application servers. An applet using the JDBC driver will run on any client browser (provided that the version of JVM is not so old that this feature is not available).
The driver is extremely small in size, so that it can be simply incorporated into an applet that can be downloaded over the Internet. No other Mimer software is required to be installed on the Java client, eliminating any need for configuration management on the client side.
For more details om the Mimer JDBC Driver, see the Mimer JDBC Driver Guide.
Description
This article is not intended to be a complete guide to the functionality provided by JDBC but it does provide a good introduction to accessing Mimer SQL through the JDBC object model.
Example
The examples are based on the sample schema that is installed into your Mimer SQL database environment using the BSQL tool, reading the crehotdb.sql statement file. The sample schema will be stored within a newly created databank named HOTELDB.
Connect
The connection provides the link between the application and the database server. To make a connection using the DriverManager class requires two operations:
- load the driver
- make the connection.
The class name of both versions of the Mimer JDBC Driver is com.mimer.jdbc.Driver. The class name of the Mimer Jtrace Driver is com.mimer.jtrace.Driver. The jar file referenced in the CLASSPATH determines which version of the driver that is loaded.
Loading a Driver
Loading a driver using the standard Class.forName method is very simple:
import java.io.*;
import java.sql.*;
. . .
try {
Class.forName("com.mimer.jdbc.Driver");
} catch (java.lang.ClassNotFoundException cnf) {
System.err.println("JDBC driver not found");
return;
}
Alternatively the DriverManager, when it initializes, looks for a jdbc.drivers property in the system properties. The jdbc.drivers property is a colon-separated list of drivers.
The DriverManager attempts to load each of the named drivers in this list of drivers. The jdbc.drivers property can be set like any other Java property, by using the -D option:
java -Djdbc.drivers=com.mimer.jdbc.Driver class
The property can also be set from within the Java application or applet:
Properties prp = System.getProperties();
prp.put("jdbc.drivers",
"com.mimer.jdbc.Driver:com.mimer.jtrace.Driver");
System.setProperties(prp);
Note! Neither of the mechanisms used to load the driver specify that the application will actually use the driver. The driver is merely loaded and registered with the DriverManager.
Making a Connection
To make the actual database connection, a URL string is passed to the DriverManager.getConnection method in the JDBC management layer. The URL defines the data source to connect to. The JDBC management layer locates a registered driver that can connect to the database represented by the URL.
Supported URL Syntax
The Mimer JDBC Driver supports the following URL syntax:
jdbc:mimer:[URL-field-list][property-list]
All fields in the URL-field-list are optional. The database server host computer, with or without a user specification, is introduced by ‘//’ and the database name is introduced by ‘/’):
[//[user[:password]@]serverName[:portNumber]] [/databaseName]
A Connection object is returned from the getConnection method, for example:
String url = "jdbc:mimer://HOTELADM:HOTELADM@localhost/HOTELDB"; Connection con = DriverManager.getConnection(url);
Alternatively, the getConnection method allows the username and password to be passed as parameters:
url = "jdbc:mimer://localhost/HOTELDB"; con = DriverManager.getConnection(url, "HOTELADM", "HOTELADM");
Mimer JDBC Driver Property List
The property-list for the Mimer JDBC Driver is optional. The list is introduced by a leading question mark ? and where there are several properties defined they are separated by ampersands &:
?property=value[&property=value[&property=value]]
The following properties are supported:
- databaseName – Name of database server to access
- user – Username used to log in to the database
- password – Password used for the login
- serverName – Computer on which the database server is running, the default is localhost
- portNumber – Port number to use on the database server host, the default is 1360
Example Connection
The following demonstrates a connection using the driver properties:
url = "jdbc:mimer:?databaseName=HOTELDB" + "&user=HOTELADM" + "&password=HOTELADM" + "&serverName=localhost"; con = DriverManager.getConnection(url);
Alternatively a java.util.Properties object can be used:
Properties dbProp = new Properties();
dbProp.put("databaseName", "HOTELDB");
dbProp.put("user", "HOTELADM");
dbProp.put("password", "HOTELADM");
con = DriverManager.getConnection("jdbc:mimer:", dbProp);
Elements from the URL-field-list and the property-list can be combined:
url = "jdbc:mimer:/HOTELDB" + "?user=HOTELADM" + "&password=HOTELADM";
The DriverPropertyInfo class is available for programmers who need to interact with a driver to discover the properties that are required to make a connection. This enables a generic GUI tool to prompt the user for the Mimer SQL connection properties:
Driver drv;
DriverPropertyInfo [] drvInfo;
drv = DriverManager.getDriver("jdbc:mimer:");
drvInfo = drv.getPropertyInfo("jdbc:mimer:", null);
for (int i = 0; i < drvInfo.length; i++) {
System.out.println(drvInfo[i].name + ": " + drvInfo[i].value);
}
After connecting to the database, all sorts of information about the driver and database is available through the use of the getMetadata method:
DatabaseMetaData dbmd;
dbmd = con.getMetaData();
System.out.println("Driver " + dbmd.getDriverName());
System.out.println(" Version " + dbmd.getDriverVersion());
System.out.println("Database " + dbmd.getDatabaseProductName());
System.out.println(" Version " + dbmd.getDatabaseProductVersion ());
The close method tells JDBC to disconnect from the Mimer SQL database server. JDBC resources are also released:
con.close
It is usual for connections to be explicitly closed when no longer required. The normal Java garbage collection has no way of freeing external resources, such as the Mimer SQL database server.
Error handling
The Class SQLException
The class SQLException provides information relating to database errors. Details include a textual description of the error, an SQLState string, and an error code. There may be a number of SQLException objects for a failure.
try {
. . .
}
catch(SQLException sqe) {
SQLException stk;
stk = sqe; // Save initial exception for stack trace
System.err.println("\n*** SQLException:\n");
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();
}
stk.printStackTrace(System.err);
}
The Class SQLWarning
The class SQLWarning provides information relating to database warnings. The difference between warnings and exceptions is that warnings, unlike exceptions, are not thrown.
The getWarnings method of the appropriate object (Connection, Statement or ResultSet) is used to determine whether warnings exist.
Warning information can be retrieved using the same mechanisms as in the SQLException example above but with the method getNextWarning retrieving the next warning in the chain:
con = DriverManager.getConnection(url);
checkSQLWarning(con.getWarnings());
. . .
private static boolean checkSQLWarning( SQLWarning sqw )
throws SQLException {
boolean rc = false;
if (sqw != null) {
rc = true;
System.err.println("\n*** SQLWarning:\n");
while (sqw != null) {
System.err.println("Message: " + sqw.getMessage());
System.err.println("SQLState: " + sqw.getSQLState());
System.err.println("NativeError: " + sqw.getErrorCode());
System.err.println();
sqw = sqw.getNextWarning();
}
}
return rc;
}
JDBC and Batch Update Operations
JDBC 2 provides support for batch update operations. The class BatchUpdateException 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:
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.
Transaction Processing
A transaction is an essential part of database programming. It defines the beginning and end of a series of database operations that are regarded as a single unit. For example, to transfer money between two bank accounts, an amount is subtracted from one account and the same amount is added to the other account. It is essential that either both of these operations succeed or neither does.
Mimer SQL uses a method for transaction management called Optimistic Concurrency Control (OCC). OCC does not involve any locking of rows as such, and therefore cannot cause a deadlock. Most other DBMSs offer pessimistic concurrency control. Pessimistic concurrency control protects a user’s reads and updates by acquiring locks on rows (or possibly database pages, depending on the implementation). These locks may force other users to wait if they try to access the locked items. The user that ‘owns’ the locks will usually complete their work, committing the transaction and thereby freeing the locks so that the waiting users can compete to attempt to acquire the locks. By completely eliminating the complicated locking overheads required by other DBMSs, Mimer SQL is able to avoid problems such as data being left inaccessible as a result of locks being held over user interactions or as a result of client processes failing.
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 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.
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, a transaction is started. The transaction continues until either the commit or the rollback method is called; after that a new transaction is automatically started.
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:/HOTELDB";
con = DriverManager.getConnection(url, "HOTELADM", "HOTELADM"); con.setAutoCommit(false);
// Explicit transaction handling
stmt = con.createStatement();
// Loop until transaction successful (or max attempts exceeded)
for (transactionAttempts = 1;; transactionAttempts++) {
// Perform a sequence of operations under transaction control
stmt.executeUpdate("update ROOM_PRICES set PRICE = PRICE * 1.05" +_ " where HOTELCODE = 'LAP'");
. . .
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;
}
}
The setTransactionIsolation method sets the transaction isolation level. The default isolation level for Mimer SQL is TRANSACTION_REPEATABLE_READ.
Note! With Enterprise Java Beans (EJB), the EJB environment provides the transaction management and therefore explicit transaction management is not required.
Executing a Command
The Connection object supports three types of Statement objects which 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 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:
Statement stmt;
int rowCount;
stmt = con.createStatement();
rowCount = stmt.executeUpdate("update ROOM_PRICES" +
" set PRICE = PRICE * 1.05" +_
" where HOTELCODE = 'LAP'");
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 ROOM_PRICES" +
" set PRICE = cast((cast(PRICE as float) * ?) as integer)" +
" where HOTELCODE = ?");
pstmt.setFloat(1, 1.05f);
pstmt.setString(2, "LAP");
rowCount = pstmt.executeUpdate();
pstmt.setFloat(1, 1.08f);
pstmt.setString(2, "WIND");
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 charge_room(?, ?)}");
cstmt.setString(1, "SKY101");
cstmt.setString(2, "900");
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(3, null);
A more complicated example illustrates how to handle an output parameter:
cstmt = con.prepareCall("{call FREEQ(?, ?, ?, ?, ?)}");
cstmt.setString(1, "STG");
cstmt.setString(2, "SSGLS");
cstmt.setString(3, "2002-12-24");
cstmt.setDate(4, Date.valueOf("2003-01-08"));
cstmt.registerOutParameter(5, Types.INTEGER);
cstmt.executeUpdate();
System.out.println(cstmt.getInt(5) + " rooms are available");
Enhancing Performance
The batch update functionality within JDBC 2 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:
PreparedStatement pstmt;
int [] affectedCount;
pstmt = con.prepareStatement("update ROOM_PRICES" +
" set PRICE = cast((cast(PRICE as float) * ?) as integer)" +
" where HOTELCODE = ?");
pstmt.setFloat(1, 1.05f);
pstmt.setString(2, "LAP");
pstmt.addBatch();
pstmt.setFloat(1, 1.08f);
pstmt.setString(2, "WIND");
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 the section on 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 HOTEL");
while (rs.next()) {
System.out.println(rs.getString("NAME"));
}
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 HOTEL");
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 in JDBC 2 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 JDBC 2 specification included scrollable cursors and the Mimer JDBC 2 Driver supports 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.
Result Set Capabilities
A ResultSet 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 updateable 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 updateable:
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Even if the options used specify that the result set will be scrollable and updateable, it is possible that the actual SQL query will return a ResultSet that is non-scrollable or non-updateable.
Positioning the Cursor
There are a number of methods provided to position the cursor: absolute, afterLast, beforeFirst, first, last, next, previous and relative.
There are also methods to determine the current position of the cursor: isAfterLast, isBeforeFirst, isFirst and isLast.
The getRow method returns the current cursor position, starting from 1. This provides a simple means to finding the number of rows in a result set, for example:
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("select NAME, CITY from HOTEL");
System.out.println("Original sort order");
while (rs.next()) {
System.out.println(rs.getString(1) + " in " + rs.getString(2));
}
System.out.println("Reverse order");
while (rs.previous()) {
System.out.println(rs.getString(1) + " in " + rs.getString(2));
}
rs.last();
System.out.println("There 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.
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();
select.setCursorName("HTL"); /* Name the cursor */
rs = select.executeQuery("select CITY" +
" from HOTEL" +
" where NAME = 'SKYLINE'" +
" for update of CITY");
update = con.prepareStatement("update HOTEL" +
" set CITY = ?" +
" where current of HTL");
while (rs.next()) {
if (rs.getString("CITY").startsWith("New York")) {
update.setString(1, "Uppsala");
} else {
update.setString(1, "New York");
}
update.executeUpdate();
}
Benefits
One of the major advantages that Java offers is that it is portable. An application written in the Java language will run on all of the major platforms. The JDBC API extends that ability and provides access to any Mimer SQL data source from any platform that supports the Java Virtual Machine.
The JDBC API is a natural Java interface for working with Mimer SQL. Programmers who are familiar with ODBC will find it extremely easy to learn.
Links
The Mimer JDBC drivers available for download are found under the Mimer JDBC heading on the download page.
For further details, there is a Mimer JDBC Driver Guide available at the Documentation site.
Read more and download java from www.java.com/download »
