How to return HashMap from JPA query?

Question

I want to return a HashMap from JPA query like the below but I don't know how to fill the HashMap from this query. Actually I want to fill charts from HashMap in the frontend

public HashMap<String,String> getCount(Date start,Date end) {
           HashMap<String, String> map=new HashMap<String, String>();
            Query q = 
                  em.createQuery(
                    "select count(i.uuid),i.username from Information i where i.entereddt between :start and :end group by i.username");
                q.setParameter("start",new Timestamp(start.getTime()));
                q.setParameter("end",new Timestamp(end.getTime()));

                 System.out.println(" query"+ q.getResultList().get(0).toString());

             return map;
        }

Any suggestions?

How do you intend to populate the map? Using `(count, username)` as pairs? — Tim Biegeleisen, Aug 05 '15 at 05:05
Possible duplicate of [With Hibernate, how can I query a table and return a hashmap with key value pair id>name?](https://stackoverflow.com/questions/4736743/with-hibernate-how-can-i-query-a-table-and-return-a-hashmap-with-key-value-pair) — Amogh, Sep 01 '17 at 06:16

score 15 · Accepted Answer · answered Aug 05 '15 at 05:15

It appears that you were trying to execute a query which return types not mapped to any Java entities you have (or if they be present you never mentioned them). In this case, you want to use createNativeQuery(), which will return a List of type Object[].

Try using this version of the method:

public HashMap<String,String> getCount(Date start,Date end) {
    HashMap<String, String> map=new HashMap<String, String>();
    Query q = em.createNativeQuery(
                    "select count(i.uuid),i.username from Information i" +
                    "where i.entereddt between :start and :end group by i.username");
    q.setParameter("start",new Timestamp(start.getTime()));
    q.setParameter("end",new Timestamp(end.getTime()));

    List<Object[]> list = query.getResultList();

    for (Object[] result : list) {
        map.put(result[0].toString(), result[1].toString());
    }

    return map;
}

Thanks ! I replaced the parameters with ? (?start and ?end ) and it worked — user2017810, Aug 05 '15 at 05:37

score 2 · Answer 2 · answered Sep 13 '17 at 07:05

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(count(i.uuid),i.username) as text) from schema.information i where i.entereddt between :start and :end group by i.username")
                   .setParameter("start",new Timestamp(start.getTime()))
                   .setParameter("end",new Timestamp(end.getTime()))
                   .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 3 · answered Jun 13 '19 at 09:58

I know that it's an old question, but you can create an object to store info

public class UserCount {
   private String username;
   private Long count;

   public UserCount(String user, Long count){
      this.username = user;
      this.count = count;
   }

}

It's important to create the constructor and to pass the parameters in the correct way.

The JPQL became

select my.package.UserCount(i.username, count(i.uuid) ) from schema.information i where i.entereddt between :start and :end group by i.username

The query returns a List<UserCount> .

How to return HashMap from JPA query?

3 Answers3

Linked