JpaRepository:Spring对运行时查询变量进行排序 [英] JpaRepository: Spring Sort for runtime query variabels
问题描述
SELECT id,title,
IF(o.fixed_on_top = 1 AND o.fixing_on_top_day_counter> 5,1,0)actual_fixed_on_top
FROM orders o
ORDER BY actual_fixed_on_top DESC,publication_date DESC;
如何在JpaRepository上执行这样的排序?
也许使用Criteria API?
但是我找不到任何例子..
Thanks!
编辑:
好的,研究了不同的方法,并且相信它不能通过Criteria API实现(但很遗憾)
一种工作变体:原生SQL查询,我确信。
public class MyEntitySpecifications {
public static Specification< MyEntity> GetByPageSpecification(){
返回新的规范< MyEntity>(){
@Override
public Predicate toPredicate(Root< MyEntity> root,CriteriaQuery<?> cq,CriteriaBuilder cb ){
Expression fixingExpr = cb.greaterThan(root.get(MyEntity_.fixingDueDate),new DateTime(DateTimeZone.UTC));
cq.orderBy(new OrderImpl(cb.selectCase()。when(fixingExpr,1).otherwise(0),false));
return cb ...;
}
};
$ b $ p
$ b 主要思想是使用case表达式而不是简单的逻辑。
明确的sql equivivalent是:
select ...
from my_entity e
其中。 ..
按大小写顺序当e.fixing_due_date> now()then 1 else 0 end desc
所以我们可以用标准api规范来构建动态查询。
没有直接访问EntityManager,没有普通的sql。
There is my plain sql query:
SELECT id, title,
IF(o.fixed_on_top = 1 AND o.fixing_on_top_day_counter > 5, 1 ,0) actual_fixed_on_top
FROM orders o
ORDER BY actual_fixed_on_top DESC, publication_date DESC;
How can I perform sorting like this on JpaRepository?
Maybe using Criteria API?
But I dint find any examples..
Thanks!
EDIT:
ok, studied different ways, and convinced that it cant be implemented through Criteria API (but it is a pity)
One working variant: Native SQL Query, i am sure.
解决方案 I found the solution:
public class MyEntitySpecifications {
public static Specification<MyEntity> GetByPageSpecification() {
return new Specification<MyEntity>() {
@Override
public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
Expression fixingExpr = cb.greaterThan(root.get(MyEntity_.fixingDueDate), new DateTime(DateTimeZone.UTC));
cq.orderBy(new OrderImpl(cb.selectCase().when(fixingExpr, 1).otherwise(0), false));
return cb...;
}
};
}
}
The main idea is using case expression instead of simple logical.
plain sql equuivalent is:
select ...
from my_entity e
where ...
order by case when e.fixing_due_date > now() then 1 else 0 end desc
So we can build dynamic queries with criteria api specifications as well.
No direct access to EntityManager, no plain sql.
这篇关于JpaRepository:Spring对运行时查询变量进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!