Search This Blog

Tuesday 16 October 2012

HQL functions

HQL has the ability to use functions in the WHERE clause.There are the JPA defined functions and also additional HQL functions available for use:
Upper and Lower functions
final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity e where e.name = lower('ENTITY2')");
Entity entity = (Entity) q.uniqueResult();
System.out.println("Entity is  " + entity);

q = session.createQuery("from Entity e where upper(e.name) = 'ENTITY2'");
entity = (Entity) q.uniqueResult();
System.out.println("Entity is  " + entity);

The resultant queries are:
3641 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.name = lower('ENTITY2') */ 
    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=lower('ENTITY2')
Entity is  [Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : null )]
...
3812 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    upper(e.name) = 'ENTITY2' */ 
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        upper(entity0_.NAME)='ENTITY2'
Entity is  [Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : null )]
Concat function:
final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity e where e.name = concat('entity','2')");
Entity entity = (Entity) q.uniqueResult();
System.out.println("Entity is  " + entity);
The sql query is :
3765 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.name = concat('entity','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_.NAME=concat('entity', '2')
Entity is  [Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : null )]

Substring function:

final Session session = sessionFactory.openSession();
//Three parameters: string, offset,length
//The offset starts at 1
Query q = session.createQuery("from Entity e where e.name = substring('Crapentity2',5,7)");
Entity entity = (Entity) q.uniqueResult();
System.out.println("Entity is  " + entity);
Logs:
3266 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.name = substring('Crapentity2',5,7) */ 
    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=substring('Crapentity2', 5, 7)
Entity is  [Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : null )]

Trim function:

final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity e where e.name = trim('  entity2  ')");
Entity entity = (Entity) q.uniqueResult();
System.out.println("Entity is  " + entity);
Logs:
6109 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.name = trim('  entity2  ') */ 
    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=trim('  entity2  ')
Entity is  [Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : null )]
Length function:
final Session session = sessionFactory.openSession();
Query q = session.createQuery("from Entity e where e.id = length('four')");
Entity entity = (Entity) q.uniqueResult();
System.out.println("Entity is  " + entity);
Logs:
6109 [main] DEBUG org.hibernate.SQL  - 
    /* 
from
    Entity e 
where
    e.id = length('four') */ 
    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=length('four')
Entity is  null
We shall see more functions in the next post.

No comments:

Post a Comment