Adding a parameter to a native query in Spring Boot causes "org.hibernate.exception.SQLGrammarE-CodePudding

I've created a native query in a Spring Boot project which works fine when there is no parameter, as follows:

@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();

However, when I add a where clause with a single parameter to it, it gives a mysql grammar exception.

Here's the new query with the WHERE clause and the parameter:

@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);

I know the raw SQL works with the where clause because I can run it in MySql Workbench.

I also know that the parameter is correctly getting through because I see it in the debugger and it's showing up on the log as well.

I've tried both the named and index variable and get the same result each time.

Any idea what the problem is?

This is the error being printed in the log:

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)

CodePudding user response：

seems you missed a space after d.id in this line "ON b.device_number = d.id", change it to "ON b.device_number = d.id " and test