How to return a Map result from a JPA or Hibernate query
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, I’m going to show you how you can return a Java Map result when executing a JPA query. I decided to write this article after answering a similar question on StackOverflow.
How to return a #Java Map result when executing a JPA query.https://t.co/8zhtAr4jTN pic.twitter.com/G09xmtS9Xh
— Java (@java) January 23, 2020
Domain Model
Assuming we are using the following Post
entity which has a createdOn
attribute of the LocalDate
Java type:
And, we have persisted the following Post
entity into our database:
entityManager.persist( new Post() .setId(1L) .setTitle( "High-Performance Java Persistence eBook " + "has been released!" ) .setCreatedOn(LocalDate.of(2016, 8, 30)) ); entityManager.persist( new Post() .setId(2L) .setTitle( "High-Performance Java Persistence paperback " + "has been released!" ) .setCreatedOn(LocalDate.of(2016, 10, 12)) ); entityManager.persist( new Post() .setId(3L) .setTitle( "High-Performance Java Persistence Mach 1 video course " + "has been released!" ) .setCreatedOn(LocalDate.of(2018, 1, 30)) ); entityManager.persist( new Post() .setId(4L) .setTitle( "High-Performance Java Persistence Mach 2 video course " + "has been released!" ) .setCreatedOn(LocalDate.of(2018, 5, 8)) ); entityManager.persist( new Post() .setId(5L) .setTitle( "Hypersistence Optimizer " + "has been released!" ) .setCreatedOn(LocalDate.of(2019, 3, 19)) );
Counting posts published per year
Now, we want to build a report which displays the number of posts by their publishing year. For this, we can use the following JPQL query:
select YEAR(p.createdOn) as year, count(p) as postCount from Post p group by YEAR(p.createdOn)
Traditionally, the getResultList
JPA Query
method has been used whenever the result set contained multiple rows. However, we don’t want to return a List
when executing this query. Since we have a projection of two columns where the first one is unique, we are better off returning a Map
instead.
Returning a Map result using JPA Query getResultStream
As I explained in this article, you can use the JPA 2.2 getResultStream
to transform the List<Tuple>
result into a Map<Integer, Integer>
:
Map<Integer, Integer> postCountByYearMap = entityManager.createQuery(""" select YEAR(p.createdOn) as year, count(p) as postCount from Post p group by YEAR(p.createdOn) """, Tuple.class) .getResultStream() .collect( Collectors.toMap( tuple -> ((Number) tuple.get("year")).intValue(), tuple -> ((Number) tuple.get("postCount")).intValue() ) );
The Collectors.toMap
method returns a Collector
that returns a HashMap
with the key mapped by the first lambda function provided and the value mapped by the second lambda function.
Returning a Map result using JPA Query getResultList
If you’re using JPA 2.1 or older versions, but your application is running on Java 8 or a newer version, then you can use getResultList
and transform the List<Tuple>
to a Java 8 stream:
Map<Integer, Integer> postCountByYearMap = entityManager.createQuery(""" select YEAR(p.createdOn) as year, count(p) as postCount from Post p group by YEAR(p.createdOn) """, Tuple.class) .getResultList() .stream() .collect( Collectors.toMap( tuple -> ((Number) tuple.get("year")).intValue(), tuple -> ((Number) tuple.get("postCount")).intValue() ) );
Returning a Map result using a Hibernate-specific ResultTransformer
Another option is to use the MapResultTransformer
class provided by the Hypersistence Utils open-source project:
Map<Number, Number> postCountByYearMap = (Map<Number, Number>) entityManager.createQuery(""" select YEAR(p.createdOn) as year, count(p) as postCount from Post p group by YEAR(p.createdOn) """) .unwrap(org.hibernate.query.Query.class) .setResultTransformer( new MapResultTransformer<Number, Number>() ) .getSingleResult();
The MapResultTransformer
is suitable for projects still running on Java 6 or using older Hibernate versions.
I'm running an online workshop on the 11th of October about High-Performance SQL.If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Both JPA and Hibernate provide a lot of flexibility when it comes to transforming the result set of a given JPQ query, be it JPQL, Criteria API, or native SQL query.
If a given JPA GROUP BY query returns only two columns where one is unique, it’s very suitable to return the result as a Java Map. For this, you can use either the Java Stream functionality or the Hibernate-specific ResultTransformer
.
