ExposedのDSL/DAOサンプル集

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

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

# 公式wiki
https://github.com/JetBrains/Exposed/wiki

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

``` sql
SELECT * FROM users
```

#### DSL
selectAll()を使用する。

``` kotlin
Users.selectAll()
```

#### DAO
all()を使用する。

``` kotlin
User.all()
```

## SELECT(count(*))
条件に一致するレコードの件数を取得するSQL。

```sql
SELECT COUNT(*) FROM users WHERE age = 20
```

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

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

#### DAO
DSLとほぼ同じ。

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

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

``` sql
SELECT * FROM users WHERE id = 1
```


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

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

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

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

## SELECT(列指定)
取得する列を指定するSQL。

``` sql
SELECT name, age FROM users WHERE id = 1
```


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

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

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

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

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

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

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

``` kotlin
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の方がシンプル。

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

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

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

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

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

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

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

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

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

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

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

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

``` kotlin
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。

``` 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を書くのと肌感は変わらない。

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

#### DAO
DSLとほぼ同じ。

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

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

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

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

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

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

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

# UPDATE

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

```sql
UPDATE FROM users SET money = 0
```

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

``` kotlin
Users.update { it[money] = 0 }
``` 

#### DAO
all()で取得してから、SizedIterable<T>をforEachして更新する。

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

## UPDATE(条件を指定して更新)
条件に一致したレコードを更新するSQL。

```sql
UPDATE FROM users SET money = 0 WHERE id = 1
```

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

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

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

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

# DELETE

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

```sql
DELETE FROM users
```

#### DSL
deleteAll()を使用する。

``` kotlin
Users.deleteAll()
```

#### DAO
all()で取得してから、SizedIterable<T>をforEachして削除する。

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

## DELETE(条件を指定して削除)
条件に一致したレコードを削除するSQL。

``` sql
DELETE FROM users WHERE id = 1
```

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

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

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

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


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