Contents: Approach Connection pool Using PreparedStatements Proper transaction utilization Conclusion Resources About the author Rate this article Related content: Build it to move: The architecture of pervasive computing Subscriptions: dW newsletters dW Subscription (CDs and downloads)

Roman Vichr (mailto:rvichr@ddlabs.com?cc=&subject=JDBC tips)
Senior Architect, DDLabs
1 October 2002

We've talked about making your application interfaces more robust and dynamic, and also about filtering content for mobile applications. We now need to focus on optimizing database connections to get the best results when pushing enterprise data to mobile device applications over HTTP (see Resources for a link to a recent developerWorks article with more information.)

The proper building of enterprise database connectivity has gained importance, and among the best choices for building applications for Connection Limited Device Configuration (CLDC) and writing MIDP- (mobile interface) specific interfaces lies in J2ME and Java technology.

Approach
Introducing a simple principle for a JDBC connection when bridging enterprise data to wireless mobile devices can help to make the solution more scalable and the performance more efficient.

Connection pool
Before any application can access a database, it has to access an active database connection. The database connection is a scarce, I/O-consuming operation and, if it must be created every time you want to use it, it will cost you a performance bottleneck.

For example, if you use the approach of Java servlets, which are created via the init() method and destroyed at the end of their life cycle (through the destroy() method), you avoid having to renew the connection every time the servlet is instantiated. Such an approach would lower the application's performance significantly. A better path for accomplishing the same thing is with a "connection pool," in which you initialize several connections (and the parameter can be read from the XML configuration file).

The connection itself is made up of a set of connection objects and a user request that keeps the connection open during the request. The key when creating a pool is to use, in the database access code, the following blocks: try{}.. catch{}... finally{}.. Then you use close() to ensure that the connection is actually returned to the connection pool rather than completely closed. Specifying close() inside a 'finally{}' block allows an exception in execution to be caught, and the statement to still be executed -- the connection returns to the connection pool, preventing a "connection leak" in the application.

Following is an example of building a JDBC connection:

Connection con = null;
try {  ds = (DataSource)myContext.lookup("<specify JDBC driver>");
  pooledCon = ds.getConnection("scott", "tiger");
 // Processing Code goes here
} catch (Exception ignored) {
  // catch JNDI or JDBC exceptions here 
} finally {
  if(pooledCon != null)
    pooledCon.close();
}

Using PreparedStatements
The PreparedStatement object is considered more efficient than Statementobjects, especially if you have to execute the same statement multiple times and the only difference is a different parameter. The PreparedStatement allows you to "compile" the SQL statement once, which, although initially more time consuming, then caches it for efficient reuse. It also provides a more readable code.

An additional advantage is the automatic escaping by the driver of the passed string into the statement by the user. That means, for instance, when you try to insert the string "D'Marco" into a character-based data field (it could be VARCHAR, VARCHAR2, CHAR, etc.), the SQL statement won't hit the first apostrophe and fail catastrophically.

Additional good practice when using a PreparedStatement object is to "close it" by calling close() method on the object, which was used to run the SQL statement. This closes any associated cursors with the executed SQL statement so as to prevent database littering with open cursors.

Following is an example of building a PreparedSatement:

PreparedStatement sqlstmt = dbCon.prepareStatement("select * 
from table1 where field_1=?");
sqlstmt.setInt(1, 12);
ResultSet rs = sqlstmt.executeQuery();
// close the resultset statement to avoid hanging cursors in database
sqlstmt.close();
// processing of new statement
sqlstmt = dbCon.prepareStatement("select * from table2 where field_2 = ?");
// repeat creating the result set 

Proper transaction utilization
An often forgotten aspect of dynamic forms and data updates is that when updating or inserting data into more than one table representing one logical transaction, the transaction should be reflected in tables at the same time -- or not at all -- in each table in case a transaction fails by "rolling" the transaction back.

Some core JDBC packages support four transaction isolation modes which allow programs to specify how they want transactions to behave. Most of them support at least two: "read committed" (default) and "serializable." Use "read committed" when the non-repeatable reads should allow modifications by one transaction to be visible to another between queries; use phantom reads for modifications made by another transaction to become visible while a query runs. The stricter "serializable" settings should be used when you need a fully consistent view of the database across multiple operations. Another useful thing to remember is to set the connection to autocommit = "false."

Following is an example of building a connection and setting its property parameters:

Connection con = null;
try {
 dtsr = (DataSource <the JDBC driver you use>");
 pConn = dtsr.getConnection("<specify login>", "<specify password>");
 pConn.setAutoCommit(false); // transaction are not committed uponm execution

 pConn.setTransactionIsolation(
 Connection.TRANSACTION_SERIALIZABLE);
 // pConn is pooled connection
 pConn.commit();
} catch (Exception ignored) {
  try { pConnn.rollback(); } catch (SQLException esgl) {} 
} finally {
  if(pConn != null) {
    pConn.setAutoCommit(true); //reset autocommit
    pConn.close();
  }
}

You can also take advantage of an optional JDBC package -- JTA (Java Transaction API) -- which allows easy integration with fully fledged transactional servers.

Conclusion
The purpose of this tip has been to help you make your mobile applications displaying enterprise data to work more reliably and efficiently. It cannot, however, compensate for incorrect coding or deployment. Always make sure you're writing code properly and test, test, test!

Resources

• Learn more about pushing enterprise data to mobile device applications over HTTP in Build it to move: The architecture of pervasive computing (developerWorks, May 2002).
  
  
• Download the JTA specification, interfaces, and javadoc files.
  
  
• Download the JDBC Data Access API and specifications -- also free of charge.
  
  
• This developerWorks article (August 2001) goes over P2P interoperability through the use of JXTA.
  
  
• What is new in JDBC 3.0 is a good written overview of the product's features and enhancements (developerWorks, July 2001).
  
  
• Download the various J2ME technologies, in a variety of languages, as well as the accompanying documentation.
  
  
• An earlier tip from Roman discusses taking mobile devices the P2P route with JXTA (developerWorks, April 2002).
  
  
• Another previous tip goes over mobile device detection (developerWorks, June 2002).
  
  
• A veritable starter kit for JXTA is yours in this paper by Jonathan Knudsen, including instruction and dowloads.
  
  

What do you think of this document? Killer! (5) Good stuff (4) So-so; not bad (3) Needs work (2) Lame! (1) Comments?

developerWorks > Wireless | Java technology