• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Thoughts on Java

  • Tutorials ▼
    • Hibernate & JPA Tutorials
    • Hibernate Tips
    • Tutorials on YouTube
  • Books & Courses ▼
    • Hibernate Tips Book
    • Online Training
    • Open Workshops
    • On-Site Training
  • Consulting ▼
    • Consulting Call
    • Project Coaching
  • About ▼
    • About Thoughts on Java
    • Support Thoughts on Java
    • Resources
  • Member Library
You are here: Home / Hibernate / Hibernate Performance / Result Set Mapping: The Basics
Do you live in Europe?
Then I hope to see you at one of my in-person workshops in December 2019. You will learn to use Hibernate advanced features and to improve the performance of your persistence layer.

Result Set Mapping: The Basics

By Thorben Janssen 20 Comments

  • tweet 
  • share 
  • share 
  • share 
  • share 
  • email 

Result Set Mappings - 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!

Follow me on YouTube to not miss any new videos.

 

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

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.

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
Make sure to subscribe to my mailing list so you don’t miss the following posts and to grab your free “What’s new in JPA 2.1” cheat sheet.
  • tweet 
  • share 
  • share 
  • share 
  • share 
  • email 

Related posts:

  1. Result Set Mapping: Constructor Result Mappings
  2. Result Set Mapping: Complex Mappings
  3. Result Set Mapping: Hibernate Specific Mappings
  4. Native Queries – How to call native SQL queries with JPA & Hibernate
Become a Thoughts on Java Supporter to claim your member perks and to help me write more articles like this.

Filed Under: Hibernate Performance, JPA Tagged With: Query, Sql

Improve Your Hibernate Skills At An In-Person Workshop

Implement Your Persistence Layer with Ease

Learn More About Hibernate

Need Some Help with Your Project?

Reader Interactions

Comments

  1. javacurious says

    July 22, 2015 at 1:48 am

    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.

    Reply
    • Thorben Janssen says

      July 22, 2015 at 5:54 am

      I moved the social bar to the bottom of the screen. Does that work better for you?

      Regards,
      Thorben

      Reply
  2. John Hogan says

    August 28, 2015 at 12:33 pm

    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.

    Reply
  3. Jonathan Perez says

    December 11, 2016 at 4:44 pm

    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!

    Reply
    • Thorben Janssen says

      January 6, 2017 at 6:51 am

      You’re welcome 🙂

      Reply
  4. Anshu Gupta says

    March 7, 2017 at 3:49 am

    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.

    Reply
    • Thorben Janssen says

      December 29, 2017 at 4:43 pm

      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?

      Reply
  5. . says

    April 14, 2017 at 4:13 am

    I tried the exact way but i got Unknown SqlResultSetMapping exception. Can yo help me with this?

    Reply
    • Thorben Janssen says

      April 14, 2017 at 7:42 am

      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.

      Reply
  6. Mike Miller says

    June 8, 2017 at 2:45 am

    First time I heard of this feature – very cool and the article and video are very helpful.

    Reply
    • Thorben Janssen says

      June 8, 2017 at 10:00 am

      Thanks Mike.

      Reply
  7. doanjv says

    October 18, 2017 at 6:14 pm

    thank you so much, it’s very useful!

    Reply
  8. Vasconcelos says

    December 28, 2017 at 7:42 pm

    Thank you, it’s working!

    Reply
    • Thorben Janssen says

      December 29, 2017 at 4:38 pm

      Awesome 🙂

      Reply
  9. Ishan says

    December 11, 2018 at 3:27 am

    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

    Reply
    • Thorben Janssen says

      July 26, 2019 at 8:12 pm

      Hi Ishan,

      You can do that with a constructor result mapping.

      Regards,
      Thorben

      Reply
  10. Javed S says

    March 20, 2019 at 7:44 pm

    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?

    Reply
    • Thorben Janssen says

      July 15, 2019 at 8:59 am

      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.

      Reply
  11. Sumit Tyagi says

    September 9, 2019 at 1:09 pm

    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

    Reply
    • Thorben Janssen says

      September 16, 2019 at 7:59 am

      Sounds like a Tuple projection might be the best fit for your use case. I explained it in more details here: https://thoughts-on-java.org/dto-projections/

      Reply

Leave a Reply Cancel reply

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.

Primary Sidebar

Join over 10.000 developers
in the
Thoughts on Java Library

Ebooks Sidebar Get free access to ebooks, cheat sheets and training videos.
Join Now!
Don't like ads?
Become a Thoughts on Java Supporter.
Special Launch Price

Let’s Connect


Thorben Janssen
Independent consultant, trainer and author
  • Facebook
  • GitHub
  • LinkedIn
  • Twitter
  • xing
  • YouTube

Speaking at

2nd-3rd December 2019
Düsseldorf (Germany):
Advanced Hibernate Workshop (2-day Workshop - English)

4th-6th December 2019
Düsseldorf (Germany):
Hibernate Performance Tuning Workshop (3-day Workshop - English)

Looking for an on-site training?

Featured Post

14 YouTube Channels You Should Follow in 2019

Getting Started With Hibernate

Entities or DTOs – When should you use which projection?

Ultimate Guide – Association Mappings with JPA and Hibernate

Ultimate Guide to JPQL Queries with JPA and Hibernate

Recent Posts

  • Don’t expose your JPA entities in your REST API
  • Hibernate Tip: How to control cache invalidation for native queries
  • Hibernate Tip: How to customize the association mappings using a composite key
  • Hibernate Tip: How to integrate an external query builder
  • Hibernate Tip: Best Way To Work with Scalar Projections
  • 6 Hibernate Mappings You Should Avoid for High-Performance Applications
  • Hibernate Tip: Best Way To Work with Scalar Projections
  • Using the Optimal Query Approach and Projection for JPA and Hibernate
  • Implementing the Outbox Pattern with CDC using Debezium
  • Hibernate Tip: Difference between @JoinColumn and @PrimaryKeyJoinColumn
Don't like ads?
Become a Thoughts on Java Supporter.

Copyright © 2019 Thoughts on Java

  • Impressum
  • Disclaimer
  • Privacy Policy