您好,登錄后才能下訂單哦!
這篇文章主要為大家展示了“怎么解決springdataJPA對原生sql支持的問題”,內(nèi)容簡而易懂,條理清晰,希望能夠幫助大家解決疑惑,下面讓小編帶領大家一起研究并學習一下“怎么解決springdataJPA對原生sql支持的問題”這篇文章吧。
在項目中用到的是springdataJPA連接數(shù)據(jù)庫進行操作,但是JPA中的hql語句不能夠滿足業(yè)務要求,因而需要用到原生sql
@Query(value = "SELECT ppd.* FROM zt_productionplandetails AS ppd \n" + " \tLEFT JOIN zt_salesplan sp ON sp.id=ppd.salesPlan_id \n" + " \tLEFT JOIN zt_employee e ON e.id=ppd.employeeId\n" + " \tWHERE ppd.enabled = TRUE \n" + " \tAND ppd.`status`=1 \n" + "\tAND IF(:clientName !='', sp.clientName LIKE %:clientName%, 1 = 1 )\n" + " \tAND IF( :productName !='', sp.productName LIKE %:productName%, 1 = 1 )\n" + " \tAND IF( :empName != '', e.name LIKE %:empName%, 1 = 1 )\n" + " \tAND IF( :startDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) >=:startDate, 1 = 1 )\n" + " \tAND IF( :endDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) <=:endDate, 1 = 1 )" ,nativeQuery = true) Page<ProductionPlanDetails> findNewPlan(@Param("productName") String productName, @Param("empName") String empName, @Param("endDate") String endDate, @Param("startDate") String startDate, @Param("clientName") String clientName, Pageable pageable);
在用這個sql的時候,會報錯
java.sql.SQLSyntaxErrorException: Unknown column 'ppd' in 'field list'
意思就是找不到ppd的字段,可是這里明顯可以看出ppd是別名
select count(ppd) FROM zt_productionplandetails AS ppd LEFT JOIN zt_salesplan sp ON sp.id=ppd.salesPlan_id LEFT JOIN zt_employee e ON e.id=ppd.employeeId WHERE ppd.enabled = TRUE AND ppd.`status`=1 AND IF(? !='', sp.clientName LIKE ?, 1 = 1 ) AND IF( ? !='', sp.productName LIKE ?, 1 = 1 ) AND IF( ? != '', e.name LIKE ?, 1 = 1 ) AND IF( ? != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) >=?, 1 = 1 ) AND IF( ? != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) <=?, 1 = 1 )
這里就發(fā)現(xiàn)了問題所在,在項目執(zhí)行sql的時候,查詢的是count(ppd),查詢文檔得知默認情況下,jpa會在執(zhí)行查詢sql的時候會加上count()
@Query(value = "SELECT ppd.* FROM zt_productionplandetails AS ppd \n" + " \tLEFT JOIN zt_salesplan sp ON sp.id=ppd.salesPlan_id \n" + " \tLEFT JOIN zt_employee e ON e.id=ppd.employeeId\n" + " \tWHERE ppd.enabled = TRUE \n" + " \tAND ppd.`status`=1 \n" + "\tAND IF(:clientName !='', sp.clientName LIKE %:clientName%, 1 = 1 )\n" + " \tAND IF( :productName !='', sp.productName LIKE %:productName%, 1 = 1 )\n" + " \tAND IF( :empName != '', e.name LIKE %:empName%, 1 = 1 )\n" + " \tAND IF( :startDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) >=:startDate, 1 = 1 )\n" + " \tAND IF( :endDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) <=:endDate, 1 = 1 )", countQuery = "SELECT count(*) FROM zt_productionplandetails AS ppd \n" + " \tLEFT JOIN zt_salesplan sp ON sp.id=ppd.salesPlan_id \n" + " \tLEFT JOIN zt_employee e ON e.id=ppd.employeeId\n" + " \tWHERE ppd.enabled = TRUE \n" + " \tAND ppd.`status`=1 \n" + "\tAND IF(:clientName !='', sp.clientName LIKE %:clientName%, 1 = 1 )\n" + " \tAND IF( :productName !='', sp.productName LIKE %:productName%, 1 = 1 )\n" + " \tAND IF( :empName != '', e.name LIKE %:empName%, 1 = 1 )\n" + " \tAND IF( :startDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) >=:startDate, 1 = 1 )\n" + " \tAND IF( :endDate != '', DATE_FORMAT(ppd.createDate, '%Y-%m-%d %k:%i:%s' ) <=:endDate, 1 = 1 )" ,nativeQuery = true)
加上countQuery參數(shù)
解決問題~
在使用 Spring Data JPA 的時候,通常我們只需要繼承 JpaRepository 就能獲得大部分常用的增刪改查的方法。有時候我們需要自定義一些查詢方法,可以寫自定義 HQL 語句
但是在使用 Spring Data JPA 的時候,通常我們只需要繼承 JpaRepository 就能獲得大部分常用的增刪改查的方法。有時候我們需要自定義一些查詢方法,可以寫自定義 HQL 語句
@Query(value = "自定義sql語句", nativeQuery = true) List<Long> findFriendsByUserId(Long userId);
如上,只需在查詢語句后邊加上nativeQuery = true 就可以了
以上是“怎么解決springdataJPA對原生sql支持的問題”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對大家有所幫助,如果還想學習更多知識,歡迎關(guān)注億速云行業(yè)資訊頻道!
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。