Search This Blog

Sunday 14 October 2012

HQL - The where clause - 2

Continuing from the previous post, I decided to try out other HQL queries
SQL provides the like operator which is used in sting comparisons. It also supports the % and _ wildcards. The same are available with HQL too. Consider the below example:
final Session session = sessionFactory.openSession();
//any name that starts with e
Query q = session.createQuery("from Entity e where e.name like 'e%'");         
List<Entity> entities = q.list();
System.out.println("values starting with e are : ");
for (Entity entity : entities) {
    System.out.println(entity.getName() + " id : " + entity.getId());
}
        
//any name that is not of the form entity#
q = session.createQuery("from Entity e where e.name not like 'entity_'"); 
//not any name like entity#
entities = q.list();
System.out.println("not any name like entity# : ");
for (Entity entity : entities) {
    System.out.println(entity.getName() + " id : " + entity.getId());
}
        
System.out.println("any name with a % character at start : ");
q = session.createQuery("from Entity e where e.name like '\\%%'");
//\ is an escape character
entities = q.list();
for (Entity entity : entities) {
    System.out.println(entity.getName() + " id : " + entity.getId());
}
On executing the below example the SQL queries generated are:
3688 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.name like 'e%' */ 
    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 like 'e%'
values starting with e are : 
entity2 id : 2
entity3 id : 3
...
3813 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.name not like 'entity_' */ 
    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 not like 'entity_'
not any name like entity# : 
newOne id : 1
...
any name with a % character at start : 
3891 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.name like '\%%' */ 
    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 like '\%%'
Arithmetic functions are available in HQL:
final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity e where ((e.id *2) + 4)/ 4 = 0");  
//no modulus operator available, % wont work
List<Entity> entities = q.list();
System.out.println("Ids satisfying the math exprn are : ");
for (Entity entity : entities) {
    System.out.println(entity.getName() + " id : " + entity.getId());
}
The SQL query generated for the above is :
3110 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    (
        (
            e.id *2
        ) + 4
    )/ 4 = 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+4
        )/4=2
Ids satisfying the math exprn are : 
entity2 id : 2
Hibernate also supports logical operators:
final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity e where" +
    " (e.id > 2 and e.name not like 't%')" +
    " or e.master.id is null"); 
List<Entity> entities = q.list();
System.out.println("Ids satisfying the logical exprn are : ");
for (Entity entity : entities) {
    System.out.println(entity);
}
The SQL query is :
3484 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    (
        e.id > 2 
        and e.name not like 't%'
    ) 
    or e.master.id 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_.ID>2 
        and (
            entity0_.NAME not like 't%'
        ) 
        or entity0_.MASTER_ID is null
Ids satisfying the logical exprn are : 
[Entity] : ( id 3 , data : entity3 , master.Id : 1 , date : null )]

No comments:

Post a Comment