Required name for ParameterBinding not available with Pageable

Question

I'm trying to do a query, using mySql fullText index "match against" syntax with a paegable.

I changed the spring-boot version from 1.5.4.RELEASE to 2.0.0m7 to use spring-data-jpa version 2.0.2.RELEASE, because in the earlier spring-data-jpa version, a native query with a countQuery wasn't working.

I've fixed a couple of problems, using the solutions here and here.

The current problem is the following error:

javax.servlet.ServletException: org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query
...
Caused by: java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).

And here is the code

@Query( value = "SELECT Distinct u.* " +
    "FROM user AS u " +
    "WHERE MATCH(u.name, u.email) against(:filterValue) " +
    "ORDER BY u.id \n#pageable\n#",
    countQuery = "SELECT count(Distinct u.id) " +
    "FROM user AS u " +
    "WHERE MATCH(u.name, u.email) against(:filterValue) " +
    "ORDER BY u.id \n#pageable\n#",
    nativeQuery = true)
Page<User> foo(@Param("filterValue") String filterValue, Pageable pageable);

If I try to set pageable as a param too, the same error occurs.

Any thoughts?

No need to mention "pageable" in @Query. Spring will take it automatically if you have extended your repository class from PagingAndSortingRepository — Afridi, Jan 26 '18 at 13:06

score 0 · Answer 1 · answered Jan 26 '18 at 15:19

0

You don't have to include the pageable explicitly in your query.

See the example from the reference documentation.

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

I strongly recommend you upgrade to the current release version of Spring Data instead of the milestone build.

answered Jan 26 '18 at 15:19

Jens Schauder

77,657
34
181
348

Yes, but when I try to start the server without it I get the same error mentioned [here](https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination) – j2sb Jan 26 '18 at 16:47
Also, when I include the pageable explicity and use the placeholder `?1` in the query, it raises the error mentioned [here](https://stackoverflow.com/questions/47703096/crudrepository-exception-required-name-for-parameterbinding-not-available) – j2sb Jan 26 '18 at 16:53
Hmm ... I'll try to take a look at https://jira.spring.io/browse/DATAJPA-928 that sounds ugly. – Jens Schauder Jan 26 '18 at 18:35
I've just submitted a PR for DATAJPA-928. After that the above works as advertised. – Jens Schauder Jan 30 '18 at 06:37

score -1 · Answer 2 · answered Jan 26 '18 at 13:04

-1

Try to put Pageable on first place:

Page<User> foo(Pageable pageable, @Param("filterValue") String filterValue);

answered Jan 26 '18 at 13:04

Den B

843
1
10
26

Required name for ParameterBinding not available with Pageable

2 Answers2