SQL inside Criteria

We earlier how a custom criterion could be created to handle special scenarios. However this may seem overkill if the condition can be easily expressed using native SQL. For Criteria allows us to add custom SQL expressions which get added in the where clause.
I took the same scenario as the custom criteria - checking the length of a string column:

public static void testViaSql() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.sqlRestriction(
            "length({alias}.name)< ?", 5, Hibernate.INTEGER)); 
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}

The result 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
        length(this_.name)< ?

As can be seen the restriction was added to the generated SQL query - with one tiny change: The {alias} parameter was replaced with the alias used for the table Entity.
The {alias} here represents the root class - the class for which the criteria is created. Also the "name" used in the SQL string represents the column name and not the class property. (This is SQL remember.)
The Restrictions.sqlRestriction method also has two more parameters - an object that will be substituted for the question mark. The third is the Hibernate built-in type. In this case as our parameter is an integer, we pass the INTEGER type.
What if we do not have any parameters ?

public static void testViaSql_Simpler() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.sqlRestriction("{alias}.master_id = 2"));
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}

As can be seen there is an overloaded version that allows for just the where condition to be specified - without any parameters.
This facility can be used to create much more complex queries too.

public static void testViaSql_Complex() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions
            .sqlRestriction("{alias}.master_id = "
                    + "(select id from entity_master where data like 'master No 1')"));
    List<Entity> entities = criteria.list();
    System.out.println(entities);
}

The SQL 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_.master_id = (
            select
                id 
            from
                entity_master 
            where
                data like 'master No 1'
        )

In this case we used it to fire nested queries. The subquery feature is also available directly in the Criteria API and we shall see that in the next post.