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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s