我在 Spring Boot 專案中創建了一個原生查詢，當沒有引數時它可以正常作業，如下所示：
@Query(nativeQuery = true, value="SELECT system_name as systemName, norcaType as norcaType, "
"device_number as deviceNumber, feature_vector as featureVector, code as norcaCode, count(*) as sum "
"FROM (SELECT a.id, a.object_id, a.system_name, b.norca_type AS norcaType, device_number, "
"b.feature_vector, a.seqnb, a.object_index, c.code "
"FROM system_objectdata a "
"JOIN sick_il_dacq.system_barcode_norca b "
"ON a.id = b.system_objectdata_id "
"AND a.partition_key = b.partition_key "
"JOIN system_feature_vector c "
"ON b.feature_vector = c.id "
"JOIN sick_il_services.system_device d "
"ON b.device_number = d.id) detail "
"GROUP BY system_name, device_number, feature_vector")
public List<INorcaSummarySystemDeviceDTO> getNorcaSummaryBySystemAndDeviceAllSystems();
但是，當我添加一個帶有單個引數的 where 子句時，它會給出一個 mysql 語法例外。
這是帶有 WHERE 子句和引數的新查詢：
@Query(nativeQuery = true, value="SELECT system_name as systemName, norcaType as norcaType, "
"device_number as deviceNumber, feature_vector as featureVector, code as norcaCode, count(*) as sum "
"FROM (SELECT a.id, a.object_id, a.system_name, b.norca_type AS norcaType, device_number, "
"b.feature_vector, a.seqnb, a.object_index, c.code "
"FROM system_objectdata a "
"JOIN sick_il_dacq.system_barcode_norca b "
"ON a.id = b.system_objectdata_id "
"AND a.partition_key = b.partition_key "
"JOIN system_feature_vector c "
"ON b.feature_vector = c.id "
"JOIN sick_il_services.system_device d "
"ON b.device_number = d.id"
"and a.system_name = ?1 ) detail "
"GROUP BY system_name, device_number, feature_vector")
public List<INorcaSummarySystemDeviceDTO> getNorcaSummaryBySystemAndDeviceOneSystem(String systemName);
我知道原始 SQL 與 where 子句一起使用，因為我可以在 MySql Workbench 中運行它。
我也知道引數正確通過，因為我在除錯器中看到它并且它也顯示在日志中。
我已經嘗試了命名變數和索引變數，每次都得到相同的結果。
知道問題是什么嗎？
這是在日志中列印的錯誤：
2021/12/21 17:25:47.799 | ERROR | http-nio-8181-exec-6 | o.a.c.c.C.[.[.[.[dispatcherServlet]] | Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a.system_name = '02') detail GROUP BY system_name, device_number, feature_vector' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1826)
uj5u.com熱心網友回復：
似乎您在“ON b.device_number = d.id”這一行中的 d.id 后遺漏了一個空格，將其更改為“ON b.device_number = d.id”并進行測驗
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/390074.html