解決springdataJPA對原生sql支持的問題

Posted on by

springdataJPA對原生sql支持問題

在項目中用到的是springdataJPA連接數據庫進行操作，但是JPA中的hql語句不能夠滿足業務要求，因而需要用到原生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是別名

後查看項目啟動查詢數據調用的sql為：

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 )

這裡就發現瞭問題所在，在項目執行sql的時候，查詢的是count(ppd)，查詢文檔得知默認情況下，jpa會在執行查詢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參數

解決問題~

Spring Data JPA 寫原生sql語句

在使用 Spring Data JPA 的時候，通常我們隻需要繼承 JpaRepository 就能獲得大部分常用的增刪改查的方法。有時候我們需要自定義一些查詢方法，可以寫自定義 HQL 語句

但是在使用 Spring Data JPA 的時候，通常我們隻需要繼承 JpaRepository 就能獲得大部分常用的增刪改查的方法。有時候我們需要自定義一些查詢方法，可以寫自定義 HQL 語句

@Query(value = "自定義sql語句", nativeQuery = true)
List<Long> findFriendsByUserId(Long userId);

如上，隻需在查詢語句後邊加上nativeQuery = true 就可以瞭

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：