TL;DR
Oracle にのみ存在する構文、MERGE INTOのように CRUD をネイティブクエリで実行せざるを得ない場合を想定し、ネイティブクエリでの実行方法を解説します。
メンバー募集中！
サーバーサイド Kotlin コミュニティを作りました、ぜひご参加ください！！
Model の作成
以下のテーブルを前提にクエリを実行します。
Department.kt
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.javatime.datetime
object Department : Table("DEPARTMENT") {
val departmentId = integer("DEPARTMENT_ID") //部署ID
val departmentName = varchar("DEPARTMENT_NAME", 32) //部署名
val createdAt = datetime("CREATED_AT") //作成日時
val updatedAt = datetime("UPDATED_AT") //更新日時
}
ネイティブクエリを実行するクラス
NativeQueryDataAccessor.kt
package example.koin.data
import org.jetbrains.exposed.sql.IColumnType
import org.jetbrains.exposed.sql.statements.api.ExposedConnection
import org.jetbrains.exposed.sql.statements.api.PreparedStatementApi
import java.sql.ResultSet
/**
* ネイティブクエリはこのクラスを通して実行する
*/
class NativeQueryDataAccessor {
private lateinit var statement: PreparedStatementApi;
/**
* ネイティブクエリを発行するのに必要な情報を準備する
*/
fun prepareStatement(
conn: ExposedConnection<*>,
sql: String,
args: Iterable<Pair<IColumnType, Any?>>,
returnKeys: Boolean = true
) {
//参考:returnKeysについて
//https://docs.oracle.com/cd/E16338_01/java.112/b56281/jdbcvers.htm#CHDEGDHJ
statement =
conn.prepareStatement(sql, returnKeys)
statement.fillParameters(args)
}
/**
* 登録・更新・削除用のネイティブクエリを実行する
*/
fun executeUpdate(): Int {
return statement.executeUpdate()
}
/**
* 検索用のネイティブクエリを実行する
* Tはクエリの列を格納するデータクラスを想定
*/
fun <T> executeQuery(fetch: (resultSet: ResultSet) -> T): List<T> {
val resultSet = statement.executeQuery()
val result = mutableListOf<T>()
while (resultSet.next()) {
val row = fetch(resultSet)
result.add(row)
}
return result
}
}
参照
Main.kt
package example.koin.data
import example.koin.data.model.Department
import example.koin.data.record.DepartmentRecord
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.selectAll
import org.jetbrains.exposed.sql.transactions.TransactionManager
import org.jetbrains.exposed.sql.transactions.transaction
fun main(){
Database.connect(
url = "jdbc:mysql://127.0.0.1:13306/exposed_local",
driver = "com.mysql.cj.jdbc.Driver",
user = "exposer",
password = "secret"
)
transaction {
val nativeQueryDataAccessor = NativeQueryDataAccessor()
nativeQueryDataAccessor.prepareStatement(
TransactionManager.current().connection,
"select DEPARTMENT_ID, DEPARTMENT_NAME from DEPARTMENT where DEPARTMENT_ID = ?",
listOf(
Pair(Department.departmentId.columnType, 1),
)
)
//実行
val result = nativeQueryDataAccessor.executeQuery {
//ここはResultSetを受ける前提で好きに書き換えられる
//Stringでアクセスするのが嫌ならindex
DepartmentRecord(
it.getInt(Department.departmentId.name),
it.getString(Department.departmentName.name),
)
}
println(result)
}
}
ポイントはResultSetの中身の取り出しは呼び出し側のラムダで定義していることです。
これにより、SELECTに指定する列がなんであれ柔軟に取り出し方を変えることができます。
実行結果
[DepartmentRecord(departmentId=1, departmentName=総務)]
登録・更新・削除
同じ要領で登録・更新・削除も実行可能です。
Main.kt
package example.koin.data
import example.koin.data.model.Department
import example.koin.data.record.DepartmentRecord
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.selectAll
import org.jetbrains.exposed.sql.transactions.TransactionManager
import org.jetbrains.exposed.sql.transactions.transaction
fun main(){
Database.connect(
url = "jdbc:mysql://127.0.0.1:13306/exposed_local",
driver = "com.mysql.cj.jdbc.Driver",
user = "exposer",
password = "secret"
)
transaction {
val nativeQueryDataAccessor = NativeQueryDataAccessor()
nativeQueryDataAccessor.prepareStatement(
TransactionManager.current().connection,
"""
INSERT INTO DEPARTMENT (
DEPARTMENT_ID,
DEPARTMENT_NAME,
CREATED_AT,
UPDATED_AT
) VALUES (
?,
?,
SYSDATE(),
SYSDATE()
)
""".trimIndent(),
listOf(
Pair(Department.departmentId.columnType, 4),
Pair(Department.departmentName.columnType, "監査"),
)
)
//実行
nativeQueryDataAccessor.executeUpdate()
commit()
}
}
おわりに
ネイティブクエリの実行についてはドキュメントには一切記載がないため、コードを解析しながら実装を進めました…
メンバー募集中！
サーバーサイド Kotlin コミュニティを作りました、ぜひご参加ください！！
また関西在住のソフトウェア開発者を中心に、関西エンジニアコミュニティを一緒に盛り上げてくださる方を募集しています。
よろしければ Conpass からメンバー登録よろしくお願いいたします。
Discussion