CODEWITHSUNDEEP
jpa
user2017810
user2017810

Reputation: 235

How to return HashMap from JPA query?

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?

Upvotes: 8

Views: 33090

Answers (3)

Daniele Licitra
Daniele Licitra

Reputation: 1608

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> .

Upvotes: 0

Darshan Patel
Darshan Patel

Reputation: 2899

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.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

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

Upvotes: 15

Related Questions