记一个Spring Data JPA自定义分页查询BUG

官方给出的自定义分页查询的示例是这样的：

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

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query

然后实际执行时报错：

org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException

这是因为在使用NativeQuery时，会有这样一个验证：

public NativeJpaQuery(JpaQueryMethod method, EntityManager em, String queryString, EvaluationContextProvider evaluationContextProvider, SpelExpressionParser parser) {
   super(method, em, queryString, evaluationContextProvider, parser);
   JpaParameters parameters = method.getParameters();
   boolean hasPagingOrSortingParameter = parameters.hasPageableParameter() || parameters.hasSortParameter();
   boolean containsPageableOrSortInQueryExpression = queryString.contains("#pageable") || queryString.contains("#sort");
   if(hasPagingOrSortingParameter && !containsPageableOrSortInQueryExpression) {
       throw new InvalidJpaQueryMethodException("Cannot use native queries with dynamic sorting and/or pagination in method " + method);
   }
}

当参数中包含Pageable或者Sort时，SQL语句中必须包含#pageable，#sort。

参考了众多解决方案都无效。例如?#{#pageable}，\n#pageable\。
https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination

最终发现，其实只需要将#pageable合理的作为注释包含在SQL里即可。
我使用的Mysql，最终解决方案为：

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 /*#pageable*/",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

我使用的是Spring boot 1.5.4，在新版本中应该此问题已经被修复了。