Select in Criteria using SQL, Hibernate

Select in Criteria using SQL

In an earlier post we saw how Criteria provided us with the ability to add restrictions in the where clause using pure SQL. The next question would then be if Criteria allows SQL in select clause ?
I tested the below code:

public static void testSqlSelect() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    int id = 4;
    criteria.add(Restrictions.eq("id", id));
    criteria.setProjection(Projections
        .projectionList()
            .add(Projections.property("name"))
            .add(Projections.property("master"))
            .add(Projections
                .sqlProjection(
                    "(select count(c.id) from CHILD_ENTITY c INNER JOIN ENTITY e on c.ENTITY_ID = e.id "
                    + "where e.id = " + id + ") as childCount",
                    new String[] { "childCount" },
                    new Type[] { Hibernate.LONG }
                )
            )
        );
    List<Object[]> rows = criteria.list();
    for (Object[] row : rows) {
        System.out.println(" Entity is " + row[0] + " and " + row[1] + " no of kids : " + row[2]);
    }
    session.close();
}

In this the normal Criteria returns me the details of Entity and its Master. But I have added a separate SQL query that will return me the number of ChildEntities associated with the particular record. The output of the code is as below:

    select
        this_.NAME as y0_,
        this_.MASTER_ID as y1_,
        (select
            count(c.id) 
        from
            CHILD_ENTITY c 
        INNER JOIN
            ENTITY e 
                on c.ENTITY_ID = e.id 
        where
            e.id = 4) as childCount 
    from
        ENTITY this_ 
    where
        this_.ID like ?
Hibernate: 
    /* load com.model.Master */ select
        master0_.ID as ID2_0_,
        master0_.DATA as DATA2_0_ 
    from
        ENTITY_MASTER master0_ 
    where
        master0_.ID=?
 Entity is entity100 and [Master] : ( id 2 , data : master No 2 )] no of kids : 1

As can be seen the SQL query was embedded within the select clause. The query being SQL, it could refer to any table or column whether known or unknown to Hibernate. The other parameters are used to update Hibernate with details of the return type from the statement. As it is an array, the inner select is capable of returning multiple columns.
An important thing to keep in mind though is that the sub-query is not allowed to return more than one row. This will result in an SQL exception if my entity row has more than one Entity_Child associations:

    select
        this_.NAME as y0_,
        this_.MASTER_ID as y1_,
        (select
c.id 
        from
            CHILD_ENTITY c 
        INNER JOIN
            ENTITY e 
                on c.ENTITY_ID = e.id 
        where
            e.id = 2) as childCount 
    from
        ENTITY this_ 
    where
        this_.ID=?

Source: http://learningviacode.blogspot.com/2013/04/select-in-criteria-using-sql.html

Creating a new Criterion, Hibernate

Creating a new Criterion

We have seen Hibernate’s set of Restrictions and they cover a very large range of queries. However Hibernate also allows the creation of a custom Criterion that can be used in the criteria.
I created a simple Criterion class that is used to check the length of string properties.

@SuppressWarnings("serial")
public class LengthExpression implements Criterion {
    private final String propertyName;
    private final int value;

    public LengthExpression(final String propertyName, final int value) {
        this.propertyName = propertyName;
        this.value = value;
    }

    @Override
    public String toSqlString(final Criteria criteria,
            final CriteriaQuery criteriaQuery) throws HibernateException {
        criteriaQuery.getFactory().getDialect();
        final String[] columns = criteriaQuery.getColumnsUsingProjection(
                criteria, this.propertyName);
        final String queryFragment = "length(" + columns[0] + ") = ?";
        return queryFragment;
    }

    @Override
    public TypedValue[] getTypedValues(final Criteria criteria,
            final CriteriaQuery criteriaQuery) throws HibernateException {
        // An ordered pair of a value and its Hibernate type
        return new TypedValue[] { new TypedValue(Hibernate.INTEGER,
                Integer.valueOf(value), EntityMode.POJO) };
    }
}

In the above code:

  • The class implements the Criterion Interface which exposes two methods.
  • The  toSqlString() method is what generates the actual sql fragment. As we work with prepared statements, “?” ares used. In this case the sql length function is used. The alias used for property in the actual query is obtained using the getColumnsUsingProjection() method.
  • The getTypedValues() method tells Hibernate about the data Type to be used in the query. In this case the parameter will be set using the preparedStatement.setInt() method.

To test the code, I used it in a criteria example:

public static void testCustomCriterion() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(new LengthExpression("name", 7));        
    List<Entity> entities = criteria.list(); 
    System.out.println(entities);
}

The result of the execution is below:

3484 [main] DEBUG org.hibernate.SQL  - 
    /* criteria query */ 
    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) = ?
...
3484 [main] DEBUG org.hibernate.jdbc.AbstractBatcher  - preparing statement
3516 [main] DEBUG org.hibernate.type.IntegerType  - binding '7' to parameter: 1
...
3625 [main] DEBUG org.hibernate.connection.DriverManagerConnectionProvider  - re
turning connection to pool, pool size: 1
[[Entity] : ( id 2 , data : entity1 , master.Id : 1 , date : null )], [Entity] :
 ( id 3 , data : entity2 , master.Id : 1 , date : null )]]

As can be seen the query generated includes the length() function. Also the parameter(7) was bound to the prepared statement.

Native SQL inside Criteria, Hibernate

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.

Hibernate 4 Util SessionFactory example


import org.hibernate.HibernateException;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

public class HibernateUtil
{
    private static SessionFactory sessionFactory;
    private static ServiceRegistry serviceRegistry;

    static
    {
        try
        {
//          Configuration configuration = new Configuration();
            Configuration configuration = new Configuration().configure();

            serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties()).buildServiceRegistry();
            sessionFactory = configuration.buildSessionFactory(serviceRegistry);
        }
        catch (HibernateException he)
        {
            System.err.println("Error creating Session: " + he);
            throw new ExceptionInInitializerError(he);
        }
    }

    public static SessionFactory getSessionFactory()
    {
        return sessionFactory;
    }
}

Credits: http://stackoverflow.com/questions/8621906/is-buildsessionfactory-deprecated-in-hibernate-4