Hibernate 3 Formulas

Hibernate and Spring are two outstanding open source frameworks gaining usage in more and more J2EE applications. Though targeting very different problem spaces, they share one key feature: dependency injection. Spring helps sort out dependencies among objects before returning these objects to a client, and thus reduces much coding inside the client. Hibernate specializes in sorting out dependencies presented by a data model before returning an integral object model to a client. When using JDBC directly to map a data model into an object model, we normally need to write a large amount of code to build an object model. Hibernate eliminates most of this coding work.

Hibernate 2.x provides basic table-to-object mapping, normal association mapping (including one-to-one, one-to-many, and many-to-many relationships), polymorphism mapping, etc. Hibernate 3.x pushes this to another level by enhancing mapping flexibility with formula, filter, subselect, etc., which provide fine-grained interpretation features.

Related Reading Hibernate: A Developer's Notebook By James Elliott

In this article, we will show how various features of formula can help in model conversion. Before Hibernate 3.x, the formula attribute could only appear in a property element. While you can still do that, Hibernate 3.x provides a formula attribute or element (both are virtually equivalent in terms of formula usage) that can be used in many elements, including discriminator, many-to-one, one-to-one, element, many-to-many, map-key, map-key-many-to-many, and property. This adds much flexibility to object-relational (O-R) mapping, and thus allows further fine-grained interpretation of complex data models.

There are basically two scenarios where using formula is necessary:

1. Cases where a formula evaluation result is needed. Using formula with the elements discriminator, element, map-key, map-key-many-to-many, and property falls into this category.
2. Cases where a formula is needed for joining purposes. Using formula with the elements many-to-one, one-to-one, and many-to-many falls into this category.

Category 1: Getting an Evaluation Result from a formula

Discriminator

In real data schema, there are often cases where one table is used to describe another table. formula can help provide flexible polymorphism in O-R mapping.

In the example shown in Figure 1, there are two tables: Product and ProductRelease. Each product record has a ProductReleaseID referencing its corresponding product release record, including product release name, type, release date, etc.

Figure 1. Product and product release data model

One interesting attribute in the ProductRelease table is SubProductAllowable, which can have a value of either 0 or 1. A value of 1 means sub-products are allowed under any product of this product release, while 0 means no sub-product is allowed. For example, some products are assembled from multiple sub-products, and some products are solely units unto themselves.

Figure 2 shows an object model interpreted from this data model. The Nested interface defines the getSubProducts and setSubProducts methods. The class NestedProduct extends the base class Product and fulfills the Nested interface, as well. Whether a product data record should be a Product or NestedProduct depends on the value of SubProductAllowable of its corresponding product release record.

Figure 2. Product and product release object domain model

In order to achieve this model conversion, we use a Hibernate 3.x mapping, as follows:

<hibernate-mapping>
  <class name="Product" 
        discriminator-value="0"  lazy="false">
    <id name="id" type="long"/>       
    <discriminator 
        formula="(select pr.SubProductAllowable 
                from ProductRelease pr 
                where pr.productReleaseID=
                        productReleaseID)"
        type="integer" />
    <subclass  name="NestedProduct"  
        discriminator-value="1"/>
  </class>
</hibernate-mapping>

If the formula expression evaluation result is 0--i.e., no sub-product is allowed--then the object will be of the class Product. If the result is 1, the object will be a NestedProduct. In tables 1 and 2, for the first record (ProductID=10000001) in the Product table, the initialized class will be a NestedProduct because it references a ProductRelease record with SubProductAllowable=1. For the second record (ProductID=20000001) in the Product table, the initialized class will be Product, because it references a ProductRelease record with SubProductAllowable=0.

Property

A formula inside of a property element allows object attributes to contain certain derived values, like the results of sum, average, max, etc., as in:

<property name="averagePrice" formula="(select avg(pc.price) from PriceCatalogue pc, SelectedItems si where si.priceRefID=pc.priceID)"/>

Furthermore, a formula can also help retrieve values from another table based on certain attribute values for the current record. For example:

<property name="currencyName" formula="(select cur.name from currency cur where cur.id= currencyID)"/>

This helps retrieve a currency name from the currency table. As you can see, these direct mappings can eliminate a lot of conversion coding.

map-key

formula allows map-key to have any possible value. In the following example (Figure 3), we want Role_roleID to be the map-key of the object model (Figure 4).

Figure 3. User role data schema

Figure 4. User role object model

In the data schema above, User and Role are linked via a many-to-many relationship table called User_has_Role. In order to get a User with all of the roles assigned to it, we use the following mapping:

<hibernate-mapping>
  <class name="User">
    <id name="userID"/>
    <map name="roles"  
        table="UserRole"/>
      <key column="User_userID"/>
      <map-key 
        formula="Role_RoleID" 
        type="string"/>
      <many-to-many 
        column="Role_RoleID"
        class="Role"/>
    </map>
  </class>
  <class name="Role">
    <id name="roleID"/>
  </class>
</hibernate-mapping>

Role_RoleID is used as the joining column value of the many-to-many element. However, Hibernate does not allow Role_RoleID to be in the column attributes of both map-key and many-to-many. But with a formula, Role_RoleID can be used for map-key as well.

Other Cases: element, map-key-many-to-many, and More

element, like property, can be given the evaluated value of any valid formula expression.

The usage of formula with map-key-many-to-many is similar to that of map-key. However, map-key-many-to-many is usually used in a ternary relationship, where a map key is a referenced object itself, rather than a referenced attribute.

However, there are cases where formula is not supported. Some databases (e.g., Oracle 7) do not support embedded select statements (i.e., a select SQL embedded in the select part of an SQL statement), and in these cases a formula used as evaluation result is not supported. Therefore, you need to first check whether embedded select SQL statements are supported.

Since the SQL that results from Hibernate's mappings takes the formula expression as part of its select target, knowing the SQL dialect of your database will help enrich your use of formula, though it may reduce code portability.

Category 2: Using a formula for Joining

many-to-one

Another common scenario in real-world data models is proprietary relationship mapping, meaning mappings other than the basic one-to-one, one-to-many, and many-to-many relationships. formula is one of the elements provided for this kind of proprietary relationship management. Figure 5 shows an example in which one company can have many contact persons, but only one of them is the default contact person. One company having many contact persons is a typical one-to-many relationship. However, in order to identify the default contact person, the ContactPerson table uses an attribute defaultFlag (where 1 is yes and 0 is no).

Figure 5. User role data schema

Figure 6. User role object model

In order to interpret default contact person relationship into the object model (Figure 6), we use the following mapping:

<hibernate-mapping>
  <class name="Company" table="Company">
    <id name="id" />
    <many-to-one 
      name="defaultContactPerson" 
      property-ref="defaultContactPerson">
        <column name="id"/>
        <formula>1</formula>
        </many-to-one>
  </class>
  <class name="Person" >
    <id name="id" />
    <properties name="defaultContactPerson">
        <property name="companyID" />
        <property name="defaultFlag" />
    </properties>
  </class>
</hibernate-mapping>

Above, we group companyID and defaultFlag in a properties element with the name defaultContactPerson, which forms a unique key of the Person table. The many-to-one element inside of the Company class joins on the defaultContactPerson properties element in the Person class. The resulting SQL will be something like:

select c.id, p.id from Company c, Person p where p.companyID=c.id and p.defaultFlag=1

one-to-one

In Hibernate, one-to-one is mainly used for two tables sharing the same primary key. For foreign key association, we normally use many-to-one instead. However, with formula, one-to-one is able to link up tables via a foreign key. The above many-to-one example can be mapped by one-to-one as follows:

<hibernate-mapping>
  <class name="Company" table="Company" >
    <id name="id" />
    <one-to-one name="defaultContactPerson" 
        property-ref="defaultContactPerson" >
    <formula>id</formula>
    <formula>1</formula>
    </many-to-one>
  </class>
  <class name="Person" >
    <id name="id" />
    <properties name="defaultContactPerson">
      <property name="companyID" />
      <property name="defaultFlag" />
    </properties>
   </class>
</hibernate-mapping>

Others: many-to-many

formula can be used with a many-to-many element for a special joining from relationship table to an entity table, although this is not often needed.

Summary

The examples in this article show most of the formula usage scenarios. When a formula evaluation value is needed, the formula expression will appear in the select part of the resulting SQL statement. And when a formula is used for joining, it appears in the where part of the resulting SQL statement. Furthermore, a formula expression can use any dialect of SQL, as long as the target database can support it. As a result, formula helps achieve fine-grained mapping from data model to object model without code.

Resources

• Sample code for this article.
• Hibernate provides most information about Hibernate ORM.
• Filter Article on TheServerSide for external filter introduction.
• Spring is a very friendly framework and provides integration with Hibernate and other data access tools.
• "Dependency Injection:" A definitive introduction of dependency injection.
• Dependency Injection Basics: An easy-to-understand introduction of dependency injection.

Dai Yifan is a technical consultant for a leading banking solution provider

Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.