Featured Image with Sidebar

Result Set Mapping: Constructor Result Mappings

By Thorben Janssen

Query, Sql

This is the third part of my series about SQL result set mappings:

In the first post of this series, we had a look at some mapping definition between the query result and one entity. The mapping definitions got more complex in the second part, as we mapped the query result to multiple entities and handled additional columns.

In this post, we will have a look at the Constructor Result Mappings introduced in JPA 2.1. This feature allows us to call the constructor of a value object with the result of the query, similar to the JPQL constructor expressions. This is often used, if we want to provide a specific view of our domain model to the client.

The example

Before we start, lets have a look at the entity model that we will use for the examples. If you read the second part of this series, you are already familiar with the Author and Book entities. Both entities are quite simple. The Author entity has an id, a version, a first name and a last name. The Book entity has an id, a version, a title and a reference to the Author. To avoid unnecessary complexity, each Book was written by only one Author.

As we want to map our query results to a value object, we need an additional class called BookValue with an id, a version, a title and the name of the author.

How to map to a value object

Selecting entities and returning a tree of objects to the caller is not always the best approach. The caller often needs only a subset of the provided information and a specific value object would be much more efficient. For these situations, JPQL supports constructor expressions that can be specified in the select part of the JPQL query and define the constructor call for each selected record.

The BookValue in our example could be used in a client to show some information about a Book and the name of the Author. As long as we only need the name of the Author, there is no need to select and transfer the whole Author entity. It is more efficient to use an object of BookValue and select the name of the Author in the query.

SELECT b.id, b.title, b.version, a.firstName || a.lastName as authorName FROM Book b JOIN Author a ON b.author_id = a.id

In the next step, we need to define a mapping that uses the query result to call the constructor of the BookValue. This is done similar to the mappings we created before with the @SqlResultSetMapping annotation. The mapping shown in the following code snippet consists of a name and a @ConstructorResult annotation.

@SqlResultSetMapping(
        name = "BookValueMapping",
        classes = @ConstructorResult(
                targetClass = BookValue.class,
                columns = {
                    @ColumnResult(name = "id", type = Long.class),
                    @ColumnResult(name = "title"),
                    @ColumnResult(name = "version", type = Long.class),
                    @ColumnResult(name = "authorName")}))

The name of the mapping, BookValueMapping in this example, will later be used to tell the EntityManager which mapping to use. The @ConstructorResult annotation defines the constructor call for a given target class. This is the BookValue in our example. The array of @ColumnResult annotations defines the columns of the query result that will be used as constructor parameters with their type and order. The type attribute is optional and you only need to provide it, if the type of the column is different to the type of the constructor parameter. In this case, the default types of the id and version columns are BigInteger and need to be converted to Long.

Similar to the mapping of multiple entities, the classes attribute of the @SqlResultSetMapping accepts an array of @ConstructorResult annotations. If the mapping maps to multiple value objects or entities, each column can be used multiple times.

And like all the mapping definitions before, also the constructor result mapping can be defined in a mapping XML file. The easiest way to do this is to use the default mapping file called orm.xml that will be automatically used, if it is added to the META-INF directory of the jar file.

<sql-result-set-mapping name="BookValueMappingXml">
    <constructor-result target-class="org.thoughts.on.java.jpa.value.BookValue">
        <column name="id" class="java.lang.Long"/>
        <column name="title"/>
        <column name="version" class="java.lang.Long"/>
        <column name="authorName"/>
    </constructor-result>
</sql-result-set-mapping>

The usage of the constructor mapping is identical to the other SQL result set mappings. We need to provide it to the createNativeQuery(String sqlString, String resultSetMapping) method of the EntityManager and we get a List.

List<BookValue> results = this.em.createNativeQuery("SELECT b.id, b.title, b.version, a.firstName || a.lastName as authorName FROM Book b JOIN Author a ON b.author_id = a.id", "BookValueMapping").getResultList();

Conclusion

After we started with simple result mappings in the first post and created more complex ones in the second post, we now had a look at constructor result mappings that were introduced with JPA 2.1.
These provide a functionality similar to the JPQL constructor expressions and map the result of a query to constructor calls. The mapping can be defined via XML or a @SqlResultSetMapping annotation and one or more @ConstructorResult annotations.

Here is again a list of the previous posts of the series in case you missed one of them:

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

  1. Good writeup, I am normal visitor of ones blog, maintain up the excellent operate, and It’s going to be a regular visitor for a lengthy time.

  2. Hi Thorben,

    First of all, whole tutorials are great about ResultSetMapping, thank you so much…

    when I use your way, I am getting this kind of error and I didn’t figure it out. I have two entity classes (Projects.java and ProjectsFlow.java) like yours (Author.java and Book.java) and also I have a class for result mapping ProjectsTables.java like your BookValue.java…This is the error

    https://gist.github.com/cortix/82036b27ce250578a8a1

    and also these are the other classes….

    ProjectsTables.java
    https://gist.github.com/cortix/19076476f2dcb4baee65

    Projects.java
    https://gist.github.com/cortix/1e3e26fc4ffbd6d9f31d

    ProjectsFlow.java
    https://gist.github.com/cortix/ee2602d445edefc90c73

    OrganisationController.java
    https://gist.github.com/cortix/1f00542e19a409a2b3de

    homepage.jsp
    https://gist.github.com/cortix/24b26a7571b7319e275e

    In the OrganisationController class (line 52), I’ve created a query (createNativeQuery) and in the homepage.jsp I’ve called the results (line 174)….

    I don’t understand the problem ? Please can you help me ?

    1. Hi Hasan,

      have you tried to use Integer.class instead of int.class to handle empty columns in the query result?

      Regards,
      Thorben

      1. Hi,

        I don’t understand meaning of “to handle empty columns”…you mean, just use Integer.class instead of int.class….If you want to say this, yes I tried before but not worked.

    1. Thanks Daniel!
      You can find the post about the Hibernate specific features here: //thorben-janssen.com/result-set-mapping-hibernate-specific-mappings/

      Regards,
      Thorben

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