2

I'm trying to execute query , but got Resolved exception caused by handler execution: org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.QueryException: Space is not allowed after parameter prefix ':' . I done as advised here How can I use MySQL assign operator(:=) in hibernate native query? and here : Hibernate exception on encountering mysql := operator But same. hibernate version 5.2.17.Final

ClientRepository.java

@Repository
public interface ClientRepository extends JpaRepository<Client, Long>, JpaSpecificationExecutor<Client> {
@Query( value = "select * from client where center_id in\n" +
    "(select  id  from    (select * from center  order by parent_center_id, id) center_sorted,  (select @pv=:centerId) initialisation\n" +
    "where   find_in_set(parent_center_id, @pv) and  length(@pv:=concat(@pv, ',', id))) or center_id=:centerId;" ,nativeQuery = true)

Page<Client> findAllByCenterId(@Param("centerId") Long centerId, Pageable pageable) ;

}

art74
  • 57
  • 1
  • 11
  • To be honest you have a mixture of the parameters and raw fields. Could you separate the query at first and clean up the whole statement? Thank you – Echoinacup Jan 09 '19 at 19:18

1 Answers1

4

Formerly, when using the assignment operator in Native Query, Hibernate threw an exception.Hibernate supports escaping the colon char not to treat it as a parameter. So, You need to escape with a backslash. : "\\:="

Note that no spaces are allowed before and after the reference placeholder.

Abdel
  • 582
  • 4
  • 6
  • I did : @Query( value = "select * from client where center_id in\n" + "(select id from (select * from center order by parent_center_id, id) center_sorted, (select @pv = :centerId) initialisation\n" + "where find_in_set(parent_center_id, @pv) and length(@pv\:= concat(@pv, ',', id))) or center_id = :centerId;" ,nativeQuery = true) and now i got : Compilation failure [ERROR] /home/src/main/java/com/mycompany/hiptest/repository/ClientRepository.java:[24,69] illegal escape character – art74 Jan 09 '19 at 20:33
  • I try more easy query : @Query( value = "select * from client where center_id = :centerId " ,nativeQuery = true) It's worked. – art74 Jan 09 '19 at 20:57
  • No. You should escape "\:=" in final SQL string passed to Hibernate and if script is dynamically prepared in Java code - with four slashes in `mySqlString.replaceAll(":=", "\\\\:=")`. Double escaping - for Hibernate and for Java. – Zon Dec 03 '20 at 19:23