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)

Like the code shows,the number field (Long), one of two values returned. BUt I could live with any Type, so long as the key is the number and the value is the name. I added the declaration for more details. — Eddie, Dec 06 '10 at 22:09

Vlad Mihalcea · Answer 1 · 2021-01-09T10:34:02.517

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.

EclipseLink 2.7 refuses to return `Tuple`s, I always get `Vector` of `Object`s instead and no information about aliases. — Panu Haaramo, Apr 25 '20 at 11:37
I though this is JPA feature, not Hibernate specific. You also did not put it under "Hibernate specific" above. Have you ever tried this with EclipseLink? — Panu Haaramo, Apr 25 '20 at 11:45
This is a JPA feature, that's why the interface is called `javax.persistence.Tuple`. It should be supported by any JPA provider. If EclipseLink does not support it, you should open an issue for that. — Vlad Mihalcea, Apr 25 '20 at 11:48
JPA seems only to have added Tuple to criteria query in the specification. The JSR adds footnotes 26+27 explicitly stating JPQL queries do not support other types like tuple: "Applications that specify other result types (e.g., Tuple.class) will not be portable." No reason EclipseLink couldn't support it, but isn't required as part of the spec. — Chris, Apr 27 '20 at 15:35
In reality, JPA portability is a myth. Just like the SQL standard. I personally never met anyone who switched JPA providers. Even on the Hibernate forum, I don't recall more than 2 or 3 questions related to provider migration. So, in reality, portability is not really an issue. In this case, I think `createQuery` should allow `Tuple`, just like it allows DTO projections via the constructor expression. — Vlad Mihalcea, Apr 27 '20 at 15:49
I can definitely think of applications that need to select large datasets. Not all algorithms are UI-facing. For example, a lot of graph algorithms are exponential in growth and some implementations store tons of intermediate results that another part of the application needs to process. However, I agree that selecting them in RAM is not a good idea and some kind of stream processing is probably more suitable. — Stuck, May 13 '20 at 08:33
Or, you can just do the processing on the DB side and get back the aggregation result. The DB might already store the working set in RAM so the processing can be done very fast. Anyway, you will save the networking overhead if you do large data set processing in the DB, rather than in the application. — Vlad Mihalcea, May 13 '20 at 10:38

score 19 · Answer 2 · edited May 23 '17 at 10:30

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.

score 10 · Answer 3 · edited May 23 '17 at 12:34

You can retrieve a list of java.util.Map.Entry instead. Therefore the collection in your entity should be modeled as a Map:

@OneToMany
@MapKeyEnumerated(EnumType.STRING)
public Map<PhoneType, PhoneNumber> phones;

In the example PhoneType is a simple enum, PhoneNumber is an entity. In your query use the ENTRY keyword that was introduced in JPA 2.0 for map operations:

public List<Entry> getPersonPhones(){
    return em.createQuery("SELECT ENTRY(pn) FROM Person p JOIN p.phones pn",java.util.Map.Entry.class).getResultList();
}

You are now ready to retrieve the entries and start working with it:

List<java.util.Map.Entry> phoneEntries =   personDao.getPersonPhoneNumbers();
for (java.util.Map.Entry<PhoneType, PhoneNumber> entry: phoneEntries){
    //entry.key(), entry.value()
}

If you still need the entries in a map but don't want to iterate through your list of entries manually, have a look on this post Convert Set<Map.Entry<K, V>> to HashMap<K, V> which works with Java 8.

This returns `null` `null` for me. I'm using BigInteger as the key/value types. — Maurice, Mar 03 '22 at 22:00

score 7 · Answer 4 · answered Sep 25 '18 at 19:50

7

This works fine.
Repository code :

@Repository
public interface BookRepository extends CrudRepository<Book,Id> {

    @Query("SELECT b.name, b.author from Book b")
    List<Object[]> findBooks();
}

service.java

      List<Object[]> list = bookRepository.findBooks();
                for (Object[] ob : list){
                    String key = (String)ob[0];
                    String value = (String)ob[1];
}

link https://codereview.stackexchange.com/questions/1409/jpa-query-to-return-a-map

answered Sep 25 '18 at 19:50

Mr Nobody

388
4
11

3

this is exactly what OP want's to avoid – Kamil Bęben May 27 '19 at 09:11
3

@KamilBęben a customer mapper would also be doing the same stuff behind the scenes. – Mr Nobody May 27 '19 at 14:44
2

hey man this is not bad at all, if your query returns more than one column you are good. – JBarros35 Nov 17 '20 at 13:10

score 4 · Answer 5 · answered Apr 03 '19 at 14:59

Map<String,Object> map = null;
    try {
        EntityManager entityManager = getEntityManager();
        Query query = entityManager.createNativeQuery(sql);
            query.setHint(QueryHints.RESULT_TYPE, ResultType.Map);
        map = (Map<String,Object>) query.getSingleResult();
    }catch (Exception e){ }

 List<Map<String,Object>> list = null;
    try {
        EntityManager entityManager = getEntityManager();
        Query query = entityManager.createNativeQuery(sql);
            query.setHint(QueryHints.RESULT_TYPE, ResultType.Map);
            list = query.getResultList();
    }catch (Exception e){  }

score 4 · Answer 6 · answered Dec 23 '20 at 05:55

JPA v2.2

Though I am late here, but if someone reaches here for solution, here is my custom working solution for multiple selected columns with multiple rows:

Query query = this.entityManager.createNativeQuery("SELECT abc, xyz, pqr,...FROM...", Tuple.class);
.
.
.
List<Tuple> lst = query.getResultList();
List<Map<String, Object>> result = convertTuplesToMap(lst);

Implementation of convertTuplesToMap():

public static List<Map<String, Object>> convertTuplesToMap(List<Tuple> tuples) {
    List<Map<String, Object>> result = new ArrayList<>();
    for (Tuple single : tuples) {
        Map<String, Object> tempMap = new HashMap<>();
        for (TupleElement<?> key : single.getElements()) {
            tempMap.put(key.getAlias(), single.get(key));
        }
        result.add(tempMap);
    }
    return result;
}

Mohamed.Abdo · Answer 7 · 2019-12-30T18:24:51.490

in case java 8 there built in entry "CustomEntryClass"

since return is stream, then caller function (repoistory layer) must have @Transactional(readonly=true|false) annotation, otherwithe exception will be thrown

make sure you will use full qualified name of class CustomEntryClass...

@Query("select new CustomEntryClass(config.propertyName, config.propertyValue) " +
                    "from ClientConfigBO config where config.clientCode =:clientCode ")
            Stream<CustomEntryClass<String, String>> getByClientCodeMap(@Param("clientCode") String clientCode);

Hendy Irawan · Answer 8 · 2013-11-28T09:26:32.250

With custom result class and a bit of Guava, this is my approach which works quite well:

public static class SlugPair {
    String canonicalSlug;
    String slug;

    public SlugPair(String canonicalSlug, String slug) {
        super();
        this.canonicalSlug = canonicalSlug;
        this.slug = slug;
    }

}

...

final TypedQuery<SlugPair> query = em.createQuery(
    "SELECT NEW com.quikdo.core.impl.JpaPlaceRepository$SlugPair(e.canonicalSlug, e.slug) FROM "
      + entityClass.getName() + " e WHERE e.canonicalSlug IN :canonicalSlugs",
    SlugPair.class);

query.setParameter("canonicalSlugs", canonicalSlugs);

final Map<String, SlugPair> existingSlugs = 
    FluentIterable.from(query.getResultList()).uniqueIndex(
        new Function<SlugPair, String>() {
    @Override @Nullable
    public String apply(@Nullable SlugPair input) {
        return input.canonicalSlug;
    }
});

Darshan Patel · Answer 9 · 2017-09-13T07:05:56.287

Please refer, JPA 2.0 native query results as map

In your case in Postgres, it would be something like,

List<String> list = em.createNativeQuery("select cast(json_object_agg(c.number, c.name) as text) from schema.client c")
                   .getResultList();

//handle exception here, this is just sample
Map map = new ObjectMapper().readValue(list.get(0), Map.class);

Kindly note, I am just sharing my workaround with Postgres.

score 0 · Answer 10 · answered Oct 30 '19 at 16:17

using java 8 (+) you can get results as a list of array object (each column will from select will have same index on results array) by hibernate entity manger, and then from results list into stream, map results into entry (key, value), then collect them into map of same type.

 final String sql = "SELECT ID, MODE FROM MODES";
     List<Object[]> result = entityManager.createNativeQuery(sql).getResultList();
        return result.stream()
                .map(o -> new AbstractMap.SimpleEntry<>(Long.valueOf(o[0].toString()), String.valueOf(o[1])))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));

score 0 · Answer 11 · answered Sep 29 '22 at 12:11

The easiest and simplest way worked for me is:

String[] columns = {"id","name","salary","phone","address", "dob"};
    String query = "SELECT id,name,salary,phone,address,dob from users ";

    List<Object[]> queryResp = em.createNativeQuery(query).getResultList();

    List<Map<String,String>> dataList = new ArrayList<>();
    for(Object[] obj : queryResp) {
        Map<String,String> row = new HashMap<>(columns.length);
        for(int i=0; i<columns.length; i++) {
            if(obj[i]!=null)
                row.put(columns[i], obj[i].toString());
            else
                row.put(columns[i], "");
        }
        dataList.add(row);
    }

score -2 · Answer 12 · answered Dec 07 '10 at 06:39

-2

How about this ?

@NamedNativeQueries({
@NamedNativeQuery(
  name="myQuery",
  query="select c.name, c.number from Client c",
  resultClass=RegularClient.class
)
})

and

     public static List<RegularClient> runMyQuery() {
     return entityManager().createNamedQuery("myQuery").getResultList();
 }

answered Dec 07 '10 at 06:39

zawhtut

8,335
5
52
76

@zawhut - thanks, but this still returns a list.. The issue is that I need to use the results inside another method and need a fast lookup method.. I thought a map woul be the best way to do this. – Eddie Dec 20 '10 at 14:31
OP asked for a Map – user1445967 Oct 28 '19 at 18:39

Can a JPA Query return results as a Java Map?

12 Answers12

Returning a Map result using JPA Query getResultStream

Returning a Map result using JPA Query getResultList and Java stream

Returning a Map result using a Hibernate-specific ResultTransformer

Avoid returning large result sets

Linked