具有求和功能的弹簧规格 [英] Spring Specification With Sum Function
问题描述
我正在尝试在spring规范中求和函数，如下所示:
I am trying a sum function in spring specification as below:
@Override
public Predicate toPredicate(Root<Stock> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
Path expression = root.get("qty");
query.select(builder.sum(expression));
return null;
}
我要执行的查询是:
SELECT SUM(o.qty) FROM Stock o;
但是Spring不会创建求和函数，而是在执行该函数:
But Spring is not creating a sum function and is executing this:
SELECT o.qty FROM Stock o
我检查了很多堆栈溢出问题，但没有用规范的方式回答，大多数人为此使用JPQL作为@Query注释.但是我的进一步查询和设计非常复杂，因此我只需要使用Specification.因为我需要完全动态的查询.
I checked so many Stack Overflow question but there is no answer in Specification way, most of the people use JPQL for @Query annotation for this. But my further query and design is very complex so I have to use Specification only. Because I need fully dynamic query.
也有类似的问题，并且<同样也是href ="https://stackoverflow.com/questions/36347705/jpa-specification-complex-query">此.
推荐答案
我认为这是不可能的. 这就是原因. 我指的是Spring JPA 1.7.3(可在此处找到源代码:
I think this is not possible out of the box. Here is the reason why. I'm referring to Spring JPA 1.7.3 (source code can be found here: http://grepcode.com/snapshot/repo1.maven.org/maven2/org.springframework.data/spring-data-jpa/1.7.3.RELEASE/)
通过JPA存储库使用JPA规范，例如:
A JPA specification is used via JPA repository, e.g.:
myRepo.findAll(mySpec);
此功能的源代码为:
/*
* (non-Javadoc)
* @see org.springframework.data.jpa.repository.JpaSpecificationExecutor#findAll(org.springframework.data.jpa.domain.Specification)
*/
public List<T> findAll(Specification<T> spec) {
return getQuery(spec, (Sort) null).getResultList();
}
这是第一问题:返回类型和查询类型绑定到实体类型T.在您的情况下，这意味着返回结果将是Stock实体的列表.
Here is the first issue here: the return type and query type are bound to entity type T. In your case it means that return result is going to be a list of Stock entities.
第二个问题在于getQuery()函数中选择列表的组成方式:
Second issue is with the way select list is composed in getQuery() function:
/**
* Creates a {@link TypedQuery} for the given {@link Specification} and {@link Sort}.
*
* @param spec can be {@literal null}.
* @param sort can be {@literal null}.
* @return
*/
protected TypedQuery<T> getQuery(Specification<T> spec, Sort sort) {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<T> query = builder.createQuery(getDomainClass());
Root<T> root = applySpecificationToCriteria(spec, query);
query.select(root);
if (sort != null) {
query.orderBy(toOrders(sort, root, builder));
}
return applyRepositoryMethodMetadata(em.createQuery(query));
}
如您所见，这种类型的调用明确选择了root(在您的情况下为T或Stock类型)，由于它是在将规范应用于条件查询后执行的，因此它会覆盖您在规范中所做的一切.
As you can see this type of call explicitly selects root (which is of type T or Stock in your case) and since it's executed after applying specification to criteria query, it overrides whatever you do in specification.
但是有一种解决方法.
您可以扩展现有的JPA存储库类:
You can extend an existing JPA repository class:
public class MyRepositoryImpl<T>
extends SimpleJpaRepository<T, Integer> implements MyRepository<T>
{
并添加带有适当签名的特殊方法来执行所需的操作:
and add special methods with appropriate signatures to perform what you need:
public <P> P calcAggregate(EntitySpecification<T> spec, SingularAttribute<?,P> column)
{
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<P> query = criteriaBuilder.createQuery(column.getJavaType());
if (spec != null)
{
Root<T> root = query.from(getDomainClass());
query.where(spec.toPredicate(root, query, criteriaBuilder));
query.select(criteriaBuilder.sum(root.get(column.getName())));
}
TypedQuery<P> typedQuery = em.createQuery(query);
P result = typedQuery.getSingleResult();
return result;
}
然后将其用作:
myRepo.calcAggregate(mySpec, Stock_.qty);
其中Stock_是Stock实体的元模型类.
where Stock_ is a metamodel class for Stock entity.
这是一个非常简单的示例，只有一个字段，并且没有选择聚合操作的能力.可以扩展以满足自己的需求.
This is a very simple example with just one field and without ability to select an aggregate operation. It can be extended to suit one's needs.
这篇关于具有求和功能的弹簧规格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
