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

Scalaとかその他諸々をやっている新米エンジニアです。
若輩者ながら「Opt Technologies Advent Calendar 2019」の7日目を書いてみました。

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

記事の主旨

自分のプロダクトではDBアクセスライブラリにScalikeJDBCを使っています。
で、SQLを実装する際にSQLが複雑だなぁと思うと、ついついSQLInterpolationでSQLをベタ書きしてしまうことが多いです。

こういうの

sql"SELECT id, name FROM hoge;"

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

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

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

ということで、実際に自分が書いたSQLInterpolationをQueryDSLに書き換えてみました。

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

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

SQLSyntaxSupportを定義

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

公式ドキュメントのSQLSyntaxSupportの章に詳細は書いてあります。

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()
  }
}

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の定義として最低限やるべきところはこんなところです。
そしてマクロを使うといわゆるボイラープレートコードを避けることができます。使ってみましょう。

マクロを使ったさらなる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: ケースクラスからカラム名を抽出してくれる

また抽出/コンストラクトしたくないフィールドについては明示すれば無視してくれるので便利。
素直に公式ドキュメントに従えば難しくないと思います。

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

シンプルなSELECT文を組み立てる

ようやくQueryDSLを組み立てる話をします。

before

    sql"""
         SELECT id, name
         FROM employee_member
         WHERE status = ${Const.1}
         AND company_code = ${Const.2}
       """

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 でのカラム名を参考にして今回は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 をうまく使えば実現できそうですね。※試してはいない

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

他に付け足せる句（例）

after(before/afterする必要もない気がしたのでafterのみ)

withSQL {
  select
    .from(Hoge as h)
    .orderBy(h.id).desc // もしくは.ascだね
    .limit(4)
    .offset(0)
  }

重複カットする (distinct)

before

sql"""
     SELECT DISTINCT id, name
     FROM hoge
   """

after

import sqls.distinct // 必要なので一応明記

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

distinct

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

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など)

before

sql"""
     SELECT id, name
     FROM hoge
     GROUP BY id, name
     HAVING SUM(num) > 0
   """

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など)

before

sql"""
     SELECT id, name
     FROM hoge
     INNER JOIN fuga ON hoge.id = fuga.id
     LEFT JOIN hige ON fuga.id = hige.id
   """

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つ取るだけ。

サブクエリーを使う

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
   """

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で作って、そのまま渡すこともできる。

例えばこんな感じに。

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で埋め込むのも一つの手段だとは思う。