Monthly Archives: July 2014

Retrieving columns from multiple tables using hibernate

Not very recently that I ran into this problem where I had to get data from two different tables (entities). There exists a primary key – foreign key relation between these two entities. Explaining this with an example:

Table1 :

ID Column11 Column12
ID1 . .
ID2 . .

 

Table2 :

ID Column21 Column22
ID1 . .
ID2 . .

 

Now I had to retrieve distinct pairs of Column11 and Column22 for a set of IDs.

Discussing two approaches to achieve this:

Approach 1: (Using criteria.createCriteria())

final Criteria criteria = getSession().createCriteria(Table1.class).add(Restrictions.eq(“ids”, ids));

final ProjectionList projList = Projections.projectionList();

projList.add(Projections.property(“Column11”));

criteria.createCriteria(“Table2”, “Table2”);

projList.add(Projections.property(“Table2.Column22”));

criteria.setProjection(Projections.distinct(projList));

final List values = criteria.list();

Note: This requires the projections to be added in this particular order only. If we add all the projections later after creating all the criteria, it will validate all the properties on the class on which last criteria is created and will not work.

 

Approach 2: (Using criteria.createAlias())

final Criteria criteria = getSession().createCriteria(Table1.class).add(Restrictions.eq(“id”, id));

criteria.createAlias(“Table2”, “Table2”);

final ProjectionList projList = Projections.projectionList();

projList.add(Projections.property(“Column11”));

projList.add(Projections.property(“Table2.Column22”));

criteria.setProjection(Projections.distinct(projList));

final List values = criteria.list();

 

I recommend to use Approach 2 as the projections list order doesn’t matter in case of createAlias(), which makes the code handier.