我是 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"; }任何人都可以在這方面幫助我嗎？請？
3 回答
月關寶盒
TA貢獻1772條經驗 獲得超5個贊
不知道如何，但下面的代碼對我有用
@Query("select a from MLFM_ORDER_OWNER a 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")
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(value = "active", required = false) String active, @RequestParam("businessType") Long typeId
) {
if(typeId==0)
typeId=null;
if(countryId==0)
countryId=null;
model.addAttribute("ownerList",ownerRepository.findOwnerDetails(typeId, countryId, name, code, status));
return "data_list";
}
湖上湖
TA貢獻2003條經驗 獲得超2個贊
JPQL 不支持可選參數。在 JPQL 中沒有簡單的方法可以做到這一點。您將不得不使用OR運算符編寫多個WHERE子句。
PS：您可能希望針對您的用例查看Query by Example。它支持處理空參數。
楊魅力
TA貢獻1811條經驗 獲得超6個贊
現在回答為時已晚，但對于任何尋找解決方案的人來說，還有一種更簡單的方法如下：
在我的情況下，我的控制器是這樣的：
@RestController
@RequestMapping("/order")
public class OrderController {
private final IOrderService service;
public OrderController(IOrderService service) {
this.service = service;
}
@RequestMapping(value = "/{username}/", method = RequestMethod.GET)
public ResponseEntity<ListResponse<UserOrdersResponse>> getUserOrders(
@RequestHeader Map<String, String> requestHeaders,
@RequestParam(required=false) Long id,
@RequestParam(required=false) Long flags,
@RequestParam(required=true) Long offset,
@RequestParam(required=true) Long length) {
// Return successful response
return new ResponseEntity<>(service.getUserOrders(requestDTO), HttpStatus.OK);
}
}
正如你所看到的，我有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)" +
" from User u join Orders o on u.id = o.user.id where u.userName = :username" +
" 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.
添加回答
舉報
0/150
提交
取消