The topic of discussion in this would not be to explain what Hibernate is or its architecture or performance issues. The reader should have a minimum knowledge of how an ORM works and Java.
One of the main reasons to move to Hibernate is to improve performance of the entire application. Traditional JDBC application is expensive and a complex job for any developer (if no DBA is available) to write the SQL queries. The developer first has to get a connection to the Database, create a Statement/PreparedStatement, ResultSets and iterate the ResultSet and embed the values into the ValueObjects (VO). Once done, the developer must ensure to close all the resources that were open during this process.
How does Hibernate eliminate all these? Hibernate requires only a DataSource to be specified and all the work of opening a connection, creating statements and executing these queries are the dirty job done by Hibernate. This shall be point of discussion in the later posts.
Let’s take a complex SQL query and convert them to Hibernate and see its advantages.
SQL Query:
select modality_type,
count(case when scheduled_ts >= DATE(NOW()) and scheduled_ts < DATE(NOW() + INTERVAL 1 DAY )
and hospital_id= 'BS' then scheduled_ts else NULL END ) as scheduled,
count(case when completed_ts != '0000-00-00 00:00:00'
and scheduled_ts >= DATE(NOW()) and scheduled_ts < DATE(NOW() + INTERVAL 1 DAY )
and hospital_id= 'BS' then completed_ts else NULL END) as completed
from order_status where modality_type in ('CT','DX','NM','US','IR','MR','PX','VA')
group by modality_type
This query is a typical Cross-tab/Pivot query. The query results in showing three columns - Modality Type, Count of Exams Scheduled for today and Count of Exams completed for the day which is grouped by Modality Type.
Let’s see how this is done with Hibernate.
1. Create a value object/java bean
Lets call our VO as CompletedAgainstScheduled
Note: For easier understanding I’ll show only a few snippets
/**
* @hibernate.class table="order_status" lazy="false"
*/
public class CompletedAgainstScheduled implements Serializable {
private Long id;
private String hospitalId;
private String modalityType;
private String completedTimestamp;
private String scheduledTimestamp;
private int completed;
private int scheduled;
private int interpretedBy;
/**
* @return
*
* @hibernate.meta attribute="id" value="ID"
* @hibernate.id type="long" column="id" generator-class="native"
*/
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
/** other setter and getters with hibernate mapping */
…..
/**
* get and set completed orders
*
* @return
*
* @hibernate.meta attribute="completedTimestamp" value="Completed Timestamp"
* @hibernate.property column="completed_ts" formula="case when completed_ts != '0000-00-00 00:00:00' then completed_ts else null end"
*/
public String getCompletedTimestamp() {
return completedTimestamp;
}
public void setCompletedTimestamp(String completedTimestamp) {
this.completedTimestamp = completedTimestamp;
}
}
For better understanding I’ve used XDoclet for Hibernate mapping.
When this class is compiled (using maven and xdoclet-plugin), it creates the Hibernate mapping file automatically.
So what is this class doing? @hibernate.class table="order_status" says that this VO is mapped to the table order_status in our database.
@hibernate.meta attribute="completedTimestamp" value="Completed Timestamp"
@hibernate.property column="completed_ts" formula="case when completed_ts != '0000-00-00 00:00:00' then completed_ts else null end"
The @hibernate.meta is basically for API Documentation and @hibernate.property column="completed_ts" is mapped to the attribute completedTimestamp in the Value Object.
Here is a very interesting concept: formula="case when completed_ts != '0000-00-00 00:00:00' then completed_ts else null end"
Why do this at all? Value Objects are ALWAYS mapped to a table and its attributes to columns. In the above Cross-tab query, we are deriving columns that are not in the table. Hence the formula on what should be done. We are counting number of rows for completed_ts that is not equal to “0000-00-00 00:00:00″. This formula will be embedded within the count() function.
NOTE: The case when.. is compatible only with MySQL and Oracle.
2. Hibernate Query
This section covers how the query is built in the DAO layer
Criteria criteria = getSession().createCriteria(CompletedAgainstScheduled.class)
.setProjection(
Projections.projectionList()
.add(Projections.groupProperty("modalityType").as("modalityType"))
.add(Projections.count("scheduledTimestamp").as("scheduled"))
.add(Projections.count("completedTimestamp").as("completed"))
)//.add(Restrictions.in("modalityType", modArr))
.add(Expression.ge("scheduledTimestamp", sdf.format(c.getTime())))
.add(Expression.lt("scheduledTimestamp", sdf.format(cd.getTime())))
.add(Expression.eq("hospitalId", hospitalId));
List csList = null;
if (criteria != null) {
csList = criteria.setResultTransformer(new AliasToBeanResultTransformer(CompletedAgainstScheduled.class)).list();
}
We have a ProjectionList that has modalityType (the column on which the resultSet should be grouped), scheduledTimestamp and completedTimestamp (the column that should be counted). Note that the attributes we are providing in the criteria. These are the attributes from the VO not the actual column names. This is what we call as HQL (Hibernate Query Language). When Hibernate complies this criteria, it generates a SQL dynamically and executes this at the database and returns a list of Objects which is of type CompletedAgainstScheduled.
Now, let’s do a little bit of post-mortem on how this Hibernate and SQL Query:
.add(Projections.groupProperty("modalityType").as("modalityType"))
.add(Projections.count("scheduledTimestamp").as("scheduled"))
.add(Projections.count("completedTimestamp").as("completed"))
is equivalent to
select modality_type,
count(scheduled_ts) as scheduled, count(completed_ts) as completed
We know that completedTimestamp is mapped to completed_ts in the table and returns an integer. But the column completed_ts itself is a datetime column. So, what happens to the integer? Well, the Projection says that count(completedTimestamp) which is mapped to completed_ts based on the formula we have specified in the VO and store the result in the completed attribute of the VO. Simple?
We can add as many expression as possible using the Expression class.
Once the query is executed, it returns a list of Objects which we have stored in csList. How does Hibernate know what and how to map? The ResultTransformer class does this job. It uses a sub-class called AliasToBeanResultTransformer class which is called for every row in the ResultSet and maps them to our VO.
That’s it. Very simple and intelligent.
If you have any doubts, do not hesitate to ask your questions.
In the next few series we’ll explore, Why Hibernate, XDoclets, Associations and other Hibernate related challenges.
