Related Topics: Java Developer Magazine, MySQL Journal

Java Developer : Article

Java Feature — The Holy Grail of Database Independence

Part 3: When your application has to support more than one database type, it makes sense to find a tool that can create the sche

The hope of using any persistence framework is absolute database independence. Database independence means that you can focus on your job as an application developer and not a DBA. However, no framework can fully make this claim. There's much more to running an application on a database than simply issuing compatible SQL queries and getting back the query results as expected. In my last article, I detailed the process by which we converted existing Enterprise Java Beans 2 (EJB2) Entity beans to Hibernate Plain Old Java Objects (POJOs). This article is less about our conversion process and more about the tools and methods we chose to work with for the Hibernate implementation and the backend databases (Oracle and PostgreSQL) supported by Hyperic HQ.

Creating the Database Schema
The relational database management system (RDBMS) is the foundation of any application. After all, the point of having a persistence layer is to map from the object model to the data model. The creation of the database schema is the most common task, and certainly there are plenty of point tools for various types of RDBMS around. However, when your application has to support more than one database type, it makes sense to find a tool that can create the schema regardless of database type. EJB2 provided no native tools for such a task, so we built our own tool, plainly named DBSetup. DBSetup is integrated with Ant and can easily incorporate into our build or installer (both of which rely on Ant). Its architecture is straightforward. There are base classes called Table, Column, Index, View, etc., that know how to generate the SQL commands to create themselves. Most RDBMS have proprietary extensions in their Data Definition Language (DDL), which allow control over non-standard features of the database system. If a RDBMS requires non-standard commands, you just subclass the base class, for example, the OracleTable class that can return the correct SQL to create a table in Oracle. We defined our own XML file format for the database schema, a proprietary DDL, if you will. DBSetup generates the sequence of database-specific commands in a single script and piped it to the database to create the schema. For example, here's how we would define a table named SUBJECT:

<table name="SUBJECT">
   <column name="ID"
       default="sequence-only"
       initial="10001"
       primarykey="true"
       required="true"
       type="INTEGER"/>
   <column name="NAME"
       required="true"
       size="100"
       type="VARCHAR2"/>
   <column name="FIRST_NAME"
       required="false"
       size="100"
       type="VARCHAR2"/>
   <column name="LAST_NAME"
       required="false"
       size="100"
       type="VARCHAR2"/>
   <column name="FSYSTEM"
       type="BOOLEAN"
       default="FALSE"/>
   <index name="SUBJECT_NAME_KEY"
       unique="true">
   <field ref="NAME"/>
  </index>
</table>

The syntax is self-explanatory. DBSetup worked fine for us, but it meant that for any new database type that we want to support, we'd have to analyze that database's command syntax and create subclasses as needed. As I had mentioned in my last article, we went through supporting Oracle, Pointbase, Cloudscape, InstantDB, MySQL, and PostgreSQL databases, and maintaining DBSetup to be compatible with all of them was tedious. Besides, there was no association between the tables being created here and the entity beans used by the application.

Hibernate provides a better tool for schema population, hbm2ddl. It is also integrated with Ant. It lets you run the task against your Hibernate mapping files (HBMs) and generate the resulting data definition language (DDL) in a file or to be exported directly into the database. Since we chose not to use the annotation feature with Hibernate, we hard-coded the HBM files. (Note that even if you were to use annotations, you can still use Hibernate tools to create the schema with your mapping.) We were able to convert our DBSetup schema files to HBM files relatively fast due to the structural similarities. Furthermore, we simplified our manually maintained Hibernate configuration files by offloading our defaults into an XSLT transformation process at build time, so our HBM files can be as minimal as possible.

Hibernate has classes that support various database dialects, so all of a sudden we've gained the ability to create schemas for a wide variety of databases without doing additional work ourselves. Hibernate's Web site lists the supported database types:
• Oracle 8i, 9i, 10g
• DB2 7.1, 7.2, 8.1
• Microsoft SQL Server 2000
• Sybase 12.5 (JConnect 5.5)
• MySQL 3.23, 4.0, 4.1, 5.0
• PostgreSQL 7.1.2, 7.2, 7.3, 7.4, 8.0, 8.1
• TimesTen 5.1, 6.0
• HypersonicSQL 1.61, 1.7.0, 1.7.2, 1.7.3, 1.8
• SAP DB 7.3
• InterSystems Cache' 2007.1

It's good to know that we have options.


More Stories By Charles Lee

Charles Lee is co-founder and vice-president of engineering of Hyperic. Prior to co-founding Hyperic, Lee was a senior software engineer at Covalent. There, he built Covalent's configuration management product for Apache (CMP), and he spearheaded and architected the application management software (CAM). Before Covalent, Lee developed a document management system for retail store build-outs based on open-source technology at WiseConnect. Lee also held senior engineering position at Hewlett-Packard, where he was instrumental in developing print drivers for network LaserJets for the Asian market, as well as developing the UI framework used for LaserJets for all markets. Lee also developed the first GUI printer configuration framework for AutoCAD while a senior engineer at Autodesk. Lee was an early engineer at Backflip, where he created the document publishing system for the website based on mod_perl.

Lee received his BS in Computer Science and BA in Chemistry with honors from the University of Washington.

Comments (1)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.