Exposed(DAO/DSL) で PostgreSQL からあれこれ SELECT するサンプル・覚書
記事の目的
自分が忘れないように!
create, update, delete は端折るけど、まあどうにかなるでしょう
もしかしたら N+1 の動きがちょっと怪しいかも
間違ってたら申し訳ないので使うときにはちゃんと確認して!
(数ヶ月がけでダラダラこの記事を書いてたら確認する気力が失せた)
補足
Table から取るやつが DSL, Entity が取るやつが DAO
今回の記事では 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 公式のリファレンスを読んだほうがいい!