HQL - The where clause

Logic in applications almost always involves execution of queries involving where clauses on the database. HQL also supports where clauses.
I decided to start with a simple where condition:

final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity e where e.id = 1");
//null return is totally fine 
Entity entity = (Entity) q.uniqueResult();
System.out.println(entity);

4563 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.id = 1 */     
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.ID=1
...
[Entity] : ( id 1 , data : newOne , master.Id : 1 , date : null )]

The where clause supports the between expression:

final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity e where e.id between 1 and 3"); //1, 3 included
List<Entity> entities = q.list();
System.out.println(entities);

The generated query is as below:

4250 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.id between 1 and 3 */ 
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.ID between 1 and 3

The query will include ids with 1,2 and 3 value.
HQL also provides for logic operators.

final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity e where e.id > 2"); //1 only
List<Entity> entities = q.list();
System.out.println(entities);

The resultant query is :

4391 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.id > 2 */ 
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.ID>2

HQL provides for the in expression too. In this the parameter is a collection.

final Session session = sessionFactory.openSession();
Set<String> entityNames = new LinkedHashSet<String>();
entityNames.add("entity3");
entityNames.add("entity2");
Query q = session.createQuery("from Entity e where e.name in (:set)"); 
    //OR
//"from Entity e where e.name in ('entity2','entity3')"
q.setParameterList("set", entityNames);
List<Entity> entities = q.list();
for (Entity entity : entities) {
System.out.println(entity.getName() + " id : " + entity.getId());

The generated logs and sql is as below:

3922 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.name in (
        :set
    ) */ 
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.NAME in (
            ? , ?
        )
entity2 id : 2
entity3 id : 3

We can also do a test against the null operator:

final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity e where e.date is null"); 
List<Entity> entities = q.list();
System.out.println("Null values are : ");
for (Entity entity : entities) {
    System.out.println(entity.getName() + " id : " + entity.getId());
}
System.out.println("Non Null values are : ");
q = session.createQuery("from Entity e where e.name is not null");
entities = q.list();
for (Entity entity : entities) {
    System.out.println(entity.getName() + " id : " + entity.getId());
}

The output indicates the sql:

4281 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.date is null */ 
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.DATE is null
Null values are : 
newOne id : 1
entity2 id : 2
entity3 id : 3
...
4359 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.name is not null */ 
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.NAME is not null
Non Null values are : 
newOne id : 1
entity2 id : 2
entity3 id : 3

There are so many more which I will cover in the next post.