Kotlin Exposed その3, DSL で select where する

前回 書いたコード(DSL)を使って select where します。

環境:

$ kotlin -version
Kotlin version 2.1.0-release-394 (JRE 17.0.14+7-LTS)

Exposed DSL

前回書いたコード:

// exposed.main.kts

@file:Repository("https://repo1.maven.org/maven2/")

@file:DependsOn("org.jetbrains.exposed:exposed-core:0.59.0")
@file:DependsOn("org.jetbrains.exposed:exposed-jdbc:0.59.0")
@file:DependsOn("com.h2database:h2:2.3.232")

import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.Database

import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.addLogger
import org.jetbrains.exposed.sql.StdOutSqlLogger
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.selectAll
import org.jetbrains.exposed.sql.ResultRow

data class Pokemon(val id: Int, val name: String)

object PokemonsTable : Table("pokemons") {
    val id = integer("id").autoIncrement()
    val name = varchar("name", length = 128)

    override val primaryKey = PrimaryKey(id)
}

val toPokemon: (ResultRow)->Pokemon = {
    Pokemon(it[PokemonsTable.id], it[PokemonsTable.name])
}

Database.connect(
    url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
    driver = "org.h2.Driver",
    user = "root",
    password = "",
)

transaction {
    addLogger(StdOutSqlLogger)

    println("--- drop and create pokemons table ---")
    SchemaUtils.drop(PokemonsTable)
    SchemaUtils.create(PokemonsTable)

    println("--- insert some pokemons ---")
    PokemonsTable.insert { it[name] = "Pikachu" }
    PokemonsTable.insert { it[name] = "Squirtle" }

    println("--- listup all pokemons ---")
    val pokemons = PokemonsTable.selectAll().map(toPokemon)
    pokemons.forEach { println(it) }
}

ポケモンを２つ生成してデータベースに追加して、それを再度取り出して標準出力するところまで。

select where する

素のSQLで表現すれば次のような問い合わせをしたい:

SELECT *
  FROM pokemons
  WHERE id = 1;

本家の README https://github.com/JetBrains/Exposed を見よう。

...
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
...

transaction {
    ...
    
    val pokemon = PokemonsTable.selectAll().where{
        ( PokemonsTable.id eq 1 )
    }.map(toPokemon).firstOrNull()
    println(pokemon)
}

実行:

$ kotlin exposed.main.kts
--- drop and create pokemons table ---
SQL: SELECT SETTING_VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME = 'MODE'
SQL: DROP TABLE IF EXISTS POKEMONS
SQL: CREATE TABLE IF NOT EXISTS POKEMONS (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(128) NOT NULL)
--- insert some pokemons ---
SQL: INSERT INTO POKEMONS ("name") VALUES ('Pikachu')
SQL: INSERT INTO POKEMONS ("name") VALUES ('Squirtle')
--- listup all pokemons ---
SQL: SELECT POKEMONS.ID, POKEMONS."name" FROM POKEMONS
Pokemon(id=1, name=Pikachu)
Pokemon(id=2, name=Squirtle)
--- select * where id = 1 from pokemons ---
SQL: SELECT POKEMONS.ID, POKEMONS."name" FROM POKEMONS WHERE POKEMONS.ID = 1
Pokemon(id=1, name=Pikachu)

完成したコード

// exposed.main.kts

@file:Repository("https://repo1.maven.org/maven2/")

@file:DependsOn("org.jetbrains.exposed:exposed-core:0.59.0")
@file:DependsOn("org.jetbrains.exposed:exposed-jdbc:0.59.0")
@file:DependsOn("com.h2database:h2:2.3.232")

import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.Database

import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.addLogger
import org.jetbrains.exposed.sql.StdOutSqlLogger
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.selectAll
import org.jetbrains.exposed.sql.ResultRow
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq

data class Pokemon(val id: Int, val name: String)

object PokemonsTable : Table("pokemons") {
    val id = integer("id").autoIncrement()
    val name = varchar("name", length = 128)

    override val primaryKey = PrimaryKey(id)
}

val toPokemon: (ResultRow)->Pokemon = {
    Pokemon(it[PokemonsTable.id], it[PokemonsTable.name])
}

Database.connect(
    url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
    driver = "org.h2.Driver",
    user = "root",
    password = "",
)

transaction {
    addLogger(StdOutSqlLogger)

    println("--- drop and create pokemons table ---")
    SchemaUtils.drop(PokemonsTable)
    SchemaUtils.create(PokemonsTable)

    println("--- insert some pokemons ---")
    PokemonsTable.insert { it[name] = "Pikachu" }
    PokemonsTable.insert { it[name] = "Squirtle" }

    println("--- listup all pokemons ---")
    val pokemons = PokemonsTable.selectAll().map(toPokemon)
    pokemons.forEach { println(it) }

    println("--- SELECT * FROM pokemons WHERE id = 1 ---")
    val pokemon = PokemonsTable.selectAll().where{
        ( PokemonsTable.id eq 1 )
    }.map(toPokemon).firstOrNull()
    println(pokemon)
}

以上です。