Result Set Mapping: The Basics

By Thorben Janssen

Query, Sql

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:

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.

class diagram Author

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:


Tags

Query, Sql


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Repl​​​​​y

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  1. It is awesome !!! . I am looking to load a specific set of columns from the database and map it to entity. I am unable to use SQLResultSetMapping as my column list change every time.

    Can you suggest something

  2. This was a very helpful article. Thanks! Can the mapping be done for a Resultset that selects columns from 2 different tables that are not related?

    1. In theory, yes! The mapping is independent of the query. You just have to make sure that the query returns all columns referenced in the mapping (or in the entity mapping).

      But if you map your query result to an entity object, you should only select the table and columns mapped by the entity. Otherwise, an entity update will either fail or store unexpected data.

  3. Thank you for the very useful post. I just have one question that what if I dont want to annotate my POJO class with @Entity and just want to map the resultset to a class. Can I do that when using SQLResultSetmapping ?

    Thanks,
    Ishan

    1. In most cases, you get this exception when you put the @SqlResultSetMapping annotation on a class which Hibernate doesn’t process or due to a typo in the mapping name.
      Please check that and if it doesn’t solve the issue, upload the code somewhere so that I can have a look at it.

  4. Thanks for the article. I tried this with custom mapping but it does not work.
    So I created a POJO class with my getters/setters and added a SQLResultMapping annotation inside SQLResultMappings. It gives me mapping exception for my POJO. Do I need to put @Id or @Entity in my Pojo class? I am using JPA 2.1. The native SQL works fine however, not able to map that to POJO class.

    1. Hi Anshu,

      If you want to map your query result to a POJO as described here, you’re defining a constructor call. Did you implement a constructor that expects the parameters as defined in the mapping?

  5. Thank you so much!! I spent over 5hrs trying to fix this and the first solution createNativeQuery(query, Entity.class) fixed it perfectly for me!

  6. I needed to do this to pull some meta data on a table yesterday, needed coloumn names, descriptions … and will be implementing today. I can’t wait to try this. It looks beautifully simple, great technique, very clean. Thank you.

  7. Skipping this site, due to the toolbar at the left. plz give an option to hide it. I am not able to read the article because of that.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}