CODEWITHSUNDEEP
jpa
Channa
Channa

Reputation: 5233

How to get SELECT values and COUNT value with JPA GROUP BY?

If my entity is get as a Man, it have name, id properties, with JPA how I get retrieve result like this query,

entityManager.createQuery("SELECT m.name AS name, COUNT(m) AS total FROM Man AS m GROUP BY m.name ORDER BY m.name ASC");

Is there any way to use org.springframework.jdbc.core.RowMapper with JPA?

Upvotes: 24

Views: 61598

Answers (1)

Vivien Barousse
Vivien Barousse

Reputation: 20875

When you execute this query, instead of getting directly a list of objects like usual, you'll retrieve a list of Object[].

For each array you retrieve, the first element will be the name of the row, the second the count.

I don't think you can use a RowMapper with JPA. RowMapper comes from Spring, which is not the same framework as JPA. Maybe some JPA implementation allow this, but I don't think it is wise to do so.

Edit - Code Example:

List<Object[]> results = entityManager
        .createQuery("SELECT m.name AS name, COUNT(m) AS total FROM Man AS m GROUP BY m.name ORDER BY m.name ASC");
        .getResultList();
for (Object[] result : results) {
    String name = (String) result[0];
    int count = ((Number) result[1]).intValue();
}

Upvotes: 57

Related Questions