Dr. Dobb's is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Channels ▼

JVM Languages

Mar02: Java Q&A

Mar02: Java Q&A

Michael is a Ph.D. candidate at the University of Texas at Austin where he develops Java applications for astrophysics research. He can be reached at [email protected].

Keeping up with the cutting edge of research generally requires sifting through piles of journals and reading tons of papers in your field. This can be a full-time job. Often, however, this activity can be shared amongst groups of researchers, with each person responsible for reading and filtering out the less important papers. The rest of the group would then only need to read and discuss a much smaller number of papers, based on the filtering. This scenario, though, requires close communication among researchers and is hard to achieve, especially if the group is not situated in the same geographical location. To improve remote communication among the participants in my group, I developed a research information system that lets collaborators around the world put in new papers, classify them into categories, and make comments. A web-based, database-powered application server was an obvious choice to give users anywhere in the world access at anytime.

The web application interacts with a relational database (MySQL) on the back end, and presents data through web interfaces using JavaServer Pages (JSP). The database contains articles, categories, comments, and user-related information in separate tables, as well as the relationships among them. For example, an article can belong to any number of categories and a category can contain any number of articles. That many-to-many mapping relationship is stored in the ArticleCategory table. Each entry in that table is a pair of article and category IDs. Listing One (schema.sql) is the database schema for tables Article, Category, and ArticleCategory.

I used JSP to handle data presentation and user interaction. JSP accesses the back-end database through a middle layer of Java classes. The advantage of this design is that the presentation logic is separated from the internal data storage structure. Therefore, database experts can work on the back end and communication experts work on the web site, independent of each other. Then, ideally, I would only need to make small changes in the middle layer to match the changes on either side. Obviously, it was important to make the middle layer simple yet flexible so that changes can be made correctly and easily.

However, Java's object structure is not compatible with the relational structure of SQL databases; so designing a simple, flexible structure to map relational data tables to objects was a major challenge. In this article, I discuss my experiences in implementing that SQL table and Java object mapping. To simplify the discussion, I use the Article table as an example. The mapping for other tables can be done in a similar manner.

A capable database server can take the burdens off the web application by running stored procedures, supporting transactions, and so on. But for maximum portability — especially among small database servers — I assume the database does nothing more than execute standard SQL commands.

The Simple Approach: Static Methods

I started by writing static methods for each database operation and grouped them into classes named after database tables. Those database operations were accomplished through raw JDBC calls. A table row is stored in a Hashtable. The field names are "keys" and values are "values." This approach was quick to implement and easy to add/remove testing/debugging methods during the initial experimental stages; however, it is not an object-oriented design. The classes were only collections of methods. Without an efficient design, new methods can be added randomly, and at will. The number of methods and the relations among them would quickly grow out of control and, even worse, the data and the methods were separated. Hashtable does not provide a readily available method to ensure data integrity, and programmers have to make sure that they do not make any mistakes in constructing SQL statements. One typo in a fieldname can be hard to debug. Clearly, this was not an optimal solution for a complex production system.

The Sophisticated Approach: Enterprise JavaBeans

A more sophisticated approach is to use Enterprise JavaBeans (EJBs) technology. EJBs run in a separate server called the "EJB container." Applications access the beans through the container. The EJB container automatically provides many support services for beans such as security, transaction, and database connection pooling. This approach provides tight integration with other Java technologies.

In an EJB solution, each bean corresponds to a row in a database table. The Remote interface of the bean declares methods to access data fields in the row and other business methods. The Home interface declares factory methods to create new beans or find multiple beans. The EJB container implements most of those methods by using automatically generated SQL statements. To access the database, the application obtains proxy objects implementing those two interfaces from the EJB container.

The container-generated Java and SQL code greatly reduce human errors and offer flexibility. The SQL templates can be defined in the EJB container configuration file. When the database schema is changed, you only need to change the configuration file without rewriting or recompiling the beans. (A tutorial for constructing database access EJBs can be found at http://java.sun.com/j2ee/j2sdkee/techdocs/guides/ejb/html/DevGuideTOC.html.)

Although EJB looks like a good solution, it is not designed for small applications. Instead, it was originally designed for distributed applications and its method calls are passed through slow network protocols with the overhead of constructing proxy objects. EJB 2.0 has improved single computer performance with a new Home interface, but running an EJB container can still be resource intensive and expensive. Few affordable web hosting companies provide EJB support.

For my small-sized application, I did not need something that sophisticated and expensive, but I did need a flexible solution to minimize the headaches for future changes. I also wanted to reuse much of the similar SQL code to minimize the chances for human errors. I decided to adapt some of the design patterns from the EJB approach but write my own class hierarchy. As an added bonus, this approach also allowed me to transfer my code to EJB-based solutions in the future — when my application grows big enough to justify the cost. However, without the EJB container, I lose useful features such as integrated database connection pooling and transaction support. To compensate for that, I either wrote at least part of those features or used third-party libraries.

New Architecture

Interface TableRow defines a set of methods to access and modify a row of data; see Listing Two. TableRow is somewhat like the Remote interface in EJBs. Each instance of a class implementing TableRow corresponds to a row of data in a database table. The setValue()/getValue() methods can query/update fields in the current row object. The changes are permanently committed into the database when commit() is called. Those methods are generic versions of the getXXXX()/setXXXX() methods in the EJB approach.

Abstract class Table implements the TableRow interface and a number of factory methods (create and find) to construct new TableRow objects; see Table.java (available electronically; see "Resource Center," page 5). It is somewhat similar to EJB's Home interface. Each class extending Table corresponds to a table in the database. Class Table serves as both a base of the type hierarchy structure and a reusable code repository. The method create() inserts a new row in the database and returns the corresponding TableRow object. A row in the database can be deleted by the deleteByPrimaryKey() method. Method findByFieldName() returns all the rows that match the given field:value pair. That method is a generic version of EJB's findByXXXX() method.

Now, to map the database table Article to the class Article, all I have to do is supply a constructor such as in Article.java (also available electronically). The constructor assigns values such as table name, field name, and the full name of the current class. Then, the constructor needs to call method init(), which is defined in the base class Table, to do some further initialization work. Figure 1 shows the structure of the application and the class diagram of the middle layer. For simplicity, I assumed that each database table has a Long Integer type trivial Primary Key field. This field does not have any meaning other than uniquely identifying the row. It is like a serial number. I assign the primary key value for each row automatically inside the middle layer when the row is inserted into the database.

When I programmed the JSP pages to interact with the middle layer, I first initialized an instance of class Article to access the factory methods. Then I retrieved rows from the database table into TableRow objects using the factory methods. The content of each row can now be viewed and updated using the getValue()/setValue() methods in Listing Three.

SQL Types

One of the biggest problems I had when constructing a generic base class was how to handle different SQL/Java types. The generic class does not know what types it will encounter in real-world tables. However, the JDBC driver and the SQL syntax require different treatments for different SQL/Java types. I solved this by using generic methods in JDBC's PreparedStatement and ResultSet classes. PreparedStatement automatically escapes any illegal SQL text. It also runs much faster than raw Statement by precompiling the SQL templates.

Generic methods PreparedStatement.setObject() and ResultSet.getObject() can handle all standard SQL types correctly. They use a JDBC standard mapping between Java object types and SQL types to construct proper SQL statements and retrieve data into objects with proper Java types. One trade-off is that all the data retrieved from the database is java.lang equivalent objects rather than Java built-in types. When I create new data rows, I also need to pass the values as java.lang objects. An exception will be thrown if the types cannot be matched correctly. The standard mapping for my example is noted in the comments in Listing One.

This object-based approach does not impose any major inconvenience on programmers. Most database contents are already either String or Blob types, which have no equivalent Java built-in types anyway.

If you have your own user-defined SQL types in tables, you need to define the corresponding Java types that implement the SQLData interface. You then need to establish the links between the custom SQL types and custom Java types in a new type map and inform the Connection object through Connection.setTypeMap(). I could have made the class Table more generic by taking custom type maps. But since this is a rarely used feature, I assume standard type map for simplicity.

Database Connections

Database connections are expensive. Creating connection context, opening network sockets, and destroying connection context are resource- and time-consuming processes on both the application and database server sides. It is inefficient to create a new connection for each SQL statement.

The EJB approach addresses this problem by using a database connection pool. The pool is a set of preestablished connection objects in the memory that is maintained by the container. When users request a new connection, the container simply fetches one from the pool instead of making a new one. When users close the connection, the connection is returned to the pool. That is considerably faster than working through the slow network protocol.

Although I do not run EJB containers, I can still do database connection pooling through third-party utilities, of which there are several available. I used the PoolMan package from CodeStudio (http://codestudio.com/), a generic Java object pool utility. The static code section in class Table loads in the connection pool driver. That makes sure that the driver is loaded before any Table or TableRow objects are initialized. At load time, the pool driver reads the configuration file poolman.xml for the appropriate JDBC driver and database connection information. File poolman.xml must be located in the JVM's classpath.

After the connection pool driver is loaded, Connection conn = DriverManager.getConnection("jdbc:poolman://" + databaseName); fetches a connection from the pool, and conn.close(); returns the connection (conn) back to the pool.

Synchronization (Transaction)

Although a complete transaction support system is beyond the scope of my project, I still wanted some basic synchronization so that the data would not get corrupted under normal multiuser conditions. My approach was to synchronize potentially conflicting code segments against the table class object: Class.forName(className). Since there is only one class object for each table in a single JVM, this approach guarantees that only one synchronized code segment is accessing the table at any give time. Of course, it will not work if the middle layer runs across several JVMs.

Another concern about data integrity comes from long updates. Suppose the application needs to update multiple fields of a row, one by one. If something goes wrong (software exception, JVM dies, and so on) in the middle of the process, some of the fields might get updated while others do not. That could cause serious database integrity problems. The commit() method can partly simulate atomic transactions and reduce the chance for such problems. The application can update the row using setValue() and the updated values can be accessed via the getValue() method. But no permanent changes will be made to the database until commit() is called. Method commit() uses a single SQL statement to do the updates. If it fails, no field is updated and the database integrity is not violated.

Adding More Table-Specific Methods

In addition to the generic methods already defined and implemented for the TableRow and Table classes, I can also add table-specific methods in the table class implementations. Those methods can conduct specific operations related to the current table. For example, for an Article row, I might want a method to report if any of the text fields are empty. I can implement such methods in the Article class.

If similar user-defined methods are implemented repeatedly in some table classes, it might be advantageous to establish new layers of abstraction for code reuse and a clear type hierarchy. It is conceivable that entity tables such as Article and Category need a different set of table-specific methods compared with relation tables such as ArticleCategory. For this purpose, I added an additional layer of interfaces and abstract classes, which are inherited from TableRow and Table, to model the two types of database tables. Figure 2 illustrates the new class diagram. My simple and flexible design has made it easy to map complex data structures and add complex business logic into the middle layer.


Listing One

# schema.sql
# NOTE: The SQL type "DATETIME" corresponds to Java type "java.sql.Timestamp";
# "BIGINT(20)" corresponds to "Long"; 
# "TEXT" and "MEDIUMTEXT" correspond to "String".

  Article.ArticleID BIGINT(20) PRIMARY KEY,
  Article.Title TEXT,
  Article.Authors TEXT,
  Article.Abstract TEXT,
  Article.Text MEDIUMTEXT,
  Article.PostDate DATETIME
  Category.CategoryID BIGINT(20) PRIMARY KEY,
  Category.Name TEXT,
  Category.ParentID BIGINT(20)
CREATE TABLE ArticleCategory (
  ArticleCategory.ArticleCategoryID BIGINT(20) PRIMARY KEY,
  ArticleCategory.ArticleID BIGINT(20) NOT NULL,
  ArticleCategory.CategoryID BIGINT(20) NOT NULL

Back to Article

Listing Two

package TableObjects;

import java.util.*;
import java.sql.*;

public interface TableRow {

  // "getValue/setValue" retrieve/update values
  // in the current row object.
  Object getValue (String tableFieldieldName) throws Exception;
  void setValue (String tableFieldName, Object tableFieldValue) 
                            throws Exception;
  // commit changes into backend database.
  void commit () throws Exception;

  String getTableName ();
  String [] getFieldNames (); 
  String getPrimaryKeyName ();
  Object getPrimaryKeyValue ();


Back to Article

Listing Three

// initialize a Factory instance 
Table ArticleFactory = new Article();

// Get a row using a factory method.
// Find the row that has a primary key value of 0.
TableRow articleRow = ArticleFactory.findByPrimaryKey( (new Long(0) );

// Retrieve a field value from the row.
String subject = (String) articleRow.getValue("Subject");

// Change a field value in the Row.
articleRow.setValue("Subject", "First Post!");

// Commit the change to the backend database

Back to Article

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.