How to Hibernate SQL query result set mapping in Java development?

JPQL is one of the most powerful languages for SQL to write the queries which will execute on all databases. In most complex cases, JPQL is not powerful enough to write the queries we need to depend on the sqlresultsetmapping native query or stored procedures as well.

The downside of these is, it will return List instead of returning List of entity classes, each object array will hold the single row of the resultset. We need to iterate this array, and cast each object to a specific type, and map them to a model through SQL server consulting.

Normal Example:

List<Object[]> results = em.createNativeQuery("SELECT a.id, a.firstName, a.lastName, a.version FROM Author a").getResultList(); forEach(Object[] record : results) { Long id = ((BigInteger) record[0]).longValue(); String firstName = (String) record[1]; String lastName = (String) record[2]; Integer version = (Integer) record[3]; };

You would have noticed that doing above required care for casting and extracting from object array. This will result in a lot of repeated code, and type cast to model variable type.

It will be more comfortable and easily maintainable if we can tell EntityManager to map the result of SQL query into entities.

JPA 2.1 version supports these by using SQL result mapping so we can use any JPA provider implements 2.1 specifications.

Whether you are making use of entity classes or ad-hoc outcome set mappings, Hibernate native query map to object provides a flexible and authoritative explanation for flawlessly mixing natural SQL queries to the Java apps while maintaining the advantages of the ORM structure.

Hibernate Default Mapping

This is the easiest way of mapping the result of the query to an entity class. We can use createNativeQuery(String sqlString, Class resultClass) method to return List object. Where resultClass indicates the entity to which result shouldbe mapped.

Example

List<Author> results = em.createNativeQuery ("SELECT a.id, a.firstName, a.lastName, a.version FROM Author a", Author.class).getResultList();

Here, we are querying for all the properties of the Author class, JPA implementation will try to each row values to entity fields based on the column name, and field type, it is the same as executing the JPQL query, which will result into the collection of entities. The limitation of this default mapping is that it requires all the property in result and no column/field alias in query. This tractability and controller over the mapping procedure creates jpa resultsetmapping an important tool for programmers functioning with interpersonal databases and composite data recovery situations.

Hibernate Custom Mapping

When the automatic mapping is not possible like, using some alias names or joining of column values, or if the query result needs to map to multiple entities, then these default mapping will not work. We need to write some custom mapping for this. In order to map multiple entities, the sqlresultsetmapping multiple entities explanation could be used at the object phase.

If more queries of these types are present, we need to write more mappings maintenance will not be an easy java job.

These mapping can be done in two ways, either we can write in an annotation style, or we can write orm.xml and place in the MET-INF folder so that JPA provider will parse this, and do the mappings.

Another way to write, JPA annotations and write the mapping using annotations.

Mappings also can be done in 2 ways either we can do the field mapping, or we can use the constructor to map values.

@SqlResultSetMappings is the annotation that contains all SQL resultset mapping for all native queries. This annotation have two main properties ‘name’ & ‘entities’ The ‘name’ property allows to specify the name of the mapping and ‘entities’ allows tospecify the java entity with @EntityResult annotation.

@EntityResult is the annotation that contains entity name to be mapped and it’s field/column settings. This annotation have two main properties ‘entityName’ & ‘fields’. The ‘entityName’ property allows to specify the entity name and ‘fields’ property allows to specify a set of @FieldResult for field/column definitions to be mapped.

@FieldResult is the annotation to specify the mapping between the resultset column name and entity property name.

@ColumnResult is the annotation to specify the mapping entity constructor argument type and resultset column name.

First, we will understand with default mapping in a custom mapping way.

So, we will write a SQL resultset mapping without using JPQL query, understand how to use field results.

List<Author> results = em.createNativeQuery("SELECT a.id as authorId, a.firstName, a.lastName, a.version FROM Author a", "AuthorMapping").getResultList();

Here, we used the AuthorMapping SQLresultsetmapping name for mapping query result to the author entity.

So, we need to write the @SqlResultSetMapping annotation with the AuthorMapping name.

@SqlResultSetMapping(name="AuthorMapping",entities = @EntityResult( entityClass= Author.class, fields = { @FieldResult(column="authorId",name="id"), @FieldResult(column="firstName",name="firstName"), @FieldResult(column="lastName",name="lastName"), @FieldResult(column="version",name="version") } ));

If we are using Java 7 or below versions, then no support for @Repeatable annotations, we need to specify all these mappings inside @SqlResultSetMappings annotation.

@SqlResultSetMappings({ @SqlResultSetMapping(name="AuthorMapping",entities = @EntityResult( entityClass= Author.class, fields = { @FieldResult(column="authorId",name="id"), @FieldResult(column="firstName",name="firstName"), @FieldResult(column="lastName",name="lastName"), @FieldResult(column="version",name="version") } )) });

If we are using Java 8 or greater version, then JDK supports @Repeatable annotation, then no need to specify @SqlResultSetMappings annotation, we can directly specify any number of @SqlResultSetMapping mappings.

Here, we can specify the AuthorMapping name, and target type of entity is Author class, and specifying the fieldresult including the column name entity property name.

We can use an orm.xml file also for specifying the mappings.

<sql-result-set-mapping name="AuthorMapping"> <entity-result entity-class="org.hibernate.sqlresultsetmapping.Author"> <field-result name="id" column="authorId" /> <field-result name="firstName" column="firstName" /> <field-result name="lastName" column="lastName" /> <field-result name="version" column="version" /> </entity-result> </sql-result-set-mapping>

Now, let's understand about constructor mapping.

Let's suppose below is the join query, we want to map each result row to Book, and Author class, if the same property name exists, we can use some alias name in either of the entities.

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

Here, we can observe that the resultset has been required field/columns from two different entities, i.e. Book and Author. For this we required a separate java class 'BookValue' to mapped the values from resultset.

We can use the below mapping for mapping Book class using Fieldresult mapping, and bookvalue using Constructorresult mapping.

@SqlResultSetMapping(name = "BookAuthorMapping", entities = { @EntityResult(entityClass = Book.class, fields = { @FieldResult(column = "id", name = "id"), @FieldResult(column = "title", name = "title"), @FieldResult(column = "author_id", name = "author"), @FieldResult(column = "version", name = "version") }) }, classes = { @ConstructorResult(targetClass = BookValue.class, columns = { @ColumnResult(name = "id", type = Long.class), @ColumnResult(name = "title"), @ColumnResult(name = "version", type = Long.class), @ColumnResult(name = "authorName") }) } ) And Constructor in BookValue class is: publicBookValue(Long id, String title, Long version, String authorName) { super(); this.id = id; this.title = title; this.version = version; this.authorName = authorName; }

Based on use cases, either we can use Constructor resultmapping or filed result mapping.

Conclusion

Here, Java Development Company explains how JPA specification provides a way to map custom queries to entities using fieldResult and constructorResult. Also, describe how you can specify these mapping either using an XML file or annotations provided by JPA specifications.