Applying Restrictions

In the previous post we saw how a Criterion was actually translated into an SQL query. In this post we shall use the different restrictions available. I started with the simple ones involving the equality operator.

public static void testEq() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.eq("name", "entity1"));
    Entity entity = (Entity) criteria.uniqueResult(); 
    System.out.println(entity);
}

The result :

    select
        this_.ID as ID0_0_,
        this_.NAME as NAME0_0_,
        this_.DATE as DATE0_0_,
        this_.MASTER_ID as MASTER4_0_0_ 
    from
        ENTITY this_ 
    where
        this_.NAME=?
[Entity] : ( id 2 , data : entity1 , master.Id : 1 , date : null )]

What if we need to check for equality between two fields ?

public static void testFieldsEq() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.eqProperty("id", "master.id"));
    List<Entity> entities = criteria.list(); 
    System.out.println(entities);
}

The above code attempts to return all entities whose id is same as the id of their parent (Master). For such an "equal" constraint to two properties we use the eqProperty() of Restrictions class that returns a PropertyExpression. Similar methods with names ending in "Property" are available for comparison operations between two properties. The query generated for above method is :

    select
        this_.ID as ID0_0_,
        this_.NAME as NAME0_0_,
        this_.DATE as DATE0_0_,
        this_.MASTER_ID as MASTER4_0_0_ 
    from
        ENTITY this_ 
    where
        this_.ID=this_.MASTER_ID

Next are the comparison operators:

public static void testFieldsLe() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.le("id", 5));
    List<Entity> entities = criteria.list(); 
    System.out.println(entities);
}

And the query:

    select
        this_.ID as ID0_0_,
        this_.NAME as NAME0_0_,
        this_.DATE as DATE0_0_,
        this_.MASTER_ID as MASTER4_0_0_ 
    from
        ENTITY this_ 
    where
        this_.ID<=?

Similarly we have methods for gt,lt,ge,ne.
The isNull() Restriction can be used to return records against a null condition.

public static void testFieldsNull() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.isNull("date"));
    List<Entity> entities = criteria.list(); 
    System.out.println(entities);
}

The query is :

    select
        this_.ID as ID0_0_,
        this_.NAME as NAME0_0_,
        this_.DATE as DATE0_0_,
        this_.MASTER_ID as MASTER4_0_0_ 
    from
        ENTITY this_ 
    where
        this_.DATE is null

Similarly we have the isNotNull() method.We can also compare for string columns using the like() method.

public static void testLike() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.like("name", "entity1"));
    Entity entity = (Entity) criteria.uniqueResult(); 
    System.out.println(entity);
}

The query is:

    select
        this_.ID as ID0_0_,
        this_.NAME as NAME0_0_,
        this_.DATE as DATE0_0_,
        this_.MASTER_ID as MASTER4_0_0_ 
    from
        ENTITY this_ 
    where
        this_.NAME like ?

Similarly for case insensitive operations we have ilike:

public static void testILike() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.ilike("name", "EnT",MatchMode.START));
    List<Entity> entities = criteria.list(); 
    System.out.println(entities);
}

The query will do a case insensitive comparison also taking into consideration the match mode(optional parameter, also available in like):

    select
        this_.ID as ID0_0_,
        this_.NAME as NAME0_0_,
        this_.DATE as DATE0_0_,
        this_.MASTER_ID as MASTER4_0_0_ 
    from
        ENTITY this_ 
    where
        lower(this_.NAME) like ?
3031 [main] DEBUG org.hibernate.type.StringType  - binding 'ent%' to parameter: 1

Other options for Match mode are

• MatchMode.ANYWHERE  %ent%
• MatchMode.END  %ent
•  MatchMode.EXACT  ent

The eq operator also supports case insensitive comparison.

criteria.add(Restrictions.eq("name", "entity1").ignoreCase());