SQL DSL#

文字列で SQL を書くこともできますが、DSL のほうが型が効きます。

引く#

List<Data> posts = db.selectList(
	SQL.select()
		.from(Post.instance())
		.where(Post.published.eq(true))
		.orderBy(Post.created_at.desc())
		.limit(20)
);

limit と offset があります。select() に何も渡さなければ全列です。列を選ぶなら渡します。

SQL.select(Post.id, Post.title).from(Post.instance())

条件#

.where(Post.id.eq(1L))
.where(Post.title.like("%jimble%"))
.where(Post.created_at.ge(from).and(Post.created_at.lt(to)))
.where(Post.id.in(List.of(1L, 2L, 3L)))

where を複数回呼ぶと AND で繋がります。

結合#

SQL.select()
	.from(Post.instance())
	.left(Comment.instance()).on(Comment.post_id.eq(Post.id))
	.where(Post.id.eq(id))

内部結合は inner(...) です。on() は直前の結合に付きます。

結果はテーブル名でネストするので、row.getData("comment") で取れます。

入れる・直す・消す#

long id = db.insert(
	SQL.insert(Post.instance())
		.value(Post.title, title)
		.value(Post.created_at, new Date())      // アプリ側の時計
);

int updated = db.update(
	SQL.update(Post.instance())
		.set(Post.published, true)
		.where(Post.id.eq(id))
);

int deleted = db.delete(
	SQL.delete(Post.instance()).where(Post.id.eq(id))
);

.value(Post.created_at, Dsl.now())       // DB 側の時計

insert は採番された ID を返します。ID が要らないときは insertNoReturnKey のほうが速いです。

送られた項目だけ直す、という書き方はこうなります。

patch("/posts/{id}", context -> {

	long id = id(context);
	BlogApp.findPost(id);

	Data request = context.request().bodyAll();

	UpdateBuilder builder = SQL.update(Post.instance());
	boolean hasChange = false;

	if (request.containsKey("title")) {
		builder.set(Post.title, request.getString("title"));
		hasChange = true;
	}

	if (request.containsKey("body")) {
		builder.set(Post.body, request.getString("body"));
		hasChange = true;
	}

	if (request.containsKey("published")) {
		builder.set(Post.published, request.getBoolean("published"));
		hasChange = true;
	}

	if (!hasChange) {
		throw new HttpException(400, "変える項目がありません（title / body / published）");
	}

	int updated = BlogExample.db().update(builder.where(Post.id.eq(id)));

	context.response().json("updated", updated);

});

関数を使う#

Dsl に関数が並んでいます。どれも MySQL と PostgreSQL の両方で動きます。

SQL.select(Dsl.count(), Dsl.max(Post.created_at)).from(Post.instance())

CASE WHEN のように値としても列としても使えるものは、 select に直接は渡せません。SelectQuery で包んでください。

SQL.select(new SelectQuery().dsl(Dsl.caseWhen()...)).from(...)

包まずに渡すと、列ではなくバインドされる値として扱われます。

一覧#

// 名前を整えて、空なら「（無名）」
Dsl.coalesce(Dsl.trim(Post.title), "（無名）").as("title")

// 月ごとの件数
SQL.select(Dsl.year(Post.created_at).as("y"), Dsl.month(Post.created_at).as("m"), Dsl.count())
    .from(Post.instance())
    .groupBy(Dsl.year(Post.created_at), Dsl.month(Post.created_at))

// 30 日後
Dsl.dateAdd(Post.created_at, 30, DateUnit.DAY)

// タグをまとめて1つの文字列に
Dsl.groupConcat(Tag.name, "/").as("tags")

単位（DateUnit）と型（CastType）は enum です。文字列では渡せません。 SQL にそのまま入るところなので、外から来た文字列を通せないようにしてあります。

まとめた値で絞る（having）#

**集計した値は、そのまま条件にできます。**列と同じ書き方です。

SQL.select(
        Department.id
        , Dsl.sum(Request.amount).as("total"))
    .from(Request.instance())
    .groupBy(Department.id)
    // 合計が 50 万を超える部署だけ
    .having(Dsl.sum(Request.amount).ge(500_000L));

CASE の条件にも置けます。

new SelectQuery().dsl(Dsl.caseWhen()
        .when(Dsl.sum(Request.amount).ge(500_000L)).then("大")
        .elseCase("小")).as("size")

書けるのは比べるものだけです （eq / not / gt / lt / ge / le / between / is_null / is_not_null）。 like や contains は足していません。集計に対しては、書けても意味がないためです。

ウィンドウ関数#

行をまとめずに、まとめた結果を各行に付けます。GROUP BY と違って行が減りません。

SQL.select(
        Sale.shop_id
        , Sale.amount
        // 店ごとの売上順位
        , Dsl.rank().partitionBy(Sale.shop_id).orderBy(Sale.amount.desc()).as("rank")
        // 累計
        , Dsl.over(Dsl.sum(Sale.amount))
            .orderBy(Sale.sold_at.asc())
            .rowsBetween(WindowFrame.unboundedPreceding(), WindowFrame.currentRow())
            .as("total"))
    .from(Sale.instance());

Dsl.over(...) には集約（sum / count / avg など）を渡します。 別名や計算を中に入れると例外になります（OVER のあとに付けてください）。

[!WARNING] ウィンドウ関数は where にも having にも書けません。 SQL の決まりで、評価されるのがどちらより後だからです。 Dsl.rowNumber().over(...).eq(1) と書くと組み立てた時点で例外になります。 順位で絞るなら、いったん副問い合わせで出してから外側で絞ってください。

in に空の一覧は渡せません#

where(Site.id.in(List.of()))   // ← 組み立てた時点で例外

IN () は構文エラーです。黙って DB に投げると、 エラーメッセージからどこで空を渡したのか辿れません（要件 F-D-07）。

「空なら条件を外す」ことはしません。 in(空) は「どれにも当たらない」で、条件を外すと全件です。 どちらの意味かは呼び出し側にしか分からないので、呼び出し側で分けてください。

if (ids.isEmpty()) {
	return List.of();          // 「どれにも当たらない」
}
where(Site.id.in(ids));

製品の違いで気をつけること#

名前が同じでも意味が違うものは DSL が揃えます。

揃えていないものもあります。

その製品に無いものは、SQL を組み立てたところで例外（DialectException）になります。 DB を使う にまとめてあります。

DB 製品が違うとき#

同じコードのまま、db.xxx.product に応じた SQL が出ます。 識別子の囲み（` と "）、ON DUPLICATE KEY UPDATE と ON CONFLICT、 INSERT IGNORE と ON CONFLICT DO NOTHING、RAND() と RANDOM() などは ビルダーが吸収します。

その製品で書けないものは、SQL を組み立てたところで例外（DialectException）になります。 どれが書けないかは DB を使う にまとめてあります。

// PostgreSQL では DialectException（to_char は書式の言語が違う）
SQL.select(Dsl.dateFormat(Post.created_at, "%Y-%m-%d")).from(Post.instance());

まとめて流す#

List<Integer> counts = db.executeBatch(builderList);   // 件数のリスト
List<Long>    ids    = db.insertBatch(builderList);    // 採番値のリスト

2つで戻り値が違います。executeBatch は件数（List<Integer>）、 insertBatch は採番値（List<Long>）です。 件数のほうは DB.isBatchSuccess(list) で全部通ったか見られます。

大きい結果#

全部をリストに載せたくないときは、カーソルで1行ずつ受け取ります。

try (DB db = BlogExample.db();
	 ResultSetFetcher fetcher = new ResultSetFetcher()) {

	db.selectListWithFetcher(fetcher, SQL.select().from(Post.instance()));

	for (Data row : fetcher) {
		// 1行ずつ来る
	}

}