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 ▼
RSS

JVM Languages

How Can I Generate Java Code for Tables in My Database?


Aug02: Java Q&A

Mark developed the code in this article while working for Fletcher Allen Health Care. He is currently a software engineer with IBM and can be contacted at mseaman@ attglobal.net.


Unless you have access to expensive tools that can generate Java code from a data model, a great deal of tedious, error-prone coding is required to create Java classes representing relational database tables. By using the DatabaseMetaData interface provided by the java.sql package (http://java.sun.com/products/jdk/1.1/docs/api/Package-java.sql.html), you can write code to extract the required information from the database and generate the classes you need. As an added benefit, if you save the metadata in the generated class, it can be used at run time to build and execute dynamic SQL statements without having to code any SQL. In this article, I present a technique I developed to accelerate database application development by using the information available through the DatabaseMetaData interface.

What prompted my writing this article was Terry Smith's article "How Can I Simplify Java Data Access Objects?" (DDJ, May 2001). I had just finished up a project where I wrote a code generator and thought it provided a fairly good solution to the issues Terry raised. Writing data access objects normally requires a Java class for every entity in the database. Even in a modest system, this results in dozens of classes just to represent the tables from the underlying RDMS.

In mid project, I joined a team writing a Java application to process real-time messages and store the contents in a relational database. My task was to write the code for object-relational mapping and the code that implemented database operations on the objects. The team had used a CASE tool during the design phase and employed it to generate the database schema. I asked that the tool be used to generate the Java classes for the entities so that I could begin my work. It generated all of the classes, but every member variable was declared an integer. Facing tight deadlines, there was no time to go back and figure out why the code produced by the tool was wrong. I immediately obtained a copy of the schema and started at the top, creating a Java class for each table. I think I got to the second member variable of the first class, looked at the 30 or so classes I had to go, and decided there had to be a better way to do this. In a short time, I discovered the answer — the getColumns method of the java.sql.DatabaseMetaData interface provided all the information I needed to write the code to generate the required Java classes.

The DatabaseMetaData interface of the java.sql package provides access to the capabilities of the underlying JDBC driver/RDBMS and allows retrieval of database object metadata. The getColumns method retrieves information about the columns of a specific table. You retrieve a DatabaseMetaData object by calling the getMetaData method of a valid Connection object. To call the getColumns method of DatabaseMetaData, provide the following parameters: Catalog, Schema Name Pattern, Table Name Pattern, and Column Name Pattern. The "patterns" are strings that follow the SQL standard for wildcards: "%" matches zero or more occurrences of a substring and "_" matches any single character. Any parameter set to Null causes that parameter to be dropped from the search criteria. To retrieve all columns for a given table using IBM's DB2 JDBC Driver, you pass "Null" for the catalog and column name arguments, the table owner for the Schema parameter, and the table name. Other vendors' requirements for parameters may be different. getColumns returns a ResultSet object containing columns for the table name, column name, column datatype, length, and others. (See the Java documentation for a complete listing of the columns returned by getColumns().)

In finding a solution to the initial object-relational coding problem, I realized the next big chunk of tedious coding was to write SQL statements to retrieve, insert, update, and delete each one of these objects. To solve this problem, I realized that if I stored the metadata in the generated class, I could use it to build dynamic SQL statements at run time. In this way, I could implement the data manipulation operations without writing individual SQL statements.

I first created an object to encapsulate information about database columns. Listing One, the ColumnInfo class I created for this purpose, includes data members for the column's name, datatype, and length along with methods to get and set these attributes. The length data member is not used at this time. To get the column information to the methods that would use it to generate SQL statements, I decided to use hash tables of column/value pairs. For this to work, callers must be able to retrieve the ColumnInfo object from the hash table using the column name as the key. For a class to be "hashable" in Java, it must override the hashCode and equals methods of java.lang.Object. The hashCode method of ColumnInfo returns the hashCode for its name member using the String class's implementation. The equals method is used by the JVM to retrieve the correct object when more than one has been stored in the same hash "bucket." ColumnInfo's equals method uses the instanceof operator to determine the type of the object passed to it. If the argument is a ColumnInfo object, the parameter's name, retrieved via its getName method, is compared to the current object's column name. If the argument is a String, the current object's column name is compared to it. If it isn't a ColumnInfo or a String, then Object's equals method is called via super.equals. This permits retrieving columns from a Hashtable by either the columnName or the corresponding ColumnInfo object. The ColumnInfo class is declared final since this implementation of the equals method does not work correctly for descendants of ColumnInfo.

Having finalized the design, I wrote the class that generated the Java code, DbGenerateClasses (available electronically; see "Resource Center," page 5). It performs the work of retrieving the column information for the requested table(s) and generating the corresponding Java class. The generated class contains a protected member variable for each column. The member name is the column name converted to title case with any underscores removed. It also contains get and set methods for each member variable, and a toString method for dumping the contents of the object using standard java.io methods. To generate one or more classes from the database, use the command line to set the ConfigFileName property to the name of a configuration file containing database connection parameters and one or more tables for which to generate the code. For example, java -DConfigFileName=my.cfg DbGenerateClasses Member outputs a file named "MemberTable.java." The class name is the name of the table from the database converted to title case with any underscores removed and the word "Title" appended. As written, it only works for a case-insensitive DBMS such as DB2.

DatabaseMetaData does provide methods that can be used to determine if a DBMS considers case significant in identifiers. These methods could be used to generalize the code so that it would work for other RDBMSs without modification. For each table, the getColumns method of the JDBC Driver's DatabaseMetaData implementation is called. A loop processes all of the columns in the ResultSet.

In addition to the methods mentioned earlier, a static initializer is created for each generated class. It loads the m_colList hash table member with the ColumnInfo object for each column keyed by the column name. Every generated class includes a getColumnInfo method that returns the corresponding ColumnInfo object given the column name. The get-Columns method returns a copy of the entire hash table using the hashtable.clone method. It is important that the m_colList member is not returned directly; it would provide a reference by which any user could modify its data.

Using the getColumnInfo method of the generated class, the datatype of every column can be quickly looked up at run time without the overhead of a database query. This made the generation and execution of dynamic SQL feasible for applications with modest database performance requirements. The DbUtils class (available electronically) is a collection of static methods that perform the basic database operations: insert, update, delete, and retrieve. Values to be inserted or updated and where criteria are specified using a Hashtable containing the values keyed by the corresponding ColumnInfo object for the column. When only specific columns are requested from the executeQuery method, they are specified using a Vector of column name Strings. Values are formatted by the formatField method that takes the datatype of a column and its value and returns either a String representing the value in the case of numeric types or a delimited string containing the value in the case of String, Date, and Timestamp types. This method relies on the implicit conversion of the value in the SQL string to the appropriate type by the underlying RDBMS. If this implicit conversion fails for a particular RDBMS, it would be necessary to modify the formatField method to produce an acceptable value. The public DbUtils methods require a valid connection object to create and execute JDBC Statements. Connections should be managed at the application level, not by utility classes. It is completely up to you to determine the composition of transactions and whether or not they are automatically committed, the transaction isolation level, total number of connections required, and whether connection pooling should be used.

Building and executing dynamic SQL statements at run time exposes low-level quirks of the underlying database. For DB2, to embed the string delimiter (') in a character string type column (CHAR, VARCHAR, LONGVARCHAR), you must double the single-quote character. I had to add a check for this condition and code to correctly process the string delimiter to the formatField method. If your DBMS has the same issue but uses a different string delimiter, you can call the setStringDelimiter method to change the character that is used. The default string delimiter is a single-quote character. Other vendor-specific idiosyncrasies need to be handled individually. The JDBC PreparedStatement hides these kinds of details from you, but in my case, the additional processing time for this check was not significant.

The main work of DbGenerateClasses is to map the column's datatype into the appropriate Java datatype. The JDBC specification provides guidance on the mapping between the JDBC types and Java types. I followed the recommendation of the 1.2 specification with the exception of Numeric and Decimal types. The specification listed java.math.BigDecimal objects as the correct mapping for these types, but JDBC did not add a getBigDecimal() method to ResultSet until JDBC 2.0 and Java 2. I chose to map these types to the double primitive type. I also did not make any provision for user-defined types. Any unrecognized type is automatically mapped to java.lang.Object. The JDBC 2.1 specification added support for SQL99 datatypes (BLOB, CLOB) and other user-defined types. JDBC 2.1 also adds a method to the Connection object that lets an application set and retrieve a type Map for translating custom SQL datatypes. I haven't had the opportunity to test this functionality; there are few commercial JDBC drivers that support JDBC 2.0. Online versions of both specifications are available at http://java.sun.com/j2se/1.3/docs/guide/jdbc.

Although it provides the basic functionality, the generated class is abstract because it is not useful on its own. To use it, create a class that extends it. The primary purpose of this class is to express and enforce the relationships between objects in the system. For example, the implementation of a Customer class for a sales system would contain the customer's Orders. This class will normally provide the methods to retrieve, insert, update, and delete objects of the given type. Here, the main application logic is implemented and code is written to enforce applicable business rules. Examples of business rules that may be implemented in descendant class are: Can objects of this type be deleted? Are there dependent objects that also need to be deleted when the parent object is deleted (cascading deletes)? Are updates permitted? Are data modifications audited? Complex relationships and referential integrity constraints would also be enforced by code in the descendent class.

I have provided an example program loosely based on the code presented in Terry Smith's article. I thought it would be instructive to show an alternative method of simplifying database access objects using the same basic example. Terry's article was written for an Oracle database and included a SQL script to create the Member table. That script contained an Oracle-specific datatype (VARCHAR2). I have provided a modified Member.sql script to create the same table in a DB2 (and presumably any other SQL) database. I have also included the code generated by DbGenerateClasses for the Member table, MemberTable.java. I supplied it only to provide a complete working example. I recommend you create the table using the script and run the DbGenerateClasses application to generate your own version of MemberTable.java.

Member.java is a sample class that extends the generated MemberTable class and provides the methods to retrieve, insert, update, and delete members. Code is included that exercises these functions. As a general business rule, I decided modification of the member ID was not permitted for an existing Member object. This prevents a number of problems that could occur when users of the class change the member ID. If it needs to be modified, the Member class manipulates the member ID directly; it has access through inheritance. The overridden setMemberid method in the Member class silently does nothing. This behavior is clearly documented in the Member class's comments.

As I mentioned previously, the Statement object has some drawbacks. It is the least efficient method (compared to PreparedStatements and CallableStatements) of performing database operations and it requires you, as the programmer, to be aware of RDBMS-specific details. CallableStatements are the JDBC implementation of stored procedures and are not useful in improving the performance of the code presented here. With some modifications, they could be used with PreparedStatements. In general, each generated class would have one or more PreparedStatement objects for each data manipulation operation. It would be straightforward to implement insert operations using PreparedStatements: Any columns not supplied in the hash table for insertion would be set to Null. PreparedStatement for updates are more problematic (because it is difficult to anticipate the columns that will be "normally" updated). Deletion and retrieval also suffer from not knowing what criteria to support. The primary key for a table can be extracted using the DatabaseMetaData interface, and using the primary key for retrieval and deletion would solve some of the problem. Another potential performance improvement would be to use column numbers instead of column names for extracting column information from ResultSets. Adding an additional data member to the ColumnInfo object and dynamically setting the column number each time a query was executed could solve this issue.

Conclusion

Using a vendor's implementation of the DatabaseMetaData interface, you can retrieve all the information required to generate Java classes that map to an underlying database table. By including the metadata in the generated class and making it available at run time, you can generate dynamic SQL statements that simplify standard SQL operations and require no knowledge of SQL from users of the class. You can almost completely eliminate the boring, mindless coding required to represent database tables as Java classes and spend your time on the more challenging aspects of your project: the application logic and business rules. The technique I present here is a general-purpose solution to the problem of object-relational mapping in Java. In my experience, general solutions trade performance for ease of use. In this case, I had no idea if the performance would be acceptable until the actual throughput testing was performed. Our requirements were to process a peak volume of 120 messages per minute. Lack of supporting indexes on key tables had a far greater impact on performance than this code. In all but the most demanding database applications, I would try to use the technique presented here and be prepared with some performance improvements if they become necessary.

DDJ

Listing One

// Copyright (c) 2000, Mark J. Seaman
/** Maintains information about a column of a database table. It is designed  
*  to be stored in a hashtable and passed to the DBUtils class methods
*  @author  Mark Seaman ($Author: mark $)
*  @version $Revision: 1.3 $
*/

public final class ColumnInfo {
  /** the column name */
  private String name;

  /** the datatype of the column from the sql.Types package */
  private int datatype;

  /** the maximum length of this column, currently not used */
  private int length;

  /** uses the column name to provide a hash value for this
  * column.  This allows the column info to be retrieved
  * from a Hastable using either the name or the columnInfo object.  
  */
  public int hashCode() { return name.hashCode();}

  /** equals method required for this object to be retreived from a 
  * <code>java.util.Hashtable</code>. It makes a <code>ColumnInfo</code> 
  * object equal to a <code>String</code> containing the name of the column. 
  * This assumes no DBMS implementation allows two columns of the same name 
  * in the same table. This class is declared <code>final</code> because 
  * this method will not work on decendants of this class 
  */
  public boolean equals (Object o) {

    if (o == null) {
      return false;
    } else if (o instanceof ColumnInfo) {
      //are the names the same?
      return this.name.equals(((ColumnInfo)o).getName());
    }
    else if (o instanceof String) {
      //is this name the same as the String value
      return this.name.equals((String)o);
    } else {
      //all others defer to Object equals
    return super.equals(o);
    }
  } //equals()

  /** sets the column name */
  public void setName (String name) { this.name = name; }
  /** sets the datatype */
  public void setDataType (int datatype) {this.datatype = datatype; }
  /** sets the length of the column */  
  public void setLength ( int length ) { this.length = length; }

  /** returns the column name */
  public String getName () { return name; }
  /** returns the column type */
  public int getDataType () { return datatype; }
  /** returns the column length */
  public int getLength() { return length; }

} //ColumnInfo

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.