英文:
Filter api by multiple optional parameters
问题
我正在尝试使用多个可选参数来筛选存储库方法。但是我没有得到预期的结果。
以下是我的查询语句。
这里的访客实体包含多次访问，一次访问可以有一个联系人和一个时间段。
谢谢帮助。
@Query("select v from Visitor v join v.visits visits join visits.contactPerson cp where "
+ "v.firstName=:firstName or :firstName is NULL or :firstName = '' and "
+ "visits.approvalStatus=:approvalStatus or :approvalStatus is NULL or :approvalStatus = '' "
+ "and cp.firstName=:firstName or :firstName is NULL or :firstName = '' ")
public List<Visitor> findByFilter(@Param("firstName") String firstName,
@Param("approvalStatus") String approvalStatus, @Param("firstName") String fName);
英文:
I am trying to filter repository method using multiple optional parameters. But I am not getting expected result.
Here is my query.
Here visitor entity contains multiple visits and one visits can have one contact person and one timeslot.
Thanks for the help
@Query("select v from Visitor v join v.visits visits join visits.contactPerson cp where "
+ "v.firstName=:firstName or :firstName is NULL or :firstName = '' and "
+ "visits.approvalStatus=:approvalStatus or :approvalStatus is NULL or :approvalStatus = '' "
+ "and cp.firstName=:firstName or :firstName is NULL or :firstName = '' ")
public List<Visitor> findByFilter(@Param("firstName") String firstName,
@Param("approvalStatus") String approvalStatus, @Param("firstName") String fName);
答案1
得分: 2
你需要添加括号，以便数据库能够按照你的意图理解你的查询：
+ "(v.firstName=:firstName 或者 :firstName 为 NULL 或者 :firstName = '') 并且 "
+ "(visits.approvalStatus=:approvalStatus 或者 :approvalStatus 为 NULL 或者 :approvalStatus = '') "
+ "并且 (cp.firstName=:firstName 或者 :firstName 为 NULL 或者 :firstName = '')"
OR 运算符的优先级低于 AND，所以 a OR b AND c OR d 被解析为 a OR (b AND c) OR d，而不是 (a OR b) AND (c OR d)。
英文:
You need to add parentheses to make the database understand your query the way you intend:
+ "(v.firstName=:firstName or :firstName is NULL or :firstName = '') and "
+ "(visits.approvalStatus=:approvalStatus or :approvalStatus is NULL or :approvalStatus = '') "
+ "and (cp.firstName=:firstName or :firstName is NULL or :firstName = '')"
The OR operator has a lower precedence than AND so a OR b AND c OR d is parsed as a OR (b AND c) OR d, not as (a OR b) AND (c OR d).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论