ブログ対応のプロジェクトテンプレート

はじめに

Mybatis には馴染みがあるかもしれませんが、今日はその文字列連結ベースの古いライブラリについては触れません。今日は QueryDSL をベースに実装されたライブラリについて話します。（このライブラリの最初のバージョンは 2009 年に誕生しました）その名は JOOQ です。

JOOQ は一言でまとめると：JOOQ を使っているとき、あなたは SQL を使っているのと同じです。SQL 標準で実現できる操作は、ほとんどすべて JOOQ でも同じように実現できます。

この言葉を覚えておけば、JOOQ の設計思想を理解したことになります。

JOOQ は、型安全で、再利用可能で、デバッグ可能な SQL です。

それでは、ページネーションクエリのトピックに戻りましょう。

Offset & Limit

普通のページネーションクエリは簡単に書けます。JOOQ は SQL そのものなので、Java API も SQL と全く同じです。

SQL

SELECT username FROM user LIMIT 2 OFFSET 1;

JOOQ

List<User> users = dsl.select(USER.USERNAME).from(USER).limit(2).offset(1).fetchInto(User.class);

OrderBy

SQL

SELECT username FROM user ORDER BY id DESC LIMIT 3 OFFSET 1;

JOOQ

List<User> users = dsl.select(USER.USERNAME)
        .from(USER)
        .orderBy(USER.ID.desc())
        .limit(3)
        .offset(1)
        .fetchInto(User.class);

上記は簡単な例ですが、次はもう少し複雑な例を見てみましょう。

Ties

USER テーブルには 5 行のデータがあり、そのうち最後の 3 行の password の値は同じです。私たちの要件は、password asc でソートして、上位 3 件の結果を取得することです。

id	username	password
1	testUserA	a
2	testUserB	b
3	testUserC	c
4	testUserD	c
5	testUserE	c

この要件は、従来の order by & limit offset で簡単に満たせます。

List<User> users = dsl.select(USER.USERNAME)
        .from(USER)
        .orderBy(USER.PASSWORD.asc())
        .limit(3)
        .offset(0)
        .fetchInto(User.class);

しかし、問題は、3 行のデータの password が同じであることです。ビジネス上、testUserC testUserD testUserE は実際には同じ優先度を持っています。これらの同じ優先度のデータを漏らしたくない場合、どうすればよいでしょうか？

ここで withTies の出番です：これは、クエリ結果セットの最後の行と同じ優先度を持つデータを返します。

 @Test
  @Sql(
      statements = {
        "INSERT INTO mjga.user (id, username, password) VALUES (1, 'testUserA','a')",
        "INSERT INTO mjga.user (id, username, password) VALUES (2, 'testUserB','b')",
        "INSERT INTO mjga.user (id, username, password) VALUES (3, 'testUserC','c')",
        "INSERT INTO mjga.user (id, username, password) VALUES (4, 'testUserD','c')",
        "INSERT INTO mjga.user (id, username, password) VALUES (5, 'testUserE','c')"
      })
  void fetchAndTiesQuery() {
    List<User> users =
        dsl.select(USER.USERNAME)
            .from(USER)
            .orderBy(USER.PASSWORD.asc())
            .limit(3)
            .withTies()
            .offset(0)
            .fetchInto(User.class);
    assertThat(users.size()).isEqualTo(5);
    assertThat(users.get(0).getUsername()).isEqualTo("testUserA");
    assertThat(users.get(4).getUsername()).isEqualTo("testUserE");
  }

上記の例では、limit 3 offset 0 を指定しましたが、5 件の結果が返されました。これは、testUserD と testUserE が testUserC と結びついた（withTies）ためです。

これは JOOQ の魔法でしょうか？いいえ、これは SQL の標準機能ですが、Mysql はこれをサポートしていません。また、Mybatis のようなライブラリはアプリケーションレベルでこの動作をシミュレートしていないため、これまで触れる機会がなかったのです。

Window Function

もっと高度な例を見てみましょう：多くの場合、ページネーションクエリでは「総数」を追加で統計する必要があります。どのライブラリやプラグインを使っても、その原理は SELECT COUNT(*) AS total_count のような集計クエリを再度実行することでこの要件を満たすことです。これにより、DB とさらに 1 回やり取りすることになります。

では、DB と 1 回だけやり取りして、クエリ結果セットと統計総数を同時に取得することはできないでしょうか？もちろんできます。ウィンドウ関数は、結果セットをウィンドウとして扱い、その結果セットの各行に対して集計値を計算しますが、結果セットの行数は変更しません。

SQL

SELECT *, COUNT(*) OVER () AS total_user
FROM user
ORDER BY id ASC
LIMIT 4 OFFSET 0;

JOOQ
Result<Record> に驚かないでください。これは単なるカスタムの List<Map> 構造です。

Result<Record> resultWithWindow = dsl.select(asterisk(), DSL.count().over().as("total_user"))
        .from(USER)
        .orderBy(USER.ID.asc())
        .limit(4)
        .offset(0)
        .fetch();

Result

id	username	total_user
1	Alice	5
2	Bob	5
3	Charlie	5
4	David	5

これは JOOQ の魔法でしょうか？いいえ、これは SQL の標準機能ですが、Mysql 8 以下のバージョンではサポートされていません。もし Mybatis-plus などの関連プラグインを長く使っていると、ウィンドウ関数を見逃してしまうかもしれません。なぜなら、それらはこれらの一般的な SQL 標準をサポートしていないからです。

SQL が好きなら

Mybatis の「実行時例外」を「コンパイル時例外」に前倒しする JOOQ の仕組みを気に入るはずです。このような型安全で、再利用可能で、デバッグ可能な SQL は、これまでの SQL コーディング体験を一変させるでしょう。

最後に

私は Chuck1sn です。現代の Jvm エコシステムの普及に長年取り組んでいる開発者です。
あなたの返信、いいね、ブックマークが、私の継続的な更新の原動力です。
簡単なワンクリックでのサポートは、私にとって大きな励みになります。どうもありがとうございます！
私のアカウントをフォローして、最新の記事をいち早く受け取ってください。

PS：上記のすべてのコード例は Github で見つけることができます。役に立ったら、ぜひ Star を付けてください。それは私にとって大きな励みになります。ありがとうございます！