Restriction class usage in Hibernate

Posted on 2008-06-22
Last Modified: 2013-11-13

I am new Hibernate. Can someone help me to resolve the following issue,
I have two table with many to many relations, Product table and Group Table. Let me provide sample data

GroupId=1 Group Name= Calvin Klein Contains Product with Ids (10,11,12)
GroupId=2 Group Name= Shorts Contains Products with Ids (11,12,13,15)
GroupId=3 Group Name=Swimmers Contains products with Ids (12,17)
GroupId43 Group Name=caps Contains products with Ids (11,19,17)

I wanted to select the groups which contains common product which is also available in groups Calvin Klein and Shorts.  So my Hibernate query should pick common products in groups Calvin Klein and Shorts i.e 12 and find out other group which contains product 12 i.e group name Swimmers.

I have written the Hibernate code for the same but it is returning Group 3,4 and it is not selecting common products in Group 1 and 2.
String s[]={Calvin Klein, Shorts};
  DetachedCriteria detachedCriteriaForNarrowBy = DetachedCriteria.forClass(GroupDO.class, "group")
        .createAlias("group.productList", "product")
        .createAlias("product.groupList", "rootGroup")
        .add(""," Calvin Klein "))
.add(""," Shorts "))
        Criteria hibernateCriteria = detachedCriteriaForNarrowBy.getExecutableCriteria(session);

Can someone help to put Restriction to select only common products in above query.

Question by:rameshsukka
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

rameshsukka earned 0 total points
ID: 21857422
I got the solution in HQL, I am posting the solution it might helpful for some others...

String s[]={"Calvin Klein","Swimwear"}; //groups to be search
String str=null;
for(int i=0;i<s.length;i++)

System.out.println(" s.length is "+s.length);

StringBuffer testBuffy = new StringBuffer("select DISTINCT rootgroups from GroupDO as rootgroups " +
" join rootgroups.productList as rootGroupProductList "+
" where rootGroupProductList IN (select DISTINCT product from ProductDO as product " );
testBuffy.append("join product.groupList as groups ");
testBuffy.append("where in (");
testBuffy.append("group by product having count(product)>"+String.valueOf(s.length-1)+" ) ");
testBuffy.append("and not in (");

Query qproducts = hibernateTemplate.getSessionFactory().getCurrentSession().createQuery(testBuffy.toString());

List<GroupDO> resultsGroup=qproducts.list();
System.out.println("size of group List is"+resultsGroup.size());
java.util.Iterator<GroupDO> i=resultsGroup.iterator();
GroupDO temp=(GroupDO);
System.out.println("Group Name is "+temp.getName());


Ramesh Sukka.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Six Sigma Control Plans

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question