How to group by result with JPA and JpaSpecificationExecutor?

Question

I'm using JpaSpecificationExecutor, JPA 2.0, Hibernate and MSSQL and want to build the following query with CriteriaBuilder:

SELECT CURR_DATE, MAX(POSITION) FROM TOP_COMPONENT_HISTORY GROUP BY CURR_DATE

My Question: Possible? And if, how?

Thanks for wrapping your mind around this!

This is my code..

Table (TOP_COMPONENT_HISTORY)

1   ARC_ID  varchar NO          
2   CURR_DATE   varchar NO          
3   REG_DATE    datetime2   YES         7
4   APPLY_DATE  datetime2   YES         7
5   POSITION    int YES 10  0   
6   REG_USER_ID varchar NO          
7   MOD_USER_ID varchar NO  

Service

public Page<TopComponentHistory> findByCurrDate(ArticleSearchForm searchForm){
        return topComponentHistoryRepository.findAll(TopComponentHistory.findAllGroupBy(),constructPageSpecification(searchForm.getPageNum());
    }

Domain

public class TopComponentHistory implements Serializable {
    public static Specification<TopComponentHistory> findAllGroupBy() {     
       How can i make query...
       return ..
    }
}

Repository

public interface TopComponentHistoryRepository extends JpaRepository<TopComponentHistory, String>, JpaSpecificationExecutor<TopComponentHistory> {


}

Answer 1

I use a generic class with some custom specifications, it is a bit dirty but it works. It works with column names in root entity, if you need group by other columns it is necessary modify the code, but this code can be a starting point.

public class GenericSpecifications<T> {


public Specification<T> groupBy(Specification<T> specification, List<String> columnNames) {
    return new Specification<T>() {

        @Override
        public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {

            List<Expression<?>> columnNamesExpression = columnNames.stream().map(x -> root.get(x))
                    .collect(Collectors.toList());

            query.groupBy(columnNamesExpression);
            return specification.toPredicate(root, query, criteriaBuilder);
        }
    };
}

Then for example in the class that you need group by funcion:

GenericSpecifications<YourEntity> genericSpecifications = new GenericSpecifications<YourEntity>();
    ..
    Specification<YourEntity> specification = Specification.where(null);
    ..
    specification = genericSpecifications.groupBy(specification, names);
    YourEntityRepository.findAll(specification);