springhibernatejpanative-sql

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

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

}

Solution

  • 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.