Spring data jpa 使用@Query分页查询

原本没有想过要用@Query来查询的，毕竟JpaRepository提供的方法已经基本够用了。但是今天这个查询用sql的前缀匹配后缀匹配都不行，然后看到了ExampleMatcher里的StringMatcher：

public static enum StringMatcher {

        /**
         * Store specific default.
         */
        DEFAULT,
        /**
         * Matches the exact string
         */
        EXACT,
        /**
         * Matches string starting with pattern
         */
        STARTING,
        /**
         * Matches string ending with pattern
         */
        ENDING,
        /**
         * Matches string containing pattern
         */
        CONTAINING,
        /**
         * Treats strings as regular expression patterns
         */
        REGEX;

    }

有正则REGEX，我以为这样就很好办了（讲道理sql用正则真的非常慢）。然而事情并不是这么简单。因为会报一个异常Unsupported StringMatcher REGEX。
上面的枚举时有REGEX，但是QueryByExamplePredicateBuilder类的这部分代码:

switch (exampleAccessor.getStringMatcherForPath(currentPath)) {

                    case DEFAULT:
                    case EXACT:
                        predicates.add(cb.equal(expression, attributeValue));
                        break;
                    case CONTAINING:
                        predicates.add(cb.like(expression, "%" + attributeValue + "%"));
                        break;
                    case STARTING:
                        predicates.add(cb.like(expression, attributeValue + "%"));
                        break;
                    case ENDING:
                        predicates.add(cb.like(expression, "%" + attributeValue));
                        break;
                    default:
                        throw new IllegalArgumentException(
                                "Unsupported StringMatcher " + exampleAccessor.getStringMatcherForPath(currentPath));
                }

可以看到并没有REGEX，这是什么骚操作。

所以，我打算使用@Query。在官网文档找到如下示例：

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

按照示例写好，然后一运行。非常好，报错了Cannot use native queries with dynamic sorting and/or pagination in method...
看下报错地方的代码：

public NativeJpaQuery(JpaQueryMethod method, EntityManager em, String queryString,
            EvaluationContextProvider evaluationContextProvider, SpelExpressionParser parser) {

        super(method, em, queryString, evaluationContextProvider, parser);

        Parameters<?, ?> 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);
        }
    }

从这段代码里看出，报异常的原因时我们的sql里没有#pageable
所以，加上这个就好了，代码如下：

public interface UserRepository extends JpaRepository<User, Long> {

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

两个\n是给sql换行的，避免#pageable对我们的sql产生影响。