[Kotlin] Komapperで少し複雑なクエリの組み立て

はじめに

KomapperはSQLを組み立てるためのDSLを提供しています。本記事ではサブクエリなど少し複雑なクエリを簡潔に組み立てられることを紹介します。

Komapperは、サーバサイドKotlinのためのORMです。詳細はWebサイトをご覧ください。
https://www.komapper.org/ja/

エンティティ定義とDDL

今回利用するエンティティ定義とDDLは次のものです。

エンティティ定義

@KomapperEntity
data class Address(
    @KomapperId @KomapperColumn(name = "address_id") val addressId: Int,
    val street: String,
    @KomapperVersion val version: Int
)

@KomapperEntity
data class Department(
    @KomapperId @KomapperColumn("department_id") val departmentId: Int,
    @KomapperColumn("department_no") val departmentNo: Int,
    @KomapperColumn("department_name") val departmentName: String,
    val location: String,
    @KomapperVersion val version: Int,
)

@KomapperEntity(["employee", "manager"])
data class Employee(
    @KomapperId @KomapperColumn("employee_id") val employeeId: Int,
    @KomapperColumn("employee_no") val employeeNo: Int,
    @KomapperColumn("employee_name") val employeeName: String,
    @KomapperColumn("manager_id") val managerId: Int?,
    val hiredate: LocalDate,
    val salary: BigDecimal,
    @KomapperColumn("department_id") val departmentId: Int,
    @KomapperColumn("address_id") val addressId: Int,
    @KomapperVersion val version: Int,
)

DDL

create table if not exists department(department_id integer not null primary key, department_no integer not null unique,department_name varchar(20),location varchar(20) default 'tokyo', version integer);
create table if not exists address(address_id integer not null primary key, street varchar(20) unique, version integer);
create table if not exists address_archive(address_id integer not null primary key, street varchar(20) unique, version integer);
create table if not exists employee(employee_id integer not null primary key, employee_no integer not null ,employee_name varchar(20),manager_id integer,hiredate date,salary numeric(7,2),department_id integer,address_id integer,version integer, constraint fk_department_id foreign key(department_id) references department(department_id), constraint fk_address_id foreign key(address_id) references address(address_id));

サブクエリ

サブクエリは、WHERE句やSELECT句に指定できます。

ただし、残念ながらFROM句の指定はサポートしていません。FROM句のサブクエリ指定は新しいリレーション（テーブル）を動的に作り出すことに相当するので、事前に定義した型情報を使うKomapperのDSLと相性が悪いためです。同じ理由でWITH句にも対応していません。そういったSQLが必要な場合はDSLを使わずにSQLテンプレートを利用するクエリを使ってもらえればと思っています。

WHERE句のサブクエリ

SELECT文はもちろんUPDATE文やDELETE文のWEHRE句にもサブクエリを指定できます。下記のサンプルコードを見てください。DSLで組み立てたクエリがどんなSQLに対応しているかはコメントに示しました。

val a = Meta.address
val e = Meta.employee

val subquery = QueryDsl.from(e).select(e.addressId)

// select t0_.address_id, t0_.street, t0_.version from address as t0_ where t0_.address_id not in (select t1_.address_id from employee as t1_)
val select = QueryDsl.from(a).where { a.addressId notInList subquery }

// update address as t0_ set street = '', version = version + 1 where t0_.address_id not in (select t1_.address_id from employee as t1_)
val update = QueryDsl.update(a).set { a.street eq "" }.where { a.addressId notInList subquery }

// delete from address as t0_ where t0_.address_id not in (select t1_.address_id from employee as t1_)
val delete = QueryDsl.delete(a).where { a.addressId notInList subquery }

QueryDslを使ってビルドしたオブジェクトは全て実質的にイミュータブルなのでいろんなクエリで再利用できます。

SELECT句のサブクエリ

集約関数を使ってスカラ値を返すようなサブクエリであればSELECT句に指定可能です。

val d = Meta.department
val e = Meta.employee

val subquery = QueryDsl.from(e).where { d.departmentId eq e.departmentId }.select(count())

// select t0_.department_name, (select count(*) from employee as t1_ where t0_.department_id = t1_.department_id) from department as t0_ order by t0_.department_id asc
val query = QueryDsl.from(d).orderBy(d.departmentId).select(d.departmentName, subquery)

この例では、サブクエリのselect関数にcount()を指定していますが、avg(e.salary)のような従業員の平均給与を返すような式を代わりに指定することも可能です。

ちなみに、下記のようにサブクエリがスカラ値を返さない場合はSELECT句に指定できません（select関数に渡している式に着目）。コンパイルエラーになります。

val subquery = QueryDsl.from(e).where { d.departmentId eq e.departmentId }.select(e.employeeName)

自己結合

employeeテーブルは従業員の上司を表すmanager_idカラムを持ちこの値はemployeeテーブルのemployee_idカラムを参照します。

例えば、従業員と上司の一覧を得るには自己結合が必要になるわけですが、Komapperではどう表現できるでしょうか？1つのSQLに同じテーブルが別物として登場するので区別できるように別名が必要です。別名は必要となるたびに作ることもできますが、今回はあらかじめエンティティ定義の際につけておきました。Employeeクラスに注釈した@KomapperEntity(["employee", "manager"])がそれです。これは、employeeテーブルをemployeeとmanagerという2つの名前で参照できることを意味しています。

クエリは次のように記述できます。

val e = Meta.employee
val m = Meta.manager

// select t0_.employee_name, t1_.employee_name from employee as t0_ left outer join employee as t1_ on (t0_.manager_id = t1_.employee_id)
val query = QueryDsl.from(e)
    .leftJoin(m) { e.managerId eq m.employeeId }
    .select(e.employeeName, m.employeeName)

上の例では従業員の名前と上司の名前をselect（射影）して取得していますが、エンティティとして取得するにはクエリの最後でselectする代わりにincludeAllを呼び出します。このクエリを実行するとエンティティが含まれたstoreを取得できます。

// select t0_.employee_id, t0_.employee_no, t0_.employee_name, t0_.manager_id, t0_.hiredate, t0_.salary, t0_.department_id, t0_.address_id, t0_.version, t1_.employee_id, t1_.employee_no, t1_.employee_name, t1_.manager_id, t1_.hiredate, t1_.salary, t1_.department_id, t1_.address_id, t1_.version from employee as t0_ left outer join employee as t1_ on (t0_.manager_id = t1_.employee_id)
val query = QueryDsl.from(e)
    .leftJoin(m) { e.managerId eq m.employeeId }
    .includeAll()

val store = db.runQuery(query)
val managerEmployeesMap: Map<Employee, Set<Employee>> = store.oneToMany(m, e)

storeからはエンティティの関連をMapで取得したりそれぞれのエンティティをSetで取得したりができます。

上の例のmanagerEmployeesMapは、上司をkey、部下のSetをvalueとするMapです。

INSERT-SELECT構文

他のテーブルをインプットに他のテーブルにデータを追加したいことがあると思います。そんな時にこの方法が使えます。ここではaddressテーブルから同じデータ構造を持つaddress_archiveテーブルにデータを追加します。

val a = Meta.address
val aa = a.clone(table = "address_archive")

val select = QueryDsl.from(a).where { a.addressId between 1..5 }

// insert into address_archive (address_id, street, version) select t1_.address_id, t1_.street, t1_.version from address as t1_ where t1_.address_id between 1 and 5
val insert QueryDsl.insert(aa).select(select)

2行目のa.clone(table = "address_archive")では、Addressのエンティティ定義を再利用して（ただしテーブル名は上書き）新しいメタモデルを作っています。もちろんcloneは必須ではなく事前に定義してあるメタモデルを使うこともできます。

現在の値をベースとした更新

例えば、特定の部門に所属する従業員の給与を2倍にしたいとしましょう。上で紹介したサブクエリの機能も使って実現してみたいと思います。

val e = Meta.employee
val d = Meta.department

val subquery = QueryDsl.from(d).where { 
    d.departmentName eq "SALES" 
    d.departmentId eq e.departmentId
}

// update employee as t0_ set salary = (t0_.salary * 2), version = version + 1 where exists (select t1_.department_id, t1_.department_no, t1_.department_name, t1_.location, t1_.version from department as t1_ where t1_.department_name = 'SALES' and t1_.department_id = t0_.department_id)
val query = QueryDsl.update(e).set {
    e.salary eq e.salary * BigDecimal(2) 
}.where {
    exists(subquery) 
}

ポイントは最後の行のe.salary eq e.salary * BigDecimal(2)の部分ですね。

ORMによってはこのようなクエリを発行するのにSELECT文とUPDATE文を2回に分けて発行する必要がああったりSQLを手で記述する必要があったりするのではないでしょうか？Komapperの場合は簡潔に記述できます。

なお、楽観的排他制御のバージョン番号を持っているテーブルに対してはバージョン番号のインクリメントも行います。

おわりに

KomapperのDSLで組み立てられるクエリの内、少し複雑（他のORMではそれほどサポートされていないかもしれない）ものを中心に紹介しました。他にもHAVING句やUNION演算などのサポートもしています。

Komapperはよくあるユースケースはサポートしたいと思っていますがSQLによってはDSLで組み立てる方がかえって煩雑になってしまうので、DSLで書くのが辛い場合はSQLテンプレートを利用するクエリの活用も検討ください。