Search This Blog

Saturday, 27 October 2012

HQL and the new keyword

It often happens that for a particular use case we need columns from several different tables. Consider the case where we would like the Entity name and its Master Name to be displayed.
public static void testFetch() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("select e.name, m.data from Entity e, Master m " +
            "where e.master = m"); 
    List<Object[]>  recs=  q.list();
    for (Object[] line : recs) {
        System.out.println("Entity name: " + line[0] + " ,Master Name : " + line[1]);
    }
}
As can be seen when we use projection, Hibernate returns each record as an object array. We are then free to perform processing on the record and manipulate it accordingly. But working with an object array is not the most intuitive of coding styles. What would be really cool is if HQL could return a list of POJOS for our use. For the above case lets say I have a POJO class:
public class EntityParent {

    private String entityName;
    private String masterName;

    public EntityParent(String entityName, String masterName) {
        super();
        this.entityName = entityName;
        this.masterName = masterName;
    }
//setter getters
}
The query could be rewritten as below:
public static void testFetch() {
    final Session session = sessionFactory.openSession();
    Query q = session
        .createQuery("select new com.vo.EntityParent(e.name, m.data) "
            + "from Entity e, Master m where e.master = m");
    List<EntityParent> recs = q.list();
    for (EntityParent pojo : recs) {
    System.out.println("Entity name: " + pojo.getEntityName()
            + " ,Master Name : " + pojo.getEntityName());
    }
}
As can be seen:
  • Instead of listing the fields in the select clause we have replaced it with the new keyword.
  • For every row returned in the result set, Hibernate will execute a constructor call with the data in the same sequence as specified in the HQL query. Hence the constructor is very important.
  • At the end a list of EntityParent is now available for processing.
No more any need to work with object arrays. It is very important to use the fully qualified class name in the Query. Otherwise Hibernate has no idea of this class and the query parser throws the below exception.
Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: Unable to
 locate class [EntityParent] [select new EntityParent(e.name, m.data) from com.m
odel.Entity e, com.model.Master m where e.master = m]
If we do not want to use the fully qualified name, then we need to import the class into the HQL namespace. For that I added the line in the hbm file for the Entity model.
<hibernate-mapping package="com.model">
    <import class="com.vo.EntityParent" />
(It can be added in any of the mapping files). Now the query can be written as:
Query q = session.createQuery("select new EntityParent(e.name, m.data) "
    + "from Entity e, Master m where e.master = m");

4 comments:

  1. Nice article, I am a big time fan of your site, keep up the nice work, and I will be a frequent visitor for a very long time.

    PIC Bonus Singapore

    ReplyDelete
  2. How to get all data with select new?
    ex: Query q = session.createQuery("select new EntityParent() "
    + "from Entity e, Master m where e.master = m");

    ReplyDelete