Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

subquery with where clause

Ketan Nale
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I want to write hibernate DetachedCriteria for following SQL query :

my sql query is :

select * from pkg_order
(select count(id) from user_pkg

i have tried like following.
Can anyone help me to optimise this query??
its giving me correct output but .... still i want the better solution .......

DetachedCriteria subquery = DetachedCriteria.forClass(EduoaksUserPkg.class, "pkgOrderAlias");
subquery.add(Expression.eqProperty("pkgOrderAlias."+EduoaksUserPkg.PROP_PKG_ORDER+"."+EduoaksPkgOrder.PROP_ID, ""));

DetachedCriteria criteria = DetachedCriteria.forClass(EduoaksPkgOrder.class, "u");

criteria.createAlias("u."+EduoaksPkgOrder.PROP_PKG, "pkgAlias");
criteria.createAlias("pkgAlias"+"."+EduoaksPkg.PROP_USAGE, "usageAlias");

criteria.add(Restrictions.eq("u."+EduoaksPkgOrder.PROP_USER, new SakaiUser(currentUserId)));
criteria.add("u."+EduoaksPkgOrder.PROP_EXPIRAY_DATE, new Date()));
criteria.add(Restrictions.eq("u."+EduoaksPkgOrder.PROP_STATUS, "Active"));

Criterion unlimited = Restrictions.eq("usageAlias"+"."+EduoaksPkgUsage.PROP_USAGE, SamSLPLConstants.UNLIMITED_VAL);
Criterion limited = Subqueries.propertyGt("usageAlias"+"."+EduoaksPkgUsage.PROP_USAGE, subquery);
Criterion criterion = Restrictions.or(unlimited, limited);

Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!