Search code examples
jpaspring-data-jpajpql

How to write JPQL query with `COUNT` and `GROUP BY`

How write JPQL query with COUNT and GROUP BY and get result as Map<Integer,Integer>?

public class CommentEntity {

 private int id;

 private int parentId;

 private EntityParentType parentType;

 private Long replyCounts;

 private String author;

 private String comment;

}

.

public enum EntityParentType {

 PERSON,
 EVENT,
 COMMENT;

}

I wrote MySQL query and this work fine:

SELECT parent_id, COUNT(*) FROM comment AS c WHERE c.parent_type = 2 AND c.parent_id IN (64,65) GROUP BY parent_id

enter image description here

but JPQL query is fail:

@Repository
@Transactional(readOnly = true)
public interface CommentRepository extends JpaRepository<CommentEntity, Integer> {

 @Query(value = "SELECT c.parentId, COUNT(c.id) FROM CommentEntity AS c WHERE c.parentType = ?1 AND c.parentId IN (?2) GROUP BY c.parentId")
 Map<Integer, Integer> findReplyCountByParentIds(EntityParentType entityParentType, List<Integer> ids);

}

. 

Method threw 'org.springframework.dao.IncorrectResultSizeDataAccessException' exception.
result returns more than one elements

below is fail too:

@Query(value = "SELECT c.parentId, COUNT (c.id) FROM CommentEntity AS c WHERE c.parentType = ?1 AND c.parentId IN (?2) GROUP BY c.parentId")
 List<Map<Integer, Integer>> findReplyCountByParentIds(EntityParentType entityParentType, List<Integer> ids);

.

Method threw 'org.springframework.dao.InvalidDataAccessApiUsageException' exception.
No aliases found in result tuple! Make sure your query defines aliases!

I tried add pacakge to CommentEntity and also is fail

Solution

  • One workaround is to use the constructor syntax as follows: 

    SELECT NEW org.apache.commons.lang3.tuple.ImmutablePair(c.parentId, COUNT(c.id)) FROM ...

    Of course, you could use any other class in place of ImmutablePair (e.g. a concrete implementation of Map.MapEntry). You then declare the result as a List<ImmutablePair> and collect the results into a map in your service method.