Spring data group by with Specification and Paging support

Question

I have an "item"-based view, where an item belongs to a container and a container consists of several items. In addition, an item has a location and several items can have the same location.

database view:

id_item   id_container   id_location   container_name   container_code   amount
'I1'      'C1'           'L1'          'container #01'  'c01'            10
'I2'      'C1'           'L1'          'container #01'  'c01'             5
'I3'      'C1'           'L2'          'container #01'  'c01'            25
'I4'      'C2'           'L3'          'container #02'  'c02'            30

I would like to select grouped by container:

entity group by container:

@Entity
public class GroupByContainerEntity {
    private String idContainer;
    private String containerName;
    private String containerCode;
    private List<String> locations; // OR private String locations; -> comma separated ids
    private Integer sumAmount;
    private Integer countItems;
}

repository:

public interface IGroupByContainerRepository extends JpaRepository<GroupByContainerEntity, String>, JpaSpecificationExecutor<GroupByContainerEntity> {
}

I need to pass addional specification (e.g. only certain locations and containers) and paging (e.g. sort by container name), therefore a (native) query approach does not work:

groupByContainerRepository.findAll(Specification, Pageable)

Is there any way to load the data grouped by container (via the spring data repository)? Specification and Paging support is mandatory.

Answer 1

Although the Specification class is designed to handle where clauses, specifying group by clause within the Specification class also works.

To circumvent the issue that Specification::toPredicate method must return a Predicate instance, you can create a Specification class with where clause equal to 1=1 while declaring group by clause within the method, just like the example as follows:

public class GroupBySpecification implements Specification<GroupByContainerEntity> {

    @Override
    public Predicate toPredicate(Root<GroupByContainerEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        query.groupBy(root.get("containerCode"));
        return criteriaBuilder.equal(criteriaBuilder.literal(1), criteriaBuilder.literal(1));
    }

}

Other additional specifications such as locations and container codes can be specified separately:

@RequiredArgsConstructor
public class LocationSpecification implements Specification<GroupByContainerEntity> {

    private final String location;


    @Override
    public Predicate toPredicate(Root<GroupByContainerEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        return criteriaBuilder.equal(root.get("idLocation"), location);
    }
}

@RequiredArgsConstructor
public class ContainerCodeSpecification implements Specification<GroupByContainerEntity> {

    private final String containerCode;

    @Override
    public Predicate toPredicate(Root<GroupByContainerEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        return criteriaBuilder.equal(root.get("containerCode"), containerCode);
    }

}

By doing so, you can reuse the groupByContainerRepository.findAll(Specification, Pageable) to fulfill your need. A sample method in the service layer is also given as follows:

public Page<GroupByContainerEntity> findAll(ItemSearchParameter params, Pageable pageable) {

    List<Pair<Optional<?>, Function<Object, Specification<GroupByContainerEntity>>>> pairs = List.of(
            Pair.of(params.getLocation(), s -> new LocationSpecification((String) s)),
            Pair.of(params.getContainerCode(), c -> new ContainerCodeSpecification((String) c))
    );

    Specification<GroupByContainerEntity> spec = pairs.stream()
            .filter(entry -> entry.getFirst().isPresent())
            .map(entry -> entry.getSecond().apply(entry.getFirst().get()))
            .reduce(new GroupBySpecification(), Specification::and);

    return repository.findAll(spec, pageable);
}

It is worth to mention that the entity class GroupByContainerEntity contains aggregate fields such as locations, sumAmount and countItems. To handle them, we can annotate these fields with @Formula and supply the query. Thus, the GroupByContainerEntity class is needed to further modify as below:

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "item")
public class GroupByContainerEntity {

    @Id
    private String idItem;

    private String idContainer;

    private String idLocation;

    private String containerName;

    private String containerCode;

    @Formula("(GROUP_CONCAT(id_location))")
    private String locations;

    @Formula("(SUM(amount))")
    private Integer sumAmount;

    @Formula("(COUNT(container_code))")
    private Integer countItems;

}

I have also pushed the code above to gitlab. The project uses SQLite as database so you can clone and have a try.

Answer 2

I like a more functional approach:

public static <TYPE> Specification<TYPE> create(
    final Function3<Root<TYPE>, AbstractQuery<?>, CriteriaBuilder, Predicate> predicate) {
    return (root, query, criteriaBuilder) -> {
      final Predicate oneEqualsOne = criteriaBuilder.equal(criteriaBuilder.literal(1), 1);
      return criteriaBuilder.and(oneEqualsOne, predicate.apply(root, query, criteriaBuilder));
    };
}

@SafeVarargs
public static <TYPE> Function3<Root<TYPE>, AbstractQuery<?>, CriteriaBuilder, Predicate> and(
    final Function3<Root<TYPE>, AbstractQuery<?>, CriteriaBuilder, Predicate>... predicates) {
    return and(Arrays.asList(predicates));
}

public static <TYPE> Function3<Root<TYPE>, AbstractQuery<?>, CriteriaBuilder, Predicate> and(
  Collection<Function3<Root<TYPE>, AbstractQuery<?>, CriteriaBuilder, Predicate>> predicates) {
  return (root, criteriaQuery, criteriaBuilder) -> {
    // Default 1=1 if List.isEmpty this is needed
    Predicate result = criteriaBuilder.equal(criteriaBuilder.literal(1), 1);
    for (final Function3<Root<TYPE>, AbstractQuery<?>, CriteriaBuilder, Predicate> predicate : predicates) {
       result = criteriaBuilder.and(result, predicate.apply(root,criteriaQuery, criteriaBuilder));
    }
    return result;
  };
}

public static <TYPE>
  Function3<Root<TYPE>, AbstractQuery<?>, CriteriaBuilder, Predicate> stringEqual(
    final String column, final String value) {
    return (root, criteriaQuery, criteriaBuilder) -> {
        if (StringUtils.isNotBlank(value) && StringUtils.isNotBlank(column)) {
          return criteriaBuilder.equal(root.get(column).as(String.class), value);
        } else {
          return criteriaBuilder.equal(criteriaBuilder.literal(1), 1);
        }
     };
}

public static <TYPE> Function3<Root<TYPE>, AbstractQuery<?>, CriteriaBuilder, Predicate> orderBy(String column, Sort.Direction direction) {
  return (root, criteriaQuery, criteriaBuilder) -> {
    if (direction != null
        && StringUtils.isNotBlank(column)
        && criteriaQuery instanceof CriteriaQuery<?>) {
      if (direction.isAscending()) {
          ((CriteriaQuery<?>) criteriaQuery).orderBy(criteriaBuilder.asc(root.get(column)));
      } else {
          ((CriteriaQuery<?>)criteriaQuery).orderBy(criteriaBuilder.desc(root.get(column)));
        }
      }
      return criteriaBuilder.equal(criteriaBuilder.literal(1), 1);
  };
}

Then you can do something like:

Specification<FOO> spec = create(
                               and(
                                 stringEqual("name", name), 
                                 stringEqual("lastName",lastName),
                                 orderBy("id", DESC)
                               )
                          );
final PageRequest pageRequest = PageRequest.of(page, size/*, Sort.by(direction, order)*/);
repo.findAll(spec,pageRequest);

The functional interface can be extended by all kinds of "equals, like, notEquals" in a util kind class.