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

1 comment:

  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