インドカレーファンクラブ

パソコン、カメラ

Exposed(DAO/DSL) で PostgreSQL からあれこれ SELECT するサンプル・覚書

記事の目的

自分が忘れないように!

create, update, delete は端折るけど、まあどうにかなるでしょう

もしかしたら N+1 の動きがちょっと怪しいかも
間違ってたら申し訳ないので使うときにはちゃんと確認して!
(数ヶ月がけでダラダラこの記事を書いてたら確認する気力が失せた)

補足

Table から取るやつが DSL, Entity が取るやつが DAO

qiita.com

今回の記事では JOIN したテーブルへの操作がメインだけど、JOIN なしのサンプルはこの記事がわかりやすい!

1:N のやつ

ついでに Enum も使う

DDL

ガバガバ

どういうテーブルか分かりにくいので、後続の Kotlin のコード見たほうが早いと思う

バンドとアルバムのテーブルがあって、バンドとアルバムが 1:N

create type country_enum as ENUM ('UK', 'US', 'JP');
create table if not exists bands
(
    id serial primary key not null,
    name varchar(50) not null,
    country country_enum not null
);
create table if not exists albums
(
    id serial primary key not null,
    title varchar(50) not null,
    band_id serial references bands (id) not null,
    release_date date not null default current_date
);

insert into bands(name, country) values
('Pink Floyd', 'UK'),
('Om', 'US'),
('筋肉少女帯', 'JP');
insert into albums(title, band_id, release_date) values
-- Pink Floyd
('The Piper At The Gates Of Dawn', 1, '1967/8/5'),
('A Saucerful Of Secrets', 1, '1968/6/29'),
-- Om
('Variations on a Theme', 2, '2005/2/14'),
('Conference of the Birds', 2, '2006/4/17'),
-- 筋肉少女帯
('仏陀L', 3, '1988/06/21'),
('SISTER STRAWBERRY', 3, '1988/12/21');

Exposed のサンプル

Table とか Entity の準備

// 列挙子はPostgreSQLのやつと揃えている!(揃えなくてもいけるけど!)
enum class Country { UK, US, JP }

// ExposedのEnumの使い方についてはこの辺をみて
// https://github.com/JetBrains/Exposed/wiki/DataTypes#how-to-use-database-enum-types
class PGEnum<T : Enum<T>>(enumTypeName: String, enumValue: T?) : PGobject() {
    init {
        value = enumValue?.name
        type = enumTypeName
    }
}

object BandsTable : IntIdTable("bands") {
    val name = varchar("name", 50)
    val country = customEnumeration(
        name = "country",
        sql = "country_enum",
        fromDb = { value -> Country.valueOf(value as String) },
        toDb = { it.name } // 上のコメントがここに関係してくる
    )
}

class BandEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object: IntEntityClass<BandEntity>(BandsTable)

    var name by BandsTable.name
    var country by BandsTable.country

    fun toModel(): Band =
        Band(
            id = id.value,
            name = name,
            country = country,
        )
}

data class Band(
    val id: Int,
    val name: String,
    val country: Country,
)
object AlbumsTable : IntIdTable("albums") {
    val title = varchar("title", 50)
    val band = reference("band_id", BandsTable)
    val releaseDate = date("release_date").clientDefault{ LocalDate.now(ZoneId.of("Asia/Tokyo")) }
}

class AlbumEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object: IntEntityClass<AlbumEntity>(AlbumsTable)

    var title by AlbumsTable.title
    var band by BandEntity.referencedOn(AlbumsTable.band)
    var releaseDate by AlbumsTable.releaseDate

    fun toModel(): Album =
        Album(
            id = id.value,
            name = title,
            band = band.toModel(),
            releaseDate = releaseDate,
        )
}

data class Album(
    val id: Int,
    val name: String,
    val band: Band, // Bandを持っちゃう!(持たないパターンは後述)
    val releasedAt: LocalDate,
)

data class Band, Album はここ(Table, Entity のレイヤ)に置かなくてもサンプル的には問題ないけど、依存性とかそういう話を考えて今回は書く

使う

transaction { ... }やら@Transactionalのなかで...

単一テーブルから全部 SELECT

Entity からも Table からもとれるけど、Entity からのほうが楽

// band
val bandAllFromEntity: List<Band> = BandEntity.all().map { it.toModel() }
val bandAllFromTable: List<Band> = BandsTable.selectAll().map { BandEntity.wrapRow(it).toModel() }

// albumでも同様
// withがなくてもAlbum::bandに値が入るけど、N+1問題が出る
val albumAllFromEntity: List<Album> = AlbumEntity.all().with(AlbumEntity::band).map { it.toModel() }
// 逆にTableからの取得時にはスマートにeagerloadする方法が分からない
val album = AlbumsTable.innerJoin(BandsTable).selectAll().map {
    BandEntity.wrapRow(it) // bandにもアクセスしておくとN+1起きなさそうだった
    AlbumEntity.wrapRow(it).toModel() // 返すのはこっちだけ
}
単一テーブルから ID で 1 件 SELECT

BandID:2 の Om を取得する

これも Entity からも Table からもとれるけど、Entity からのほうが自明に楽

// 対象(ID:2)が確実に存在する場合
val omFromEntity: Band = BandEntity[2].toModel()

val omFromTable: Band =
    BandsTable.select {
        BandsTable.id.eq(2)
    }.single().let { // 取る対象が明確に 1 件なら First(OrNull)じゃなくて Single(OrNull)のほうがいい
        BandEntity.wrapRow(it).toModel()
    }

// 対象(ID:2)が確実に存在するか分からない場合
val omFromEntity: Band? = BandEntity.findById(2)?.toModel()

val omFromTable: Band? =
    BandsTable.select {
        BandsTable.id.eq(2)
    }.singleOrNull()?.let {
        BandEntity.wrapRow(it).toModel()
    }
単一/複数テーブルから where で指定して SELECT

まず JOIN が要らない Band テーブルのみを考えて name を指定して SELECT

これも Entity からも Table からもとれるけど、このあとの複数テーブルからの話を踏まえると Table からとっておいた方が楽になる場合が多い(改修の手間的に)

// 後々にJOINするならば

// おすすめしない
val floydFromEntity: Band? =
    BandEntity.find {
        BandsTable.name.eq("Pink Floyd")
    }.singleOrNull()?.toModel()

// おすすめ
val floydFromTable: Band? =
    BandsTable.select {
        BandsTable.name.eq("Pink Floyd")
    }.singleOrNull()?.let {
        BandEntity.wrapRow(it).toModel()
    }

80 年代のアルバムを SELECT

// 後々にJOINするならば

// おすすめしない
val eightiesFromEntity: List<Album> =
    AlbumEntity.find {
        AlbumsTable.releasedAt.greaterEq(LocalDate.of(1980, 1, 1))
            .and(AlbumsTable.releasedAt.less(LocalDate.of(1990, 1, 1)))
    }.with(
        AlbumEntity::band
    ).sortedBy {
        AlbumsTable.title
    }.map {
        it.toModel()
    }

// おすすめ
val eightiesFromTable: List<Album> =
    AlbumsTable
        .innerJoin(BandsTable)
        .select {
            AlbumsTable.releasedAt.greaterEq(LocalDate.of(1980, 1, 1))
                .and(AlbumsTable.releasedAt.less(LocalDate.of(1990, 1, 1)))
        }.sortedBy {
            AlbumsTable.title
        }.map {
            BandEntity.wrapRow(it) // ここも同上の理由で...
            AlbumEntity.wrapRow(it).toModel()
        }

次に Album テーブルと Band テーブルを JOIN した状態で name を指定して SELECT

これをやる場合には Entity からだと微妙で、Table からとる必要がある

val floydAlbumFromTable: List<Album> =
    AlbumsTable
        .innerJoin(BandsTable) // 忘れずに
        .select {
            BandsTable.name.eq("Pink Floyd")
        }.sortedBy {
            AlbumsTable.title
        }.map {
            AlbumEntity.wrapRow(it).toModel()
        }

// There was an unexpected error (type=Internal Server Error, status=500).
//   org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "bands"
//   org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "bands"
val floydAlbumFromEntity: List<Album> =
    AlbumEntity.find {
        BandsTable.name.eq("Pink Floyd") //error
    }.sortedBy {
        AlbumsTable.title
    }.map {
        it.toModel()
    }

// だめっぽい例
// こっちだと動くけど、N+1
val floydAlbumFromEntity: List<Album> =
    AlbumEntity.wrapRows(
        // ここでTable使うなら最初からDSLでいいよね
        AlbumsTable
            .innerJoin(BandsTable)
            .select {
                BandsTable.name.eq("Pink Floyd")
            }
    )
    .with(AlbumEntity::band) //これ書いてもうまくeagerloadingされない
    .sortedBy {
        AlbumsTable.title
    }.map {
        it.toModel()
    }

さっきの 80 年代アルバム取得の実装にバンド名を追加することを考えると、やっぱり Table からとっておいた方が楽

val kingShowEighties: List<Album> =
    AlbumsTable
        .innerJoin(BandsTable)
        .select {
            AlbumsTable.releasedAt.greaterEq(LocalDate.of(1980, 1, 1))
                .and(AlbumsTable.releasedAt.less(LocalDate.of(1990, 1, 1)))
                .and(BandsTable.name.eq("筋肉少女帯")) // 追加した行
        }.sortedBy {
            AlbumsTable.title
        }.map {
            AlbumEntity.wrapRow(it).toModel()
        }

1:N のやつ(その 2)

DDL

上のと同じ

Exposed のサンプル

Table とか Entity の準備

基本上のと同じだけど、Album の Entity と Model だけ変更する

Table, Entity 的には Band と Album は関連してるけど、Model 上では切り離すような作りにしてみる

class AlbumEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object: IntEntityClass<AlbumEntity>(AlbumsTable)

    var title by AlbumsTable.title
    var band by BandEntity.referencedOn(AlbumsTable.band)
    var releaseDate by AlbumsTable.releasedAt

    fun toModel(): Album =
        Album(
            id = id.value,
            name = title,
            // band = band.toModel(),
            band_id = band.id.value, //★ これ!
            releaseDate = releaseDate,
        )
}

data class Album(
    val id: Int,
    val name: String,
    // val band: Band,
    val band_id: Int, //★ これ!
    val releaseDate: LocalDate,
)

使う

複数テーブルから where で指定して SELECT

何がしたいのかというと...こういうこと

data class Container(
    val band: Band,
    val albums: List<Album>
)

// 特に変えていないけど、見やすくするためにここにも書いただけ
data class Band(
    val id: Int,
    val name: String,
    val country: Country,
)
data class Album(
    val id: Int,
    val name: String,
    val band_id: Int,
    val releasedAt: LocalDate,
)
val ukAlbumContainers = AlbumsTable
    .innerJoin(BandsTable)
    .select {
        BandsTable.country.eq(Country.UK)
    }.sortedBy {
        AlbumsTable.title
    }.map {
        val band = BandEntity.wrapRow(it).toModel()
        val album = AlbumEntity.wrapRow(it).toModel()
        Container(band, album)
    }

今回の例だと微妙(1:1 のオブジェクトでやったほうがいいかも)だけど、こんな感じに Model 間の依存関係を廃した場合、Table から select したあとの map で複数回 HogeEntity.wrapRow した結果を適当なオブジェクトに詰め込むといい

あとこのパターンだと N+1 が発生しなくて気が楽

中間テーブルを使って N:N のやつ

話が変わって N:N

参考:

DAO · JetBrains/Exposed Wiki · GitHub

set many-to-many relation using a DAO · Issue #347 · JetBrains/Exposed · GitHub

DDL

ガバガバ(上に同じく)

ユーザとコースのテーブルがあって、中間テーブルを介して N:N

テーブル自体はここのを参考にしている

やさしい図解で学ぶ 中間テーブル 多対多 概念編 - Qiita

create table if not exists users
(
    id serial primary key not null,
    name varchar(20) not null,
    email varchar(254);
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp
);
create table if not exists courses
(
    id serial primary key not null,
    language varchar(30) not null
);
create table if not exists users_courses_relations
(
    id serial primary key not null,
    user_id serial not null references users (id),
    course_id serial not null references courses (id)
);

insert into users(name, created_at, updated_at, email) values
('john', now(), now(), 'john@example.com'),
('bob', now(), now(), 'bob@example.com');
insert into courses(language) values
('HTML'),
('CSS'),
('Kotlin'),
('React'),
('Java'),
('C#');
insert into users_courses_relations (user_id, course_id) values
-- John: HTML
(1, 1),
-- John: Kotlin
(1, 3),
-- Bob: HTML
(2, 1),
-- BoB: Kotlin
(2, 2),
-- Bob: React
(1, 4);

Exposed のサンプル

Table とか Entity の準備

object UsersTable : IntIdTable("users") {
    val name = varchar("name", 20)
    val email = varchar("email", 254).nullable()
    val createdAt = datetime("created_at").clientDefault{ LocalDateTime.now(ZoneId.of("Asia/Tokyo"))}
    val updatedAt = datetime("updated_at").clientDefault{ LocalDateTime.now(ZoneId.of("Asia/Tokyo"))}
}

class UserEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object: IntEntityClass<UserEntity>(UsersTable)

    var name by UsersTable.name
    var email by UsersTable.email
    var courses by CourseEntity via UserCourseRelations // ★ここがポイント
    var createdAt by UsersTable.createdAt
    var updatedAt by UsersTable.updatedAt

    fun toModel(): User =
        User(
            id = id.value,
            name = name,
            email = email,
            company = company?.toModel(),
            courses = courses.map { it.toModel() }.toList(),
            createdAt = createdAt,
            updatedAt = updatedAt,
        )
}

data class User(
    val id: Int,
    val name: String,
    val email: String?,
    val company: Company?,
    val courses: List<Course>,
    val createdAt: LocalDateTime,
    val updatedAt: LocalDateTime
)
object CoursesTable : IntIdTable("courses") {
    val language = varchar("language", 30)
}

class CourseEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object: IntEntityClass<CourseEntity>(CoursesTable)
    var language by CoursesTable.language

    fun toModel(): Course =
        Course(
            id = id.value,
            language = language,
        )
}

data class Course(
    val id: Int,
    val language: String,
)
object UserCourseRelations : IntIdTable("users_courses_relations") {
    val user = reference("user_id", UsersTable)
    val course = reference("course_id", CoursesTable)
}

使う

// EntityからID指定
val user = UserEntity[1].toModel()
// Entityから全部
val users = UserEntity.all().with(UserEntity::courses).map { it.toModel() }

// tableから名前指定
// すごくめんどくさいけど、こうするとN+1にならない
// 解決してね~ https://stackoverflow.com/questions/58586466/how-to-load-related-entities-in-jetbrains-exposed-dsl-approach
val johnBobFromTable =
    UsersTable
        .innerJoin(UserCourseRelationsTable)
        .innerJoin(CoursesTable)
        .select {
            UsersTable.name.inList(listOf("John", "Bob"))
        }.map {
            UserVO(
                id = it[UsersTable.id].value,
                name = it[UsersTable.name],
                email = it[UsersTable.email],
                courses = emptyList(), // mapで差し替える
                createdAt =it[UsersTable.createdAt],
                updatedAt =it[UsersTable.updatedAt],
            ) to Course(
                id = it[CoursesTable.id].value,
                language = it[CoursesTable.language]
            )
        }.groupBy {
            it.first
        }.map { (_, pair) ->
            val userVO = pair.first().first
            val courseVOs = pair.map { it.second }
            userVO.copy(courses = courseVOs)
        }

おわりに

この記事をあてにしすぎず、Exposed 公式のリファレンスを読んだほうがいい!

github.com