TEMPLATEクエリ

SQLテンプレートを利用するクエリ

概要

TEMPLATEクエリはSQLテンプレートを利用して構築するクエリです。

TEMPLATEクエリはコアのモジュールには含まれないオプション機能です。利用するにはGradleの依存関係に次のような宣言が必要です。

val komapperVersion: String by project
dependencies {
    implementation("org.komapper:komapper-template:$komapperVersion")
}

Note

すべてのスターターは上記の設定を含んでいます。したがって、Starterを使う場合には上記の設定は不要です。

Note

komapper-templateモジュールは内部でリフレクションを使います。

fromTemplate

検索を実施するにはfromTemplate関数に SQLテンプレート、bind関数にデータを渡します。検索結果を任意の型に変換するために、select関数にラムダ式を渡します。

val sql = "select * from ADDRESS where street = /*street*/'test'"
val query: Query<List<Address>> = QueryDsl.fromTemplate(sql)
    .bind("street", "STREET 10")
    .select { row: Row -> 
        Address(
            row.getNotNull("address_id"),
            row.getNotNull("street"),
            row.getNotNull("version")
        )
}

select関数に渡すラムダ式に登場するRowはjava.sql.ResultSetやio.r2dbc.spi.Rowの薄いラッパーです。カラムのラベル名やインデックスで値を取得する関数を持ちます。なお、インデックスは0から始まります。

selectAsEntity

結果を任意のエンティティとして受け取りたい場合はselectAsEntityを呼び出します。第一引数にはエンティティのメタモデルを指定します。 SQLテンプレートのSELECT句にはエンティティの全プロパティに対応するカラムが含まれていなければいけません。

次の例では結果をAddressエンティティとして受け取っています。

val sql = "select address_id, street, version from ADDRESS where street = /*street*/'test'"
val query: Query<List<Address>> = QueryDsl.fromTemplate(sql)
  .bind("street", "STREET 10")
  .selectAsEntity(a)

デフォルトではSELECTリストのカラムの順序でエンティティにマッピングしますが、 selectAsEntityの第二引数にProjectionType.NAMEを渡すことでカラムの名前でマッピングできます。

val sql = "select street, version, address_id from ADDRESS where street = /*street*/'test'"
val query: Query<List<Address>> = QueryDsl.fromTemplate(sql)
  .bind("street", "STREET 10")
  .selectAsEntity(a, ProjectionType.NAME)

結果として受け取りたいエンティティクラスに@KomapperProjectionを付与している場合、専用の拡張関数を使って以下のように簡潔に記述できます。

val sql = "select address_id, street, version from ADDRESS where street = /*street*/'test'"
val query: Query<List<Address>> = QueryDsl.fromTemplate(sql)
  .bind("street", "STREET 10")
  .selectAsAddress()

val sql = "select street, version, address_id from ADDRESS where street = /*street*/'test'"
val query: Query<List<Address>> = QueryDsl.fromTemplate(sql)
  .bind("street", "STREET 10")
  .selectAsAddress(ProjectionType.NAME)

options

クエリの挙動をカスタマイズするにはoptionsを呼び出します。ラムダ式のパラメータはデフォルトのオプションを表します。変更したいプロパティを指定してcopyメソッドを呼び出してください。

val sql = "select * from ADDRESS where street = /*street*/'test'"
val query: Query<List<Address>> = QueryDsl.fromTemplate(sql)
  .options {
    it.copy(
      fetchSize = 100,
      queryTimeoutSeconds = 5
    )
  }
  .bind("street", "STREET 10")
  .select { row: Row ->
    Address(
      row.getNotNull("address_id"),
      row.getNotNull("street"),
      row.getNotNull("version")
    )
}

指定可能なオプションには以下のものがあります。

escapeSequence: LIKE句に指定されるエスケープシーケンスです。デフォルトはnullでDialectの値を使うことを示します。
fetchSize: フェッチサイズです。デフォルトはnullでドライバの値を使うことを示します。
maxRows: 最大行数です。デフォルトはnullでドライバの値を使うことを示します。
queryTimeoutSeconds: クエリタイムアウトの秒数です。デフォルトはnullでドライバの値を使うことを示します。
suppressLogging: SQLのログ出力を抑制するかどうかです。デフォルトはfalseです。

executionOptions の同名プロパティよりもこちらに明示的に設定した値が優先的に利用されます。

executeTemplate

更新系のDMLを実行するにはexecuteTemplate関数に SQLテンプレート、bind関数にデータを渡します。

クエリ実行時にキーが重複した場合、org.komapper.core.UniqueConstraintExceptionがスローされます。

val sql = "update ADDRESS set street = /*street*/'' where address_id = /*id*/0"
val query: Query<Long> = QueryDsl.executeTemplate(sql)
    .bind("id", 15)
    .bind("street", "NY street")

returning

returning関数を使うことで、更新系のDMLを実行しかつ結果を取得できます。 returning関数実行後は、fromTemplateで言及したselect関数やselectAsEntity関数が利用できます。

val sql = """
    insert into address
        (address_id, street, version)
    values
        (/*id*/0, /*street*/'', /*version*/0)
    returning address_id, street, version
""".trimIndent()
val query: Query<Address> = QueryDsl.executeTemplate(sql)
    .returning()
    .bind("id", 16)
    .bind("street", "NY street")
    .bind("version", 1)
    .select { row: Row ->
        Address(
            row.getNotNull("address_id"),
            row.getNotNull("street"),
            row.getNotNull("version")
        )
    }
    .single()

Note

上述のSQLテンプレートではPostgreSQLなどでサポートされているRETURNING句を使用していますが、更新系のDMLから結果を返すSQLはDBMSごとに異なることに注意ください。

options

val sql = "update ADDRESS set street = /*street*/'' where address_id = /*id*/0"
val query: Query<Long> = QueryDsl.executeTemplate(sql)
  .bind("id", 15)
  .bind("street", "NY street")
  .options {
    it.copy(
      queryTimeoutSeconds = 5
    )
}

指定可能なオプションには以下のものがあります。

escapeSequence: LIKE句に指定されるエスケープシーケンスです。デフォルトはnullでDialectの値を使うことを示します。
queryTimeoutSeconds: クエリタイムアウトの秒数です。デフォルトはnullでドライバの値を使うことを示します。
suppressLogging: SQLのログ出力を抑制するかどうかです。デフォルトはfalseです。

executionOptions の同名プロパティよりもこちらに明示的に設定した値が優先的に利用されます。

SQLテンプレート

Komapperが提供するSQLテンプレートはいわゆる2-Way-SQL対応のテンプレートです。バインド変数や条件分岐に関する記述をSQLコメントで表現するため、テンプレートをアプリケーションで利用できるだけでなく、pgAdmin など一般的なSQLツールでも実行できます。

例えば条件分岐とバインド変数を含んだSQLテンプレートは次のようになります。

select name, age from person where
/*% if name != null */
  name = /* name */'test'
/*% end */
order by name

上記のテンプレートはアプリケーション上でname != nullが真と評価されるとき次のSQLに変換されます。

select name, age from person where name = ? order by name

name != nullが偽と評価されるとき次のSQLに変換されます。

select name, age from person order by name

Note

上述の例でname != nullが偽と評価されるとき最終的にSQLにwhereキーワードが含まれていないことに気づいたでしょうか？ KomapperのSQLテンプレートは、WHERE句、HAVING句、GROUP BY句、ORDER BY句の内側にSQLの要素が1つも含まれない場合その句を表すキーワードを出力しません。したがって、不正なSQLが生成されることを防ぐために1 = 1を必ずWHERE句に含めるなどの対応は不要です。

select name, age from person where 1 = 1  // このような対応は不要
/*% if name != null */
  and name = /* name */'test'
/*% end */
order by name

バインド変数ディレクティブ

バインド変数は/* expression */のように/*と*/で囲んで表します。 expressionには任意の値を返す式が入ります。

次の'test'のようにディレクティブの直後にはテスト用の値が必須です。

where name = /* name */'test'

最終的にはテスト用の値は取り除かれ上述のテンプレートは次のようなSQLに変換されます。 /* name */は?に置換され、?にはnameが返す値がバインドされます。

where name = ?

IN句にバインドするにはexpressionはIterable型の値でなければいけません。

where name in /* names */('a', 'b')

IN句にタプル形式で値をバインドするにはexpressionをIterable<Pair>型やIterable<Triple>型の値にします。

where (name, age) in /* pairs */(('a', 'b'), ('c', 'd'))

リテラル変数ディレクティブ

リテラル変数は/*^ expression */のように/*^と*/で囲んで表します。 expressionには任意の値を返す式が入ります。

次の'test'のようにディレクティブの直後にはテスト用の値が必須です。

where name = /*^ name */'test'

最終的にはテスト用の値は取り除かれ上述のテンプレートは次のようなSQLに変換されます。 /*^ name */はnameが返す値（この例では"abc"）のリテラル表現（'abc'）で置換されます。

where name = 'abc'

埋め込み変数ディレクティブ

埋め込み変数は/*# expression */のように/*#と*/で囲んで表します。 expressionには任意の値を返す式が入ります。

select name, age from person where age > 1 /*# orderBy */

上述のorderByの式が"order by name"という文字列を返す場合、最終的なSQLは次のように変換されます。

select name, age from person where age > 1 order by name

ifディレクティブ

ifの条件分岐は/*% if expression */で始めて/*% end */で終わります。 expressionには真偽値を返す式が入ります。

/*% if name != null */
  name = /* name */'test'
/*% end */

/*% if expression */と/*% end */の間に/*% else */を入れることもできます。

/*% if name != null */
  name = /* name */'test'
/*% else */
  name is null
/*% end */

forディレクティブ

ループ処理を開始するには、forディレクティブを使用します。

forディレクティブは、/*% for と */ で囲まれたSQLコメントです。

ループ処理は、forディレクティブで開始し、endディレクティブで終了しなければなりません。

次の例では、/*% for name in names */ がforディレクティブです：

/*% for name in names */
employee_name like /* name */'hoge'
  /*% if name_has_next */
/*# "or" */
  /*% end */
/*% end */

/*% for name in names */ ディレクティブでは、names は Iterable オブジェクトを表し、name はその Iterable オブジェクトの各要素に対する識別子（identifier）です。

forディレクティブとendディレクティブの間では、以下の特別な変数を使用できます。

identifier_has_next: 次の繰り返しが実行されるかどうかを示すブール値を返します。
identifier_next_comma: 次の繰り返しが実行される場合は , を返し、それ以外の場合は空の文字列を返します。
identifier_next_or: 次の繰り返しが実行される場合は or を返し、それ以外の場合は空の文字列を返します。
identifier_next_and: 次の繰り返しが実行される場合は and を返し、それ以外の場合は空の文字列を返します。

上記の例では、name_has_next が特別な変数です。

上記の例は、name_next_or を使用して次のように書き換えることができます：

/*% for name in names */
employee_name like /* name */'hoge'
/*# name_next_or */
/*% end */

endディレクティブ

条件分岐やループ処理を終了するには、endディレクティブを使います。

endディレクティブは/*% end */というSQLコメントで表現されます。

パーサーレベルのコメントディレクティブ

パーサーレベルのコメントディレクティブを使用すると、SQLテンプレートが解析された後にコメントを削除できます。

パーサーレベルのコメントを表現するには、/*%! コメント */ という構文を使います。

次のようなSQLテンプレートがあるとします。

select
  name
from
  employee
where /*%! このコメントは削除されます */
  employee_id = /* employeeId */99

上記のSQLテンプレートは、次のSQLへと解析されます。

select
  name
from 
  employee
where
  employee_id = ?

式

ディレクティブ内で参照される式の中では以下の機能がサポートされています。

演算子の実行
プロパティアクセス
関数呼び出し
クラス参照
拡張プロパティや拡張関数の利用

演算子

次の演算子がサポートされています。意味はKotlinの演算子と同じです。

==
!=
>=
<=
>
<
!
&&
||

次のように利用できます。

/*% if name != null && name.length > 0 */
  name = /* name */'test'
/*% else */
  name is null
/*% end */

プロパティアクセス

.や?.を使ってプロパティにアクセスできます。?.はKotlinのsafe call operatorと同じ挙動をします。

/*% if person?.name != null */
  name = /* person?.name */'test'
/*% else */
  name is null
/*% end */

関数呼び出し

関数を呼び出せます。

/*% if isValid(name) */
  name = /*name*/'test'
/*% else */
  name is null
/*% end */

クラス参照

@クラスの完全修飾名@という記法でクラスを参照できます。例えばexample.Directionというenum classにWESTという要素がある場合、次のように参照できます。

/*% if direction == @example.Direction@.WEST */
  direction = 'west'
/*% end */

拡張プロパティと拡張関数

Kotlinが提供する以下の拡張プロパティと拡張関数をデフォルトで利用できます。

val CharSequence.lastIndex: Int
fun CharSequence.isBlank(): Boolean
fun CharSequence.isNotBlank(): Boolean
fun CharSequence.isNullOrBlank(): Boolean
fun CharSequence.isEmpty(): Boolean
fun CharSequence.isNotEmpty(): Boolean
fun CharSequence.isNullOrEmpty(): Boolean
fun CharSequence.any(): Boolean
fun CharSequence.none(): Boolean

/*% if name.isNotBlank() */
  name = /* name */'test'
/*% else */
  name is null
/*% end */

また、Komapperが定義する以下の拡張関数も利用できます。

fun String?.asPrefix(): String?
fun String?.asInfix(): String?
fun String?.asSuffix(): String?
fun String?.escape(): String?

例えば、asPrefix()を呼び出すと"hello"という文字列が"hello%"となり前方一致検索で利用できるようになります。

where name like /* name.asPrefix() */

同様にasInfix()を呼び出すと中間一致検索用の文字列に変換し、asSuffix()を呼び出すと後方一致検索用の文字列に変換します。

escape()は特別な文字をエスケープします。例えば、"he%llo_"という文字列を"he\%llo\_"のような文字列に変換します。

Note

asPrefix()、asInfix()、asSuffix()は内部でエスケープ処理を実行するので別途escape()を呼び出す必要はありません。

最終更新 October 5, 2024: Add special variables in the for directive (4d36446)