Programmatically Generating Database Schema with Hibernate 5

Today, I was working with a code base that was using Hibernate‘s schema generation facilities to create the database schema for a customer on the fly. The application creates separate database schema for each customer. Then, depending on the client specific identifier it connects to the correct database. This blog will not talk about how you should design your application with multi tenant database. I will cover that in a future post. In this post, I will share the code snippet that helped me generate database schema with Hibernate 5. In this post, I am using MySQL database as an example. The same code snippet should also work with other RDBMS as well.
Continue reading “Programmatically Generating Database Schema with Hibernate 5”

Advertisements

HQL which gives number of hours between two timestamps

Intent

To write down a hql (hibernate query language) which gives the difference between two timestamps.

Motivation

Today, i had to write down a hql which should return me the difference between two timestamps. It took me quite a long time to write down the hql query which gives me the correct result. So, i am writing down hql in this blog in order to help any developer who might face this problem.To explain the problem, suppose we have to find out the number of hours before which a user was last updated. The problem seemed quite easy when I first thought about the solution.  The most obvious solution i thought was to use hour() function of hql. So, I wrote down the hql

select hour(current_timestamp – user.lastUpdatedTimeStamp) from com.test.User as user where user.id=1;

It worked but only when both the timestamps are of the same date i.e. if the current_timestamp and lastUpdateTimeStamp are of same date (18th April 2010). This solution does not work if the timestamp are on different date as hour function gives number of hours based on time only, it does not consider dates. So, if current_timestamp is 19-04-2010 11:00:00 and lastUpdatedTimeStamp is 18-04-2010 5:00:00 , it will give answer as 6 which is wrong as number of hour between these timestamps is 30.

Solution

HQL which works is

select

(days(current_timestamp) *24 + hour(current_timestamp)) -( days(user.lastUpdatedTimeStamp)*24 + hour( user.lastUpdatedTimeStamp))

from com.test.User as user where user.id=1;

This hql will give the correct answer as it will add the number of hours corresponding to the date to the number of hours on that particular date.

Mathematically, this will be like this

((19*24 + 11)  -(18*24 +5)) == 467 – 437 = 30

So, we will get the correct answer 30.

Blog at WordPress.com.

Up ↑