CODEWITHSUNDEEP
javahibernatehibernate-criteria
Sudersan
Sudersan

Reputation: 25

Criteria API in Hibernate, multiple restrictions for same column in joining table

I have tables person and personDoc as below:

person :

personNo
dateofBirth
firstname
LastName
MiddleName
salutationCode

personDoc :

personNo
doctype
documentNo

I have to search person with name = "John" and (doctype = "passport" and documentNo = "XXXX') and (doctype = "Driving license" and documentNo= "YYYY")

I am using the criteria API to do the search.

I have joined the person and persondoc tables using alias.

How do I add restrictions (for the same table and column) more than once?

Note:for further scenarios, I need to search like:

person with name like "John" and (doctype = "passport" and documentNo = "XXXX') or (doctype = "Driving license" and documentNo= "YYYY").

Upvotes: 0

Views: 1889

Answers (2)

Madhusudana Reddy Sunnapu
Madhusudana Reddy Sunnapu

Reputation: 8247

To create the following query with Criteria:

person with name like "John" and (doctype = "passport" and documentNo = "XXXX') or (doctype = "Driving license" and documentNo= "YYYY").

You can try with Criteria's Disjunction and Conjunction feature

Without looking how Person and PersonDoc tables are mapped using the entity classes it is difficult to build the exact criteria. But I hope below example should give you the hint on you can do it.

Disjunction disjunction = Restrictions.disjunction(); //OR condition

Conjunction conjunction1  = Restrictions.conjunction(); //AND condition
conjunction1.add(Restrictions.eq("doc.doctype", "passport"));
conjunction1.add(Restrictions.eq("doc.documentNo", "XXXX"));

Conjunction conjunction2  = Restrictions.conjunction(); //AND condition
conjunction2.add(Restrictions.eq("doc.doctype", "Driving License"));
conjunction2.add(Restrictions.eq("doc.documentNo", "YYYY"));

disjunction.add(conjunction1);
disjunction.add(conjunction2);

And this disjunction can be used in criteria building (as an example):

session.createCriteria(Person.class, "p").createAlias(.., "doc").
add(Restrictions.and(Restrictions.like("p.name", "John"),   disjunction)).   
setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Added setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) to make sure it returns only distinct Person entities.

Note that we can achieve the same using Restrictions.and and Restrictions.oras well.

If this doesn't help, do post entities related to Person and PersonDoc tables.

Upvotes: 1

Ilya Dyoshin
Ilya Dyoshin

Reputation: 4624

You can achieve this like following:

Root<Person> r = cq.from(Person.class);
Root<PersonDoc> p = cq.from(PersonDoc.class);
cq.where(
   cb.and(
      cb.equal(r.get(Person_.name), "John"),     
      cb.or(
        cb.and(
           cb.equal(p.get(PersonDoc_.doctype), "passport"), 
           cb.equal(p.get(PersonDoc_.documentNo), "XXXX")
        ), 
        cb.and(
           cb.equal(p.get(PersonDoc_.doctype), "driverLicense"), 
           cb.equal(p.get(PersonDoc_.documentNo), "XXXX")
        )
      ),  
      cb.equal(p.get(PersonDoc_.person), r)
   );

Upvotes: 0

Related Questions