In Relation To

Back to home

Hibernate 3.2: Transformers for HQL and SQL

Posted by    |       Hibernate ORM

People using the Criteria API have either transparently or knowingly used a ResultTransformer . A ResultTransformer is a nice and simple interface that allows you to transform any Criteria result element. E.g. you can make any Criteria result be returned as a java.util.Map or as a non-entity Bean.

Criteria Transformers

Imagine you have a StudentDTO class: 

public class StudentDTO {
  private String studentName;
  private String courseDescription;
  
  public StudentDTO() { }
      
  ...
}

Then you can make the Criteria return non-entity classes instead of scalars or entities by applying a ResultTransformer: 

List resultWithAliasedBean = s.createCriteria(Enrolment.class)
  .createAlias("student", "st").createAlias("course", "co")
  .setProjection( Projections.projectionList()
                   .add( Projections.property("st.name"), "studentName" )
                   .add( Projections.property("co.description"), "courseDescription" )
          )
          .setResultTransformer( Transformers.aliasToBean(StudentDTO.class) )
          .list();

 StudentDTO dto = (StudentDTO)resultWithAliasedBean.get(0);

This is how ResultTransformer have been available since we introduced projection to the Criteria API in Hibernate 3.

It is just one example of the built in transformers and users can provide their own transformers if they so please.

Jealous programming

Since I am more a HQL/SQL guy I have been jealous on Criteria for having this feature and I have seen many requests for adding it to all our query facilities.

Today I put an end to this jealousy and introduced ResultTransformer for HQL and SQL in Hibernate 3.2.

HQL Transformers

In HQL we already had a kind of result transformers via the (select new http://www.hibernate.org/hib_docs/v3/reference/en/html/queryhql.html#queryhql-select) syntax, but for returning non-entity beans it only provided value injection of these beans via its constructor. Thus if you used the same DTO in many different scenarios you could end up having many constructors on this DTO purely for allowing the select new functionality to work.

Now you can get the value injected via property methods or fields instead, removing the need for explicit constructors. 

List resultWithAliasedBean = s.createQuery(
  "select e.student.name as studentName," +
  "       e.course.description as courseDescription" +
  "from   Enrolment as e")
  .setResultTransformer( Transformers.aliasToBean(StudentDTO.class))
  .list();

StudentDTO dto = (StudentDTO) resultWithAliasedBean.get(0);

SQL Transformers

With native sql returning non-entity beans or Map's is often more useful instead of basic Object[]. With result transformers that is now possible. 

List resultWithAliasedBean = s.createSQLQuery(
  "SELECT st.name as studentName, co.description as courseDescription " +
  "FROM Enrolment e " +
  "INNER JOIN Student st on e.studentId=st.studentId " +
  "INNER JOIN Course co on e.courseCode=co.courseCode")
  .addScalar("studentName")
  .addScalar("courseDescription")
  .setResultTransformer( Transformers.aliasToBean(StudentDTO.class))
  .list();

StudentDTO dto =(StudentDTO) resultWithAliasedBean.get(0);

Tip: the addScalar() calls were required on HSQLDB to make it match a property name since it returns column names in all uppercase (e.g. STUDENTNAME). This could also be solved with a custom transformer that search the property names instead of using exact match - maybe we should provide a fuzzyAliasToBean() method ;)

Map vs. Object[]

Since you can also use a transformer that return a Map from alias to value/entity (e.g. Transformers.ALIASTOMAP), you are no longer required to mess with index based Object arrays when working with a result. 

List iter = s.createQuery(
  "select e.student.name as studentName," +
  "       e.course.description as courseDescription" +
  "from   Enrolment as e")
  .setResultTransformer( Transformers.ALIAS_TO_MAP )
  .iterate();

String name = (Map)(iter.next()).get("studentName");

Again, this works equally well for Criteria, HQL and native SQL.

Reaching Nirvana of native sql

We still miss a few things, but with the addition of ResultTranformer support for SQL and the other additions lately to the native sql functionality in Hibernate we are close to reach the Nirvana of native sql support.

Combined with StatelessSession you actually now got a very flexible and full powered sql executor which transparently can map to and from objects with native sql without any ORM overhead.

...and when you get tired of managing the sql, objectstate, lifecycles, caching etc. of your objects manually and want to benefit from the power of an ORM then you got it all readily available to you ;)

Back to top