英文:
Spring boot SUM(column) with specification
问题
我有一个实体,如下所示:
class MyEntity {
Long id;
String attr1;
String attr2;
String attr3;
String attr4;
Double attr5;
}
我使用 `Specification` 查询由属性筛选的结果,如下所示:
class MySpecification implements Specification<MyEntity> {
private String attr1;
private String attr2;
private String attr3;
private String attr4;
@Override
public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
List<Predicate> restriction = new ArrayList<>();
if (!StringUtils.isEmpty(attr1)) {
restriction.add(criteriaBuilder.equal(root.get("attr1"), attr1));
}
if (!StringUtils.isEmpty(attr2)) {
restriction.add(criteriaBuilder.equal(root.get("attr2"), attr2));
}
// 以此类推
Predicate predicate = criteriaBuilder.disjunction();
predicate.getExpressions().add(criteriaBuilder.and(restriction.toArray(new Predicate[restriction.size()])));
return predicate;
}
}
现在我想通过 `Specification` 获取 `attr5` 的总和,应该如何做?
提前谢谢您。
英文:
I have an entity, says:
class MyEntity {
Long id;
String attr1;
String attr2;
String attr3;
String attr4;
Double attr5;
}
I use Specification
to query the result filtered by attributes like:
class MySpecification implements Specification<MyEntity> {
private String attr1;
private String attr2;
private String attr3;
private String attr4;
@Override
public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
List<Predicate> restriction = new ArrayList<>();
if (!StringUtils.isEmpty(attr1)) {
restriction.add(criteriaBuilder.equal(root.get("attr1"), attr1));
}
if (!StringUtils.isEmpty(attr2)) {
restriction.add(criteriaBuilder.equal(root.get("attr2"), attr2));
}
// And so on
Predicate predicate = criteriaBuilder.disjunction();
predicate.getExpressions().add(criteriaBuilder.and(restriction.toArray(new Predicate[restriction.size()])));
return predicate;
}
}
Now I want to get sum of attr5
by the Specification
, how can I do that ?
Thank you in advanced.
答案1
得分: 2
经过一些研究,以下是解决方案:
创建一个接口:
interface MyRepositoryCustom {
<S extends Number> S sum(Specification<MyEntity> spec, Class<S> resultType, String fieldName);
}
实现:
@Repository
class MyRepositoryCustomImpl implements MyRepositoryCustom {
@Autowired
private EntityManager entityManager;
@Override
public <S extends Number> S sum(Specification<MyEntity> spec, Class<S> resultType, String fieldName) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<S> query = builder.createQuery(resultType);
Root<MyEntity> root = applySpecificationToCriteria(spec, query);
query.select(builder.sum(root.get(fieldName).as(resultType)));
TypedQuery<S> typedQuery = entityManager.createQuery(query);
return typedQuery.getSingleResult();
}
protected <S> Root<MyEntity> applySpecificationToCriteria(Specification<MyEntity> spec, CriteriaQuery<S> query) {
Root<MyEntity> root = query.from(MyEntity.class);
if (spec == null) {
return root;
}
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
Predicate predicate = spec.toPredicate(root, query, builder);
if (predicate != null) {
query.where(predicate);
}
return root;
}
}
主要的仓库应该同时扩展JpaRepository
和MyRepositoryCustom
:
@Repository
interface MyEntityRepository extends JpaRepository<MyEntity, Long>, MyRepositoryCustom {
}
英文:
After some research, here is solution:
Create an interface:
interface MyRepositoryCustom {
<S extends Number> S sum(Specification<MyEntity> spec, Class<S> resultType, String fieldName);
}
Implementation:
@Repository
class MyRepositoryCustomImpl implements MyRepositoryCustom {
@Autowired
private EntityManager entityManager;
@Override
public <S extends Number> S sum(Specification<MyEntity> spec, Class<S> resultType, String fieldName) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<S> query = builder.createQuery(resultType);
Root<MyEntity> root = applySpecificationToCriteria(spec, query);
query.select(builder.sum(root.get(fieldName).as(resultType)));
TypedQuery<S> typedQuery = entityManager.createQuery(query);
return typedQuery.getSingleResult();
}
protected <S> Root<MyEntity> applySpecificationToCriteria(Specification<MyEntity> spec, CriteriaQuery<S> query) {
Root<MyEntity> root = query.from(MyEntity.class);
if (spec == null) {
return root;
}
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
Predicate predicate = spec.toPredicate(root, query, builder);
if (predicate != null) {
query.where(predicate);
}
return root;
}
}
Main repository should extend both JpaRepository
and MyRepositoryCustom
:
@Repository
interface MyEntityRepository extends JpaRepository<MyEntity, Long>, MyRepositoryCustom {
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论