Querying in Hibernate, Part 1

There are three ways to query objects in Hibernate:

1. Using Hibernate Query Language (HQL).

2. Using the Criteria API and doing Query By Criteria and Query by Example.

3. Direct SQL with or without automatic mapping of resultsets to objects.

Querying involves the following steps:

1. Create the query with needed filters and projections.

2. Bind runtime arguments to query parameters.

3. Execute the query and retrieve the results.

Now Hibernate is very rich and has many options so be patient and read through all the available options, it will really help you make the right design choice in future.

How to prepare a query?

There are two interfaces to control query execution in Hibernate: org.hibernate.Query and org.hibernate.Criteria. You obtain an instance of these objects using the Session object. You can use createQuery method to create a Query object that deals with HQL queries. You can use createSQLQuery method to create Query object that deals with native SQL queries. You call the createCriteria method to get a Criteria object. You pass the class of the object you want the query to return for this method. The following are the examples of each of the methods.

Query hqlQuery = session.createQuery(“from Employee”);

Query sqlQuery = session.createSQLQuery(“Select {employee.*} from Employee {employee}”).addEntity(“employee”,Employee.class);

Criteria crit = session.createCriteria(Employee.class);

Note that for the sqlQuery you define placeholders {employee} for the objects to be created and populated with the data that comes off the query.

How to paginate the results of a query?

You can use two methods, setFirstResult and setMaxResults to set the starting record number and the number of records you want the query to return. Hibernate automatically creates the query that will work of a particular SQL dialect. These methods are available for both Query and Criteria objects.

How to bind parameters?

You can use positional or named parameters in a HQL query. Here is an example of named parameter in HQL:

String queryString = “from Account account where account.name like :data”;

Query q = session.createQuery(queryString).setString(“data”,givenData);

Here is an example of positional parameter in HQL:

String queryString = “from Account account where account.name like ? and account.type = ?”;

Query q = session.createQuery(queryString);

q.setString(0,givenData);

q.setString(1,givenType);

How to execute a Query?

You call the list method of the Query or Criteria object to execute the query.

List result = myQuery.list();

List result = myCriteria.list();

There is also another method called iterate to execute the query. In calling the iterate method you are asking Hibernate to take advantage of the cache in the current persistent context or the cache in the second level cache. When you call the iterator method, only the primary key of a given table will be queried and Hibernate will retrieve the actual object from the cache. If the object is not found it the cache, Hibernate will issue another query per row. So it is an optimization mechanism, but use it with caution.

You can also create ScrollableResults by calling the scroll method. This is similar to how you use Scrollable resultses in JDBC. Here is an example of how you do that:

ScrollableResults accountCursor = session.createQuery(“from Account”).scroll();

accountCursor.first();

accountCursor.get();

accountCursor.scroll(3);

accountCursor.previous();

…

accountCursor.close();

How to put my queries in the Configuration file?

You first put the query in the hbm.xml file as follows:

<query name=”findAccountsByType”>

<![CDATA[

from Account account where accout.type = :type

]]>

</query>

Then you obtain a Query as follows:

Query query = session.getNamedQuery(“findAccountsbyType”).setString(“type”,givenType);

This will end my discussion for today. I will write more on Hibernate queries in the part 2 of this series.

How inheritance works in Hibernate

Inheritance is one of the most visible facets of Object-relational mismatch. Object oriented systems can model both “is a” and “has a” relationship. Relational model supports only “has a” relationship between two entities. Hibernate can help you map such Objects with relational tables. But you need to choose certain mapping strategy based on your needs. There are four possible strategies to use. I am listing them as follows:

1. Map one table per concrete class. Your mapping ignores the inheritance relationship and maps one table per concrete class.

2. Map one table per concrete class with union-subclass mapping. You still have one table per concrete class, but you use a “union-subclass” clause in your mapping. This helps mitigate some of the problems we will face in the previous strategy.

3. Map one table per class hierarchy. Here your table will have a row for all the fields in the full class hierarchy. You will have a lot of potential null values and you will have a discriminator column to hold type information.

4. Map one table per subclass. Here you convert the object oriented “is a” relationship into a relational “has a” relationship using foreign keys.

Let us discuss each of these strategies and see where it is appropriate.

1. Map one table per concrete class:

You create one table for each non abstract class. All the properties of a class including inherited properties are mapped to columns of a table. Let us think of a class called Employee with the following properties: employeeName, location. Now this hypothetical organization has two kinds of employees. RegularEmployee and ContractEmployee. RegularEmployee class has the following properties: salary, bonus and ContractEmployee has the following properties: hourlyRate, perDiemRate, contractPeriod. Now this kind of relationship is usually modeled as an inheritance relationship, where RegularEmployee class and ContractEmployee class will inherit Employee class. Now in this ORM strategy, you will have two Tables REG_EMP and CON_EMP with REG_EMP having fields: reg_emp_id, emp_name, location, salary, bonus and CON_EMP with fields con_emp_id, emp_name, location, rate, per_diem, con_period. You do not do anything in hibernate mapping. Your mapping will look similar to what you will do for regular classes.

There are certain disadvantages in using this strategy. Let me list them here.

Firstly, you will face problems when you want to associate another entity with the super class. For example, if you want to associate another class called Department with Employee Class. You usually want departmentID associated with Employee entity as a foreign key relationship. Now in this scenario, the foreign key relationship has to be made in all the mapped tables per sub-class. Even further let us say that you have a business scenario where many Departments will have to be associated with one employee. Here you will have to have at least two foreign key constraints on the Department table.

Secondly, if you have a query against the super class, you will have to query several times for each sub-class tables.

Thirdly a change in super class properties will have to be reflected in changes in each sub-class tables.

Given the above disadvantages, the only advantage of this approach is that it is simple and efficient. Therefore you should use this approach in only the simplest of the scenarios and where you do not see any major changes in the future. The following diagram depicts this kind of mapping

2. Map one table per concrete class with union-subclass mapping:

This is the same as the above scenario where you have two tables with duplicate Employee details in each table. But in this strategy, you will use a special kind of mapping in Hibernate. The mapping will look as follows

<hibernate-mapping>

<class name=”Employee” abstract=”true”>

<id name=”id” column=”EMP_ID” type=”long”>

<generator class=”native” />

</id>

<property name=”employeeName” column=”EMP_NAME” type=”string” />

<property name=”location” column=”LOCATION” type=”string” />

<union-subclass name=”RegularEmployee” table=”REG_EMP”>

<property name=”salary” column=”SALARY” />

<property name=”bonus” column=”BONUS” />

</union-subclass>

<union-subclass name=”ContractEmployee” table=”CON_EMP” >

<property name=”hourlyRate” column=”RATE” />

<property name=”perDiem” column=”PER_DIEM />

<property name=”contractPeriod” column=”CON_PERIOD” />
</union-subclass>

</class>

</hibernate-mapping>

The key here is you need to say abstract=”true” otherwise a separate table for instances of the superclass is needed. The advantage with the above mapping is that this allows polymorphic queries like below:

select EMP_ID, EMP_NAME, LOCATION, SALARY, BONUS, RATE, PER_DIEM, CON_PERIOD

from

( select EMP_ID, EMP_NAME, LOCATION, SALARY, BONUS, null as RATE, null as PER_DIEM, null as CON_PERIOD from REG_EMP

union

select EMP_ID, EMP_NAME, LOCATION, null as SALARY, null as BONUS, RATE, PER_DIEM, CON_PERIOD from CON_EMP)

Now you can also do polymorphic association mapping for the Employee class. The disadvantage with this approach is that still this approach is little inefficient, since hibernate handles all this by using SQL unions.

3. Map one table per class hierarchy

Here you map the entire hierarchy of classes to a single table. A concrete subclass is identified by a value in the discriminator column. In our example you will have a column called EMP_TYPE. The following picture depicts this kind of mapping.

This is a very efficient mapping but there are few problems which you need to be aware of. Firstly, sub-class columns should be declared nullable. This may cause data integrity problems. Secondly we are violating third normal form. Let us see how your hibernate mapping will look like.

<hibernate-mapping>

<class name=”Employee” table=”EMPLOYEE” >

<id name=”id” column=”EMP_ID” type=”long>

<generator class=”native” />

</id>

<discriminator column=”EMP_TYPE” type=”int” />

<property name=”employeeName” column=”EMP_NAME” />

<property name=”location” column=”LOCATION” />

<subclass name=”RegularEmployee” discriminator-value=”1″>

<property name=”salary” column=”SALARY” />

<property name=”bonus” column=”BONUS” />

</subclass>

<subclass name=”ContractEmployee” discriminator-value=”2″ >

<property name=”hourlyRate” column=”RATE” />

<property name=”perDiemRate” column=”PER_DIEM” />

<property name=”contractPeriod” column=”CON_PERIOD” />

</subclass>

</class>

</hibernate-mapping>

4. Map one table per subclass :

In this option you represent inheritance relationships as relational foreign key associations. Every subclass, class including abstract classes has it own tables. The primary key of the Super class will became both primary key and foreign key in the sub class. The advantages here is that the SQL Schema is normalized and the data integrity is properly maintained and it allows for schema evolution. The disadvantage is of course added complexity. The following picture depicts this scenario.

Let us see how the hibernate mapping looks like here:

<hibernate-mapping>

<class name=”Employee” table=”EMP”>

<id name=”id” column=”EMP_ID” type=”long”>

<generator class=”native” />

</id>

<property name=”employeeName” column=”EMP_NAME” />

<property name=”location” column=”LOCATION” />

<joined-subclass name=”RegularEmployee” table=”REG_EMP” >

<key column=”EMP_ID” />

<property name=”salary” column=”SALARY” />

<property name=”bonus” column=”BONUS” />

</joined-subclass>

<joined-subclass name=”RegularEmployee” table=”REG_EMP” >

<key column=”EMP_ID” />

<property name=”hourlyRate” column=”RATE” />

<property name=”perDiemRate” column=”PER_DIEM” />

<property name=”contractPeriod” column=”CON_PERIOD” />

</joined-subclass>

</class>

</hibernate-mapping>

Here Hibernate will rely on outer join for querying Employee class. It will rely on inner join while query the sub class. As you can see all this will become a performance issue in a complex inheritance relationship.

Summary:

So you can model inheritance using <class>, <union-subclass>, <subclass> or <joined-subclass> mapping elements. If you don’t want polymorphic associations or queries, use “table per concrete class” strategy. If you want polymorphic associations or queries use union-subclass mapping. This strategy is little inefficient, use it only when you have fewer sub-classes. If you want polymorphic associations or queries and your sub-classes have fewer properties use “table per class hierarchy” strategy. If you want polymorphic associations and queries and your sub classes have a lot of properties, then use “table per subclass” strategy. Finally, in the object oriented world, composition is better than inheritance, so make sure your model is efficient. Too many persistent inheritance relationship will actually slow down your system.