HIBERNATE Examples - Step 5. DetachedCriteria, Restrictions and Projections

The following examples takes through the different ways of using DetachedCriteria with Restrictions and Projections.

Restrictions:

Restricting your query result by using WHERE clause with IN, AND OR, etc.

Projections:

How to project your query result by using GROUP BY, ORDER BY, MIN, MAX, etc.

Restrictions.ilike

public void printNoOfDeptsBasedOnYear() {

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);

    DetachedCriteria criteria = DetachedCriteria.forClass(
            Departments.class, "dept");
    criteria.add(Restrictions.ilike("yearStarted", "1", MatchMode.START));
    criteria.addOrder(Order.asc("deptName"));

    List<Departments> results = ht.findByCriteria(criteria);
    for (Departments dept : results) {
        System.out.println("Dept Name : " + dept.getDeptName()
                + ", Year : " + dept.getYearStarted() + ", Id : "
                + dept.getDeptId());
    }
}

Restrictions.sqlRestriction

public void printNoOfDeptsBasedOnYear1() {

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);

    DetachedCriteria criteria = DetachedCriteria
            .forClass(Departments.class);
    criteria
            .add(Restrictions.sqlRestriction("year_started like '2%'"));
    criteria.addOrder(Order.asc("deptName"));

    List<Departments> results = ht.findByCriteria(criteria);
    for (Departments dept : results) {
        System.out.println("Dept Name : " + dept.getDeptName()
                + ", Year : " + dept.getYearStarted() + ", Id : "
                + dept.getDeptId());
    }
}

Projections.distinct

public void addProjectionList() {

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);

    DetachedCriteria criteria = DetachedCriteria
            .forClass(Departments.class);
    criteria.setProjection(Projections.distinct(Projections
            .property("yearStarted")));

    List results = ht.findByCriteria(criteria);
    System.out.println("Distinct years count : " + results.size());
}

Projections.projectionList

public void addProjectionList() {

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);

    DetachedCriteria criteria = DetachedCriteria
            .forClass(Departments.class);

    ProjectionList pl = Projections.projectionList();
    pl.add(Projections.rowCount());
    pl.add(Projections.groupProperty("yearStarted"));
    
    criteria.setProjection(pl);

    List results = ht.findByCriteria(criteria);
    System.out.println("Count : " + results.size());
    System.out.println("First Value : " + results.get(0));
}

Generated SQL:

select count(*) as y0_, this_.YEAR_STARTED as y1_ from departments this_ group by this_.YEAR_STARTED

Projections.sqlProjection

public void addProjectionList() {

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);

    DetachedCriteria criteria = DetachedCriteria
            .forClass(Departments.class);

    ProjectionList pl = Projections.projectionList();
    pl.add(Projections.groupProperty("yearStarted"));
    pl.add(Projections.sqlProjection("count(*) AS countOne, year_started AS ys",
            new String[] { "countOne", "ys" }, new Type[] {
                    Hibernate.INTEGER, Hibernate.INTEGER }));

    criteria.setProjection(pl);

    List results = ht.findByCriteria(criteria);
    System.out.println("Count : " + results.size());
    System.out.println("First Value : " + results.get(0));
}

Generated SQL:

select this_.YEAR_STARTED as y0_, count(*) AS countOne, year_started AS ys from departments this_ group by this_.YEAR_STARTED

Projections.sqlGroupProjection

public void addProjectionList() {

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);

    DetachedCriteria criteria = DetachedCriteria
            .forClass(Departments.class);

    ProjectionList pl = Projections.projectionList();
    pl.add(Projections.sqlGroupProjection(
            "count(*) AS countOne, year_started AS ys", "ys HAVING ys < 2006", new String[] {
                    "countOne", "ys" }, new Type[] { Hibernate.INTEGER,
                    Hibernate.INTEGER }));

    criteria.setProjection(pl);

    List results = ht.findByCriteria(criteria);
    System.out.println("Count : " + results.size());
    System.out.println("First Value : " + results.get(0));
}

Generated SQL:

select count(*) AS countOne, year_started AS ys from departments this_ group by ys HAVING ys < 2006

Projections.resultTransformer

public void addProjectionList1() {

    HibernateTemplate ht = new HibernateTemplate(sessionFactory);

    DetachedCriteria criteria = DetachedCriteria
            .forClass(Departments.class);

    ProjectionList pl = Projections.projectionList();
    pl.add(Projections.sqlGroupProjection(
            "count(*) AS deptId, year_started AS yearStarted",
            "yearStarted HAVING yearStarted < 2006", new String[] {
                    "deptId", "yearStarted" }, new Type[] {
                    Hibernate.INTEGER, Hibernate.STRING }));

    criteria.setProjection(pl);
    criteria.setResultTransformer(new AliasToBeanResultTransformer(
            Departments.class));

    List results = ht.findByCriteria(criteria);
    Iterator itr = results.iterator();
    while (itr.hasNext()) {
        Departments d = (Departments) itr.next();
        System.out.println(d.getDeptId() + ", " + d.getYearStarted() + ", "
                + d.getDeptName());
    }
}