LoginSignup
0
3

More than 3 years have passed since last update.

@jas

【ScalikeJDBC】QueryDSLでSELECT パターン別記述例

Posted at

既存のSQLをQueryDSLで書き直していくにあたり、「これどう書けばいいんだろう」と困ることがあっていろいろ調べたのでメモしておく。

例に使うテーブルとクラス定義

テーブル

userテーブル

user_id name role_id
1 山田 太郎 1
2 田中 一郎 2
3 佐藤 史郎 2

roleテーブル

role_id name
1 Administrator
2 Developer

userとroleはrole_idで紐付くものとする。

クラス定義

case class User(userId: Int, name: String, roleId: Int)
object User extends SQLSyntaxSupport[User] {
  def apply(rs: WrappedResultSet, rn: ResultName[User]): User = autoConstruct(rs, rn)
}

case class Role(roleId: Int, name: String)
object Role extends SQLSyntaxSupport[Role] {
  def apply(rs: WrappedResultSet, rn: ResultName[Role]): Role = autoConstruct(rs, rn)
}

// ユーザーとロールをJOINした結果を表すクラス
case class UserWithRole(userId: Int, userName: String, roleId: Int, roleName: String)
object UserWithRole {
  def apply(rs: WrappedResultSet, rnUser: ResultName[User], rnRole: ResultName[Role]): UserWithRole =
    UserWithRole(
      rs.int(rnUser.userId),
      rs.string(rnUser.name),
      rs.int(rnRole.roleId),
      rs.string(rnRole.name),
    )
}

パターン別記述例

前提として、全ての例において

val u = User.syntax("u")
val r = Role.syntax("r")

が定義されているものとする。
また、DBSessionのimplicit引数は省略している。

基本

SQL
SELECT * FROM user
QueryDSL
val result = withSQL {
  select(u.result.*).from(User as u)
}.map(User(_, u.resultName)).list().apply()

println(result) // List(User(1,山田 太郎,1), User(2,田中 一郎,2), User(3,鈴木 史郎,2))

特定のカラムだけ指定

SQL
SELECT role_id FROM user
QueryDSL
val result = withSQL {
  select(u.result.roleId).from(User as u)
}.map(_.int(1)).list().apply()

println(result) // List(1, 2, 2)

重複排除(DISTINCT)

SQL
SELECT DISTINCT role_id FROM user
QueryDSL
val result = withSQL {
  select(sqls.distinct(u.result.roleId)).from(User as u)
}.map(_.int(1)).list().apply()

println(result) // List(1, 2)

ポイント: 「DISTINCTはどう書けば...？」とか困ったら SQLSyntax(エイリアスはsqls)のメソッドを探せば大体ある。

WHERE

SQL
SELECT * FROM user
 WHERE user_id < 3
 AND (name = '山田 太郎' OR role_id = 2)
QueryDSL
val result = withSQL {
  select(u.result.*).from(User as u)
    .where.lt(u.userId, 3)
    .and.withRoundBracket(_.eq(u.name, "山田 太郎").or.eq(u.roleId, 2))
}.map(User(_, u.resultName)).list().apply()

println(result) // List(User(1,山田 太郎,1), User(2,田中 一郎,2))

ポイント: 括弧はwithRoundBracket, またはsqls.roundBracketで表現できる。

GROUP BY

SQL
SELECT COUNT(1) FROM user
 GROUP BY role_id
 HAVING COUNT(1) > 1
QueryDSL
val result = withSQL {
  select(sqls.count).from(User as u)
    .groupBy(u.roleId)
    .having(sqls.gt(sqls.count, 1))
}.map(_.int(1)).list().apply()

println(result) // List(2)

ポイント: 集計関数(COUNT, MAXとか)もSQLSyntaxにあるので、sqls.count等の書き方ができる。

JOIN

SQL
SELECT u.user_id, u.name, r.role_id, r.name
 FROM user u
 INNER JOIN role r
 ON u.role_id = r.role_id
QueryDSL
val result = withSQL {
  select(u.result.userId, u.result.name, r.result.roleId, r.result.name)
    .from(User as u)
    .innerJoin(Role as r)
    .on(u.roleId, r.roleId)
}.map(UserWithRole(_, u.resultName, r.resultName)).list().apply()

println(result) // List(UserWithRole(1,山田 太郎,1,Administrator), UserWithRole(2,田中 一郎,2,Developer), UserWithRole(3,佐藤 史郎,2,Developer))

JOIN (ON句に複数条件指定)

SQL
SELECT u.user_id, u.name, r.role_id, r.name
 FROM user u
 INNER JOIN role r
 ON u.role_id = r.role_id
 AND r.role_id = 2
QueryDSL
val result = withSQL {
  select(u.result.userId, u.result.name, r.result.roleId, r.result.name)
    .from(User as u)
    .innerJoin(Role as r)
    .on(u.roleId, r.roleId)
    .append(sqls.and.eq(r.roleId, 2))
}.map(UserWithRole(_, u.resultName, r.resultName)).list().apply()

println(result) // List(UserWithRole(2,田中 一郎,2,Developer), UserWithRole(3,佐藤 史郎,2,Developer))

ポイント: ONに条件を複数つけたい場合、appendで対応できる。

こうも書ける。こっちの方がいいかも？

val result2 = withSQL {
  select(u.result.userId, u.result.name, r.result.roleId, r.result.name)
    .from(User as u)
    .innerJoin(Role as r)
    .on(sqls.eq(u.roleId, r.roleId).and.eq(r.roleId, 2))
}.map(UserWithRole(_, u.resultName, r.resultName)).list().apply()

println(result2) // List(UserWithRole(2,田中 一郎,2,Developer), UserWithRole(3,佐藤 史郎,2,Developer))

サブクエリ(FROM)

SQL
SELECT * FROM (
 SELECT u.user_id, u.name user_name, r.role_id, r.name role_name
  FROM user u
  INNER JOIN role r
  ON u.role_id = r.role_id
) AS t
QueryDSL
val t = SubQuery.syntax("t").include(u, r)
val result = withSQL {
  select(u.resultName.userId, u.resultName.name, r.resultName.roleId, r.resultName.name).from(
    select(u.result.userId, u.result.name, r.result.roleId, r.result.name)
      .from(User as u)
      .innerJoin(Role as r)
      .on(u.roleId, r.roleId) as t
  )
}.map(UserWithRole(_, u.resultName, r.resultName)).list().apply()

println(result) // List(UserWithRole(1,山田 太郎,1,Administrator), UserWithRole(2,田中 一郎,2,Developer), UserWithRole(3,佐藤 史郎,2,Developer))

ポイント: SubQuery.syntaxでサブクエリ結果のエイリアスにあたるものを作成できる。メインのselectに指定するカラム名にはresultNameをつける。(理由は後述)

サブクエリ(WHERE)

SQL
SELECT * FROM user
WHERE user_id = (
 SELECT user_id FROM user
 WHERE role_id = 1
)
QueryDSL
val result = withSQL {
  val a = select[User](u.result.*).from(User as u)
    .where.eq(u.userId,
      sqls.roundBracket(select(u.result.userId)
        .from(User as u)
        .where.eq(u.roleId, 1)
        .sql
      )
  )
  println(a.sql.value)
  a
}.map(User(_, u.resultName)).list().apply()

println(result) // List(User(1,山田 太郎,1))

ポイント: サブクエリを括弧で囲むためにはsqls.roundBracketを使う。
sqls.roundBracketは引数にSQLSyntaxをとるので、SQLBuilder(select~)の最後に.sqlをつけてSQLSyntaxに変換する。
ちなみに、.where.inの場合は第2引数にSQLBuilder自体を渡せるので、.sqlでSQLSyntaxに変換する必要もsqls.roundBracketで囲む必要もない。

おまけ1: 実際に発行されているSQLをみる方法

SQLBuilderで組み立てたSQLは、カラムに別名がつけられたりしていて想定と異なる場合がある。
発行されているSQLは SQLBuilder.sql.value で取得できる。

val sqlBuilder = select(u.result.*).from(User as u)
println(sqlBuilder.sql.value) 

// select u.user_id as ui_on_u, u.name as n_on_u, u.role_id as ri_on_u from user u

おまけ2: u.result.*, u.resultName.*, u.* の違い

それぞれ発行されるSQLのカラム名の記述が異なる。

u.result.* はカラム名 + 別名の形になる。たいていはこの書き方が一番エラーになりにくい。

val sqlBuilder1 = select(u.result.*).from(User as u)
println(sqlBuilder1.sql.value)
// select u.user_id as ui_on_u, u.name as n_on_u, u.role_id as ri_on_u from user u

u.resultName.* は別名のみになるため、通常はUnknown Columnでエラーとなる。

val sqlBuilder2 = select(u.resultName.*).from(User as u)
println(sqlBuilder2.sql.value)
// select ui_on_u, n_on_u, ri_on_u from user u

u.* はカラム名のみで、別名はつかない。
テーブル結合した時にそれぞれのテーブルに同じ名前のカラムがあれば、Duplicate column nameでエラーとなる。

val sqlBuilder3 = select(u.*).from(User as u)
println(sqlBuilder3.sql.value)
// select u.user_id, u.name, u.role_id from user u

ここまでをふまえて、サブクエリ(FROM) で、メインのselectにresultNameを指定した理由を説明する。

val t = SubQuery.syntax("t").include(u, r)
val result = withSQL {
  select(u.resultName.userId, u.resultName.name, r.resultName.roleId, r.resultName.name).from(
    select(u.result.userId, u.result.name, r.result.roleId, r.result.name)
      .from(User as u)
      .innerJoin(Role as r)
      .on(u.roleId, r.roleId) as t
  )
}.map(UserWithRole(_, u.resultName, r.resultName)).list().apply()

上記のコードから発行されるSQL全体は以下のようになる。

select ui_on_u, n_on_u, ri_on_r, n_on_r 
from (
 select 
  u.user_id as ui_on_u, 
  u.name as n_on_u, 
  r.role_id as ri_on_r, 
  r.name as n_on_r 
 from user u 
 inner join role r 
 on u.role_id = r.role_id
) t;

サブクエリ結果のカラム名はui_on_uのように別名で返ってくるため、メインでのカラム名指定は別名で行わなければならない。
resultだと、u.user_id as ui_on_uのように変換されるため、Unknown column 'u.user_id' in 'field list'のようなエラーが発生する。
よって、別名のみに変換されるresultNameを指定する必要がある。

まとめ

ソースのコメントが一番の参考資料でした。

0
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
3