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

To get Start and End date by given Month, Year and Day

import java.util.Calendar;
import java.util.Date;

public class HelloWorld {

	public static void main(String[] args) {

		Date fromDate = getFromDate(2014, 7, null);
		System.out.println(" From Date is  " + fromDate);

		fromDate = getFromDate(2014, 7, 14);
		System.out.println(" From Date is  " + fromDate);

		Date toDate = getToDate(2014, 7, null);
		System.out.println(" To Date is  " + toDate);

		toDate = getToDate(2014, 7, 14);
		System.out.println(" To Date is  " + toDate);

	}

	private static Date getFromDate(Integer year, Integer month, Integer day) {
		// Setup a Calendar instance.
		Calendar cal = Calendar.getInstance();
		cal.setLenient(false);
		// Set the year as 2009
		cal.set(Calendar.YEAR, year);
		// Set the month as February (can be set as 1 or Calendar.FEBRUARY)
		cal.set(Calendar.MONTH, month);
		// Set the date as 1st - optional
		if (day == null)
			cal.set(Calendar.DATE, 1);
		else
			cal.set(Calendar.DAY_OF_MONTH, day);
		cal.set(Calendar.HOUR_OF_DAY, 0);
		cal.set(Calendar.MINUTE, 0);
		cal.set(Calendar.SECOND, 0);
		return cal.getTime();
	}

	private static Date getToDate(Integer year, Integer month, Integer day) {
		// Setup a Calendar instance.
		Calendar cal = Calendar.getInstance();
		cal.setLenient(false);
		// Set the year as 2009
		cal.set(Calendar.YEAR, year);
		// Set the month as February (can be set as 1 or Calendar.FEBRUARY)
		cal.set(Calendar.MONTH, month - 1);
		// Set the date as 1st - optional

		if (day == null) {
			int lastDateOfMonth = cal.getActualMaximum(Calendar.DATE);
			cal.set(Calendar.DATE, lastDateOfMonth);
		} else
			cal.set(Calendar.DAY_OF_MONTH, day);
		cal.set(Calendar.HOUR_OF_DAY, 23);
		cal.set(Calendar.MINUTE, 59);
		cal.set(Calendar.SECOND, 59);
		return cal.getTime();
	}
}

Output:

From Date is  Fri Aug 01 00:00:00 IST 2014
From Date is  Thu Aug 14 00:00:00 IST 2014
To Date is  Thu Jul 31 23:59:59 IST 2014
To Date is  Mon Jul 14 23:59:59 IST 2014