Can JPA return results as a map?

0 votes
asked Dec 6, 2010 by eddie

We are currently build a map manually based on the two fields a named query returns, because JPA only provides a getResultList().

@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)

5 Answers

0 votes
answered Dec 7, 2010 by zawhtut

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();
 }
0 votes
answered Dec 19, 2011 by wrschneider

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.

0 votes
answered Dec 28, 2013 by hendy-irawan

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;
    }
});
0 votes
answered Dec 29, 2015 by mika

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.

0 votes
answered Sep 15, 2017 by darshan-patel

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.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...