HIBERNATE Examples - Step 4. Criteria based queries Examples

The following examples takes you through a series of aggregate functions, where clauses, group by and order by criteria using hibernate.

MAX()

public int getMaxDeptId() {

    int maxId = 0;

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);
    DetachedCriteria criteria = DetachedCriteria
            .forClass(Departments.class);
    criteria.setProjection(Projections.max("deptId"));

    List result = ht.findByCriteria(criteria);
    maxId = (Integer) result.get(0);

    return maxId;
}

MIN()

public int getMinDeptId() {

    int minId = 0;

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);
    DetachedCriteria criteria = DetachedCriteria
            .forClass(Departments.class);
    criteria.setProjection(Projections.min("deptId"));

    List result = ht.findByCriteria(criteria);

    minId = (Integer) result.get(0);
    return minId;
}

COUNT()

public int getDepartmentsCountStartedInYear(String year) {

    int value = 0;

    try {
        HibernateTemplate ht = new HibernateTemplate(sessionFactory);
        DetachedCriteria criteria = DetachedCriteria
                .forClass(Departments.class);
        criteria.setProjection(Projections.rowCount()).add(
                Restrictions.eq("yearStarted", year));

        List results = ht.findByCriteria(criteria);
        value = (Integer) results.get(0);

    } catch (Throwable e) {
        e.printStackTrace();
    }

    return value;
}

Restrictions based Query (AND, OR, comparators)

public List getCustomDepartments() {

 ArrayList values = new ArrayList();
 values.add("ECE");
 values.add("EEE");
 values.add("Info Tech");
 values.add("Mechanical");

 HibernateTemplate ht = new HibernateTemplate(sessionFactory);
 DetachedCriteria criteria = DetachedCriteria
   .forClass(Departments.class);

 criteria.add(Restrictions.in("deptName", values));
 criteria.add(Restrictions.and(Restrictions.in("deptName", values),
   Restrictions.le("deptId", 3)));

 criteria.addOrder(Order.desc("deptName"));

 return ht.findByCriteria(criteria);
}

GROUP BY

public void getDepartmentsInEachYear() {

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);
    DetachedCriteria criteria = DetachedCriteria
            .forClass(Departments.class);

    criteria.setProjection(Projections.groupProperty("yearStarted"));

    System.out.println(">>>>" + ht.findByCriteria(criteria).size());
}

Order By

public List<Departments> getDepartments(boolean ascending) {

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);
    DetachedCriteria criteria = DetachedCriteria
            .forClass(Departments.class);

    if (!ascending) {
        criteria.addOrder(Order.desc("deptName"));
    } else {
        criteria.addOrder(Order.asc("deptName"));
    }

    return ht.findByCriteria(criteria);
}