TL;DR
- Oracle の独自関数である
NVLを exposed で使う方法がわかる -
NVLに限らず各 DB の独自関数については ORM がデフォルトで提供していないため、同様の方法で実装することができる
メンバー募集中！
サーバーサイド Kotlin コミュニティを作りました！
Kotlin ユーザーはぜひご参加ください！！
What's exposed ?
Kotlin 製の ORM（Object-Relational Mapping）ライブラリです。
wiki が提供されていますが結構情報が貧弱で、サブクエリを使った JOIN の方法なども書かれていないため、ハマりポイントになっています。
サブクエリを使った JOIN についてはまた別途記事を出します。
What's NVL?
SQL の NVL 関数とは Null Value Logic の略で、NULL 値を別の値に変換する関数です。
Oracle が独自実装した関数で、Oracle でのみ利用できます。
NVL 関数では第一引数が NULL でなければ第一引数の値を、NULL であれば第二引数の値を返します。
select nvl(1, null) from dual; -- 1
select nvl(null, 2) from dual; -- 2
で、どうするの？
CustomFunctionを使うことで実装が可能となります。
/**
* Represents a custom SQL function.
*/
open class CustomFunction<T>(
/** Returns the name of the function. */
val functionName: String,
columnType: IColumnType,
/** Returns the list of arguments of this function. */
vararg val expr: Expression<*>
) : Function<T>(columnType) {
override fun toQueryBuilder(queryBuilder: QueryBuilder): Unit = queryBuilder {
append(functionName, '(')
expr.appendTo { +it }
append(')')
}
}
toQueryBuilderを見ると、受け取ったfunctionName(...)というようにクエリを組み立ててくれるようです。
実装例
では表題のとおり、Oracle の NVL 関数を呼べるように実装してみようと思います。
今回は仮にHOGEというID,作成日,更新日のみを持つテーブルを例に考えてみます。
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.javatime.datetime
object HogeTable : Table("HOGE") {
val hogeId = varchar("HOGE_ID", 10) //ID
val createdAt = datetime("CREATED_AT") // 作成日
val updatedAt = datetime("UPDATED_AT").nullable() // 更新日
}
NVL 関数を定義し、呼び出す実装例は以下の通りです。
class HogeDataAccessor {
fun hoge() {
val nvlDate = nvl(HogeTable.createdAt, HogeTable.updatedAt).alias("latestDate")
val result = HogeTable
.slice(
HogeTable.hogeId,
nvl(HogeTable.createdAt, HogeTable.updatedAt).alias("latestDate")
)
.select {
(HogeTable.hogeId eq "1")
}.firstOrNull()
if (result != null) {
print(result[nvlDate])
}
}
private fun nvl(value: ExpressionWithColumnType<*>, compareTo: ExpressionWithColumnType<*>) =
CustomFunction<Any>("nvl", value.columnType, value, compareTo)
}
ちなみに、nvl の第二引数に 0 や"hoge"といったただの値(リテラル)を渡したい場合は、
nvl(hogeColumn, stringLiteral)
のように型Literalと書くと良いようです。
ポイントは大きく 3 つあります。
nvl 関数の引数の型
定義した nvl 関数の引数の型は Kotlin の世界の型に合わせてあげる必要があります。
上記のHogeTableの実装例の場合だと、厳密に型を定義しようとするならば以下のように nvl 関数を定義する必要があります。
private fun nvl(value: ExpressionWithColumnType<Column<LocalDateTime?>>, compareTo: ExpressionWithColumnType<Column<LocalDateTime>>) =
CustomFunction<Any>("nvl", value.columnType, value, compareTo)
しかし、そうしてしまうと汎用的ではない nvl 関数が出来上がってしまうため、引数の型をExpressionWithColumnType<*>としています。
型の書き方はいろいろとあるかと思うので、お好みの書き方で問題ないと思います。
ResultSet へのアクセス
クエリ発行後、exposed が DB から取得したデータを取得するためにnvl(HogeTable.createdAt, HogeTable.updatedAt).alias("latestDate")を変数として定義しておく必要があります。
where 句の条件式でも呼び出すことが可能
条件は適当ですが、以下のようなイメージです。
fun hoge() {
val nvlDate = nvl(HogeTable.createdAt, HogeTable.updatedAt).alias("latestDate")
val result = HogeTable
.slice(
HogeTable.hogeId,
)
.select {
(nvl(HogeTable.createdAt, HogeTable.updatedAt).alias("latestDate") eq HogeTable.createdAt)
}.firstOrNull()
if (result != null) {
print(result[nvlDate])
}
}
おわりに
exposed のドキュメントは英語な上にちょっと捻ったクエリが出てきた場合にはあまり参考にならないことが多いため、実際に手を動かして学んだ内容は都度アウトプットとして残しておきたいです。
メンバー募集中！
サーバーサイド Kotlin コミュニティを作りました！
Kotlin ユーザーはぜひご参加ください！！
また関西在住のソフトウェア開発者を中心に、関西エンジニアコミュニティを一緒に盛り上げてくださる方を募集しています。
よろしければ Conpass からメンバー登録よろしくお願いいたします。
Discussion