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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Adobe Customization Wizard XI issues 26 166
java constructor error 8 86
word0 challenge 3 56
wordmultiple challenge 12 89
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now