Monday, 25 August 2014

Hibernate Criteria sqlRestriction example

	Criteria criteria = getCurrentSession().createCriteria(LabOrder.class);
criteria.add(Restrictions.sqlRestriction("YEAR(createdDate)="
+ labOrderConditions.getYear()));
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.sqlGroupProjection(
"MONTHNAME(createdDate) as orderMonthName",
"MONTHNAME(createdDate)", new String[] { "orderMonthName" },
new Type[] { StandardBasicTypes.STRING }));
projectionList.add(Projections.sqlGroupProjection(
"Month(createdDate) as orderMonthNo", "Month(createdDate)",
new String[] { "orderMonthNo" },
new Type[] { StandardBasicTypes.INTEGER }));
projectionList.add(Projections.rowCount());
criteria.setProjection(projectionList);
criteria.addOrder(org.hibernate.criterion.Order.asc("createdDate"));
return criteria.list();

MySQL Query for the above.


select
MONTHNAME(createdDate) as orderMonthName,
Month(createdDate) as orderMonthNo,
count(*) as y2_
from
laborder this_
where
YEAR(createdDate)=2014
group by
MONTHNAME(createdDate),
Month(createdDate)
order by
this_.createdDate asc

3 comments:

  1. Hi, what is the type of return object ?
    Is it "laborder" object or another type since we have aggregation which does not exist as column in "laborder" ?
    Thanks

    ReplyDelete
  2. Thanks a lot! You made a new blog entry to answer my question; I really appreciate your time and effort.And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article...very interesting to read..please sharing like this information
    java training in chennai |
    java training institutes in chennai

    ReplyDelete