Skip to main content

一覧 API

前の章までの API は、注文を 1 件ずつ作り、読み、取り消してきました。この章では、商品の一覧を返す API を作ります。一覧は、行が増えると 1 回の応答に収まらなくなるので、件数を区切って返す ページング が要ります。あわせて、在庫のある商品だけに絞る指定と、価格の順に並べる指定を受けます。

この章のコードは tag api-14 にあります。

一覧 API の形​

一覧 API は GET /api/v1/products で、次のクエリパラメータを受けます。

パラメータ意味省略したとき
sort並び。id (ID の順) か price (価格の安い順)id
limit1 回に返す件数。1 から 100 まで20
cursor続きの位置。前の応答の nextCursor の値先頭から
allocatabletrue なら、引き当てられる在庫のある (在庫が 1 個以上の) 商品だけにするfalse

limit の既定の値と上限は、7 章で作った ListingProperties の値 (shop.listing.default-limit と shop.listing.max-limit) です。

応答は、商品の配列 items と、続きのページの位置 nextCursor です。最後のページでは nextCursor が null になります。総件数は返しません。理由は「一覧を読む」の節で述べます。

offset のページング​

件数を区切って読むいちばん素朴な方法は、SQL の LIMIT と OFFSET です。1 ページ 20 件の 3 ページ目なら、先頭の 40 行を飛ばして 20 行を読みます (limit 20 offset 40)。

この方法には問題が 2 つあります。

1 つ目は、後ろのページほど遅くなることです。PostgreSQL の文書は、OFFSET で飛ばす行もサーバーの中で計算されるので、大きな OFFSET は効率が悪いとしています1。PostgreSQL 18.6 で、20 万行の表に (price, id) の索引を張り、order by price, id limit 20 offset 100000 を EXPLAIN で見ました。20 行を返すために、索引から 100,020 行を読んで、先頭の 100,000 行を捨てていました。

2 つ目は、読んでいるあいだに行が増減すると、ページが重なるか、行が抜けることです。1 ページ目を読んだ後で、先頭の側に並ぶ行が 1 つ増えると、2 ページ目は 1 行ずれて、1 ページ目の最後の行がもう一度出てきます。行が消えたときは逆に、1 行を読み飛ばします。どう重なるかは、章末の練習問題で確かめます。

続きの位置から読むページング​

この章の一覧は、行を数えて飛ばす代わりに、直前のページの最後の行の値を覚えておき、その値の次から読みます。価格の順の一覧なら、直前のページの最後の商品の価格と ID を覚えて、それより後ろに並ぶ行を読みます。

このような読み方を キーセットのページング (keyset pagination) と呼びます。読む位置を行の数でなく値で決めるので、前のページを読んだ後に行が増減しても、続きの行は重ならず、抜けもしません。並びに合う索引があれば、何ページ目でも、読む行はほぼ返す件数だけです。

代わりに、「5 ページ目へ飛ぶ」ことはできません。前のページの最後の値が要るので、先頭から順にたどります。商品を「もっと見る」で読み足す画面や、全件を順に取り込む処理に向く方式です。

並びを一意にする​

価格だけで並べると、同じ価格の商品どうしの順が決まりません。PostgreSQL の文書は、LIMIT を使うなら、行の順を一意に決める ORDER BY を書くよう求めています。そうしないと、どの行が返るかを予測できません1。続きの位置から読む方式では、同じ価格の行がページの境目にあると、どちらが前のページに入ったかが決まらず、行が重なったり抜けたりします。

そこで、並びの項目の後ろに主キーの id を足して、order by p.price, p.id にします。続きの位置も、価格と ID の 2 つの値で表します。

行の値をまとめて比べる​

価格の順の一覧で、続きの位置の次から読む条件は (p.price, p.id) > (:afterPrice, :afterId) です。これは PostgreSQL の行の値の比較で、左の組から 1 つずつ比べ、最初に等しくない組で結果が決まります2。価格が大きい行か、価格が同じで ID が大きい行を選ぶので、order by p.price, p.id の並びで直前の行より後ろにある行と一致します。

同じ条件は p.price > :afterPrice or (p.price = :afterPrice and p.id > :afterId) とも書けます。結果は同じですが、PostgreSQL の読み方が変わります。先の 20 万行の表で比べると、行の値の比較は索引の範囲の条件になり、読んだ行は 20 行でした。or で書いた形は索引の範囲の条件にならず、10 万行を読んでから条件で捨てていました。

この並びを索引で読めるように、マイグレーションで索引を足します。

app/src/main/resources/db/migration/V4__add_products_price_index.sql
/*
 * 商品の一覧を価格の順に読むための索引。並びの項目 (price) の後ろに主キー (id) を足し、一覧の
 * ORDER BY p.price, p.id と、続きの位置の条件 (p.price, p.id) > (...) の両方を、索引の順で読めるようにする。
 */
create index products_price_id_idx on products (price, id);

索引の列の順は、ORDER BY の項目の順と同じにします。ORDER BY と LIMIT の組では、ORDER BY に合う索引があれば、PostgreSQL は残りの行を読まずに先頭の行を取り出せます3。ID の順の一覧は、主キーの索引をそのまま使えます。

8 章の練習問題の解答例は、V4__add_product_description.sql を db/migration/ に足すものでした。サンプルリポジトリでは、解答例のファイルの番号を V5 に変えています。手元の db/migration/ に解答の V4 を足していたら、この章の索引のファイルを V5__add_products_price_index.sql として足してください。

並びの許可リスト​

並びは、リクエストの sort の値で選びます。ただし、受けた値を ORDER BY にそのまま入れることはしません。

列の名前は、値と違って束縛変数 (SQL の ? や :name の場所に、後から値を渡す仕組み) にできません。並べる列を変えるには、SQL の文字列そのものを変えることになります。そこへリクエストの値をそのまま入れると、price; drop table products のような値で SQL を書き換えられます (SQL インジェクション)。

OWASP の SQL インジェクション対策の手引きは、列の名前のように束縛変数にできない部分について、利用者の値をコードで決めた列の名前に写すよう勧めています4。この章では、並びを enum にして、リクエストの値から、コードに書いた SQL の断片を選びます。このように、受け付ける値をあらかじめ列挙しておき、それ以外を断る作りを 許可リスト と呼びます。

app/src/main/kotlin/com/example/shop/product/ProductListing.kt (抜粋)
enum class ProductSort(val parameter: String, val orderBy: String, val after: String) {
    ID("id", "p.id", "p.id > :afterId"),
    PRICE("price", "p.price, p.id", "(p.price, p.id) > (:afterPrice, :afterId)"),
    ;

    companion object {
        fun fromParameter(value: String): ProductSort? = entries.firstOrNull { it.parameter == value }
    }
}

parameter はリクエストで受ける値、orderBy は ORDER BY の断片、after は続きの位置の次から読む条件です。fromParameter は、リクエストの値に一致する並びを探し、無ければ null を返します。SQL に入るのは orderBy と after の文字列だけで、どちらもコードに書いた定数です。リクエストの値は、どの定数を選ぶかに使うだけで、SQL には入りません。

列の名前を束縛変数で渡すと何が起きるかは、章末の練習問題で確かめます。

絞り込みも、どの列で絞るかをクライアントに指定させません。絞り込みの種類ごとに決めたパラメータ (この章では allocatable の 1 つ) を受け、その真偽値で、コードに書いた条件を足すかどうかを選びます。ただし、並びと違って、知らない名前のパラメータは断りません。Spring MVC は、コントローラの引数に無いクエリパラメータを使わないので、allocatabel=true のように名前を誤ると、絞り込まない一覧が 200 で返ります。

一覧を読む​

一覧は、9 章で作った ProductSummaryQuery に関数を足して読みます。

app/src/main/kotlin/com/example/shop/product/ProductSummaryQuery.kt (抜粋)
    fun findPage(sort: ProductSort, after: ProductCursor?, allocatableOnly: Boolean, limit: Int): ProductPage {
        val conditions = listOfNotNull(
            "i.quantity > 0".takeIf { allocatableOnly },
            sort.after.takeIf { after != null },
        )
        val where = if (conditions.isEmpty()) "" else conditions.joinToString(" and ", prefix = "where ")
        val rows = jdbcClient.sql(
            """
            select p.id, p.name, p.price, p.tax_rate, i.quantity
            from products p
            join inventories i on i.product_id = p.id
            $where
            order by ${sort.orderBy}
            limit :limit
            """.trimIndent(),
        )
            .param("afterId", after?.id)
            .param("afterPrice", after?.price)
            .param("limit", limit + 1)
            .query(::toSummary)
            .list()
        val items = rows.take(limit)
        val next = if (rows.size > limit) items.last().let { ProductCursor(sort, it.price, it.id) } else null
        return ProductPage(items, next)
    }

WHERE 句は、条件があるときだけ組み立てます。在庫のある商品だけにする条件は、9 章の練習問題の findAllocatable と同じ i.quantity > 0 で、パラメータの名前もこれにそろえています。続きの位置の条件は、並びの enum が持つ断片です。SQL の文字列に埋め込む $where と ${sort.orderBy} は、どちらもコードに書いた定数から組み立てています。続きの位置の価格と ID は、束縛変数で渡します。

limit には、求められた件数より 1 つ多い値を渡しています。返ってきた行が件数より多ければ、続きのページがあるとわかるので、そのページの最後の商品から次の位置を作ります。Spring Data のページングの部品の Slice も、同じく 1 件多く読んで続きの有無を決めています5。

総件数 (「全部で 123 件」のような値) は数えていません。数えるには、ページを読む問い合わせとは別に、条件に合う行を数える count(*) の問い合わせが要ります。先の 20 万行の表では、count(*) は表の 20 万行をすべて読んでいました。この章の一覧は「もっと見る」で読み足す使い方を前提にしているので、続きの有無だけを返します。

続きの位置を文字列にする​

続きの位置 (価格と ID) は、1 つの文字列にしてクライアントに渡します。

app/src/main/kotlin/com/example/shop/product/ProductListing.kt (抜粋)
data class ProductCursor(val sort: ProductSort, val price: Int, val id: UUID) {

    fun encode(): String =
        Base64.getUrlEncoder().withoutPadding().encodeToString("${sort.parameter}:$price:$id".toByteArray())

    companion object {
        /** [token] を [sort] の一覧の位置として読む。読めないか、別の並びの位置なら null。 */
        fun decode(sort: ProductSort, token: String): ProductCursor? {
            val parts = try {
                String(Base64.getUrlDecoder().decode(token)).split(":")
            } catch (e: IllegalArgumentException) {
                return null
            }
            if (parts.size != 3 || parts[0] != sort.parameter) return null
            // ...
        }
    }
}

encode は、並びの名前・価格・ID を : でつないだ文字列を、URL にそのまま書ける形の Base64 (Base64url) にします。価格の順の一覧で、付箋 (220 円) の次から読む位置は price:220:0199f3a8-0000-7000-8000-000000000003 です。これを encode すると cHJpY2U6MjIwOjAxOTlmM2E4LTAwMDAtNzAwMC04MDAwLTAwMDAwMDAwMDAwMw になります。

1 つの文字列にしたのは、位置の中身をクライアントに約束しないためです。価格と ID を別々のパラメータにすると、クライアントが自分で位置を組み立てるようになり、後から位置の中身 (たとえば並びに使う項目) を変えられなくなります。クライアントに求めるのは、前の応答の nextCursor を、次のリクエストにそのまま渡すことだけです。

並びの名前を入れたのは、別の並びの一覧の位置を見分けるためです。ID の順の一覧に価格の順の位置が渡されたら、decode は null を返し、コントローラはリクエストを 400 で断ります。省いた部分では、価格を数として、ID を UUID として読み、読めなければ null を返します。

コントローラ​

app/src/main/kotlin/com/example/shop/product/ProductController.kt (抜粋)
    @GetMapping
    fun list(
        @RequestParam(defaultValue = "id") sort: String,
        @RequestParam limit: Int?,
        @RequestParam cursor: String?,
        @RequestParam(defaultValue = "false") allocatable: Boolean,
    ): ResponseEntity<ProductListResponse> {
        val productSort = ProductSort.fromParameter(sort) ?: return ResponseEntity.badRequest().build()
        val size = limit ?: listing.defaultLimit
        if (size !in 1..listing.maxLimit) {
            return ResponseEntity.badRequest().build()
        }
        val after = cursor?.let { ProductCursor.decode(productSort, it) ?: return ResponseEntity.badRequest().build() }
        return ResponseEntity.ok(ProductListResponse.from(query.findPage(productSort, after, allocatable, size)))
    }

並び・件数・続きの位置のどれかが正しくなければ、400 で断ります。11 章で、検証に通らない注文の作成を 400 にしたのと同じ考え方です。範囲の外の件数を黙って上限に丸めると、クライアントは自分の指定が効いていないことに気づけません。

応答の ProductListResponse と ProductResponse は、13 章と同じく、応答のための DTO です。一覧の 1 件には、在庫の数 (inventoryQuantity) も出します。

limit=abc のように数でない値は、Spring MVC が Int? の引数に変えられず、コントローラの関数を呼ぶ前に 400 を返します。こちらは Spring Boot の既定のエラー応答の本文が付き、並びや件数の範囲で断ったときは本文がありません。失敗の応答の形は、15 章でそろえます。

Spring Data のページングを使わない理由​

Spring Data には、ページングの部品 (Pageable・Page・Sort) があります。コントローラの引数に Pageable を書くと、page・size・sort のクエリパラメータから組み立ててくれます。この章で使わなかった理由は 3 つです。

  • Spring Data JDBC 4.1.2 は、続きの位置から読むページングに対応していません。Spring Data にある、この方式の仕組み (Scroll API) を JDBC にも入れる要望は、issue のまま開いています6
  • Pageable の引数は、不正な値を 400 にせず丸めます。size=abc や size=0 は既定の 20 件に、size=5000 は上限の 2000 件にして、200 で返します7。この上限は shop.listing.max-limit とは別の設定 (spring.data.web.pageable.max-page-size) です
  • sort の値は、検証されずにそのまま並べ替えの項目になります。Spring Data の文書は、その値を確かめるのはアプリの責任だとしています8

N+1​

一覧では、N+1 と呼ばれる問い合わせの増え方に気をつけます。一覧の行を 1 本の問い合わせで N 件読み、その後で行ごとに関連するデータを 1 本ずつ読むと、問い合わせが合わせて N+1 本になります。DB との往復が件数に比例して増えるので、1 ページの件数を増やすほど遅くなります。

商品の一覧は、商品と在庫という 2 つの集約の値を並べます。repository で、商品を findAll で読んでから、商品ごとに在庫を findById で読むと、N+1 の形になります。findPage は、9 章の findAll と同じく、商品と在庫を join した 1 本の SQL で読むので、何件読んでも問い合わせは 1 本です。

集約の中でも、同じことが起きます。Spring Data JDBC は、集約ルートを読んだ後、集約ルートが参照するエンティティを別の文で読みます9。明細を 2 行ずつ持つ注文 5 件を、注文の repository の findAll で読むと、SQL は 6 本でした。注文を読む 1 本と、注文ごとに明細を読む 5 本です。注文の一覧を作るなら、repository の findAll を使わず、JdbcClient で注文と明細をまとめて読む問い合わせを書きます。

Spring Data JDBC には、集約を 1 本の SQL で読む Single Query Loading という仕組みもあります。ただし、文書は実験的な機能としており、効くのは CrudRepository の find のメソッドだけです9。並べ替え (findAll(Sort)) やページング (findAll(Pageable)) の読み方には効かないので、一覧の N+1 はこれでは避けられません。Spring Boot 4.1.2 には、これを有効にする設定もありません10。

テスト​

findPage は、Testcontainers の PostgreSQL に商品を入れて確かめます。ボールペンと消しゴムは、どちらも 150 円です。

app/src/test/kotlin/com/example/shop/product/ProductSummaryQueryTest.kt (抜粋)
    @Test
    fun `同じ価格の商品がページの境目にあっても、重ならず抜けない`() {
        insertProducts()

        // 続きの位置が進まない誤りでも終わるように、商品の数より多い 10 ページで打ち切る。
        val ids = generateSequence(query.findPage(ProductSort.PRICE, after = null, allocatableOnly = false, limit = 1)) { page ->
            page.next?.let { query.findPage(ProductSort.PRICE, after = it, allocatableOnly = false, limit = 1) }
        }.take(10).flatMap { it.items.map(ProductSummary::id) }.toList()

        assertEquals(listOf(pen, eraser, sticky, notebook, tea), ids)
    }

1 件ずつのページを、続きの位置が無くなるまでたどり、読んだ商品を並べています。generateSequence は、最初の値と「前の値から次の値を作る関数」から列を作り、関数が null を返したところで終わります。ボールペンと消しゴムが別のページに分かれても、ID の順に 1 回ずつ現れることを確かめています。

take(10) は、続きの条件を >= と書き誤ったときのためです。続きの位置が同じ行を指したまま進まなくなり、打ち切らないと列が終わりません。

ただし、このテストは、ORDER BY から p.id を外しても通ります。テストの DB では、PostgreSQL が先に足した (price, id) の索引の順に商品を読むので、同じ価格の行が ID の順で返るためです。索引を消して試すと、先に入れた消しゴムがボールペンより先に返りました。PostgreSQL は、LIMIT と OFFSET の値によって違う読み方を選ぶことがあり、読み方が変われば同じ値の行の順も変わります1。行の少ない開発の DB で一覧が正しく見えても、並びが一意とは限りません。

そこで、並びのどの値も最後に主キーで並べることを、別のテストで確かめています。

app/src/test/kotlin/com/example/shop/product/ProductSortTest.kt (抜粋)
    @Test
    fun `どの並びも、最後に主キーで並べる`() {
        ProductSort.entries.forEach { sort ->
            assertTrue(sort.orderBy.endsWith("p.id"), "${sort.name} の ORDER BY が主キーで終わらない: ${sort.orderBy}")
        }
    }

並びを足すときに p.id を書き忘れると、このテストが落ちます。

コントローラは、受けた値で断るかどうかを、実際のサーバーに送って確かめます。

app/src/test/kotlin/com/example/shop/product/ProductControllerTest.kt (抜粋)
    @ParameterizedTest(name = "{0}")
    @ValueSource(strings = ["sort=name", "sort=price;drop", "limit=0", "limit=101", "limit=abc", "cursor=not-a-cursor"])
    fun `許可リストに無い並び、範囲の外の件数、読めない続きの位置は 400`(query: String) {
        client.get().uri("/api/v1/products?$query")
            .exchange()
            .expectStatus().isBadRequest()
    }

@ParameterizedTest を付けたテストは、@ValueSource に並べた値ごとに 1 回ずつ動きます。1 つの値が通らなくても、ほかの値の結果は別に報告されます。

curl で試す​

アプリを起動して、商品を価格の順に 2 件ずつ読みます。この節の例は、手元の DB が初期データだけの状態から打つことを前提にしています (手元の DB を確かめる、戻す)。前の章で注文を作っていたら、在庫の数 (inventoryQuantity) が例と違います。

curl -s -w '\n%{http_code}\n' 'http://localhost:8080/api/v1/products?sort=price&limit=2'
結果
{
  "items" : [ {
    "id" : "0199f3a8-0000-7000-8000-000000000002",
    "name" : "ボールペン",
    "price" : 150,
    "taxRate" : 10,
    "inventoryQuantity" : 100
  }, {
    "id" : "0199f3a8-0000-7000-8000-000000000003",
    "name" : "付箋",
    "price" : 220,
    "taxRate" : 10,
    "inventoryQuantity" : 30
  } ],
  "nextCursor" : "cHJpY2U6MjIwOjAxOTlmM2E4LTAwMDAtNzAwMC04MDAwLTAwMDAwMDAwMDAwMw"
}
200

URL の & はシェルでは特別な文字なので、URL 全体を ' で囲んでいます。nextCursor を cursor に渡すと、付箋の次から読みます。

curl -s -w '\n%{http_code}\n' 'http://localhost:8080/api/v1/products?sort=price&limit=2&cursor=cHJpY2U6MjIwOjAxOTlmM2E4LTAwMDAtNzAwMC04MDAwLTAwMDAwMDAwMDAwMw'
結果
{
  "items" : [ {
    "id" : "0199f3a8-0000-7000-8000-000000000001",
    "name" : "ノート",
    "price" : 330,
    "taxRate" : 10,
    "inventoryQuantity" : 50
  }, {
    "id" : "0199f3a8-0000-7000-8000-000000000006",
    "name" : "緑茶",
    "price" : 540,
    "taxRate" : 8,
    "inventoryQuantity" : 0
  } ],
  "nextCursor" : "cHJpY2U6NTQwOjAxOTlmM2E4LTAwMDAtNzAwMC04MDAwLTAwMDAwMDAwMDAwNg"
}
200

在庫での絞り込みを指定していないので、在庫の無い商品 (inventoryQuantity が 0 の商品) も出ています。allocatable=true を付けると、在庫のある商品だけになります。

curl -s -w '\n%{http_code}\n' 'http://localhost:8080/api/v1/products?sort=price&allocatable=true'
結果
{
  "items" : [ {
    "id" : "0199f3a8-0000-7000-8000-000000000002",
    "name" : "ボールペン",
    "price" : 150,
    "taxRate" : 10,
    "inventoryQuantity" : 100
  }, {
    "id" : "0199f3a8-0000-7000-8000-000000000003",
    "name" : "付箋",
    "price" : 220,
    "taxRate" : 10,
    "inventoryQuantity" : 30
  }, {
    "id" : "0199f3a8-0000-7000-8000-000000000001",
    "name" : "ノート",
    "price" : 330,
    "taxRate" : 10,
    "inventoryQuantity" : 50
  }, {
    "id" : "0199f3a8-0000-7000-8000-000000000004",
    "name" : "マーカー",
    "price" : 880,
    "taxRate" : 10,
    "inventoryQuantity" : 3
  }, {
    "id" : "0199f3a8-0000-7000-8000-000000000005",
    "name" : "コーヒー豆",
    "price" : 1280,
    "taxRate" : 8,
    "inventoryQuantity" : 20
  } ],
  "nextCursor" : null
}
200

limit を省いたので既定の件数 (20 件) までを読みます。在庫のある商品はそれより少ないので、nextCursor は null です。

許可リストに無い並びを指すと、400 で断ります。

curl -s -w '\n%{http_code}\n' 'http://localhost:8080/api/v1/products?sort=name'
結果
400

本番で効く注意点​

並びを足すときは、索引も足す​

許可リストに並びを足しても、その ORDER BY に合う索引が無ければ、PostgreSQL は件数の上限があっても、条件に合う行をすべて読んで並べてから先頭を返します3。先の 20 万行の表では、索引の無い order by name, id limit 20 は、20 万行をすべて読んで並べていました。行が少ない開発の DB では、速さの違いに気づけません。並びを足すときは、(並びの項目, id) の索引を同じ変更で足します。

続きの位置の文字列は、中身を隠していない​

Base64url は暗号ではないので、誰でも元の文字列に戻せます。続きの位置には、クライアントに見せてよい値だけを入れます。クライアントが書き換えた位置を送ってきても、decode で形を確かめ、値は束縛変数で SQL に渡すので、SQL は変わりません。書き換えた位置で読めるのは、もともと一覧で読める商品だけです。

まとめ​

  • 一覧は件数を区切って返します。件数の既定と上限は設定で決め、範囲の外の値は丸めずに 400 で断ります
  • offset のページングは、後ろのページほど遅くなり、読むあいだに行が増減するとページが重なったり抜けたりします
  • この章の一覧は、直前のページの最後の行の値から次を読みます。並びの後ろに主キーを足して一意にし、条件は行の値の比較で書き、同じ順の索引を張ります
  • 並べる列は束縛変数にできないので、リクエストの値を許可リスト (enum) でコードの SQL の断片に写します
  • 続きの有無は 1 件多く読んで決め、総件数は数えません。続きの位置は 1 つの文字列にして渡し、中身をクライアントに約束しません
  • 行ごとに関連するデータを読むと N+1 本の問い合わせになります。Spring Data JDBC の repository は、集約の参照するエンティティを別の文で読みます。一覧は join した 1 本の SQL で読みます

次に読む​

前の章は レスポンスの形 です。

次章 エラーレスポンス では、ここまで本文の無い 400 や Spring Boot の既定の形で返してきた失敗の応答を、Problem Details の形にそろえます。

練習問題​

商品を価格の順に 2 件ずつ読みます。1 ページ目を読んだ後で、どの商品より安い商品が足されました。2 ページ目を offset で読むと、何が起きますか。続きの位置から読むとどうですか

解答例

offset で読むと、2 ページ目の先頭に、1 ページ目の最後の商品がもう一度出てきます。続きの位置から読むと、1 ページ目の続きの商品が出て、重なりません。

OffsetPagingOverlapTest.kt (抜粋)
        val firstByOffset = offsetPage(offset = 0, limit = 2)
        val firstByCursor = query.findPage(ProductSort.PRICE, after = null, allocatableOnly = false, limit = 2)

        // 1 ページ目を読んだ後で、どの商品より安いクリップが足される。
        insert(clip, "クリップ", 100)
        val secondByOffset = offsetPage(offset = 2, limit = 2)
        val secondByCursor = query.findPage(ProductSort.PRICE, after = firstByCursor.next, allocatableOnly = false, limit = 2)

        assertEquals(listOf(pen, sticky), firstByOffset)
        assertEquals(listOf(sticky, notebook), secondByOffset)
        assertEquals(listOf(notebook, marker), secondByCursor.items.map { it.id })

offsetPage は、order by p.price, p.id limit :limit offset :offset で読む関数です。クリップが足されると、価格の順はクリップ・ボールペン・付箋・ノート・マーカーになります。offset の 2 ページ目は「先頭から 2 行を飛ばした次の 2 行」なので、付箋とノートを返し、付箋が 1 ページ目と重なります。クリップは、どちらのページにも出ません。

続きの位置から読む方式の 2 ページ目は「付箋 (220 円) より後ろの 2 行」なので、ノートとマーカーを返します。前に足された行は、続きの行に影響しません。足されたクリップを読むには、一覧を先頭から読み直します。

並べる列の名前を、JdbcClient の束縛変数で渡しました (order by :column に column = "price")。どうなりますか

解答例

エラーにならないまま、価格の順に並びません。

OrderByBindParameterTest.kt (抜粋)
        assertEquals(byPrice, ids("select id from products order by price"))
        assertNotEquals(byPrice, ids("select id from products order by :column", column = "price"))
        // 同じ文字列を定数で書くと、PostgreSQL は並びに使えない定数として断る。
        assertThrows<BadSqlGrammarException> { ids("select id from products order by 'price'") }

束縛変数は、値の代わりにしかなれません。order by :column に "price" を渡すと、PostgreSQL は price の列でなく、すべての行で同じ 'price' という文字列の値で並べます。どの行も同じ値なので、ORDER BY は行の順を決めません。

同じ文字列を SQL に直接書いた order by 'price' は、PostgreSQL が non-integer constant in ORDER BY のエラーで断ります。束縛変数で渡したときは値が後から入るので、このエラーにもなりません。並べ替えが効いていないことに、結果の順を見るまで気づけないので、列の名前は本文の許可リストのように、コードに書いた断片から選びます。

Footnotes​

  1. 出典: PostgreSQL 18 の文書の LIMIT and OFFSET。 ↩ ↩2 ↩3

  2. 出典: PostgreSQL 18 の文書の Row and Array Comparisons の行コンストラクタの比較 (Row Constructor Comparison)。 ↩

  3. 出典: PostgreSQL 18 の文書の Indexes and ORDER BY。 ↩ ↩2

  4. 出典: OWASP の SQL Injection Prevention Cheat Sheet の Defense Option 3: Allow-list Input Validation の節。 ↩

  5. 出典: Spring Data Relational 4.1.2 の文書の Query Methods ("Selects LIMIT+1 rows to determine whether there's more data to consume.")。 ↩

  6. 出典: spring-data-relational の issue #1605 Add support for Window scrolling queries。 ↩

  7. 出典: Spring Data Commons 4.1.2 の PageableHandlerMethodArgumentResolverSupport のソース。 ↩

  8. 出典: Spring Data Commons 4.1.2 の文書の Web support ("Applications are responsible for verifying these values to prevent unintended sorting.")。 ↩

  9. 出典: Spring Data Relational 4.1.2 の文書の Persisting Entities の Loading Aggregates の節。 ↩ ↩2

  10. 出典: Spring Boot 4.1.2 の DataJdbcProperties のソース。 ↩