6

I am doing some data analysis and I have a SQL query that uses SELECT ..., @rownum:=@rownum+1 as row_number, ... to get the row number along with the results. However when I put this into a native query on my JPA method (using Spring Boot / Spring Data JPA), I get the following exception:

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: Space is not allowed after parameter prefix ':'

I have added @Query(nativeQuery = true, to my method, and have changed my parameters from things like where first_name = :firstname to where first_name = ?1 and removed the @Param annotation from my method parameters, but it hasn't helped.

Is there a way to escape the colon in this query, or some other way of assigning the rownum variable that doesn't use a colon?

Matt
  • 3,303
  • 5
  • 31
  • 53
  • try to use `"SELECT ..., @rownum\\:=@rownum+1 as row_number, ... "` – Youcef LAIDANI Jun 04 '18 at 10:35
  • Doh the JPA stacktrace was so huge it didn't even occur to me to try the simplest of Java escaping, that fixed it thanks! If you make it an answer I'll accept it :) – Matt Jun 04 '18 at 10:39

1 Answers1

8

To solve this issue you need to escape the two dots with double backslash \\: 

"SELECT ..., @rownum\\:=@rownum+1 as row_number, ... "
                    ^^^
Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140