ExposedでDBの独自関数を呼び出す

TL;DR

Oracle の独自関数であるNVLを Exposed で使う方法がわかる
NVLに限らず各 DB の独自関数については ORM がデフォルトで提供していないため、同様の方法で実装することができる

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

空の箱

からのはこ