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> {


}

I have a hard time wrapping my mind around your initial query. It's missing a `FROM` clause and I'm unsure how you want `TITLE` to be handled? — mabi, Sep 01 '14 at 12:45
The updated query is not valid. It's addressing the `FROM` issue but you still can't select a raw column that's not in your `GROUP BY` clause. Can you describe what you actually want to achieve? — mabi, Sep 02 '14 at 08:45
I want to group by result, using JPA and JpaSpecificationExecutor. — 박주성, Sep 02 '14 at 10:14

score 4 · Answer 1 · answered Oct 09 '20 at 07:46

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

I can see this, how can allow it group with date only with a datetime field — Neri, Oct 27 '21 at 15:07

Alain Cruz · Answer 2 · 2022-07-20T20:18:23.923

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.

DarkCrow · Answer 3 · 2019-07-23T10:21:13.300

-1

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

edited Jul 23 '19 at 10:21

answered Feb 09 '16 at 16:30

DarkCrow

785
2
8
29

3

an explanation would be nice – Aziz Feb 09 '16 at 16:56
JpaSpecificationExecutor has the method List findAll(Specification spec); where we can give Specification as parameter. The Specification can be created by overriding the toPredicate(Root,CriteriaQuery, CriteriaBuilder) method. We can create any number of Predicates and use query.grouBy() for GROUP BY clause. – DarkCrow Feb 09 '16 at 17:57
2

It took me some time to figure out how to find the right way to define the column name in the group by. so in Deepak example replace query.groupBy(column_name); by query.groupBy(root.get(TopComponentHistory_.YOUR_COLUMN)); – Xavier Bouclet Sep 13 '17 at 13:29
2

where is the return statement? – Oleg Mikhailov Jun 25 '19 at 10:35
its returning new Specification – DarkCrow Jul 23 '19 at 10:21
1

I think @OlegMikhailov meant where's the return statement for toPredicate method and not for findAllGroupBy method. It's clearly missing – Jad B. Apr 03 '20 at 20:29

How to group by result with JPA and JpaSpecificationExecutor?

3 Answers3