This is the fourth and last part of my series about SQL result set mappings:

In the first post, we had a look at some basic mapping definitions to map the query result to an entity. The mapping definitions got more complex in the second part, as we mapped the query result to multiple entities and handled additional columns. And in the third part, we had a look at one of the new features introduced in JPA 2.1, the constructor result mappings.

This time we have a look at some Hibernate specific features, that are not part of the JPA specification. Hibernate provides its own API to map query results. While this creates a vendor lock and makes migration to another framework difficult, it also provides some interesting features. As always, you need to decide which trade-off you want to make.

The example

Before we start, lets have a look at the entity model that we will use for the examples. If you read the second part of this series, you are already familiar with the Author and Book entities. Both entities are quite simple. The Author entity has an id, a version, a first name and a last name. The Book entity has an id, a version, a title and a reference to the Author. To avoid unnecessary complexity, each Book was written by only one Author.

How to use Hibernate specific features

In the previous posts of this series, we used JPA standard features and therefore the EntityManager to perform native queries. This time we will use some Hibernate specific features and so we need to use a Hibernate Session instance instead. In an Java EE environment, this can be accessed via the EntityManager.getDelegate() method as shown in the following code snippet:

@PersistenceContext private EntityManager em; ... public void queryWithAuthorBookCountHibernateMapping() { Session session = (Session)this.em.getDelegate(); ... }

Aliases make the mapping easier

Hibernate provides its own API that supports a similar set of features as the JPA standard. But using the Hibernate API is sometimes more convenient as the result mappings we created in the previous posts. One example for this is the following code snippet in which all Books and Authors are selected from the database and mapped to the corresponding entities. In a real world project, you would probably not use a native query for such a simple select. But it is good enough to explain the result mapping. I leave the very complex queries of your real projects for you and your DBA ;-).

List<Object[]> results = ((Session)this.em.getDelegate()).createSQLQuery("SELECT {b.*}, {a.*} FROM Book b JOIN Author a ON b.author_id = a.id").addEntity("b", Book.class).addEntity("a", Author.class).list(); results.stream().forEach((record) -> { Book book = (Book) record[0]; Author author = (Author) record[1]; System.out.println("Author: ID [" + author.getId() + "] firstName [" + author.getFirstName() + "] lastName [" + author.getLastName() + "]"); System.out.println("Book: ID [" + book.getId() + "] title[" + book.getTitle() + "]"); });

The syntax of the query might look strange at the beginning, but it provides a very easy way to select all attributes of an entity. Instead of selecting all attributes in the select part of the query and map them one by one to the entity attributes, as we did in part 2 of this series, we now use {a.*} and {b.*} to select them. The mapping between the aliases a and b to the entity classes is done by calling addEntity(String tableAlias, Class entityType).

The following snippet shows a similar result mapping. This time, we select an Author entity and the number of her/his books as a scalar value. We used the same query in the second part of this series, when we used @SqlResultSetMapping annotation of the JPA standard to map the result.

List<Object[]> results = ((Session)this.em.getDelegate()).createSQLQuery("SELECT {a.*}, count(b.id) as bookCount FROM Book b JOIN Author a ON b.author_id = a.id GROUP BY a.id, a.firstName, a.lastName, a.version").addEntity(Author.class).addScalar("bookCount", StandardBasicTypes.LONG).list(); results.stream().forEach((record) -> { Author author = (Author) record[0]; Long bookCount = (Long) record[1]; System.out.println("Author: ID [" + author.getId() + "] firstName [" + author.getFirstName() + "] lastName [" + author.getLastName() + "] number of books [" + bookCount + "]"); });

Up to now, we created two result mappings that could also be done with JPA. From my point of view, the Hibernate API is a little bit easier to use, if the result mapping is specific for one query. But if there are no other reasons to create a dependency to Hibernate instead of JPA, I would still use JPA. Additionally, the result mapping annotations (or XML configuration) of the JPA standard can be used to map the results of multiple queries.

ResultTransformer for more flexibility

Another and more powerful way to transform the query result is ResultTransformer. It provides the option to define the result mapping in Java code. OK, you might say that this is what we tried to avoid in the beginning and you are right about that. But as you can see in the JavaDoc, Hibernate provides quite a list of different implementations of this interface. So in most cases, there is no need to implement the mapping ourself. Otherwise the ResultTransformer provides only minimal benefits compared to a programmatic mapping using the Streams API. One of the provided ResultTransformer is the AliasToBeanResultTransformer, which maps the query result to a Java Bean. But instead of using a constructor call, as we did with the constructor result mappings in the third part, the transformer uses the setter methods or fields to populate the object. This can be beneficial, if the class has lots of fields and we would need to create a constructor with a parameter for each of them or if we would need multiple constructors because multiple query results need to be mapped to the same class. The following code snippet shows an example of the AliasToBeanResultTransformer:

List<BookValue> results = ((Session)this.em.getDelegate()).createSQLQuery("SELECT b.id, b.title, b.version, a.firstName || ' ' || a.lastName as authorName FROM Book b JOIN Author a ON b.author_id = a.id") .addScalar("id", StandardBasicTypes.LONG).addScalar("title").addScalar("version", StandardBasicTypes.LONG).addScalar("authorName") .setResultTransformer(new AliasToBeanResultTransformer(BookValue.class)).list(); results.stream().forEach((book) -> { System.out.println("Book: ID [" + book.getId() + "] title [" + book.getTitle() + "] authorName [" + book.getAuthorName() + "]"); });

The AliasToBeanResultTransformer uses the default constructor of the BookValue to instantiate an object and searches the getter methods based on the alias and type of the return column. Therefore we need to use the addScalar() method to rename the columns and change the types of the id and version column.

Conclusion

This was the last post of the result mapping series. After we had a look at the different options to define result mappings in the JPA standard in the previous posts, we now had a look at some Hibernate specific features. Hibernate provides an API and supports aliases to define the mapping between the query result and the Java entities or value objects. Besides being easier to use, this also provides the advantage, that all information is in the same place. There is no need to search for the mapping definition in some annotations or XML files. On the other hand, it requires more work to define the mapping and it is not as easy to reuse as the JPA standard approach.

The ResultTransformer on the other hand can provide some real benefits compared to the standard mapping. These can be used to do more complex mappings and Hibernate already provides a list of ResultTransformer implementations. If none of the existing transformation implementation provides the required functionality, there is also the option to implement your own one. But in this case I would prefer to use the Streams API to map the query results inside my business code.

Here is again a list of the previous posts of the series in case you missed one of them: