Result Set Mapping: The Basics

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

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 working 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.

List<Object[]> results = this.em.createNativeQuery("SELECT a.id, a.firstName, a.lastName, a.version FROM Author a").getResultList();

results.stream().forEach((record) -> {
        Long id = ((BigInteger) record[0]).longValue();
        String firstName = (String) record[1];
        String lastName = (String) record[2];
        Integer version = (Integer) record[3];
});

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

The example

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

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.

List<Author> results = this.em.createNativeQuery("SELECT a.id, a.firstName, a.lastName, a.version FROM Author a", Author.class).getResultList();

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:

SELECT a.id as authorId, a.firstName, a.lastName, a.version FROM Author a

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.

@SqlResultSetMapping(
        name = "AuthorMapping",
        entities = @EntityResult(
                entityClass = Author.class,
                fields = {
                    @FieldResult(name = "id", column = "authorId"),
                    @FieldResult(name = "firstName", column = "firstName"),
                    @FieldResult(name = "lastName", column = "lastName"),
                    @FieldResult(name = "version", column = "version")}))

Since Hibernate 5 and JPA 2.2, the @SqlResultMapping annotation is repeatable. You, therefore, no longer 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.

<sql-result-set-mapping name="AuthorMappingXml">
    <entity-result entity-class="org.thoughts.on.java.jpa.model.Author">
        <field-result name="id" column="authorId"/>
        <field-result name="firstName" column="firstName"/>
        <field-result name="lastName" column="lastName"/>
        <field-result name="version" column="version"/>
    </entity-result>
</sql-result-set-mapping>

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.

List<Author> results = this.em.createNativeQuery("SELECT a.id as authorId, a.firstName, a.lastName, a.version FROM Author a", "AuthorMapping").getResultList();

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.

The mappings described in this post were quite simple. In the following posts of this series, we will have a look at more complex mappings that can handle more than one entity and additional columns or that can map to value objects instead of entities:

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