Spring boot：Query方法中的可选参数查询

Question

我是 Spring Boot 和休眠的新手。在这里，我正在尝试运行基于搜索的可选参数查询，我可以在其中按名称、国家/地区等进行搜索。如果我将此字段保留为空，则查询应全部列出。但问题是我的方法是返回所有数据而忽略我的搜索参数。我的模型课看起来像

@Entity(name="MLFM_ORDER_OWNER")

public class ModelOrderOwner {

    @Id @GenericGenerator(name = "custom_sequence", strategy = 

            "com.biziitech.mlfm.IdGenerator")

    @GeneratedValue(generator = "custom_sequence")

    @Column(name="ORDER_OWNER_ID")

    private Long orderOwnerId;

    @Column(name="OWNER_NAME")

    private String ownerName;

    @OneToOne

    @JoinColumn(name="BUSINESS_TYPE_ID")

    private ModelBusinessType businessTypeId;

    @Column(name="SHORT_CODE")

    private String shortCode;

    @ManyToOne

    @JoinColumn(name="OWNER_COUNTRY")

    private ModelCountry ownerCountry;

// getter setter..

我的存储库界面看起来像

public interface OrderOwnerRepository extends 

    JpaRepository<ModelOrderOwner,Long>{

        @Query("select a from MLFM_ORDER_OWNER a where a.businessTypeId.typeId=coalsec(:typeId,a.businessTypeId.typeId) and a.ownerCountry.countryId=coalsec(:countryId,a.ownerCountry.countryId) and a.ownerName LIKE %:name and a.shortCode LIKE %:code")

        public List <ModelOrderOwner> findOwnerDetails(@Param("typeId")Long typeId,@Param("countryId")Long countryId,@Param("name")String name,@Param("code")String code);

    }

这是我在控制器中的方法

@RequestMapping(path="/owners/search")

     public String getAllOwner(Model model,@RequestParam("owner_name") String name,@RequestParam("shortCode") String code,

                            @RequestParam("phoneNumber") String phoneNumber,@RequestParam("countryName") Long countryId,

                            @RequestParam("businessType") Long typeId

             ) {

 model.addAttribute("ownerList",ownerRepository.findOwnerDetails(typeId, countryId, name, code));

            return "data_list";

    }

任何人都可以在这方面帮助我吗？请？

Answer 1

不知道如何，但下面的代码对我有用@Query("select a from MLFM_ORDER_OWNER a&nbsp; where a.businessTypeId.typeId=COALESCE(:typeId,a.businessTypeId.typeId) and a.ownerCountry.countryId=COALESCE(:countryId,a.ownerCountry.countryId) and a.ownerName LIKE %:name and a.shortCode LIKE %:code")&nbsp; &nbsp; public List <ModelOrderOwner> findOwnerDetails(@Param("typeId")Long typeId,@Param("countryId")Long countryId,@Param("name")String name,@Param("code")String code);&nbsp;并在控制器中@RequestMapping(path="/owners/search")&nbsp; &nbsp; &nbsp;public String getAllOwner(Model model,@RequestParam("owner_name") String name,@RequestParam("shortCode") String code,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @RequestParam("phoneNumber") String phoneNumber,@RequestParam("countryName") Long countryId,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @RequestParam(value = "active", required = false) String active, @RequestParam("businessType") Long typeId&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;) {&nbsp; &nbsp; &nbsp; &nbsp; if(typeId==0)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; typeId=null;&nbsp; &nbsp; &nbsp; &nbsp; if(countryId==0)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; countryId=null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;model.addAttribute("ownerList",ownerRepository.findOwnerDetails(typeId, countryId, name, code, status));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return "data_list";&nbsp; &nbsp; }

Answer 2

JPQL 不支持可选参数。在 JPQL 中没有简单的方法可以做到这一点。您将不得不使用OR运算符编写多个WHERE子句。请参阅类似问题的这些答案：答案 1和答案 2PS：您可能希望针对您的用例查看Query by Example。它支持处理空参数。

Answer 3

现在回答为时已晚，但对于任何寻找解决方案的人来说，还有一种更简单的方法如下：在我的情况下，我的控制器是这样的：@RestController@RequestMapping("/order")public class OrderController {&nbsp; &nbsp; private final IOrderService service;&nbsp; &nbsp; public OrderController(IOrderService service) {&nbsp; &nbsp; &nbsp; &nbsp; this.service = service;&nbsp; &nbsp; }&nbsp; &nbsp; @RequestMapping(value = "/{username}/", method = RequestMethod.GET)&nbsp; &nbsp; public ResponseEntity<ListResponse<UserOrdersResponse>> getUserOrders(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @RequestHeader Map<String, String> requestHeaders,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @RequestParam(required=false) Long id,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @RequestParam(required=false) Long flags,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @RequestParam(required=true) Long offset,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @RequestParam(required=true) Long length) {&nbsp; &nbsp; &nbsp; &nbsp; // Return successful response&nbsp; &nbsp; &nbsp; &nbsp; return new ResponseEntity<>(service.getUserOrders(requestDTO), HttpStatus.OK);&nbsp; &nbsp; }}正如你所看到的，我有Username作为@PathVariable和length和offset这是我所需要的参数，但我接受id并flags用于过滤的搜索结果，所以他们都是我的可选参数，并没有必要调用REST服务。现在在我的存储库层中，我刚刚创建了@Query如下：@Query("select new com.ada.bourse.wealth.services.models.response.UserOrdersResponse(FIELDS ARE DELETED TO BECOME MORE READABLE)" +&nbsp; &nbsp; &nbsp; &nbsp; " from User u join Orders o on u.id = o.user.id where u.userName = :username" +&nbsp; &nbsp; &nbsp; &nbsp; " and (:orderId is null or o.id = :orderId) and (:flag is null or o.flags = :flag)")Page<UserOrdersResponse> findUsersOrders(String username, Long orderId, Long flag, Pageable page);就是这样，你可以看到，我检查了我的可选参数与(:orderId is null or o.id = :orderId)和(:flag is null or o.flags = :flag)，我认为它需要强调的是，我检查了我的说法与is null条件不是我的列中的数据，因此，如果客户端发送Id和flags对我的参数，我会筛选与他们的结果否则我只是查询username哪个是我的@PathVariable.