Is it possible in Hibernate to replace multiple calls with different parameters in a loop with one c-CodePudding

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?

CodePudding user response：

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