Spring Boot使用规范进行SUM(column)操作。

huangapple go评论79阅读模式
英文:

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&lt;MyEntity&gt; {
    private String attr1;
    private String attr2;
    private String attr3;
    private String attr4;

@Override
public Predicate toPredicate(Root&lt;MyEntity&gt; root, CriteriaQuery&lt;?&gt; query, CriteriaBuilder criteriaBuilder) {
    List&lt;Predicate&gt; restriction = new ArrayList&lt;&gt;();
    if (!StringUtils.isEmpty(attr1)) {
        restriction.add(criteriaBuilder.equal(root.get(&quot;attr1&quot;), attr1));
    }
    if (!StringUtils.isEmpty(attr2)) {
        restriction.add(criteriaBuilder.equal(root.get(&quot;attr2&quot;), 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;
    }
}

主要的仓库应该同时扩展JpaRepositoryMyRepositoryCustom

@Repository
interface MyEntityRepository extends JpaRepository<MyEntity, Long>, MyRepositoryCustom {

}
英文:

After some research, here is solution:

Create an interface:

interface MyRepositoryCustom {
    &lt;S extends Number&gt; S sum(Specification&lt;MyEntity&gt; spec, Class&lt;S&gt; resultType, String fieldName);
}

Implementation:

@Repository
class MyRepositoryCustomImpl implements MyRepositoryCustom {

    @Autowired
    private EntityManager entityManager;

    @Override
    public &lt;S extends Number&gt; S sum(Specification&lt;MyEntity&gt; spec, Class&lt;S&gt; resultType, String fieldName) {
	    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
	    CriteriaQuery&lt;S&gt; query = builder.createQuery(resultType);
	    Root&lt;MyEntity&gt; root = applySpecificationToCriteria(spec, query);
	    query.select(builder.sum(root.get(fieldName).as(resultType)));
	    TypedQuery&lt;S&gt; typedQuery = entityManager.createQuery(query);
	    return typedQuery.getSingleResult();
    }

    protected &lt;S&gt; Root&lt;MyEntity&gt; applySpecificationToCriteria(Specification&lt;MyEntity&gt; spec, CriteriaQuery&lt;S&gt; query) {
	    Root&lt;MyEntity&gt; 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&lt;MyEntity, Long&gt;, MyRepositoryCustom {

}

huangapple
  • 本文由 发表于 2020年9月24日 12:56:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/64039786.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定