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: