SQL does not define unified methods to calculate differences between timestamp values. Many database vendors provide somehow their own solution for this problem, but when it comes to generalizing the persistence layer with Hibernate, we are faced again with the missing transparent approach. This article shows a concise solution proven to work transparently on Oracle 10g and HSQL.
In our case, we had
- A model class, let's call it Task. Task has two fields startTime & endTime, both of type java.sql.Timestamp. And several other fields of different primitive types.
- Task was subject to a Grails application (Although the persistence layer was not GORM, but plain Hibernate with Spring due to several other reasons).
- We had to display lists of tasks, paginated and orderable by either all fields or the duration, which is actually the difference between endTime and startTime.
- In production mode the data is stored in an Oracle 10g Database
- Unit & Integration Tests were done with an In Memory HSQL Database.
- We have to avoid redundancy. Storing the duration also as a field of Task is not an option. If it could be calculated, then it has to be calculated.
- We want to handle pagination and ordering of the fields and duration similar. We could have introduced some dispatching logic, like if duration is not envolved we use query A otherwise we use query B. But that's not a proper solution and most likely not very well maintainable. We just need a single query which can cope with both cases. Ultimately this means, we have a property name and tell Hibernate to order the result according to this property.
- We need mechanism to cope with Oracle & HSQL.
The solution we found consists of several aspects. Let's go through them top down:
Adding a property formula to the Hibernate Mapping
Since we have to handle Hibernate properties and the calculated value in the same way, we need to add duration as an "artificial property" to the Hibernate Mapping of Task. Luckily Hibernate provides a mechanism for this use case: Properties could consist of aformula, which is basically the call to a valid HQL function. We simply need to define
<hibernate-mapping>
<class name="foo.Task" table="TASKS">
...
<property
name="duration"
formula="MY_DIFF_TIME_SECONDS(START_DATE, END_DATE)"
/>
...
</class>
</hibernate-mapping>
What do we do here? We assume there is a registered function called MY_DIFF_TIME_SECONDS(x,y), which calculates the difference between two timestamps in seconds. This method is called with Task's properties START_DATE and END_DATE.
Adding custom functions to the Hibernate Dialects
We have expected that MY_DIFF_TIME_SECONDS(x,y) exists. Since it is not given by an Intelligent Creator for free, we need to take care that such a function will be created. Hibernate itself does actually the same thing. There are different Hibernate Dialects for different database vendors. Those dialects register several types and function which have individual names in the database with a unified name for Hibernate. We just have to apply this to our function as well.
public class MyOracle10gDialect extends Oracle10gDialect {
public MyOracle10gDialect()
{
super();
registerFunction(
"MY_DIFF_TIME_SECONDS",
new SQLFunctionTemplate(
Hibernate.INTEGER,
"MY_DIFF_TIME_SECONDS(?1,?2)"));
}
}
This is the example of our custom tailored Oracle Dialect. We extend Hibernate's Dialect, make sure that super() is called and just add our new function. We do the same with MyHsqlDialect by extending HSQLDialect but just don't show it here.
Implementing the functions in the Database
Up to now we've created the unified facade for the Java world using Hibernate, now we need to look at the other side: Calculating timestamp differences in both Databases. This is, as outlined above, the actually problem we are facing. It is possible to do that in Oracle and HSQL, but with two completely different approaches. But since we have unified everything from Hibernate's point of view, we simply have to take care that the databases get a function MY_DIFF_TIME_SECONDS(x,y) to calculate our timestamp differences.
Let's do it for Oracle first:
CREATE OR REPLACE FUNCTION MY_DIFF_TIME_SECONDS(
start_date IN DATE,end_date in DATE)
return NUMBER
IS
BEGIN
if start_date is null
then
return null;
end if;
if end_date is null
then
return null;
end if;
return (end_date - start_date)*24*60*60;
END;
/
This is PL/SQL and it does what we need. Besides some defensive null checks we simply utlize the Minus operater, which is also defined on Oracle's date types. Its result is a fraction of a day, hence we need to shift the result to become our difference in seconds.
HSQL does not know PL/SQL (lucky dude!). But it enables us to add arbitrary Java functions which could be called from SQL. Fine thing:
import java.sql.Timestamp;
import org.joda.time.Period;
public class HsqlStoredFunctions {
/**
* Returns the difference between two timestamps in seconds.
*
* @param start the early time
* @param end the later time
* @return if start and end != null then the difference is returned,
* otherwise null is returned
*
*/
public static Integer diffTimeInSeconds(Timestamp start,Timestamp end){
if(start == null){
return null;
}
if(end == null){
return null;
}
Period period = new Period(start.getTime(),end.getTime());
return period.toStandardSeconds().getSeconds();
}
}
This is the function. It has to be static, but that's actually the only requirement. We make it aware to HSQL by:
public class HsqlDataSourceInitializer implements InitializingBean{
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource){
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public void afterPropertiesSet() throws Exception {
String aliasDiffTime = "CREATE ALIAS MY_DIFF_TIME_SECONDS FOR " +
" \"mypackage.testing.HsqlStoredFunctions.diffTimeInSeconds\"";
jdbcTemplate.execute(aliasDiffTime);
}
}
Here we see actually two quite interesting features. First the function is registered with the shown SQL statement. Secondly we can also see when we do this registration. Just reconsider: We use HSQL only in our testing scenario. In our particular case the whole persistence layer (and all other layers of the application as well) is nicely decoupled via Spring. Hence the difference between the production and the test environment is the Spring Bean configuration of the DataSource. But since the whole setup is now silently down by Spring we need a hook to register our function when the In Memory Database is created. Therefore HsqlDataSourceInitializer is a Spring Bean which implements InitializingBean. The method afterPropertiesSet() is called after all Spring Beans have been instantiated. Since the DataSource of HSQL Database is nothing but another Spring Bean, we can safely assume that it is up and running.
Setting the sort order on Hibernate Criteria Queries
Now we have a brief look on how we set the order of Hibernate Criteria Queries to show that we have our required unified solution. We've got this nice helper class:
final class CriteriaUtil {
private static final String ORDER_ASC = "asc";
private static final String ORDER_DESC = "desc";
public final Criteria withOrder(Criteria criteria,String sort,String order){
if(criteria == null){
throw new NullPointerException();
}
if(order != null){
if(!(order.equals(ORDER_ASC) || order.equals(ORDER_DESC) )){
throw new IllegalArgumentException("Unknown order direction: " + order);
}
}
if(sort != null){
if(order != null && order.equals(ORDER_DESC)){
criteria.addOrder(Property.forName(sort).desc());
}
else {
criteria.addOrder(Property.forName(sort).asc());
}
}
return criteria;
}
}
And we can use the attributes as provided by Grails' build-in pagination mechanism to extend our Criteria Queries.
No comments:
Post a Comment