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产生影响。