Can a JPA Query return results as a Java Map?

Question

We are currently building a Map manually based on the two fields that are returned by a named JPA query because JPA 2.1 only provides a getResultList() method:

@NamedQuery{name="myQuery",query="select c.name, c.number from Client c"}  HashMap<Long,String> myMap = new HashMap<Long,String>();  for(Client c: em.createNamedQuery("myQuery").getResultList() ){      myMap.put(c.getNumber, c.getName); } 

But, I feel like a custom mapper or similar would be more performant since this list could easily be 30,000+ results.

Any ideas to build a Map without iterating manually.

(I am using OpenJPA, not hibernate)

Answer 1

Returning a Map result using JPA Query getResultStream

Since the JPA 2.2 version, you can use the getResultStream Query method 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()     ) ); 

Returning a Map result using JPA Query getResultList and Java stream

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.

Avoid returning large result sets

The OP said:

But, I feel like a custom mapper or similar would be more performant since this list could easily be 30,000+ results.

This is a terrible idea. You never need to select 30k records. How would that fit in the UI? Or, why would you operate on such a large batch of records?

You should use query pagination as this will help you reduce the transaction response time and provide better concurrency.

Answer 2

There is no standard way to get JPA to return a map.

see related question: JPA 2.0 native query results as map

Iterating manually should be fine. The time to iterate a list/map in memory is going to be small relative to the time to execute/return the query results. I wouldn't try to futz with the JPA internals or customization unless there was conclusive evidence that manual iteration was not workable.

Also, if you have other places where you turn query result Lists into Maps, you probably want to refactor that into a utility method with a parameter to indicate the map key property.