Kotlin Exposed

Exposed に入門しました。

Kotlin Script で動かせるので、あれこれ実験しやすい。

環境はこれ:

$ kotlin -version
Kotlin version 2.0.10-release-540 (JRE 17.0.6+10-LTS)

DSLで書いたコードが実際にどういうSQLになるか標準出力する:

addLogger(StdOutSqlLogger)

DSLとDAOの二つの方法でデータベース問い合わせができるらしいです。 ここでは DSL 方式を試しています。

特に難しいことはなくコードがそのまま説明になっている感じです。 どうかけばいいかわからないことも適当に AI に聞けば解決できました。

コード全体:

// exposed.main.kts

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

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

import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.StdOutSqlLogger
import org.jetbrains.exposed.sql.addLogger
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.selectAll
import org.jetbrains.exposed.sql.SortOrder

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

    override val primaryKey = PrimaryKey(id)
}

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(Pokemons)
    SchemaUtils.create(Pokemons)

    println("--- insert some pokemons ---")
    Pokemons.insert {
        it[name] = "Squirtle"
        it[hp] = 100
    }

    Pokemons.insert {
        it[name] = "Charamander"
        it[hp] = 120
    }

    Pokemons.insert {
        it[name] = "Metapod"
        it[hp] = 90
    }

    println("--- select all pokemons ---")
    val allPokemons0 = Pokemons.selectAll()
    allPokemons0.forEach { pokemon->
        println(pokemon)
    }

    println("--- select all pokemons and ORDER BY hp DESC ---")
    val allPokemons1 = Pokemons.selectAll().orderBy(Pokemons.hp to SortOrder.DESC)
    allPokemons1.forEach { pokemon->
        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, HP INT NOT NULL)
--- insert some pokemons ---
SQL: INSERT INTO POKEMONS ("name", HP) VALUES ('Squirtle', 100)
SQL: INSERT INTO POKEMONS ("name", HP) VALUES ('Charamander', 120)
SQL: INSERT INTO POKEMONS ("name", HP) VALUES ('Metapod', 90)
--- select all pokemons ---
SQL: SELECT POKEMONS.ID, POKEMONS."name", POKEMONS.HP FROM POKEMONS
Exposed_main$Pokemons.id=1, Exposed_main$Pokemons.name=Squirtle, Exposed_main$Pokemons.hp=100
Exposed_main$Pokemons.id=2, Exposed_main$Pokemons.name=Charamander, Exposed_main$Pokemons.hp=120
Exposed_main$Pokemons.id=3, Exposed_main$Pokemons.name=Metapod, Exposed_main$Pokemons.hp=90
--- select all pokemons and ORDER BY hp DESC ---
SQL: SELECT POKEMONS.ID, POKEMONS."name", POKEMONS.HP FROM POKEMONS ORDER BY POKEMONS.HP DESC
Exposed_main$Pokemons.id=2, Exposed_main$Pokemons.name=Charamander, Exposed_main$Pokemons.hp=120
Exposed_main$Pokemons.id=1, Exposed_main$Pokemons.name=Squirtle, Exposed_main$Pokemons.hp=100
Exposed_main$Pokemons.id=3, Exposed_main$Pokemons.name=Metapod, Exposed_main$Pokemons.hp=90

以上です。