How to return a Map result from a JPA or Hibernate query

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

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.

Domain Model

Assuming we are using the following Post entity which has a createdOn attribute of the LocalDate Java type:

Post entity JPQ Query Map result

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 return 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 Hibernate Types 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.

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.

SUBSCRIBE TO NEWSLETTER

2 Comments on “How to return a Map result from a JPA or Hibernate query

  1. Is this MapResultTransformer able to generate a map with a List of values? Let’s say you have a query which returns columns userId and roleId and you want to get a Map<String, List> userRoleIds with userId as the key and all roleId’s as a list of values.

    • Aggregating multiple values requires a manual ResultTransformer. Check out the Fetching chapter in my High-Performance Java Persistence book for more details.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.