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

So far, the best solution I have found for this problem, was to extend my JpaRepository and return a custom object using EntityManager and Criteria API. It is an easy to implement solution and quite organized. Also, it allows to reuse the custom repository and just use the needed specification.

For example, let's say that we have the following specification.

public class TopComponentHistorySpecifications {

    public static Specification<TopComponentHistory> equalDate(LocaldDateTime date) {
        return (root, cq, cb) -> {
            if (date != null) return cb.equal(root.get("currDate"), date);
            else return cb.and();
        };
    }

    public static Specification<TopComponentHistory> groupByCurrDate() {
        return (root, cq, cb) -> {
            cb.groupBy(root.get("currDate"));
            return cb.and();
        };
    }

}

Now, we would have our custom repository and model like the ones below.

public class MaxPositionByCurrDate {

   private LocalDateTime currDate;
   private Long max;

   // Rest

}

public interface CustomTopComponentHistoryRepository {
   List<MaxPositionByCurrDate> findAllMaxPositionByCurrDate(Specification<TopComponentHistory> specifications);
}

@Repository
public class CustomTopComponentHistoryRepositoryImpl implements CustomTopComponentHistoryRepository {

   @PersistenceContext
   private EntityManager entityManager;

   @Override
   List<MaxPositionByCurrDate> findAllMaxPositionByCurrDate(Specification<TopComponentHistory> specifications) {

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<MaxPositionByCurrDate> query = cb.createQuery(MaxPositionByCurrDate.class);
        Root<TopComponentHistory> root = query.from(TopComponentHistory.class);
        query.where(specifications.toPredicate(root, query, cb), TopComponentHistorySpecifications.groupByCurrDate().toPredicate(root, query, cb));
        query.select(cb.construct(MaxPositionByCurrDate.class, root.get("currDate"), cb.max(root.get("maxPosition"))));
        return entityManager.createQuery(query).getResultList();

   }
}

With this, we can easily re-use our specifications and group them however we want. If you were to add more specifications, you could easily add them ass parameters. For example.

List<MaxPositionByCurrDate> result = this.repository.findAllMaxPositionByCurrDate(Specifications.where(
    // Add all the specifications you need, as long as the parameters needed are found on the group by clause
));

Important: I am assuming you know how to extend your JpaRepository if not, you can check it out here.

Answer 2

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

Answer 3

public static Specification<TopComponentHistory> findAllGroupBy() {
        return new Specification<TopComponentHistory>(){
            @Override
            public Predicate toPredicate(Root<TopComponentHistory> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
    query.groupBy(column_name);
    }
    }