Zenn
Nstock Tech BlogPublicationへの投稿
📄

Spring Data JDBCでJOINした複数テーブルをページ・ソートする方法

ryan5500
2024/03/12に公開
Java
Spring Boot
tech

こんにちは。Nstockエンジニアのryan5500です。

今回は、私たちが利用しているSpring Data JDBCというORMで、複数テーブルに対してページング・ソートを行う方法についてご紹介します。

忙しい方向けの要点

やり方は主に２つあります。

  • RepositoryImplを用意し自前実装する
    • 記事内でサンプルコードを用意
  • DBで複数テーブルをJOINしたViewを用意し、それに紐付けたEntity, Repositoryを作る

背景 & 想定読者

私たちNstockはバックエンドにてJava 17 / Spring Boot2系 / PostgreSQLを利用してSaaSを開発しており、ORMとしてSpring Data JDBCを利用しています。

本記事の想定読者はSpring Data JDBCを利用されている方です。そのため、Spring Data JDBCの個々の機能の詳細説明は省きます。

Spring Boot3系についての注意

私達はSpring Boot 2系を利用して開発していたのですが、3.0系から、本記事で書いているPagingAndSortingRepositoryが、CrudRepositoryに統合されました。

本記事の内容は引き続き利用できますが、Spring Boot 3.0以降をご利用の読者は、PagingAndSortingRepositoryをextendsしている箇所はCrudRepositoryで対応できると読み替えて頂けますと幸いです。

私達は現時点では3系を利用しています。

Spring Data JDBCについて

Spring Data JDBCを利用する中で、シンプルで学習コストが低いORMという印象を受けています。

利用している中で助かっている機能としては以下があります。

  • CRUD処理が簡単に導入できる
  • 特定の命名規則に従ったメソッド定義をinterfaceに追加すると、SQLクエリや実装を書かずに、メソッドとして利用できる
    • e.g. findBy[カラム名] , findAllBy[カラム名]
  • 複雑な処理は @Query アノテーションでSQLを書ける
    • それ用のEntityを用意すればJOINも書ける
    • 引数でプレースホルダーを渡せる
  • ページング・ソートに対応できるRepositoryのinterfaceが用意されているため、簡単に導入できる

開発の初期フェーズにおいてはシンプルなモデルが多かった関係で、特に苦労なく利用できており、必要十分という印象でした。

課題: JOINしたテーブルへ、ページングを実現したい

しかし、開発を進めていく中で、対応が難しいケースに遭遇しました。それは、「JOINしたテーブルへ、ページングを実現したい」というものです。

具体例を上げると、以下のようなケースになります。

要件: 「 memberテーブルの一覧に member_purchase_summaryテーブルの結果を混ぜ込んで表示したい。さらにページングもしたい。」

member テーブルのイメージ

id name address
1 Alice 1-2-3 some blvd
2 Bob 2-1 where street

member_purchase_summary テーブルのイメージ

id member_id purchase_total_num
1 1 10
2 2 20

実現したい表(各カラムでソート & ページングしたい)

memberテーブルのname member_purchase_summaryテーブルのpurchase_total_num
Alice 10
Bob 20

解決策として、いくつかアイデアを検討しました。

@Queryアノテーションで書ける？

@Query アノテーションでSQLクエリが書けるので、それを応用すると Pageable 型のオブジェクトを引数に取る関数を用意し、Queryアノテーション内で展開できるのではないかと考えました。

Pageable 型は、ページングとソートに必要な情報をまとめたデータ構造と言えそうです。

具体的には、ページングのために表示するページ数pageNumberと1ページあたりのサイズpageSize を、ソートのためにソート対象カラムと降順・昇順を複数持つ Sort 型を保持しています。

Pageableを利用する場合は以下のようなコードで利用できます。

// Memberテーブルの内容をページングして返すControllerがあるとする

Integer page = 1; // ページ番号。実際にはクエリパラメータで指定するイメージ
Integer pageSize = 50; // ページサイズ。動的に変える場合はクエリパラメータで指定するイメージ

// idカラムの昇順でソートする
Sort sort = Sort.by(new Order(Sort.Direction.ASC, "id").nullsLast());

Pageable pageable = PageRequest.of(page, pageSize, sort);
// PagingAndSortingRepositoryを拡張するRepositoryは、findAll(Pageable)メソッドが生えている。
Page<MemberEntity> memberPage = memberRepository.findAll(pageable);

@Query アノテーションで書くというのは、以下のコードは実際には動きませんが、Repository側の実装はこのようになるイメージでした。

interface MemberRepository implements PagingAndSortingRepository<Integer, MemberEntity> {
    @Query("""
        SELECT
            m.name,
            member_purchase_summary.purchase_total_num
        FROM member m
        LEFT JOIN member_purchase_summary mps
            ON m.id = mps.member_id
        LIMIT :page.pageSize -- 実際には:page.pageSizeのように書くことはできない
        OFFSET :page.offset
    """)
    public List<MemberPurchaseSummaryEntity> findAll(Pageable page);
}

しかし @Query アノテーションで引数利用できるのはプリミティブな型のみであり、 Pageable 型の引数の中身を取り出してSQLクエリに差し込むことは難しいようでした。

PagingAndSortingRepositoryでなんとかできる？

単一のテーブルに対して、ページングやソートを提供する PagingAndSortingRepository があるので、このクラスのオプションでテーブルのJOINが実現できないかを調査しました。

ただ、オプションで対応する方法を見つけることはできませんでした。

JOINしたテーブルのページングだけ別のORMを使う？

Spring Data JDBC以外のORM、例えばSpring Data JPAを導入すれば対応できるのではないかという方法です。

ですが、複数のORMを1アプリ内に同居させるのは認知負荷が高くなりそうなので、採用しませんでした。

解決策

解決策は以下の２つありそうです。

  1. DBのViewを利用する
  2. RepositoryImplクラスを用意しカスタムクエリを書く

DBのViewを利用する

JOINしたテーブルを、DBのViewとして定義すれば、1テーブルとして表現できます。これを応用すると、JOINしたテーブルを @Table アノテーションでEntityに対応付けることができます。

そのEntityの結果を返すRepositoryは、 PagingAndSortingRepository を拡張すれば、ページングやソートを実現できます。

具体的には以下です。

// DBでは、以下のクエリでViewが用意されていることとする。
CREATE VIEW member_summary_view AS
SELECT
  m.id,
	m.name, 
	mps.purchase_total_num
FROM member m
LEFT OUTER JOIN member_purchase_summary mps
	ON mps.member_id = m.id;

// MemberPurchaseSummaryEntity.java
// Entityクラス
@Table("member_purchase_summary_view")　 // Viewを指定する
public class MemberPurchaseSummaryEntity {
}

// MemberPurchaseSummaryRepository.java
// Repositoryクラス
public class MemberPurchaseSummaryRepository extends PagingAndSortingRepository<MemberPurchaseSummaryEntity, Integer> {} // PagingAndSortingRepositoryを拡張する

この方式のメリットは、Spring Data JDBCに用意されている PagingAndSortingRepository を利用するので、困ったときの検索時にも情報を得やすく、認知負荷が小さくなりそうな点です。

一方デメリットは、Viewが利用できるかは各社の管理ポリシーによるため、常に選択肢として利用可能ではないことが想定されます。他にも、ページングを利用したいJOINテーブルごとにビューが増えてしまう点があります。

私たちもこの方法の動作検証はしたものの、Viewに対するRLSの対応状況から、この解決策を取ることはできませんでした。

具体的には、私達はサービスでRLSを利用しているのですが、当時利用していたPostgreSQLのv14ではViewに対してクエリする際、Viewの中で利用しているテーブルに対してRLSが効いている状態でクエリすることができなかったためです。

PostgreSQLのv15以降ではsecurity_invokerを設定することで利用できます。

RepositoryImplクラスを用意しカスタムクエリを書く

他の解決策を調べたところ、RepositoryImplクラスを用意するとカスタムクエリを書けることがわかりました。

これを用いて、JOINしたテーブルに対して Pageable 型のオブジェクトを引数に取って処理できるように実装を作りました。

一部コードを修正していますが、イメージとしては以下のようにコードを書きました。

// infrastructure/MemberPurchaseSummaryRepositoryImpl.java

@Repository
public class MemberPurchaseSummaryRepositoryImpl implements MemberPurchaseSummaryRepository {
    // SQLクエリの操作を行うために、jdbcOperationsを導入する
    @Autowired
    private NamedParameterJdbcOperations jdbcOperations;

    // 取得したデータをどのカラムに紐付けるかを決めるRowMapperを、Entityから定義する
    private RowMapper<MemberPurchaseSummaryEntity> entityRowMapper = DataClassRowMapper
        .newInstance(MemberPurchaseSummaryEntity.class);

    @Override
    public Page<MemberPurchaseSummaryEntity> findAll(Pageable pageable) {
        // ページングしたい、JOINしたテーブルのクエリ
        String baseSql = """
            SELECT
                m.id,
                m.name, 
                mps.purchase_total_num
            FROM member m
            LEFT OUTER JOIN member_purchase_summary mps
            ON mps.member_id = m.id
        """;

        // SQLインジェクション対策として、ソート対象の列名を許可リストでチェックするための変数
        Set<String> validSortColumnNames = Set.of("id", "purchase_total_num");

        // ORDER BY句の列指定・順序のクエリ配列を生成する
        List<String> sortStatements = pageable.getSort().stream()
            .filter(order -> validSortColumnNames.contains(order.getProperty())) // SQLインジェクション対策として正しい列名のソートのみ受け付ける
            .map(order -> {
                // "id ASC" のような文字列を返す
                return String.format("%s %s", order.getProperty(), order.getDirection());
            }).collect(Collectors.toList());

        // もしORDER BY句の列指定・順序のクエリが存在するならセットする
        if (sortStatements.size() > 0) {
            // "ORDER BY id ASC, purchase_total_num DESC" のような文字列をbaseSqlへ追加する
            baseSql += " ORDER BY " + String.join(", ", sortStatements);
        }

        // SQLクエリのpreparedStatement用に指定する値をセットする変数
        Map<String, Object> params = new HashMap<>();

        // ページングのために、レコードの総数を取得
        String countSql = "SELECT COUNT(1) FROM (" + baseSql + ") AS total";
        long total = jdbcOperations.queryForObject(countSql, params, Long.class);

        // ページングを適用
        String pagingSql = baseSql + " LIMIT :limit OFFSET :offset";
        params.put("limit", pageable.getPageSize());
        params.put("offset", pageable.getOffset());

        // ページングされた結果を取得
        List<MemberPurchaseSummaryEntity> content = jdbcOperations.query(pagingSql, params, entityRowMapper);

        // Pageオブジェクトを作成して返す
        return new PageImpl<>(content, pageable, total);
    }
}

感想

ありそうなユースケースに思うのですが、やってみると意外と関連する情報が見当たらなかったことが、記事にする動機になりました。

PagingAndSortingRepository に似せて Pageable 型のオブジェクトを引数に取るようにすることで、ソート条件とページングの条件をまとめることができたのは、認知負荷を下げる意味でよかったように思います。

もしもっと良い方法があるよ！などあればぜひお知らせいただければ幸いです。

まとめ

Spring Data JDBCでJOINしたテーブルにページングするには、解決策が２種類ありそう

  • DBのビューを利用すること
  • Implでカスタムクエリを作ること

Nstockではエンジニアを募集しています！

👨‍👩‍👧‍👦カジュアル面談から気軽にお話しましょう🤞

Nstock Tech Blog
Nstock Tech BlogPublication

スタートアップエコシステムをブーストし、日本からGoogle級の会社を生み出す | 株式報酬SaaSやセカンダリー事業などを開発するNstockのエンジニアによるPublicationです。

Discussion