網上查了很多資料都是下面的方法,但是不適用于Oracle

@Query(value = "select * from xxx where if(?1 !='',x1=?1,1=1) and if(?2 !='',x2=?2,1=1)" +

"and if(?3 !='',x3=?3,1=1) ?",nativeQuery = true)

List find(String X1,String X2,String X3);

————————————————

版權聲明：本文為CSDN博主「小碼蟻啊」的原創文章，遵循 CC 4.0 BY-SA 版權協議，轉載請附上原文出處鏈接及本聲明。

原文鏈接：https://blog.csdn.net/qq_36802726/article/details/81208853

----------------------------------------------

以下是Oracle中的方法,在service層對條件做判定

String equityLever;

if (StringUtils.isEmpty(params.get("equityLevel"))){

equityLever = null;

}else {

equityLever = String.valueOf(params.get("equityLevel"));

}

return commodityRepository.findAllByLever(equityLever);

@Query(value = "SELECT t.equityType FROM Commodity t where t.isDelete = 0 " +

"and t.shelfStatus = 2 " +

"and t.equityLevel like concat(concat('%',nvl(?1,null)),'%') " +

"GROUP BY t.equityType")

List findAllByLever(@Param("equityLevel") String equityLevel);

或者 加上nativeQuery =?true ,寫原生sql

@Query(value = "SELECT t.equity_type FROM EQUITY_COMMODITY t where t.IS_DELETE = 0 " +

"and t.SHELF_STATUS = 2 " +

"and t.EQUITY_LEVEL like concat(concat('%',nvl(?1,null)),'%') " +

"GROUP BY t.EQUITY_TYPE",nativeQuery = true)

List findAllByLever(@Param("equityLevel") String equityLevel);

如果你要分頁,可能要重寫count語句,我這邊遇到加上pageable對象時count語句有問題的時候,看下面的sql

@Query(value = "SELECT ID,CUST_ID,LEGAL_RIGHTS_INST_NBR,STATUS_CD,REQ_TYPE,ACCEPT_SEQ_NO,PROD_INST_ID,OPEN_STATUS,IS_OPEN,LAN_ID,IS_SETTLEMENT,MQ_INFO, CREATED_AT FROM EQUITY_REQUEST_LOG " +"WHERE 1=1 " +"AND CUST_ID = nvl(?1,null) " +"AND LEGAL_RIGHTS_INST_NBR = nvl(?2,null) " +"AND STATUS_CD = nvl(?3,null) " +"ORDER BY ?#{#pageable} ",countQuery = "SELECT count (ID) FROM EQUITY_REQUEST_LOG " +"WHERE 1=1 " +"AND CUST_ID = nvl(?1,'') " +"AND LEGAL_RIGHTS_INST_NBR = nvl(?2,null) " +"AND STATUS_CD = nvl(?3,null)",nativeQuery = true)Page findAllByParams(String custId,String legalRightsInstNbr,String statusCd, Pageable pageable);

這樣寫的限制是你的入參必須是是字符串類型的.我試過用number和date類型.但是為null的時候查詢會報錯數據類型異常.暫時沒找到解決方案.希望大佬們可以一起探討一下.

個人覺得?jpa?普通用用還是可以的.但是要到復雜sql?或者?自定義反饋對象的時候用起來,不如mybatis順手.僅僅是個人感覺哈