如何在Spring Boot中为未与另一个实体映射的字段提供排序和过滤支持

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

How to support sorting and filtering for a field which is not mapped with another entity using spring boot

问题

我有一个实体 Person,它有字段 id,name,email

另一个实体 Employee 有字段 id,name,personid(存储了 person 的字符串 id,但与 person 表没有映射)。

现在我想要获取一个分页列表,其中包含 person 的姓名,employee 的 id,employee 的姓名,并且可以根据 person 的姓名和 employee 的姓名进行动态搜索和排序。
也就是说,用户可以按照 person 的姓名或者 employee 的姓名进行排序,并且可以按照 person 的姓名或者 employee 的姓名或者两者兼而有之进行筛选。

在查询格式中,我们可以写成:

select emp.id, emp.name, p.name from employee emp join person p on
emp.personid=p.id

这个查询返回了预期的输出,但是为了实现搜索和排序,我无法使用投影,因为 Employee 实体中的 personid 与 Person 实体的 id 没有映射。

不能使用 Criteria Builder 并且不能更改实体。

如果有人能提供一个最佳和最优的解决方案来获取所需的分页结果,并支持排序和过滤,将不胜感激。

英文:

I have an entity Person which has fields id,name,email

Another entity Employee which has fields id,name,personid (stores the string id of person but not mapped with person table)

Now I want to get paginated list which contains person name, employee id, employee name along with searching and sorting based on person name and employee name dynamically.
i.e., User can sort by person name or employee name and filter by person name or employee name or both

In query format we can write,

select emp.id,emp.name,p.name from employee emp join person p on
emp.personid=p.id

This query returns expected output but to achieve searching and sorting, i'm unable to use projections as personid in Employee entity is not mapped with id of Person entity.

Criteria builder shouldn't be used and entities cannot be changed.

It would be greatly appreciated if someone could provide a best and optimal solution to get the required paginated result which supports sorting and filtering.

答案1

得分: 1

您可以使用 order by 子句修改现有的查询并传递 Pageable,例如这是按雇员姓名排序的示例:

@Query("select emp.id, emp.name, p.name from employee emp join person p on emp.personid = p.id order by emp.name")
Page<YourDto> findAllOrderByEmployeeNamePersonName(Pageable pageable);

要按雇员姓名进行筛选,只需添加 where 子句:

@Query("select emp.id, emp.name, p.name from employee emp join person p on emp.personid = p.id where emp.name = :empName")
Page<YourDto> findByEmployeeName(String empName, Pageable pageable);
英文:

You could modify existing query with order by clause and pass Pageable, e.g. here is the example of sorting by employee name:

@Query(&quot;select emp.id,emp.name,p.name from employee emp join person p on emp.personid=p.id order by emp.name&quot;)
Page&lt;YourDto&gt; findAllOrderByEmployyNamePersonName(Pageable pageable);

For filtering by employee name just add where clause:

@Query(&quot;select emp.id,emp.name,p.name from employee emp join person p on emp.personid=p.id where emp.name = :empName&quot;)
Page&lt;YourDto&gt; findByEmployeeName(String empName, Pageable pageable);

huangapple
  • 本文由 发表于 2023年6月1日 17:49:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/76380651.html
匿名

发表评论

匿名网友

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

确定