Result Set Mapping: The Basics

Quite often JPQL is not powerful enough to perform the queries we need in real world projects. In general, this is not an issue because JPA is designed as a leaky abstraction and we can use the full potential of SQL by using native queries or calling stored procedures.
The only downside is, that these queries return a List of Object[] instead of the mapped entities and value objects we are used to work with. Each Object[] contains one record returned by the database. We then need to iterate through the array, cast each Object to its specific type and map them to our domain model. This creates lots of repetitive code and type casts as you can see in the following example.
It would be more comfortable, if we could tell the EntityManager to map the result of the query into entities or value objects as it is the case for JPQL statements. The good news is, JPA provides this functionality. It is called SQL result set mapping and we will have a detailed look at it during this series:

• Result Set Mapping: The Basics
• Result Set Mapping: Complex Mappings
• Result Set Mapping: Constructor Result Mappings
• Result Set Mapping: Hibernate Specific Features

Don’t want to read? You can watch it here!

The example

We only need a simple Author entity with an id, a version, a first name and a last name for this post.

I used Wildfly 8.2 with Hibernate 4.3.7 to test the examples in this series. But as these are standard JPA features, you should be able to use them with every JPA 2.1 implementations, like EclipseLink. You can find the source code on my github account.

How to use the default mapping

The easiest way to map a query result to an entity is to provide the entity class as a parameter to the createNativeQuery(String sqlString, Class resultClass) method of the EntityManager and use the default mapping. The following snippet shows how this is done with a very simple query. In a real project, you would use this with a stored procedure or a very complex SQL query.
The query needs to return all properties of the entity and the JPA implementation (e.g. Hibernate) will try to map the returned columns to the entity properties based on their name and type. If that is successful, the EntityManager will return a list of fully initialized Author entities that are managed by the current persistence context. So the result is the same as if we had used a JPQL query, but we are not limited to the small feature set of JPQL.

How to define a custom mapping

While this automatic mapping is useful and easy to define, it is often not sufficient. If we perform a more complex query or call a stored procedure, the names of the returned columns might not match the entity definition. In these cases we need to define a custom result mapping. This needs to define the mapping for all entity properties, even if the default mapping cannot be applied to only one property.

Let’s have a look at our example and change the query we used before and rename the id column to authorId:
The default mapping to the Author entity will not work with this query result because the names of the selected columns and the entity properties do not match. We need to define a custom mapping for it. This can be done with annotations or in a mapping file (e.g. orm.xml). The following code snippet shows how to define the result mapping with the @SqlResultSetMapping annotation. The mapping consists of a name and an @EntityResult definition. The name of the mapping, AuthorMapping in this example, will later be used to tell the EntityManager which mapping to use. The @EntityResult defines the entity class to which the result shall be mapped and an array of @FieldResult which defines the mapping between the column name and the entity property. Each @FieldResult gets the name of the property and the column name as a parameter.
As Java EE 7 is based on Java 7, there is no support for repeatable annotations. Therefore you need to place your @SqlResultSetMapping annotations within a @SqlResultMappings annotation, if you want to define more than one mapping at an entity.

If you don’t like to add huge blocks of annotations to your entities, you can define the mapping in an XML mapping file. The default mapping file is called orm.xml and will be used automatically, if it is added to the META-INF directory of the jar file.
As you can see below, the mapping is very similar to the annotation based mapping that we discussed before. I named it AuthorMappingXml to avoid name clashes with the annotation based mapping. In a real project, you don’t need to worry about this, because you would normally use only one of the two described mappings.
OK, so now we have defined our own mapping between the query result and the Author entity. We can now provide the name of the mapping instead of the entity class as a parameter to the createNativeQuery(String sqlString, String resultSetMapping) method. In the code snippet below, I used the annotation defined mapping.

Conclusion

In this first post of the series, we had a look at two basic ways to map the query result to an entity:

1. If the names and the types of the query result match to the entity properties, we only need to provide the entity class to the createNativeQuery(String sqlString, Class resultClass) method of the EntityManager to use the default mapping.
2. If the default mapping cannot be applied to the query result, we can use XML or the @SqlResultSetMapping annotation to define a custom mapping between the columns of the query result and the properties of an entity. The name of the mapping can then be provided to the createNativeQuery(String sqlString, String resultSetMapping) method.