Introduction

In this article, I’m going to explain how to use the JPA SqlResultSetMapping, as well as the EntityResult, ConstructorResult, and ColumnResult options.

Domain Model

Let’s consider we have the following post and post_comment tables in our database:

We are going to create 50 post rows, each post having 5 post_comment child records.

LocalDateTime timestamp = LocalDateTime.of(
    2016, 10, 9, 12, 0, 0, 0
);

LongStream.rangeClosed(1, POST_COUNT)
.forEach(postId -> {
    Post post = new Post()
    .setId(postId)
    .setTitle(
        String.format(
            "High-Performance Java Persistence - Chapter %d",
            postId
        )
    )
    .setCreatedOn(
        Timestamp.valueOf(timestamp.plusDays(postId))
    );

    LongStream.rangeClosed(1, COMMENT_COUNT)
    .forEach(commentOffset -> {
        long commentId = ((postId - 1) * COMMENT_COUNT) + commentOffset;

        post.addComment(
            new PostComment()
            .setId(commentId)
            .setReview(
                String.format("Comment nr. %d - A must read!", commentId)
            )
            .setCreatedOn(
                Timestamp.valueOf(
                    timestamp
                    .plusDays(postId)
                    .plusMinutes(commentId)
                )
            )
        );

    });

    entityManager.persist(post);
});

Next, we are going to execute several native SQL queries and see how we can fetch DTOs, entities, or mix entities with scalar values.

JPA SqlResultSetMapping

The SqlResultSetMapping JPA annotation looks as follows:

@Repeatable(SqlResultSetMappings.class)
@Target({TYPE}) 
@Retention(RUNTIME)
public @interface SqlResultSetMapping { 

    String name(); 

    EntityResult[] entities() default {};

    ConstructorResult[] classes() default {};

    ColumnResult[] columns() default {};
}

The SqlResultSetMapping annotation is repeatable and is applied at the entity class level. Apart from taking a unique name, which is used by Hibernate to register the mapping, there are three mapping options:

• EntityResult
• ConstructorResult
• ColumnResult

Next, we are going to see how al these three mapping options work, as well as the use cases where you will need to use them.

JPA SqlResultSetMapping – EntityResult

The EntityResult option allows you to map the JDBC ResultSet columns to one or more JPA entities.

Let’s assume we want to fetch the first 5 Post entities along with all their associated PostComment entities that match a given title pattern.

As I explained in this article, we can use the DENSE_RANK SQL Window Function to know how to filter the post and post_comment joined records, as illustrated by the following SQL query:

SELECT *
FROM (
  SELECT
    *,
    DENSE_RANK() OVER (
    ORDER BY
      "p.created_on",
      "p.id"
    ) rank
  FROM (
    SELECT
      p.id AS "p.id", p.created_on AS "p.created_on",
      p.title AS "p.title", pc.post_id AS "pc.post_id",
      pc.id as "pc.id", pc.created_on AS "pc.created_on",
      pc.review AS "pc.review"
    FROM post p
    LEFT JOIN post_comment pc ON p.id = pc.post_id
    WHERE p.title LIKE :titlePattern
    ORDER BY p.created_on
  ) p_pc
) p_pc_r
WHERE p_pc_r.rank <= :rank

However, we don’t want to return a list of scalar column values. We want to return JPA entities from this query, so we need to configure the entities attribute of the @SqlResultSetMapping annotation, like this:

@NamedNativeQuery(
    name = "PostWithCommentByRank",
    query = """
        SELECT *
        FROM (
          SELECT
            *,
            DENSE_RANK() OVER (
            ORDER BY
              "p.created_on",
              "p.id"
            ) rank
          FROM (
            SELECT
              p.id AS "p.id", p.created_on AS "p.created_on",
              p.title AS "p.title", pc.post_id AS "pc.post_id",
              pc.id as "pc.id", pc.created_on AS "pc.created_on",
              pc.review AS "pc.review"
            FROM post p
            LEFT JOIN post_comment pc ON p.id = pc.post_id
            WHERE p.title LIKE :titlePattern
            ORDER BY p.created_on
          ) p_pc
        ) p_pc_r
        WHERE p_pc_r.rank <= :rank
        """,
    resultSetMapping = "PostWithCommentByRankMapping"
)
@SqlResultSetMapping(
    name = "PostWithCommentByRankMapping",
    entities = {
        @EntityResult(
            entityClass = Post.class,
            fields = {
                @FieldResult(name = "id", column = "p.id"),
                @FieldResult(name = "createdOn", column = "p.created_on"),
                @FieldResult(name = "title", column = "p.title"),
            }
        ),
        @EntityResult(
            entityClass = PostComment.class,
            fields = {
                @FieldResult(name = "id", column = "pc.id"),
                @FieldResult(name = "createdOn", column = "pc.created_on"),
                @FieldResult(name = "review", column = "pc.review"),
                @FieldResult(name = "post", column = "pc.post_id"),
            }
        )
    }
)

With the SqlResultSetMapping in place, we can fetch the Post and PostComment entities like this:

List<Object[]> postAndCommentList = entityManager
    .createNamedQuery("PostWithCommentByRank")
    .setParameter("titlePattern", "High-Performance Java Persistence %")
    .setParameter("rank", POST_RESULT_COUNT)
    .getResultList();

And, we can validate that the entities are properly fetched:

assertEquals(
    POST_RESULT_COUNT * COMMENT_COUNT, 
    postAndCommentList.size()
);

for (int i = 0; i < COMMENT_COUNT; i++) {
    Post post = (Post) postAndCommentList.get(i)[0];
    PostComment comment = (PostComment) postAndCommentList.get(i)[1];

    assertTrue(entityManager.contains(post));
    assertTrue(entityManager.contains(comment));

    assertEquals(
        "High-Performance Java Persistence - Chapter 1",
        post.getTitle()
    );

    assertEquals(
        String.format(
            "Comment nr. %d - A must read!",
            i + 1
        ),
        comment.getReview()
    );
}

The @EntityResult is also useful when fetching JPA entities via SQL stored procedures. Check out this article for more details.

JPA SqlResultSetMapping – ConstructorResult

Let’s assume we want to execute an aggregation query that counts the number of post_coment records for each post and returns the post title for reporting purposes. We can use the following SQL query to achieve this goal:

SELECT
  p.id AS "p.id",
  p.title AS "p.title",
  COUNT(pc.*) AS "comment_count"
FROM post_comment pc
LEFT JOIN post p ON p.id = pc.post_id
GROUP BY p.id, p.title
ORDER BY p.id

We also want to encapsulate the post title and the comment count in the following DTO:

public class PostTitleWithCommentCount {

    private final String postTitle;
    
    private final int commentCount;

    public PostTitleWithCommentCount(
            String postTitle,
            int commentCount) {
        this.postTitle = postTitle;
        this.commentCount = commentCount;
    }

    public String getPostTitle() {
        return postTitle;
    }

    public int getCommentCount() {
        return commentCount;
    }
}

To map the result set of the above SQL query to the PostTitleWithCommentCount DTO, we can use the classes attribute of the @SqlResultSetMapping annotation, like this:

@NamedNativeQuery(
    name = "PostTitleWithCommentCount",
    query = """
        SELECT
          p.id AS "p.id",
          p.title AS "p.title",
          COUNT(pc.*) AS "comment_count"
        FROM post_comment pc
        LEFT JOIN post p ON p.id = pc.post_id
        GROUP BY p.id, p.title
        ORDER BY p.id
        """,
    resultSetMapping = "PostTitleWithCommentCountMapping"
)
@SqlResultSetMapping(
    name = "PostTitleWithCommentCountMapping",
    classes = {
        @ConstructorResult(
            columns = {
                @ColumnResult(name = "p.title"),
                @ColumnResult(name = "comment_count", type = int.class)
            },
            targetClass = PostTitleWithCommentCount.class
        )
    }
)

The ConstructorResult annotation allows us to instruct Hibernate what DTO class to use as well as which constructor to be called when instantiating the DTO objects.

Note that we used the type attribute of the @ColumnResult annotation to specify that the comment_count should be cast to a Java int. This is needed since some JDBC drivers use either Long or BigInteger for the SQL aggregation function results.

This is how you can call the PostTitleWithCommentCount named native query using JPA:

List<PostTitleWithCommentCount> postTitleAndCommentCountList = entityManager
    .createNamedQuery("PostTitleWithCommentCount")
    .setMaxResults(POST_RESULT_COUNT)
    .getResultList();

And, we can see that the returned PostTitleWithCommentCount DTOs have been fetched properly:

assertEquals(POST_RESULT_COUNT, postTitleAndCommentCountList.size());

for (int i = 0; i < POST_RESULT_COUNT; i++) {
    PostTitleWithCommentCount postTitleWithCommentCount = 
        postTitleAndCommentCountList.get(i);

    assertEquals(
        String.format(
            "High-Performance Java Persistence - Chapter %d",
            i + 1
        ),
        postTitleWithCommentCount.getPostTitle()
    );

    assertEquals(COMMENT_COUNT, postTitleWithCommentCount.getCommentCount());
}

For more details about the best way to fetch DTO projections with JPA and Hibernate, check out this article.

JPA SqlResultSetMapping – ColumnResult

The previous example showed how we could map the SQL aggregation result set to a DTO. But, what if we want to return the JPA entity for which we are counting the comments?

To achieve this goal we can use the entities attribute to define the Post entity we are fetching, and the classes attribute of the @SqlResultSetMapping annotation to map the scalar value, which in our case is the number of associated post_comment records:

@NamedNativeQuery(
    name = "PostWithCommentCount",
    query = """
        SELECT
          p.id AS "p.id",
          p.title AS "p.title",
          p.created_on AS "p.created_on",
          COUNT(pc.*) AS "comment_count"
        FROM post_comment pc
        LEFT JOIN post p ON p.id = pc.post_id
        GROUP BY p.id, p.title
        ORDER BY p.id
        """,
    resultSetMapping = "PostWithCommentCountMapping"
)
@SqlResultSetMapping(
    name = "PostWithCommentCountMapping",
    entities = @EntityResult(
        entityClass = Post.class,
        fields = {
            @FieldResult(name = "id", column = "p.id"),
            @FieldResult(name = "createdOn", column = "p.created_on"),
            @FieldResult(name = "title", column = "p.title"),
        }
    ),
    columns = @ColumnResult(
        name = "comment_count",
        type = int.class
    )
)

When executing the PostWithCommentCount named native query:

List<Object[]> postWithCommentCountList = entityManager
    .createNamedQuery("PostWithCommentCount")
    .setMaxResults(POST_RESULT_COUNT)
    .getResultList();

we will get both the Post entity and the commentCount scalar column value:

assertEquals(POST_RESULT_COUNT, postWithCommentCountList.size());

for (int i = 0; i < POST_RESULT_COUNT; i++) {
    Post post = (Post) postWithCommentCountList.get(i)[0];
    int commentCount = (int) postWithCommentCountList.get(i)[1];

    assertTrue(entityManager.contains(post));

    assertEquals(i + 1, post.getId().intValue());
    assertEquals(
        String.format(
            "High-Performance Java Persistence - Chapter %d",
            i + 1
        ),
        post.getTitle()
    );

    assertEquals(COMMENT_COUNT, commentCount);
}

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

JPA offers multiple ways of mapping the result set of a given query. You can use the JPQL constructor expression or fetch the results as a Tuple.

However, the SqlResultSetMapping annotation is the most flexible approach as you can use it to fetch DTOs, entities, or scalar column values.

While having this as an API, similar to the Hibernate ResultTransformer would be a much better way of building the SQL result set mapping compared to the declarative approach given by the @SqlResultSetMapping annotation, until the JPA specification provides a programmatical approach, you can use the @SqlResultSetMapping annotation for this task.

Insert details about how the information is going to be processed

DOWNLOAD NOW