Introduction

One of the main goals of Hibernate ORM is to recover data from database. In the normal behaviour, It is conceived to map the entities of our database to objects in our domain. However, in some cases this is not the desired behaviour. For example, in case we want to recover a set of concrete columns from some tables using Named Queries (NQ).

The Hibernate Named Queries are a powerful mechanism to have our queries specified in Strings or even in XML files (in my opinion, the best solution). Such queries can be written in HQL (Hibernate Query Language) or SQL. In the case we use. As a first option probably we’ll use HQL, which maps directly the results to our domain objects. This feature is as powerful that will cover our requirements in the most of our cases.

However, sometimes we’ll need some specific utilities of our DBMS, like special functions, search hints, etc. In such case we can use SQL queries. With SQL queries we’ll have the flexibility (and sometimes the problem) to write totally customized queries, however, we lose the possibility to map the results directly to our domain objects (not really true, as we’ll see later).

Hibernate HQL-NQ and non-domain objects

Sometimes, for performance reasons or simply because we don’t need all the data, we’ll need to recover only some specific values of some tables. However, if such information is located in different tables we’ll have to join that data, and recover it following one of the following strategies:

1.- Recover it as an array, where each element of the array maps to one of the columns recovered. Bad solution, as we lose the semantics of each column, and we have to do extra job in our code.

2.- Encapsulate the columns in a DTO. “The solution”, as we won’t lose the semantics, we won’t have to do extra job and the recovered objects can go directly to our business layers, without having any data-adaptation algorithm.

To do the solution, we have to follow the next steps. Let’s suppose we have a DTO called ExampleDTO with 2 attributes id and desc. Our model has a table called Entity with 6 columns id, desc, name, cre_date, mod_date, exp_date.

In our DAO we’ll have a method like the following:

//Search method
public List findByNamedQuery(String q, Map<String,Object> params) throws Exception {
 //gimme a session
 Session session =HibernateUtil.currentSession(user);
 List result = null;
 try {
    Query qu = session.getNamedQuery(q);
    if (qu.getNamedParameters().length != params.size()){
       throw new HibernateException("Parameters needed");
    }

    for (String param: params.keySet()){
       qu.setParameter(param, params.get(param));
    }
 result = qu.list();
 } catch (Exception e){
    throw e;
 }
 return result;
}

And in the NQ’s file (supposing we have the NQ in one separated file), a query like the following:

<query name="findEntitiesById">
   SELECT new org.my.example.exampleDTO(ent.id, ent.description)
   FROM Entity ent
   WHERE ent.id = :id
   ORDER BY ent.id ASC
</query>

Hibernate SQL-NQ and non-domain objects

The solution exposed in the former section is practical, simple and clean, but it has a clear problem, it only can be used with HQL. In the case that SQL is needed, we need to apply a simple trick in our DAO code, like the following:

public List findByNamedQuery(String q, Map<String,Object> params, Class clazz ) throws Exception {
   //gimme a session
   Session session =HibernateUtil.currentSession();
   List result = null;
   try {
      Query qu = session.getNamedQuery(q);
      if (qu.getNamedParameters().length != params.size()){
         throw new HibernateException("Parameters needed");
      }
      for (String param: params.keySet()){
         qu.setParameter(param, params.get(param));
      }
      resultat = qu.setResultTransformer(Transformers.aliasToBean(clazz)).list();
   } catch (Exception e){
      throw e;
   }
   return result;
}

The query in our SQL queries file will look like this:

<sql-query name="findEntitiesByIdUsingSQL">
<return-scalar column="id" type="string"/>
<return-scalar column="desc" type="string"/>
   SELECT ent.id AS id, ent.desc AS desc
   FROM DDBB_ENTITY ent
   WHERE ent.id = :id
   ORDER BY ent.id ASC
</sql-query>

The main difference is in our DAO method. In this case we have to pass to it the class in which we want to encapsulate the data. Doing this Hibernate will automatically assign to each new register the values obtained using a Result Transformer. It is very important to make sure that the columns in our query, and the attributes of our returning DTO have the same name, otherwise our code won’t work.

Conclusion

In conclusion, with Hibernate and “a pair of tricks” present in its API, it’s possible to interact and use both SQL and HQL queries, and the most important point, without treating them with very different code.