ExposedのDSL/DAOサンプル集

はじめに

Exposedを利用する際に、こういうSQLはこう書くという内容をDSL/DAOでそれぞれ記載しています。
基本的には公式wiki読めばわかるんですが、読んだけどわからない人向け。
私自身も備忘録として使います。

Exposedが何なのかの説明等は省きますので、他の記事をご参照ください。

公式wiki

SELECT

SELECT(全件)

条件なしで全てのレコードを取得するSQL。

SELECT * FROM users

DSL

selectAll()を使用する。

Users.selectAll()

DAO

all()を使用する。

User.all()

SELECT(count(*))

条件に一致するレコードの件数を取得するSQL。

SELECT COUNT(*) FROM users WHERE age = 20

DSL

selectの後にcount()を繋げる。

Users.select { Users.age eq age }.count()

DAO

DSLとほぼ同じ。

User.find { Users.age eq age }.count()

SELECT(検索条件指定)

検索条件を指定してレコードを取得するSQL。

SELECT * FROM users WHERE id = 1

DSL

select { Op <Boolean> }で指定する。
Opは検索条件と思ってもらって大丈夫です。

val id = 1
Users.select { Users.id eq id }

DAO

IntIdTableの場合はfindByIDが利用できる。
PK以外で取得したい場合は、find { Op <Boolean> }　で指定する。

val id = 1
User.findByID(id)
//または
User.find { Users.id eq id }

SELECT(列指定)

取得する列を指定するSQL。

SELECT name, age FROM users WHERE id = 1

DSL

sliceに取得したいカラムを定義する。

val id = 1
Users.slice(Users.name Users.age).select { Users.id eq id }

DAO

DAO固有の書き方がないので、やりたいならDSLをラップして利用する。
ただしDAOの場合は、列指定せずにレコード全体を取得してから操作する場合が多いと思うので、
わざわざこんなことをすることはほぼないと思われます。
（列を限定したいなら、専用のdata classを用意してそこに設定したほうがいい）

val id = 1
val query = Users.slice(Users.name Users.age).select { Users.id eq id }
User.wrapRows(query).singleOrNull()

IN

IN句で指定したidのレコードを取得するSQL。

SELECT * FROM users WHERE id IN (1, 2, 3)

DSL

WHERE句を組み立てる。
ここに限った話ではないですが、Op.bulidは複雑なWHERE句になる際などは可読性のために
別メソッドで定義して使用するのに便利です。(ifやwhenを活用して、〜の場合は条件に〜を追加など）

val ids = listOf(1, 2, 3)
Users.select { Users.id.inList(ids) }

// こういうこともできる
val ids = listOf(1, 2, 3)
Users.select(makeCondition(ids))

/** 検索条件を組み立てる */
private fun makeCondition(ids: List<Int>): Op<Boolean> {
    return Op.build { Users.id.inList(ids) }
}

DAO

IntIdTableの場合はforIdsの方がシンプル。

val ids = listOf(1, 2, 3)
User.forIds(ids)
//または 
User.find { Users.id.inList(ids) }

ORDER BY

取得結果をORDER BYにより並び替えするSQL。

SELECT * FROM users WHERE age = 20 ORDER BY id DESC 

DSL

selectの後にメソッドチェーンで繋げる。とてもわかりやすい。

val age = 20
Users.select { Users.age eq age }.orderBy(Users.id, SortOrder.DESC)

DAO

orderByで引数にDESCを指定するのではなく、sortedByDescendingを使用する。
ASCの場合は、sortedByとなる。

val age = 20
User.find { Users.age eq age }.sortedByDescending { it.id }

GROUP BY、SUM

GROUP BY句で集約して合計した結果を取得するSQL。

SELECT * id, SUM(money) totalMoney
FROM users GROUP BY id, money

DSL

sum()で特定の項目を合計しaliasで合計した項目に別名をつける。
groupByで集約する項目を指定。

Users.slice(Users.id, Users.money.sum()
    .alias("totalMoney")).selectAll().groupBy(Users.id, Users.job_id)

DAO

ラップしなくてもいい方法があるのか調査中。

val query = Users.slice(Users.id, Users.money.sum()
    .alias("totalMoney")).selectAll().groupBy(Users.id, Users.job_id)
User.wrapRows(query)

EXISTS、INNER JOIN

EXISTS句で副問い合わせを行い、副問い合わせ内でJOINを行うSQL。

SELECT * FROM users 
WHERE EXISTS (
             SELECT * FROM users 
             INNER JOIN jobs 
             ON users.job_id = jobs.id
             WHERE users.job_id = jobs.id 
             AND jobs.job_name = '会社員'
            )

DSL

existsの中に副問い合わせの内容を書いていく。生SQLを書くのと肌感は変わらない。

val jobName = "会社員"
Users.select { exists(Users.innerJoin(Jobs)
    .select(Users.job_id eq Jobs.id and (Jobs.job_name eq jobName))) }

DAO

DSLとほぼ同じ。

val jobName = "会社員"
User.find { exists(Users.innerJoin(Jobs)
   .select(Users.job_id eq Jobs.id and (Jobs.job_name eq jobName))) }

INSERT

INSERT

テーブルにレコードを挿入するSQL。

INSERT INTO users (name, money, jobID) VALUES ('田中太郎', 10000, 1)

DSL

ラムダ式でInsertStatementにTableクラスの項目名を指定し、値を設定する。

Users.insert {
    it[name] = "田中太郎"
    it[money] = 10000
    it[jobID] = 1
}

DAO

エンティティクラス.newでInsert項目に値を設定する。

User.new {
    name = "田中太郎"
    money = 10000
    jobID = 1
}

UPDATE

UPDATE(全レコード)

テーブルのレコード全てを更新するSQL。

UPDATE FROM users SET money = 0

DSL

INSERTのDSLと同じような感じ。

Users.update { it[money] = 0 }

DAO

all()で取得してから、SizedIterableをforEachして更新する。

User.all().forEach { it.money = 0 }

UPDATE(条件を指定して更新)

条件に一致したレコードを更新するSQL。

UPDATE FROM users SET money = 0 WHERE id = 1

DSL

update { Op <Boolean> }で指定する。

val id = 1
Users.update({ Users.id eq id }) { it[money] = 0 } 

DAO

findで更新対象を取得してから更新する。

val id = 1
val user = User.find { Users.id eq id }.single()
user.money = 0

DELETE

DELETE(全レコード削除)

テーブルのレコード全てを削除するSQL。

DELETE FROM users

DSL

deleteAll()を使用する。

Users.deleteAll()

DAO

all()で取得してから、SizedIterableをforEachして削除する。

User.all().forEach{ it.delete() }

DELETE(条件を指定して削除)

条件に一致したレコードを削除するSQL。

DELETE FROM users WHERE id = 1

DSL

delete { Op <Boolean> }で指定する。

val id = 1
Users.deleteWhere { Users.id eq id }

DAO

findで削除対象を取得してから削除する。

val id = 1
User.find { Users.id eq id }.single().delete()

おわりに

DSLとDAO、どちらがいいんだろう…
使いやすいほうを使えば良いんでしょうか。