The best way to use the JPA SqlResultSetMapping
Are you struggling with performance issues in your Spring, Jakarta EE, or Java EE application?
What if there were a tool that could automatically detect what caused performance issues in your JPA and Hibernate data access layer?
Wouldn’t it be awesome to have such a tool to watch your application and prevent performance issues during development, long before they affect production systems?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, Micronaut, or Play Framework.
So, rather than fixing performance issues in your production system on a Saturday night, you are better off using Hypersistence Optimizer to help you prevent those issues so that you can spend your time on the things that you love!
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:
EntityResultConstructorResultColumnResult
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
@EntityResultis 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);
}
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.
