org.hibernate.QueryException: Space is not allowed after parameter prefix ':'

Question

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

}

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
– Echoinacup, Commented Jan 9, 2019 at 19:18

Abdel · Accepted Answer · 2019-01-09 20:28:43Z

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.

answered Jan 9, 2019 at 20:28

Abdel

5804 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

art74 Over a year ago

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 Over a year ago

I try more easy query : @Query( value = "select * from client where center_id = :centerId " ,nativeQuery = true) It's worked.

Zon Over a year ago

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.

Collectives™ on Stack Overflow

org.hibernate.QueryException: Space is not allowed after parameter prefix ':'

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related