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.

 

First hibernate application

Tools used:

  1. Postgresql Database(9.3) – http://www.postgresql.org/download/
  2. Hibernate Jars (4.3.4)- http://hibernate.org/orm/downloads/
  3. Postgres JDBC Driver – http://jdbc.postgresql.org/

Steps to create:

  1. Create a testProject in your eclipse and include the hibernate Jars and Postgres JDBC driver in your class path.
  2. Create a hibernate.cfg.xml file and it should look like this.
  3. Create a DTO POJO with getters and setters method. Add @Entity Annotation to the top of that class.
  4. Create a test class with main() function. The main() function will look something like:
  5. Run the application as a Java application and see the results in postgresDB.

Changes can be updated from this location: https://github.com/volcanicprateek/hibernateTest.git

References: