Hi,

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(Restrictions.ne("group.name"," Calvin Klein "))
.add(Restrictions.ne("group.name"," Shorts "))
.add(Restrictions.in("rootGroup.name",s));

detachedCriteriaForNarrowBy.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
Criteria hibernateCriteria = detachedCriteriaForNarrowBy.getExecutableCriteria(session);

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

Tnx,
Sukka

I think you may need to leverage the Dysjunction class:

http://www.hibernate.org/hib_docs/v3/api/index.html

Thanks for your comment Cameron, 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++)
{

if(str!=null)
str=str+"'"+s[i]+"'";
else
str="'"+s[i]+"'";
if(i+1<s.length)
str=str+",";
}
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 ");
if(s.length!=0)
{
testBuffy.append("where groups.name in (");
testBuffy.append(str+")");
testBuffy.append("group by product having count(product)>"+String.valueOf(s.length-1)+" ) ");
testBuffy.append("and rootgroups.name not in (");
testBuffy.append(str+")");
}
else
testBuffy.append(")");

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


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

}

Regards,
Ramesh Sukka.