しおしお

IntelliJ IDEAのことなんかを書いてます

古のspring-data-jpaでNativeQueryとPageableを使ってハマった件

Spring Boot 1.5系のバッチ的な処理に急ぎ手を入れる必要があって、RepositoryにNativeQueryでページングするようなメソッド追加したら謎のエラーでめっちゃハマったお話です。*1

Spring Bootバージョン

1.5系の古いやつですね。過去の遺産じゃないかぎり使うことはないですね…

  id 'org.springframework.boot' version '1.5.22.RELEASE'

Repositoryの実装

Repositoryでは、NativeQueryを使ってページングさせるようなメソッドを定義しています。

@Query(
        nativeQuery = true,
        value = "select * from users ",
        countQuery = "select count(*) from users"
)
Page<UsersEntity> find(Pageable pageable);

動作検証用のコード

動作検証用にこんな感じのテストコードを書いています。

@Test
public void test() throws Exception {
    final Page<UsersEntity> result = sut.find(new PageRequest(0, 10));
    System.out.println("result = " + result);
}

実行結果

実行すると、InvalidJpaQueryMethodExceptionが発生してしまいます。

Caused by: org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting and/or pagination in method public abstract org.springframework.data.domain.Page siosio.datajpaexample.repository.UsersRepository.find(org.springframework.data.domain.Pageable)
    at org.springframework.data.jpa.repository.query.NativeJpaQuery.<init>(NativeJpaQuery.java:58)

例外が発生している、NativeJpaQueryの実装(↓)を見てみるとPageableパラメータを持つ場合には#pageableという文字列がSQL内にないとダメなようです。

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を追加して再実行してみよう

Repositoryの実装を修正して、SQL#pageableを追加してみます。

@Query(
        nativeQuery = true,
        value = "select * from users #pageable",
        countQuery = "select count(*) from users"
)
Page<UsersEntity> find(Pageable pageable);

対応後でも残念ながら別のエラーで落ちてしまいましたね…SQLを実行するところまではいっているようなのでどんなSQLを実行しようとしているのか見てみましょう。

could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query
org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:333)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)

まさかのSQL内の#pageableが残ったままになってますね(´・ω・`) f:id:sioiri:20190817064721p:plain

エラーの回避方法

Spring Data JPA @Query | Baeldung4.3. Spring Data JPA Versions Prior to 2.0.4に回避方法が書いてありますね。
どうやら、Spring Data JPA 2.0.4より前の場合には、バグ?的なものがあるようです。

リンク先の回避方法を真似て、#pageableSQLコメントとして書いてあげます。大事なのは、#pageableの前後に改行を入れることですね。 改行忘れると、SQLの最後にくっつくlimitまでコメントになってしまいます。

@Query(
        nativeQuery = true,
        value = "select * from users \n -- #pageable \n",
        countQuery = "select count(*) from users"
)
Page<UsersEntity> find(Pageable pageable);

これで正常に実行できるようになりました。

result = Page 2 of 1 containing UNKNOWN instances

Spring Data JPAを2.0.4以降にしてみると

最初に#pageableの実装がないよと例外投げてたNativeJpaQueryから#pageableに関する実装が消えていますね。

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

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

    Parameters<?, ?> parameters = method.getParameters();

    if (parameters.hasSortParameter() && !queryString.contains("#sort")) {
        throw new InvalidJpaQueryMethodException(
                "Cannot use native queries with dynamic sorting in method " + method);
    }
    this.resultType = getTypeToQueryFor();
}

ということで、Repositoryの実装はこんな感じで#pageableなしで書けるようになります。

@Query(
        nativeQuery = true,
        value = "select * from users",
        countQuery = "select count(*) from users"
)
Page<UsersEntity> find(Pageable pageable);

#pageableは消えたけど、#sortに関する実装がまだ残っているのでどうなるのか見てみます。 Repositoryの実装をソートのみに変えてみます。SQLには、#sortを含めてあげます。

@Query(
        nativeQuery = true,
        value = "select * from users #sort"
)
List<UsersEntity> find(Sort sort);

エラーにならず実行できました。#sortに関する処理は正しく動くようです。

result = [siosio.datajpaexample.entity.UsersEntity@6dff619a, siosio.datajpaexample.entity.UsersEntity@3f73d455, siosio.datajpaexample.entity.UsersEntity@24df2d20]

まとめ

バージョンアップして幸せになりたい。

おわり。

*1:色々と制約があってこうするしかなかった…