Is it possible in Hibernate to replace multiple calls with different parameters in a loop with one call?

Question

There is a @NamedQuery which counts number of Person with given name:

 @NamedQuery(name = "Person.countNames", query = "SELECT count(*) FROM Person p WHERE p.name= :name")

I'm using this query in a loop to count Person for a specific list with names:

List<String> names = Arrays.asList("John", "Stan", "Robert");
HashMap<String, Long> results = new HashMap<>();
for (String name : names){
   Long count = entityManager.createNamedQuery('Person.countNames').setParameter("name", name)
   results.put(name, count);
}

I would like to optimize this process to only call once to the database. Is it possible to create such @NamedQuery which will give immediate results in HashMap or in any other form?

You can use Lists with the EntityManager object as a parameter. I mean, `WHERE p.name = :name` can be replaced with `WHERE p.name IN :names`. Then, add a `GROUP BY` clause. — Nakarukatoshi Uzumaki, Sep 28 '21 at 11:41
That would be a really nice solution, but e.g. in my case, if there is zero Stan's in a database, it would return only 2 results but there should be 3 with one row with zero value. — hubigabi, Sep 28 '21 at 12:45

Nakarukatoshi Uzumaki · Accepted Answer · 2021-09-29T07:36:21.093

0

You can do something like this:

public Map<String, Integer> getCountForNames(String... names) {
    List<String> namesAsList = Arrays.asList(names);

    String hql = "SELECT p.name, count(*) FROM Person p WHERE p.name IN (:names) GROUP BY p.name";
    TypedQuery<Object[]> q = entityManager.createQuery(hql, Object[].class);
    q.setParameter("names", namesAsList);

    List<Object[]> result = q.getResultList();
    final Map<String, Integer> toReturn = new HashMap<>();

    result.forEach(res -> {
        String name = (String) res[0];
        Integer count = (Integer) res[1];
        toReturn.put(name, count);
    });
  return Collections.unmodifiableMap(toReturn);
}

More info can be found here: JPQL IN clause: Java-Arrays (or Lists, Sets...)?.

Also denote that, if you want to use a Named Query, you can change:

String hql = "SELECT p.name, count(*) FROM Person p WHERE p.name IN (:names) GROUP BY p.name";
TypedQuery<Object[]> q = entityManager.createQuery(hql, Object[].class);

with:

TypedQuery<Object[]> q = entityManager.createNamedQuery("Person.countNames", Object[].class);

edited Sep 29 '21 at 07:36

answered Sep 28 '21 at 11:51

Nakarukatoshi Uzumaki

325
4
14

That's a better solution than my current one, but I would like to avoid casting or creating a new class (with only name and count) for this entity. Is there a solution to retrieve only count numbers without names? – hubigabi Sep 28 '21 at 12:54
You can either retrieve an array of objects or a specific object. You can do `.createQuery(hql,Integer.class);` and return `List`, but you won't be able to know which number represents which name. You can also directly return a `List – Nakarukatoshi Uzumaki Sep 29 '21 at 07:29
Also, please, consider marking the answer as accepted if it was helpful and it resolved your question. Thanks in advance. – Nakarukatoshi Uzumaki Sep 29 '21 at 07:37

Is it possible in Hibernate to replace multiple calls with different parameters in a loop with one call?

1 Answers1