Reputation: 41
sorry for my english first.
i want use jpa to groupby, like : select scrip, dustup, count(*) from data flow group by scrip, dstip. so, write these code:
public class DataflowSpec {
public static Specification<Dataflow> search(final String[] group, final String[] sort, final String[] desc) {
return new Specification<Dataflow>() {
@Override
public Predicate toPredicate(Root<Dataflow> root1, CriteriaQuery<?> query1, CriteriaBuilder builder) {
// TODO Auto-generated method stub
CriteriaQuery<Tuple> query = builder.createQuery(Tuple.class);
Root<Dataflow> root = query.from(Dataflow.class);
query.multiselect(root.get("srcip"), root.get("dstip"), builder.count(root));
query.groupBy(root.get("srcip"), root.get("dstip"));
query.orderBy(builder.desc(root.get("srcip").as(BigInteger.class)));
return query.getRestriction();
}
};
}
}
but , SQL log is: Hibernate:
select
count(dataflow0_.id) as col_0_0_
from
Dataflow dataflow0_
Hibernate:
select
dataflow0_.id as id1_2_,
dataflow0_.byteall as byteall2_2_,
dataflow0_.bytedn as bytedn3_2_,
dataflow0_.byteup as byteup4_2_,
dataflow0_.dstip as dstip5_2_,
dataflow0_.dstport as dstport6_2_,
dataflow0_.engieid as engieid7_2_,
dataflow0_.flag as flag8_2_,
dataflow0_.netid as netid9_2_,
dataflow0_.pkgall as pkgall10_2_,
dataflow0_.pkgdn as pkgdn11_2_,
dataflow0_.pkgup as pkgup12_2_,
dataflow0_.protocolid as protoco17_2_,
dataflow0_.rtt as rtt13_2_,
dataflow0_.srcip as srcip14_2_,
dataflow0_.srcport as srcport15_2_,
dataflow0_.updatetime as updatet16_2_
from
Dataflow dataflow0_ limit ?
so, how to resolve it? thanks!
Upvotes: 4
Views: 26021
Reputation: 2721
Because I was unhappy with the provided solution(s), I decided to write a generic executor that groups by a single field, and counts the results.
First, declare the interface:
import jakarta.persistence.metamodel.SingularAttribute;
import org.springframework.data.jpa.domain.Specification;
import java.util.Map;
public interface GroupAndCountExecutor<T> {
<V> Map<V, Long> groupAndCount(Specification<T> specification,
SingularAttribute<T, V> attribute
);
}
Then, implement it:
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.metamodel.SingularAttribute;
import org.springframework.data.jpa.domain.Specification;
import java.util.Map;
import java.util.stream.Collectors;
public class GroupAndCountExecutorImpl<T> implements GroupAndCountExecutor<T> {
@PersistenceContext
private EntityManager em;
@Override
public <V> Map<V, Long> groupAndCount(final Specification<T> specification,
final SingularAttribute<T, V> attribute
) {
final var cb = em.getCriteriaBuilder();
final var query = cb.createTupleQuery();
final var root = query.from(attribute.getDeclaringType().getJavaType());
final var expression = root.get(attribute);
final var predicate = specification.toPredicate(root, query, cb);
query.multiselect(expression, cb.count(root))
.select(cb.tuple(expression, cb.count(root)))
.where(predicate)
.groupBy(expression);
return em.createQuery(query)
.getResultStream()
.collect(Collectors.toMap(
tuple -> tuple.get(0, attribute.getJavaType()),
tuple -> tuple.get(1, Long.class)
));
}
}
Now, the only thing you need to do is have your repository extend the interface, as follows:
public interface PetRepository extends JpaRepository<Pet, Long>,
JpaSpecificationExecutor<Pet>,
GroupAndCountExecutor<Pet> {
}
public enum Species {
CAT, DOG, OTHER
}
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@EqualsAndHashCode(of = "id")
@Entity
public class Pet {
private @Id Long id;
private @Enumerated(EnumType.STRING) Species species;
private String name;
}
@StaticMetamodel(Pet.class)
public class Pet_ {
public static volatile SingularAttribute<Pet, Species> source;
}
To get a count of all species that match the specification, do the following:
final Specification<Pet> spec = (root, query, criteriaBuilder) -> ...;
final var counts = petRepository.groupAndCount(spec, Pet_.species);
Upvotes: 0
Reputation: 4797
You can achieve spring data group by
by specification
, just follow
[section 2.6][1] or [section 3.6][2] for version before or after 2.0. For single repository manipulation, the two versions have identical solution. For the *all * repository solution, before 2.0 use [customized factory bean][3], while after 2.0 this factory bean manipulation is omitted.
public Map<AlarmMsg.AlarmLevel, Long> testSpecification(String neId) {
SingularAttribute attribute = AlarmData_.isClear;
Specification<Object> where = Specification.where(
(root, query, cb) -> cb.equal(root.get(attribute), false)
);
final Map<AlarmMsg.AlarmLevel, Long> result = alarmDataRepository.groupAndCount(AlarmData_.alarmLevel, where );
return result;
}
repository:
public interface AlarmDataRepository extends JpaRepository<AlarmData, Long>, JpaSpecificationExecutor<AlarmData>, CustomizedGroupCountRepository {
Fragment repository and its implementation:
public interface CustomizedGroupCountRepository {
Map<AlarmMsg.AlarmLevel, Long> groupAndCount(SingularAttribute singularAttribute, Specification where);
}
public class CustomizedGroupCountRepositoryImpl implements CustomizedGroupCountRepository {
private final EntityManager entityManager;
public CustomizedGroupCountRepositoryImpl(EntityManager entityManager) {
Assert.notNull(entityManager, "EntityManager must not be null!");
this.entityManager = entityManager;
}
@Override
public Map<AlarmMsg.AlarmLevel, Long> groupAndCount(SingularAttribute singularAttribute, Specification where) {
final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Tuple> query = criteriaBuilder.createQuery(Tuple.class);
final Root<AlarmData> root = query.from(AlarmData.class);
final Path<AlarmMsg.AlarmLevel> expression = root.get(singularAttribute);
query.multiselect(expression, criteriaBuilder.count(root));
query.select(criteriaBuilder.tuple(expression, criteriaBuilder.count(root)));
query.where(where.toPredicate(root, query, criteriaBuilder));
query.groupBy(expression);
final List<Tuple> resultList = entityManager.createQuery(query).getResultList();
return resultList.stream()
.collect(toMap(
t -> t.get(0, AlarmMsg.AlarmLevel.class),
t -> t.get(1, Long.class))
);
}
}
The main difference between one-for-all-repository and one-for-single-repository is, in one-for-single-repository case, it can access the real entity class, like User
in spring reference document. So that you don't need to use generic types to refer an any-typed entity, while in one-for-all-repository case, the implementation of the customized method uses generic types, and its class information could (or must) be gained from an injected JpaEntityInformation
as stated in both section 3.6.
[1]: https://docs.spring.io/spring-data/jpa/docs/1.8.0.RELEASE/reference/html/#repositories.single-repository-behaviour
[2]: https://docs.spring.io/spring-data/jpa/docs/2.0.5.RELEASE/reference/html/#repositories.single-repository-behavior
[3]: https://jeroenbellen.com/spring-data-extending-the-jpa-specification-executor/
Upvotes: 3
Reputation: 1479
For people still looking for how to apply "group by" in Spring jpa Specification, you can use something like the following snippet:
...
private Dataflow dataflowFilter;
@Override
public Predicate toPredicate(Root<Dataflow> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
Predicate predicate = cb.conjunction();
predicate.getExpressions().add(cb.equal(root.get("id"), dataflowFilter.getId()));
...
cq.groupBy(root.get("id"));
...
return predicate;
}
Upvotes: 3
Reputation: 11
Specification doesn't support groupBy. SimpleJpaRepository replaced query.select/multiselect by query.select(root)
Upvotes: 0