Left join using hibernate criteria

Question

I have two entity: Issue and Issue_Tracker. I am using Hibernate 3.6.

SELECT `issues`.`issue_id`,
       `issues`.`issue_raised_date`,
       `issues`.`issue_description`,
       `issue_tracker`.`tracker_status`
FROM `issues`
   LEFT JOIN  `issue_tracker` ON `issues`.`issue_id` = `issue_tracker`.`issue_id`
WHERE `issues`.`status`="Escalate To"

How to achieve this using Hibernate Criteria, and most Important, I have to use it for pagination.

and My Dao is as follows to show the list of Issues in jqgrid

public List showHelpDeskIssues(DetachedCriteria dc, int from, int size) {

Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 try
  {

    Criteria criteria = dc.getExecutableCriteria(session);
    criteria.setFirstResult(from);
    criteria.setMaxResults(size);
    criteria.add(Restrictions.eq("status","Escalate To"));

    return criteria.list();
  }
  catch (HibernateException e)
  {
    e.printStackTrace();
    throw e;
  } }

For brief explanation please refer this question how to show two tables data in jqgrid using struts2 - jqgrid plugin and hibernate any help would be great.

You have probably misunderstood SQL join queries. Should be LEFT JOIN `issue_tracker` ON `issues`.`issue_tracker_id` = `issue_tracker`.`id` — Roman C, Jan 08 '13 at 12:54
thanks...but right now this is not my area of concern. I want to know how to achieve this using criteria — arvin_codeHunk, Jan 08 '13 at 12:59

score 8 · Accepted Answer · edited Jul 08 '22 at 15:13

8

you can try the following

Criteria criteria = session.createCriteria(Issues.class);
criteria.setFirstResult(from);
criteria.setMaxResults(size);
criteria.setFetchMode('parent.child', FetchMode.JOIN);
criteria.add(Restrictions.eq("status", "Escalate To"));
List<Issues> list= criteria.list();

here parent is the property name in Issues.java and child is the property in IssueTracker.java.

edited Jul 08 '22 at 15:13

Ruzihm

19,749
5
36
48

answered Jan 08 '13 at 12:53

Anantha Sharma

9,920
4
33
35

thank for quick response, but i did not mapped these two pojo as one to many. Still it works and what is the property name? – arvin_codeHunk Jan 08 '13 at 12:57
have u done a many-to-one... these properties have to be mapped somhow for criteria to select them in the expected manner. – Anantha Sharma Jan 08 '13 at 13:02
either use (issues) one-to-many (issue-tracker) or (issue-tracker) many-to-one (issue). with the 2nd approach you should create criteria on issue tracker and transform result to bean for issue. – Anantha Sharma Jan 08 '13 at 13:03
ok.. I got it, one more question how what is property name you have mentioned in your answer – arvin_codeHunk Jan 08 '13 at 13:04
here you go.. http://www.mkyong.com/hibernate/hibernate-one-to-many-relationship-example/ – Anantha Sharma Jan 08 '13 at 13:06

score 8 · Answer 2 · answered Jan 08 '13 at 13:05

8

Well,

follow one sample...

Criteria crit = session.createCriteria(Issues.class);
crit.createAlias("otherClass", "otherClass");
crit.add(Restrictions.eq("otherClass.status", "Escalate To"));
List result = crit.list();

I think so this can to help!!

answered Jan 08 '13 at 13:05

Altieres de Matos

111
1
5

This saved me! This is the only example of `createAlias` that I could find. – NobleUplift Mar 18 '15 at 15:29

score 2 · Answer 3 · answered Feb 26 '16 at 13:36

Try this out because this worked for me

Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Issues.class, "issues");
criteria.setFetchMode("issues.issuetracker", FetchMode.JOIN);
criteria.createAlias("issues.issuetracker", "issuetracker");
criteria.add(Restrictions.eq("status","Escalate To"));
List list = criteria.list();
return list;

score 1 · Answer 4 · answered Jun 03 '20 at 08:43

Note: In Hibernate criteria, inner outer join and inner join are one and the same. Do not get confused.

Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 try{
     Criteria criteria = session.createCriteria(Issues.class, "issues");
     criteria.setFirstResult(from);
     criteria.setMaxResults(size);
     criteria.createAlias("issues.issuestracker", "issuestracker", JoinType.LEFT_OUTER_JOIN);
     criteria.add(Restrictions.eq("issues.status", "Escalate To"));

     return criteria.list();
   }catch(HibernateException e){
       e.printStackTrace();
       throw e;
     }

This should solve your issue. Let me know if you face any trouble.

Left join using hibernate criteria

4 Answers4

Linked