This is the second part of my SQL result set mappings series. We had a look at some basic result type mappings in the first post Result Set Mapping: The Basics. In this one, we will define more complex mappings that can map a query result to multiple entities and handle additional columns that cannot be mapped to a specific entity.
- Result Set Mapping: The Basics
- Result Set Mapping: Complex Mappings
- Result Set Mapping: Constructor Result Mappings
- Result Set Mapping: Hibernate Specific Features
Before we dive into the more complex mappings, lets have a look at the entity model that we will use for the examples. We used the Author entity with an id, a version, a first name and a last name already in the first post of this series. For the more complex mappings, we need the additional Book entity which has an id, a version, a title and a reference to the Author. To keep it simple, each book is only written by one 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 implementation, e.g. with the GlassFish 4.1 application server which uses EclipseLink.
You can find the source code on my github account.
How to map multiple entities
In real life applications we often select multiple entities with one query to avoid the additional queries that would be required to initialize lazy relations. If we do this with a native query or a stored procedure call, we get a List instead of entities. We then need to provide a custom mapping that tells the EntityManager to which entities the Object shall be mapped and how this is done.
In our example we could define a query that returns books and its author in one query.
As the Author and the Book table both have an id and a version column, we need to rename them in the SQL statement. I decided to rename the id and version column of the Author to authorId and authorVersion. The columns of the Book stay unchanged. OK, so how do we define a SQL result set mapping that transforms the returned List of Object to a List of fully initialized Book and Author entities? The mapping definition looks similar to the custom mapping that we defined in the post about basic result set mappings. As in the previously discussed mapping, the @SqlResultMapping defines the name of the mapping that we will use to reference it later on. The main difference here is, that we provide two @EntityResult annotations, one for the Book and one for the Author entity. The @EntityResult looks again similar to the previous mapping and defines the entity class and a list of @FieldResult mappings.
If you don’t like to add such a huge block of annotations to your entity, you can also define the mapping in an XML file. As described before, the default mapping file is called orm.xml and will be automatically used, if it is added to the META-INF directory of the jar file.
The mapping definition itself looks similar to the already described annotation based mapping definition.
Now we have a custom result set mapping definition, that defines the mapping between our query result and the Book and Author entity. If we provide this to the createNativeQuery(String sqlString, String resultSetMapping) method of the EntityManager, we get a List. OK, that might not look like what we wanted to achieve in the first place. We wanted to get rid of these Object. If we have a more detailed look at the Objects in the array, we see that these are no longer the different columns of the query but the Book and Author entities. And as the EntityManager knows that these two entities are related to each other, the relation on the Book entity is already initialized.
How to map additional columns
Another very handy feature is the mapping of additional columns in the query result. If we want to select all Authors and their number of Books, we can define the following query.
So how do we map this query result to an Author entity and an additional Long value? That is quite simple, we just need to combine a mapping for the Author entity with an additional @ColumnResult definition. The mapping of the Author entity has to define the mapping of all columns, even if we do not change anything as in the example below. The @ColumnResult defines the name of the column that shall be mapped and can optionally specify the Java type to which it shall be converted. I used it to convert the BigInteger, that the query returns by default, to a Long.
As before, this mapping can also be defined with a similar looking XML configuration.
If we use this mapping in the createNativeQuery(String sqlString, String resultSetMapping) of the EntityManager, we get a List that contains the initialized Author entity and the number of her/his Books as a Long.
This kind of mapping comes quite handy, if your query becomes complex and the result has no exact mapping to your entity model. Reasons for this can be additional attributes calculated by the database, as we did in the example above, or queries that select only some specific columns from related tables.
- can map a query result to multiple entities by annotating multiple @EntityResult annotations and
- can handle columns, that are not part of the entity, with the @ColumnResult annotation.