Reputation: 111
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> {
}
Upvotes: 11
Views: 17905
Reputation: 5097
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.
Upvotes: 1
Reputation: 93
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);
Upvotes: 4
Reputation: 850
public static Specification<TopComponentHistory> findAllGroupBy() {
return new Specification<TopComponentHistory>(){
@Override
public Predicate toPredicate(Root<TopComponentHistory> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
query.groupBy(column_name);
}
}
Upvotes: -1