【ScalikeJDBC入門】SQLInterpolationをQueryDSLに書き換えてみた

Scalaとかその他諸々をやっている新米エンジニアです。
若輩者ながら「[Opt Technologies Advent Calendar 2019](https://qiita.com/advent-calendar/2019/opt-technologies)」の7日目を書いてみました。

何か変なことを言っていたら優しくマサカリを投げてください:pray:

## 記事の主旨
自分のプロダクトではDBアクセスライブラリに[ScalikeJDBC](http://scalikejdbc.org/)を使っています。
で、SQLを実装する際にSQLが複雑だなぁと思うと、ついつい[SQLInterpolation](http://scalikejdbc.org/documentation/sql-interpolation.html)でSQLをベタ書きしてしまうことが多いです。

```scala:こういうの
sql"SELECT id, name FROM hoge;"
```

ただこれは書く時は楽だけど、

- 型安全にしたい
- 楽にリファクタしたい
- エディターでみたときに色が付いて欲しい

みたいな問題が出てきますよね。

ということで、実際に自分が書いた[SQLInterpolation](http://scalikejdbc.org/documentation/sql-interpolation.html)を[QueryDSL](http://scalikejdbc.org/documentation/query-dsl.html)に書き換えてみました。

今回はそのBefore/Afterをまとめます。
[ScalikeJDBC](http://scalikejdbc.org/)には情報豊富な公式ドキュメントがありますが、公式だけだと自分のような入門者にはちょっと読み取りにくい部分があったのでそういった方の参考になったら良いなという気持ちです。

※実際に使っているコードをいい感じに抽象化して書いています。

## SQLSyntaxSupportを定義
早速QueryDSLの書き方の話をしたいところですが、まずはテーブルやカラムを定義しそれらをQueryDSL上に埋め込む必要があります。（そうしないと元のハードコードとあまり変わりがありませんので..）

[公式ドキュメント](http://scalikejdbc.org/documentation/sql-interpolation.html)の`SQLSyntaxSupport`の章に詳細は書いてあります。

```scala:before
import Const // これはサンプル
import scalikejdbc._

case class Member(
  id: Int,
  name: String)

object Member extends SQLSyntaxSupport[Member] {
  def select()(implicit session: DBSession = autoSession): Seq[Member] = {
    sql"""
         SELECT id, name
         FROM employee_member
         WHERE company_code = ${Const}
       """.map(rs => Member(
       id = rs.int("id"),
       name = rs.string("name"))).list().apply()
  }
}
```
```scala:after
import Const
import scalikejdbc._

case class Member(
  id: Int,
  name: String,
  companyCode: Option[Int] = None)
  // SQLSyntaxSupportを使う都合上、出力時に必要ないcompanyCodeもフィールドを作る。
  // デフォルト値を設定しているのはケースクラスをインスタンス化するときに値を渡したくないため。
  // DBアクセス時に使うケースクラスと出力後に使うケースクラスを分けるなどの手もあると思う。

object Member extends SQLSyntaxSupport[Member] {

  override val tableName = "employee_member"
  override lazy val columns: Seq[String] = Seq("id", "name", "company_code")
  val m = Member.syntax("m")
  override val autoSession = AutoSession

  def apply(rn: ResultName[Member], rs: WrappedResultSet): Member = {
    Member(
      id = rs.int(rn.id),
      name = rs.string(rn.name))
  }

  def select()(implicit session: DBSession = autoSession): Seq[Member] = {
    withSQL {
      select(m.result.id, m.result.name)
        .from(Member as m)
        .where.eq(m.status, Const)
    }.map(rs => Member(m.resultName, rs)).list().apply()
  }
}
```
### tabelName
DB上に存在するテーブル名がコンパニオンオブジェクト名と異なる場合はoverrideする。
もしoverrideしなかった場合、今回では`member`テーブルを読みにいくことになる。

### columns
SQL実行時に使用するカラムを定義する。
DB上の実際のカラム名である必要があるため、camelなのかsnakeなのかには注意が必要。

### SQLSyntaxProvider
変数`m`の定義が該当します。
`Member.syntax`の定義を見に行くと、どうやら`QuerySQLSyntaxProvider`を定義しているようです。

詳細は理解しきれていないのですが、ざっくり言うと「テーブルに対応する適切なカラム名を提供してくれる子」のようです。そのため、`m.id`,`m.status`などの記載が可能になります。

### apply
beforeと同様にSQLの末尾でコンストラクトすることももちろん可能ですが、こっちの方がスッキリ書けるので切り出しました。
呼び出し時に渡す`resultName`を使うことで、`"id"`,`"status"`などを直接渡すハードコードをする必要もなくなりました。
※@seratch さんからコメントいただいたので書き換えました。

### Auto Macrosを使う
さて、SQLSyntaxSupportの定義として最低限やるべきところはこんなところです。
そしてマクロを使うといわゆるボイラープレートコードを避けることができます。使ってみましょう。

```scala:マクロを使ったさらなるafter
import Const
import scalikejdbc._

case class Member(
  id: Int,
  name: String,
  companyCode: Option[Int] = None)

object Member extends SQLSyntaxSupport[Member] {

  override val tableName = "employee_member"
  override lazy val columns: Seq[String] = autoColumns[Member]()//マクロにした
  val m = Member.syntax("m")
  override val autoSession = AutoSession

  def apply(rn: ResultName[Member])(rs: WrappedResultSet): Member = autoConstruct(rs, rn, "companyCode") //マクロにした
  // コンストラクトしないで欲しいカラムは第3引数目から渡す(可変長引数)
  // 「ケースクラスのフィールド名と一致するString型」で渡すことに注意

  def select()(implicit session: DBSession = autoSession): Seq[Member] = {
    withSQL {
      select(m.result.id, m.result.name)
        .from(Member as m)
        .where.eq(m.status, Const)
    }.map(Member(m.resultName)).list().apply()
  }
}
```

- autoConstruct: `ResultSet`から値を自動で抽出してコンストラクトしてくれる
- autoColumns: ケースクラスからカラム名を抽出してくれる

また抽出/コンストラクトしたくないフィールドについては明示すれば無視してくれるので便利。
素直に[公式ドキュメント](http://scalikejdbc.org/documentation/auto-macros.html)に従えば難しくないと思います。

さて、次からは改めてQueryDSLの書き方にフォーカスしていきます。
以降は`case class`や`tableName`のoverrideあたりは省略し、SQLの実装のみ記載します。

## シンプルなSELECT文を組み立てる
ようやくQueryDSLを組み立てる話をします。

```scala:before
    sql"""
         SELECT id, name
         FROM employee_member
         WHERE status = ${Const.1}
         AND company_code = ${Const.2}
       """
```
```scala:after
    withSQL {
      select(m.result.id, m.result.name)
        .from(Member as m)
        .where.eq(m.status, Const.1)
        .and.eq(m.companyCode, Const.2)
    }
```

### SELECT句
`select`の書き方はまさに公式ドキュメントにはあまり書いてないと思いました。
ライブラリのコードを読めばわかるのですが、実はいくつか書き方があります。
https://github.com/scalikejdbc/scalikejdbc/blob/c0feba09c8b4d9cecec4cf8667b52da61e958094/scalikejdbc-interpolation/src/main/scala/scalikejdbc/QueryDSLFeature.scala#L33-L50

#### 引数ありのselect
`SQLSyntax`を可変長引数で取るので、`SQLSyntax`になったカラム名を好きなだけ渡せると考えてよいでしょう。

`m.id`,`m.status`はどうやら`SQLSyntax`なのでそれを渡すことができそうです。
ただし、こちらの[scalikeJDBC でのカラム名](https://qiita.com/saikyouyaki/items/f9e3e4f36615e26f9c17#3%E7%A8%AE%E3%81%AE%E3%82%AB%E3%83%A9%E3%83%A0%E5%90%8D)を参考にして今回は`m.result.id`で渡しています。

#### 引数なしのselect
`select.from`,`select.all`,`selectFrom`などの書き方もあります。

### FROM句
ここだけ`.`で繋いで書かないので個人的には違和感を感じる。

ただまあとりあえず入門時には、

- `from`は`TableAsAliasSQLSyntax`を取るメソッド
- `${オブジェクト名} as ${そのオブジェクトのsyntaxを定義した変数名}`のような書き方をする

ということを覚えておけば、それっぽいものを書くことはできます。
後日ちゃんと読み解いて追記したい。

### WHERE句
`where`は引数を取るメソッド、もしくは単なる変数の複数種類あるようです。
https://github.com/scalikejdbc/scalikejdbc/blob/c0feba09c8b4d9cecec4cf8667b52da61e958094/scalikejdbc-core/src/main/scala/scalikejdbc/interpolation/SQLSyntax.scala#L53-L55

ただしメソッドの場合の引数は1つなので、比較条件が複数ある場合は`.and.eq(hoge, fuga).and ...` とする方が良いのでしょうね。

#### 比較演算子の使い方
引数を渡す場合は`.eq(column, value)`のように使えるよう。
なので第1引数はもちろん比較したいカラム名。
第2引数は第1引数と比較したいカラムでも良いし、値を入れても良い。

ちなみにライブラリのコード読んだ感じだと、nullとかNoneを渡しても良いみたいだね...?

#### その他の比較演算子(?)
`eq(equal)`以外にも基本的なものは揃っています。
例えば`ne(not equal)`,`gt(greater than)`,`le(less or equal)`,`isNull`,`in`,`like`など。
https://github.com/scalikejdbc/scalikejdbc/blob/c0feba09c8b4d9cecec4cf8667b52da61e958094/scalikejdbc-core/src/main/scala/scalikejdbc/interpolation/SQLSyntax.scala#L57-L195

#### ※()を使う場合
上記のようなメソッドを引数なしで使う場合は、単なるSQLSyntax(`sqls"and"`みたいなの)を組み立てるだけのようです。
なので次のように()を使いたい場合は `roundBracket` をうまく使えば実現できそうですね。※試してはいない

```sql
SELECT *
FROM hoge
WHERE (id = 10 AND name = 'fuga')
  OR (id = 15 AND name = 'hige');
```

### 他に付け足せる句（例）
```scala:after(before/afterする必要もない気がしたのでafterのみ)
withSQL {
  select
    .from(Hoge as h)
    .orderBy(h.id).desc // もしくは.ascだね
    .limit(4)
    .offset(0)
  }
```

## 重複カットする (distinct)
```scala:before
sql"""
     SELECT DISTINCT id, name
     FROM hoge
   """
```
```scala:after
import sqls.distinct // 必要なので一応明記

withSQL {
  select(distinct(h.id, h.id))
    .from(Hoge as h)
}
```

### distinct
どうやらdistinctは可変長を引数を受け取って、CSV形式(カンマ区切り)のSQLSyntaxを作ってくれるらしい。※[参照](https://github.com/scalikejdbc/scalikejdbc)

>def distinct(columns: SQLSyntax*): SQLSyntax = sqls"distinct ${toCSV(columns)}"

>private[scalikejdbc] def toCSV(parts: scala.collection.Seq[SQLSyntax]): SQLSyntax = join(parts, sqls",", false)



## 集計する (groupBy, having, sumなど)
```scala:before
sql"""
     SELECT id, name
     FROM hoge
     GROUP BY id, name
     HAVING SUM(num) > 0
   """
```
```scala:after
import sqls.{ sum, gt } // 必要なので一応明記

withSQL{
  select(h.id, h.name)
    .from(Hoge as h)
    .groupBy(h.id, h.name)
    .having(gt(sum(h.num), 0))
}
```
### groupBy
distinctと少し実装は異なっているようだけど、同じように可変長引数を渡して使える。

### having
SQLSyntaxを引数として受け取るよう。
whereのように変数としては扱えないことに注意。

### 集計メソッド
`sum`以外にも、もちろん`count`,`avg`,`max`,`min`などがありますね。
https://github.com/scalikejdbc/scalikejdbc/blob/c0feba09c8b4d9cecec4cf8667b52da61e958094/scalikejdbc-core/src/main/scala/scalikejdbc/interpolation/SQLSyntax.scala#L332-L345

ちなみにシンプルなSQLSyntaxを作っているだけなので、SELECT句にも埋められますね。
>def sum(column: SQLSyntax): SQLSyntax = sqls"sum(${column})"

## テーブル結合 (inner join, left joinなど)
```scala:before
sql"""
     SELECT id, name
     FROM hoge
     INNER JOIN fuga ON hoge.id = fuga.id
     LEFT JOIN hige ON fuga.id = hige.id
   """
```
```scala:after
withSQL{
  select(h.id, h.name)
    .from(Hoge as h)
    .innerJoin(Fuga as Fuga.f).on(h.id, Fuga.f.id)
    .leftJoin(Hige as Hige.hi).on(f.id, Hige.hi.id)
}
```
### join
join系は`from`と同様に`TableAsAliasSQLSyntax`を取る。
そのため上述したように書くには、`Fuga`,`Hige`も`Hoge`と同様に以下が定義されている必要がある。

- `SQLSyntaxSupport[A]`を継承したオブジェクト
- 上記オブジェクトの`tableName`,`columns`
- 上記オブジェクトの`syntax`を定義した変数名

※上記SQLを実装したオブジェクト内で`val f = Fuga.syntax("f")`のような定義をすれば、`Fuga.f`と書かずとも`f`で事足りる。
※SQLSyntaxSupport[A]を継承したオブジェクトを作らずにテーブル結合させたいときはjoin系を諦めて、`.append(sqls"INNER JOIN ...")`を使う手もある。

### on
`on`はシンプルに`SQLSyntax`を2つ取るだけ。

## サブクエリーを使う
```scala:before
val subQuery: SQLSyntax = 
  sqls"""
    SELECT id, name
    from fuga
  """

// -------------------------

sql"""
     SELECT id, name
     FROM hoge
     INNER JOIN (${subQuery}) AS sub ON hoge.id = sub.id
   """
```

```scala:after
val sub = SubQuery.syntax("sub").include(f)

val subQuery: TableAsAliasSQLSyntax =
  select(h.id).from(fuga as f).as(sub)

withSQL{
  select(h.id, h.name)
    .from(Hoge as h)
    .innerJoin(subQuery as sub).on(h.id, sub(f).id)
}
```
サブクエリーを`TableAsAliasSQLSyntax`として定義することで、普通のテーブルと同じように扱える。
後日もうちょっと追記したい。

## (非推奨)うまくいかない時の荒技
「`SQLSyntax`を受け取って、それをSQL化して`SQLSyntax`としてまた返す」みたいなメソッドは多い。
そのため`SQLSyntax`を`sqls""`のようなString interpolationで作って、そのまま渡すこともできる。

例えばこんな感じに。

```scala
withSQL{
    select(
      h.id,
      h.name,
      sqls"fuga AS hoge_fuga"
      sqls"sum(CASE WHEN h.hoge IS NULL THEN 0 ELSE 1 END) AS hogehoge",
      sqls"IFNULL(h.hoge, 'hoge') AS hogenull")
      .from(Hoge as h)
      .groupBy(h.id)
      .where
      .eq(h.status, Const.1)
      .append(sqls"AND ${h.hoge} REGEXP '^[0-9]+$$'")
}
```

あまりよろしくないと思うけど、「①QueryDSLでメソッドが用意されていないもの」「②うまくいかなかったので後でリファクタする気のもの」をとりあえずString interpolationで埋め込むのも一つの手段だとは思う。