Search This Blog

Friday 25 January 2013

Restrictions AND OR

I need to execute a query to fetch all entities with id is greater than 5 and the names start with "ent". That means there are two distinct conditions. Doing that using HQL would be pretty straightforward:
public static void testTwoConditions() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("from Entity e where e.id > 3 " +
            "and e.name like 'ent%'");
    List<Entity> entities = q.list();
    System.out.println(entities);
}
Doing the same in Criteria means applying two criterions on the same criteria instance.
public static void testTwoConditions() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.gt("id",3));
    criteria.add(Restrictions.like("name", "ent",MatchMode.START));
    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_.ID>? 
        and this_.NAME like ?
As can be seen applying two conditions together is as simple as adding two criterions. Every addition of a Criterion instance will result in an additional and condition being applied to the where clause of the SQL query.
But what if need an OR condition ?
public static void testTwoOrConditions() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.or(Restrictions.gt("id",3),
            Restrictions.like("name", "ent",MatchMode.START)));
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}
The query will be:
    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>? 
            or this_.NAME like ?
        )
The other way to do the same would be using the Restrictions.disjunction() method:
public static void testDisjunction() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(
        Restrictions.disjunction()
            .add(Restrictions.gt("id",3))
            .add(Restrictions.like("name", "ent",MatchMode.START)
            )
        );
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}
This will also generate an identical query.
What if we need to add an additional and clause to the above or clauses. That is I need all entities whose id is greater than 5 or the names start with "ent" . But the master for all records must have  id equal to 1.
public static void testDisjunctionWithConjuction() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(
            Restrictions.disjunction()
                .add(Restrictions.gt("id", 3))
                .add(Restrictions.like("name", "ent", MatchMode.START))
            )
            .add(Restrictions.eq("master.id", 1l)
        );
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}
The query generated 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>? 
            or this_.NAME like ?
        ) 
        and this_.MASTER_ID=?
The same result will be produced with the below method:
public static void testOrFollowedByAnd() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(
                Restrictions.or(
                    Restrictions.gt("id",3),
                    Restrictions.like("name", "ent",MatchMode.START)
                ))        
            .add(
                Restrictions.eq("master.id",1l)
                );
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}
I decided to tweak the query a little - I need all entities whose id is greater than 5 and the names start with "ent" . Or the master must have  id equal to 1. The Criteria query now is :
public static void testAndFollowedByOr() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(
            Restrictions.or(
                Restrictions.and(
                        Restrictions.gt("id",3),
                        Restrictions.like("name", "ent",MatchMode.START)
                )        
            ,Restrictions.eq("master.id",1l)
            )
        );
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}
The query generated 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>? 
           and this_.NAME like ?
       ) 
       or this_.MASTER_ID=?
    )
[[Entity] : ( id 2 , data : entity1 , master.Id : 1 , date : null )], [Entity] :
 ( id 3 , data : entity2 , master.Id : 1 , date : null )], [Entity] : ( id 4 , d
ata : entity100 , master.Id : 2 , date : null )], [Entity] : ( id 5 , data : ent
ity102 , master.Id : 2 , date : null )]]

No comments:

Post a Comment