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?

Answer 1

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;
}

Answer 2

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.