如何将JPA的@OrderBy排序值作为动态参数接受?

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

How to accept @OrderBy of JPA sort value as a dynamic parameter?

问题

我想动态排序一个嵌套的集合属性,基于查询参数 "sort"。

假设我有一个实体 A:

class A {
    
    @OneToMany(mappedBy="a")
    private Set<B> bset;
    
}

class B {
    
    private LocalDate datefield;
    @ManyToOne
    private C c;
    
}

class C {
    private Double quantity;
}

我正在调用 A 的仓库方法:findAll(Specification specification, Pageable page)

从用户界面(UI)调用 REST 控制器时,使用以下模式的排序参数:

url?page=0&size=10&sort=bset_datefield

由于这是一个嵌套集合,上述排序不起作用。可排序的字段是 datefieldquantity

我知道 @OrderBy("bset.datefield") 可以工作,但参数应该是动态的。

在调用 A 仓库上的 findAll 方法时,我该如何实现这一点?

英文:

I want to sort a nested collection property dynamically based on the query parameter sort.

Suppose i have an entity A as

class A{

@OneToMany(mappedBy=&quot;a&quot;)
private Set&lt;B&gt; bset;

}

class B{

private LocalDate datefield;
@ManyToOne
private C c;

}

class C
{
private Double quantity;

}

I am calling repository of A with findAll(Specification specification,Pageable page)

From UI, rest controller is called with sort param as below pattern

 url?page=0&amp;size=10&amp;sort=bset_datefield

As it is a nested collection,the above sort is not working.
The sortable fields are datefield,quantity.

I know @OrderBy("bset.datefield") will work,but the parameter should be dynamic.

How can i achieve this when invoking find All on A repository ?

答案1

得分: 0

无法完成,因为在初始化entityManager时,会评估注释。

最佳选项是创建一个已排序的查询,或者一旦获取了列表,如果您从现在开始使用Java 8,可以使用流的sort方法进行排序。

英文:

It cannot be done because when the entityManager is initialized the annotations are evaluated.

The best options are, create a query that comes ordered or once the list is obtained if you use Java 8 from now on, order with the sort method of stream.

答案2

得分: 0

你不能这样做,仅使用Spring Data是不可能的。

在概念上,您正在使用JpaSpecificationExecutor和基于类A参数化的Specification

在指定的findAll方法中,您可以按任何属性对A进行排序,甚至可以按嵌套属性进行排序,但您正在对A进行排序。

您可以指示JPA提供程序fetch一个集合,按集合字段排序,但不能对集合本身进行排序。

即使在ASpecification体中使用某种涉及必须排序的集合的子查询,并在这个子查询中按集合字段排序,我认为 - 也许应该测试一下 - JPA提供程序将返回类A的实例,然后根据提供的@OrderBy标准初始化集合。

顺便说一句,在语义上,使用@OrderBy时,始终最好使用List而不是Set

所以如果您想对集合进行排序,我认为您唯一的选择是在查询字符串中处理接收到的排序标准,并在将数据返回给客户端之前通过对象比较在内存中对集合进行排序。

英文:

You cannot do that, it is not possible using only Spring Data.

Conceptually, you are using a JpaSpecificationExecutor and Specifications parameterized in terms on the class A.

In the indicated findAll method you can sort A by any property, even by a nested one, but you are sorting A.

You can instruct the JPA provider to fetch a collection, to sort by a collection field, but you cannot sort the collection itself.

Even in the case that in the Specification body of A you use some kind of subquery which involve the collection that must be sorted, and you establish a sort by the fields of the collection in this subquery, I think - maybe it should be tested - that the JPA provider will return the instances of class A and then initialize the collection according to the provided @OrderBycriteria.

By the way, and semantically, it is always a good practice to use a List instead of a Setwhen using @OrderBy.

So if you want to sort the collection I think the only alternative you have is to process the sort criteria received in the query string and sort the collection in memory by object comparison before returning the data to the client.

答案3

得分: 0

以下是翻译好的部分:

"Following this tutorial: REST Query Language with Spring Data JPA and Querydsl

When you use

> MyUserPredicatesBuilder

It does the magic trick.

@Controller
public class UserController {

@Autowired
private MyUserRepository myUserRepository;

@RequestMapping(method = RequestMethod.GET, value = "/myusers")
@ResponseBody
public Iterable<MyUser> search(@RequestParam(value = "search") String search) {
MyUserPredicatesBuilder builder = new MyUserPredicatesBuilder();

if (search != null) {
Pattern pattern = Pattern.compile("(\w+?)(:|<|>)(\w+?),");
Matcher matcher = pattern.matcher(search + ",");
while (matcher.find()) {
builder.with(matcher.group(1), matcher.group(2), matcher.group(3));
}
}
BooleanExpression exp = builder.build();
return myUserRepository.findAll(exp);
}
}

英文:

Following this tutorial: REST Query Language with Spring Data JPA and Querydsl

When you use

> MyUserPredicatesBuilder

It does the magic trick.

@Controller
public class UserController {

@Autowired
private MyUserRepository myUserRepository;

@RequestMapping(method = RequestMethod.GET, value = &quot;/myusers&quot;)
@ResponseBody
public Iterable&lt;MyUser&gt; search(@RequestParam(value = &quot;search&quot;) String search) {
    MyUserPredicatesBuilder builder = new MyUserPredicatesBuilder();

    if (search != null) {
        Pattern pattern = Pattern.compile(&quot;(\w+?)(:|&lt;|&gt;)(\w+?),&quot;);
        Matcher matcher = pattern.matcher(search + &quot;,&quot;);
        while (matcher.find()) {
            builder.with(matcher.group(1), matcher.group(2), matcher.group(3));
        }
    }
    BooleanExpression exp = builder.build();
    return myUserRepository.findAll(exp);
}

}

huangapple
  • 本文由 发表于 2020年7月22日 10:17:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/63025795.html
匿名

发表评论

匿名网友

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

确定