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.