How to write this join query with hibernate CRITERIA

Question

select ptm.* from ProofTestMaster ptm LEFT JOIN 
ProofTestApplicationMap ptam on ptm.proofTestID = ptam.proofTestID 
LEFT JOIN ProofTestComapartmentMap ptcm on ptm.proofTestID = ptcm.proofTestID 
where (ptam.applicationID = 3 and ptm.isDeleted = 0) or 
(ptcm.compartmentID = 4 and ptm.isDeleted = 0)

Where ProofTestApplicationMap and ProofTestComapartmentMap is map table and they don't have entity in java end.

Hope this link will help you out :) http://stackoverflow.com/questions/6165743/how-to-write-hibernate-criteria-query-for-two-different-tables — DushanthaR, Aug 19 '13 at 06:18
It is really hard to map 2 relations and 3 tables. Furthermore, it is really complicated to maintain such mapping. If you have one to many, many to many relations, you also need to maintain lazy load strategies which get your work harder. In my humble opinion, it is needless such a mapping. — erencan, Aug 19 '13 at 06:38
@erencan then how you suggest to maintain the data structure for make life easier? — Robert Smith, Aug 19 '13 at 06:45
Check: http://stackoverflow.com/questions/8726396/hibernate-criteria-join-with-3-tables — rags, Aug 19 '13 at 07:01

score 1 · Answer 1 · answered Aug 19 '13 at 12:00

It is always better to put proper indentation for your query. The advantages are:

You will understand the query easilly
Other person will also understand quickly.

So I did this for you. Now the query looks like:

select 
    ptm.* 
from ProofTestMaster ptm 
    LEFT JOIN ProofTestApplicationMap ptam on ptm.proofTestID = ptam.proofTestID 
    LEFT JOIN ProofTestComapartmentMap ptcm on ptm.proofTestID = ptcm.proofTestID 
where 
    (ptam.applicationID = 3 and ptm.isDeleted = 0) or 
    (ptcm.compartmentID = 4 and ptm.isDeleted = 0);

Now below is the implementation with CriteriaBuilder with Static Metamodel:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> criteriaQuery = criteriaBuilder.createQuery(Long.class);

Root<ProofTestMaster> mainRoot = criteriaQuery.from(ProofTestMaster.class);

Join<ProofTestMaster, ProofTestApplicationMap> firstJoin = mainRoot.join(ProofTestMaster_.proofTestID, JoinType.LEFT);
Join<ProofTestMaster, ProofTestComapartmentMap> secondJoin = mainRoot.join(ProofTestMaster_.proofTestID, JoinType.LEFT);

Predicate p1 = criteriaBuilder.equal(firstJoin.get(ProofTestApplicationMap_.applicationID),3);
Predicate p2 = criteriaBuilder.equal(mainRoot.get(ProofTestMaster_.isDeleted),0);
Predicate p3 = criteriaBuilder.equal(secondJoin.get(ProofTestComapartmentMap_.compartmentID), 4);
Predicate p4 = criteriaBuilder.and(p1,p2);
Predicate p5 = criteriaBuilder.and(p3,p2);
Predicate p6 = criteriaBuilder.or(p4,p5);

criteriaQuery.where(p6);

criteriaQuery.select(criteriaBuilder.count(mainRoot));

Long count = entityManager.createQuery(criteriaQuery).getSingleResult();

If you see the above code, there are total 6 Predicates, which can be put into List. But I kept it like this for your understanding.

Let me know if it helps you. Thanks and happy coding.

First of all I am sorry for not formatted my query properly and thanks for your solution. Can you please suggest something other than static metamodel? — Robert Smith, Aug 20 '13 at 08:21
No problem. But may I know why you don't want to do this without static metamodel? Using Static Metamodel gives you typesafety features. Adding Static Metamodel is also very easy. You may check this blogpost: [link](http://dailytechdairy.blogspot.in/2013/08/java-persistency-api-20-jpa-hibernate.html) — Sudipta Deb, Aug 20 '13 at 11:17
Yes sure its actually personal issue.. I am in middle of a project and have a tight deadline. If I go for this I have to work on several section so it will bit time consuming. So I just want save time. — Robert Smith, Aug 21 '13 at 10:24

How to write this join query with hibernate CRITERIA

1 Answers1