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.