|

Hibernate Tips: How to map native query results to a POJO

Avatar photoByThorben Janssen

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!

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question you like me to answer, please leave a comment below.

Question:

My query is too complex for JPQL, and I have to use a native query. What is the easiest way to map the result of the query to a POJO?

Solution:

JPA supports @SqlResultSetMappings which you can use to map the query result to a POJO. The following code snippet shows an example of such a mapping.

@SqlResultSetMapping(name = "BookValueMapping", 
		classes = @ConstructorResult(
				targetClass = BookValue.class, 
				columns = {@ColumnResult(name = "title"),
					   @ColumnResult(name = "date")}
				)
)

The @ConstructorResult annotation defines a constructor call of the BookValue class. The @ColumnResult annotations define how the columns of the result shall be mapped to the parameters of the constructor. In this example, Hibernate will perform a constructor call with the value of the title column as the first and the value of the date column as the second parameter.

When you provide the name of the @SqlResultSetMapping as the second parameter to the createNativeQuery method, Hibernate will apply the mapping to the query result. You can use it to map the results of all queries that return at least the parameters defined by the @ColumnResult annotations.

BookValue b = (BookValue) em.createNativeQuery("SELECT b.publishingDate as date, b.title, b.id FROM book b WHERE b.id = 1", "BookValueMapping").getSingleResult();

Learn More:

@SqlResultSetMappings are a powerful feature which allow you to define complex mappings for native query results. You can read more about native queries and @SqlResultSetMappings in my “Native Queries with Hibernate” ebook which you can download from the free Thoughts on Java Library.

Hibernate Tips Book

Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!

4 Comments

  1. Avatar photo Michael Stewart says:

    Hey Thorben,

    Thanks for all the great stuff you share!!

    I have a question, Is there a way to map a list (many to one relationship) to the DTO as well?

    1. Avatar photo Thorben Janssen says:

      Hi Michael,

      Thanks 🙂

      No, you can’t map lists. You would need to create and populate them yourself.

      Regards,
      Thorben

  2. Thorben, another great post. I really appreciate this series. You show a lot of gems in JPA that normally stay hidden.
    One remark: In today’s post you mention Hibernate a few times, but this functionality is not a Hibernate extension on JPA, it is native to JEE7: http://docs.oracle.com/javaee/7/api/javax/persistence/ConstructorResult.html

    To avoid any confusion maybe it is better to substitute “Hibernate” with “JPA”.

    1. Avatar photo Thorben Janssen says:

      Thanks for your comment Ron.
      You’re right, it’s not a Hibernate-specific feature and it wasn’t my attention to present it in that way. If you have a look at my other posts here on the blog, you see that I, in general, prefer JPA features over proprietary solutions.
      I changed the first sentence of the solution to point out that it’s a JPA features.

Comments are closed.