官方给出的自定义分页查询的示例是这样的：
1
2
3
4
5
6
| 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
然后实际执行时报错：
1
| org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException
|
这是因为在使用NativeQuery时，会有这样一个验证：
1
2
3
4
5
6
7
8
9
| 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语句中必须包含
参考了众多解决方案都无效。例如
1
2
| ?#{#pageable}
\n#pageable\
|
https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination
最终发现，其实只需要将#pageable合理的作为注释包含在SQL里即可。
我使用的Mysql，最终解决方案为：
1
2
3
4
5
6
| 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，在新版本中应该此问题已经被修复了。
