Exposed 1.1.1 소개

Debop - ਷ృೠ Backend Developer, 2026-04-03 Introduction to Exposed (1.1.1)
Version 1.1.1

Agenda Introduction to Kotlin Exposed • Why Kotlin Exposed? •
What is Kotlin Exposed • Exposed DSL - Typesafe SQL • Exposed DAO • Advanced Features • Implement JPA Features Generated by ChatGPT

Why Kotlin Exposed

JPAܳ فҊ, ৵ ࢜۽਍ ߑधਸ ଺ইঠ ೞա? ҃೷੸ੋ ޙઁ ੄ध
• High throughput ਃҳ ࢚ട੉ ݆ই૗ • ױࣽ Entity ী ؀ೠ ୊ܻо ؊ ݆ই૗ -> DTO݅ ੓যب غח ҃਋о ݆ই૗ (Projection) • ࠂ੟ೠ JPA Relation ੿੄ ߂ ҙܻ ߑधী ؀ೠ ޖ૑, ೙ਃࢿ хࣗ • ই૒ب N+1 Problem ਸ ٜ݅Ҋ ੓਺ • ా҅, Join, SubQuery ١ ࠂ੟ೠ ؘ੉ఠ ઁҕ੉ ೙ਃ -> SQL ҳޙਵ۽ ୊ܻೞח Ѫ੉ ࡅܴ • Convering Index, Hints , HASH Index ١ DB Vendor ౠച ӝמਸ ࢎਊೞӝ য۰਑ • NoSQL ١਷ ੉޷ Async/Non-Blocking ਸ ૑ਗ, য়۽૑ DB݅ زӝߑध੐ • CPU-Bounded ۄب غӝ ਤ೧ࢲח Coroutines ژח Virtual Threads ܳ ഝਊ೧ঠ ೣ • Spring Web fl ux ӝ߈ী ݏח ۄ੉࠳۞ܻ ೙ਃ (r2dbc, hibernate-reactive, vert.x sql client, exposed)

࢜۽਍ ో੄ ࢶఖ ઑѤ • Async/Non-Blocking ਸ ૑ਗ೧ঠ ೠ׮. (IO-Bounded/CPU-Bounded)
• R2DBC, Vert.x | Coroutines, Virtual Threads • ؀۝ ؘ੉ఠ ੘স ߂ Vendor ౠച ੘স੉ ࣻਘ೧ঠ ೠ׮ (B2C ࢲ࠺झ ࠙ঠ) • JPA ӝמੋ Schema ࢤࢿ, Entity ੿੄ ߂ ҙ҅ ಴അ੉ оמ೧ঠ ೠ׮ (ORM) • Typesafe SQL DSL ૑ਗ • ౠࣻ ࣻഋ (Money, Encrypted, JSON) ਸ ૑ਗ೧ঠ ೠ׮

Candidates Vert.x SQL Clients MyBatis DynamicSQL Kotlin Exposed w/ Coroutines
Hibernate Reactive JDBC

ࢶ੿ ੉ਬ • Hibernate Reactive • QueryDSL ޷૑ਗ, JpaModelGen ݅
૑ਗ • Mutiny (reactive lib) ೟ण ೙ਃ (reactor, rxjava ৬ ਬࢎ) • MyBatis Dynamic SQL • Type-safe SQL Builder ۽ࢲ ಞܻೣ • ҕध੸ਵ۽ח Spring Template ݅ ૑ਗ, Vertx SQLTemplates ૑ਗ਷ ҳഅ ೙ਃ • Schema ࢤࢿ ӝמ ࠗ઒, ORM ӝࠄ ӝמ ࠗ੤ • Exposed • Typesafe DSL for SQL, Lightweight ORM • Coroutines ૑ਗ ژח Virtual Threads ܳ ഝਊೠ Async/Non-Blocking ૑ਗ • IO-Bounded Async/Non-Blocking ੉ ইצ CPU Bounded ੉׮.

What is Kotlin Exposed

Kotlin Exposed ۆ?

Introduction to Kotlin Exposed • Exposed is a Kotlin SQL
database library with two fl avors • Typesafe SQL (using DSL) - Kotlin ӝ߈ Database ੘স੄ ୶࢚ചܳ ࣻ೯ • A Lightweight ORM (using DAO) - ORM ӝ߈੄ Database ੘সਸ ࣻ೯ • JDBC ಴ળ ળࣻ, Spring Framework Transaction ૑ਗ • Coroutines ૑ਗ - CPU bounded Async/Non-Blocking -> With Virtual Threads • Support Databases • H2, MariaDB, MySQL, Oracle, PostgreSQL, Microsoft SQL Server, SQLite

Type-safe SQL DSL - DDL

Database Database ࢤࢿҗ ࢏ઁ @ParameterizedTest @MethodSource(ENABLE_DIALECTS_METHOD) fun `ؘ੉ఠ߬੉झ ࢤࢿҗ ࢏ઁ
੘স`(testDB: TestDB) { // MySQL਷ ҅੿ ӂೠ੉ ੓যঠ ೧ࢲ, పझ౟о ࠛоמ೤פ׮. Assumptions.assumeTrue { testDB in TestDB.ALL_H2 + TestDB.ALL_POSTGRES } withDb(testDB) { withAutoCommit(true) { val dbName = DB_NAME // ؘ੉ఠ߬੉झо ઓ੤ೞݶ ࢏ઁ (৘৻о ߊࢤ೧ب ޖद) runCatching { SchemaUtils.dropDatabase(dbName) } // ؘ੉ఠ߬੉झ ࢤࢿ SchemaUtils.createDatabase(dbName) // ؘ੉ఠ߬੉झ ࢏ઁ SchemaUtils.dropDatabase(dbName) } } } Path: 04-exposed-ddl/02-ddl Source: Ex01_CreateDatabase.kt

Database Database ࢤࢿҗ ࢏ઁ @ParameterizedTest @MethodSource(ENABLE_DIALECTS_METHOD) fun `ؘ੉ఠ߬੉झ ࢤࢿҗ ࢏ઁ
੘স`(testDB: TestDB) { // MySQL਷ ҅੿ ӂೠ੉ ੓যঠ ೧ࢲ, పझ౟о ࠛоמ೤פ׮. Assumptions.assumeTrue { testDB in TestDB.ALL_H2 + TestDB.ALL_POSTGRES } withDb(testDB) { withAutoCommit(true) { val dbName = DB_NAME // ؘ੉ఠ߬੉झо ઓ੤ೞݶ ࢏ઁ (৘৻о ߊࢤ೧ب ޖद) runCatching { SchemaUtils.dropDatabase(dbName) } // ؘ੉ఠ߬੉झ ࢤࢿ SchemaUtils.createDatabase(dbName) // ؘ੉ఠ߬੉झ ࢏ઁ SchemaUtils.dropDatabase(dbName) } } } Path: 04-exposed-ddl/02-ddl Source: Ex01_CreateDatabase.kt CREATE DATABASE bluetape4k; DROP DATABASE bluetape4k;

Schema Schema ࢤࢿ val currentSchema = getSchemaDefinition(tenant) SchemaUtils.createSchema(currentSchema) SchemaUtils.setSchema(currentSchema) transaction
{ MigrationUtils.statementsRequiredForDatabaseMigration(ActorTable, MovieTable, ActorInMovieTable) .forEach { exec(it) } } Path: 10-multitenant/01-multitenant-spring-web Source: DatabaseInitializer.kt

{ MigrationUtils.statementsRequiredForDatabaseMigration(ActorTable, MovieTable, ActorInMovieTable) .forEach { exec(it) } } Path: 10-multitenant/01-multitenant-spring-web Source: DatabaseInitializer.kt CREATE SCHEMA IF NOT EXISTS korean

{ MigrationUtils.statementsRequiredForDatabaseMigration(ActorTable, MovieTable, ActorInMovieTable) .forEach { exec(it) } } Path: 10-multitenant/01-multitenant-spring-web Source: DatabaseInitializer.kt CREATE SCHEMA IF NOT EXISTS korean CREATE SCHEMA IF NOT EXISTS English

Tables object PersonTable: Table("person") { val id1 = integer("id1") val
id2 = integer("id2") override val primaryKey = PrimaryKey(id1, id2, name = "PK_Person_ID") } Path: 04-exposed-ddl/02-ddl Source: Ex02_CreateTable.kt

id2 = integer("id2") override val primaryKey = PrimaryKey(id1, id2, name = "PK_Person_ID") } @ParameterizedTest @MethodSource(ENABLE_DIALECTS_METHOD) fun `2ѐ੄ ஸۢ੉ PRIMARY KEY۽ ૑੿ػ ప੉࠶ਸ ࢤࢿ೤פ׮`(testDB: TestDB) { withDb(testDB) { val ddl = PersonTable.ddl.single() log.debug { "DDL: $ddl" } SchemaUtils.create(PersonTable) PersonTable.exists().shouldBeTrue() SchemaUtils.drop(PersonTable) } } Path: 04-exposed-ddl/02-ddl Source: Ex02_CreateTable.kt

id2 = integer("id2") override val primaryKey = PrimaryKey(id1, id2, name = "PK_Person_ID") } @ParameterizedTest @MethodSource(ENABLE_DIALECTS_METHOD) fun `2ѐ੄ ஸۢ੉ PRIMARY KEY۽ ૑੿ػ ప੉࠶ਸ ࢤࢿ೤פ׮`(testDB: TestDB) { withDb(testDB) { val ddl = PersonTable.ddl.single() log.debug { "DDL: $ddl" } SchemaUtils.create(PersonTable) PersonTable.exists().shouldBeTrue() SchemaUtils.drop(PersonTable) } } CREATE TABLE IF NOT EXISTS person ( id1 INT, id2 INT, CONSTRAINT PK_Person_ID PRIMARY KEY (id1, id2) ) Path: 04-exposed-ddl/02-ddl Source: Ex02_CreateTable.kt

ColumnDe fi nition Path: 04-exposed-ddl/02-ddl Source: Ex04_ColumnDefinition.kt val comment =
"Amount of testers" val tester = object: Table("tester") { val amount = integer("amount") .withDefinition("COMMENT", stringLiteral(comment)) // ஸۢী ઱ࢳ ୶о }

"Amount of testers" val tester = object: Table("tester") { val amount = integer("amount") .withDefinition("COMMENT", stringLiteral(comment)) // ஸۢী ઱ࢳ ୶о } -- MySQL 8 CREATE TABLE IF NOT EXISTS tester ( amount INT COMMENT 'Amount of testers' NOT NULL )

"Amount of testers" val tester = object: Table("tester") { val amount = integer("amount") .withDefinition("COMMENT", stringLiteral(comment)) // ஸۢী ઱ࢳ ୶о } val tester = object: Table("tester") { val amount = integer("amount") val active = bool("active").nullable() .withDefinition("INVISIBLE") // Implicit ઑഥ द (select * from tester), ஸۢਸ ऀӣ } -- MySQL 8 CREATE TABLE IF NOT EXISTS tester ( amount INT COMMENT 'Amount of testers' NOT NULL )

"Amount of testers" val tester = object: Table("tester") { val amount = integer("amount") .withDefinition("COMMENT", stringLiteral(comment)) // ஸۢী ઱ࢳ ୶о } val tester = object: Table("tester") { val amount = integer("amount") val active = bool("active").nullable() .withDefinition("INVISIBLE") // Implicit ઑഥ द (select * from tester), ஸۢਸ ऀӣ } -- MySQL 8 CREATE TABLE IF NOT EXISTS tester ( amount INT COMMENT 'Amount of testers' NOT NULL ) -- MySQL 8 CREATE TABLE IF NOT EXISTS tester ( amount INT NOT NULL, active BOOLEAN INVISIBLE NULL -- INVISIBLE ஸۢ )

Index Path: 04-exposed-ddl/02-ddl Source: Ex05_CreateIndex.kt val tester = object: Table("tester")
{ val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // ੋؙझ ੿੄ val byName = index("tester_by_name", isUnique = false, name) }

{ val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // ੋؙझ ੿੄ val byName = index("tester_by_name", isUnique = false, name) } -- Postgres CREATE TABLE IF NOT EXISTS tester ( id SERIAL PRIMARY KEY, "name" VARCHAR(255) NOT NULL ); CREATE INDEX tester_by_name ON tester ("name");

{ val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // ੋؙझ ੿੄ val byName = index("tester_by_name", isUnique = false, name) } -- Postgres CREATE TABLE IF NOT EXISTS tester ( id SERIAL PRIMARY KEY, "name" VARCHAR(255) NOT NULL ); CREATE INDEX tester_by_name ON tester ("name"); val tester = object: Table("tester") { val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // Hash index ੿੄ val byName = index("tester_by_name", isUnique = false, name, indexType = "HASH") }

{ val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // ੋؙझ ੿੄ val byName = index("tester_by_name", isUnique = false, name) } -- Postgres CREATE TABLE IF NOT EXISTS tester ( id SERIAL PRIMARY KEY, "name" VARCHAR(255) NOT NULL ); CREATE INDEX tester_by_name ON tester ("name"); val tester = object: Table("tester") { val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // Hash index ੿੄ val byName = index("tester_by_name", isUnique = false, name, indexType = "HASH") } -- Postgres CREATE TABLE IF NOT EXISTS tester ( id SERIAL PRIMARY KEY, "name" VARCHAR(255) NOT NULL ); CREATE INDEX tester_by_name ON tester USING HASH ("name");

Sequence Path: 04-exposed-ddl/02-ddl Source: Ex06_Sequence.kt private val myseq = org.jetbrains.exposed.v1.core.Sequence(
name = "my_sequence", startWith = 4, incrementBy = 2, minValue = 1, maxValue = 100, cycle = true, cache = 20 ) // द௫झ ࢤࢿ SchemaUtils.createSequence(myseq)

name = "my_sequence", startWith = 4, incrementBy = 2, minValue = 1, maxValue = 100, cycle = true, cache = 20 ) // द௫झ ࢤࢿ SchemaUtils.createSequence(myseq) -- Postgres CREATE SEQUENCE IF NOT EXISTS my_sequence START WITH 4 INCREMENT BY 2 MINVALUE 1 MAXVALUE 100 CYCLE CACHE 20;

name = "my_sequence", startWith = 4, incrementBy = 2, minValue = 1, maxValue = 100, cycle = true, cache = 20 ) // द௫झ ࢤࢿ SchemaUtils.createSequence(myseq) -- Postgres CREATE SEQUENCE IF NOT EXISTS my_sequence START WITH 4 INCREMENT BY 2 MINVALUE 1 MAXVALUE 100 CYCLE CACHE 20; val tester = object: Table("tester") { val id = integer("id").autoIncrement(myseq) val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) }

name = "my_sequence", startWith = 4, incrementBy = 2, minValue = 1, maxValue = 100, cycle = true, cache = 20 ) // द௫झ ࢤࢿ SchemaUtils.createSequence(myseq) -- Postgres CREATE SEQUENCE IF NOT EXISTS my_sequence START WITH 4 INCREMENT BY 2 MINVALUE 1 MAXVALUE 100 CYCLE CACHE 20; val tester = object: Table("tester") { val id = integer("id").autoIncrement(myseq) val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) } -- Postgres CREATE SEQUENCE IF NOT EXISTS my_sequence START WITH 4 INCREMENT BY 2 MINVALUE 1 MAXVALUE 100 CYCLE CACHE 20; CREATE TABLE IF NOT EXISTS tester ( id INT PRIMARY KEY, "name" VARCHAR(255) NOT NULL ); INSERT INTO tester ("name", id) VALUES ('John Doe', NEXTVAL('my_sequence')); INSERT INTO tester ("name", id) VALUES ('Jane Doe', NEXTVAL('my_sequence'));

Custom Enumeration Path: 04-exposed-ddl/02-ddl Source: Ex07_CustomEnumeration.kt internal enum class Status
{ ACTIVE, INACTIVE; override fun toString(): String = "Status: $name" } val tester = object: IntIdTable("tester") { var statusName = enumerationByName<Status>("status_name", 32).default(Status.ACTIVE) }

Custom Enumeration Path: 04-exposed-ddl/02-ddl Source: Ex07_CustomEnumeration.kt internal enum class Status
{ ACTIVE, INACTIVE; override fun toString(): String = "Status: $name" } -- Postgres CREATE TABLE IF NOT EXISTS tester ( id SERIAL PRIMARY KEY, status_name VARCHAR(32) DEFAULT 'ACTIVE' NOT NULL ) val tester = object: IntIdTable("tester") { var statusName = enumerationByName<Status>("status_name", 32).default(Status.ACTIVE) }

Type-safe SQL DSL - DML

SELECT val row = users.selectAll() .where { users.id eq "andrey"
} .andWhere { users.name.isNotNull() } .single() Path: 05-exposed-dml/01-dml Source: Ex01_Select.kt val r1 = users .selectAll() .where { users.id inList listOf("andrey", "alex") } .orderBy(users.name) .toList()

} .andWhere { users.name.isNotNull() } .single() Path: 05-exposed-dml/01-dml Source: Ex01_Select.kt -- Postgres SELECT users.id, users."name", users.city_id, users.flags FROM users WHERE (users.id = 'andrey') AND (users."name" IS NOT NULL) val r1 = users .selectAll() .where { users.id inList listOf("andrey", "alex") } .orderBy(users.name) .toList()

} .andWhere { users.name.isNotNull() } .single() Path: 05-exposed-dml/01-dml Source: Ex01_Select.kt -- Postgres SELECT users.id, users."name", users.city_id, users.flags FROM users WHERE (users.id = 'andrey') AND (users."name" IS NOT NULL) val r1 = users .selectAll() .where { users.id inList listOf("andrey", "alex") } .orderBy(users.name) .toList() -- Postgres SELECT users.id, users."name", users.city_id, users.flags FROM users WHERE users.id IN ('andrey', 'alex') ORDER BY users."name" ASC

INSERT val id1 = idTable.insertAndGetId { it[name] = "name-1" }
val id3 = idTable.insert { it[name] = "name-3" } get idTable.id Path: 05-exposed-dml/01-dml Source: Ex02_Insert.kt val cityNames = listOf("Paris", "Moscow", "Helsinki") val allCitiesIDs: List<ResultRow> = cities.batchInsert(cityNames) { name -> this[cities.name] = name } val idTable = object: IntIdTable("tmp") { val name = varchar("foo", 10).uniqueIndex() }

val id3 = idTable.insert { it[name] = "name-3" } get idTable.id Path: 05-exposed-dml/01-dml Source: Ex02_Insert.kt -- Postgres INSERT INTO TMP (FOO) VALUES (‘name-1'); INSERT INTO TMP (FOO) VALUES (‘name-3’); val cityNames = listOf("Paris", "Moscow", "Helsinki") val allCitiesIDs: List<ResultRow> = cities.batchInsert(cityNames) { name -> this[cities.name] = name } val idTable = object: IntIdTable("tmp") { val name = varchar("foo", 10).uniqueIndex() }

val id3 = idTable.insert { it[name] = "name-3" } get idTable.id Path: 05-exposed-dml/01-dml Source: Ex02_Insert.kt -- Postgres INSERT INTO TMP (FOO) VALUES (‘name-1'); INSERT INTO TMP (FOO) VALUES (‘name-3’); val cityNames = listOf("Paris", "Moscow", "Helsinki") val allCitiesIDs: List<ResultRow> = cities.batchInsert(cityNames) { name -> this[cities.name] = name } -- Postgres INSERT INTO cities ("name") VALUES ('Paris'); INSERT INTO cities ("name") VALUES ('Moscow'); INSERT INTO cities ("name") VALUES ('Helsinki'); val idTable = object: IntIdTable("tmp") { val name = varchar("foo", 10).uniqueIndex() }

UPDATE val newName = "Alexey" users.update(where = { users.id eq
alexId }) { it[users.name] = newName } Path: 05-exposed-dml/01-dml Source: Ex03_Update.kt val join = users.innerJoin(userData) join.update { it[userData.comment] = users.name it[userData.value] = 123 } users.update(where = { users.id like "a%" }, limit = 1) { it[users.id] = "NewName" }

alexId }) { it[users.name] = newName } Path: 05-exposed-dml/01-dml Source: Ex03_Update.kt -- Postgres UPDATE users SET "name"='Alexey' WHERE users.id = 'alex' val join = users.innerJoin(userData) join.update { it[userData.comment] = users.name it[userData.value] = 123 } users.update(where = { users.id like "a%" }, limit = 1) { it[users.id] = "NewName" }

alexId }) { it[users.name] = newName } Path: 05-exposed-dml/01-dml Source: Ex03_Update.kt -- Postgres UPDATE users SET "name"='Alexey' WHERE users.id = 'alex' val join = users.innerJoin(userData) join.update { it[userData.comment] = users.name it[userData.value] = 123 } users.update(where = { users.id like "a%" }, limit = 1) { it[users.id] = "NewName" } -- MySQL V8 UPDATE Users SET id='NewName' WHERE Users.id LIKE 'a%' LIMIT 1

alexId }) { it[users.name] = newName } Path: 05-exposed-dml/01-dml Source: Ex03_Update.kt -- Postgres UPDATE users SET "name"='Alexey' WHERE users.id = 'alex' val join = users.innerJoin(userData) join.update { it[userData.comment] = users.name it[userData.value] = 123 } -- Postgres UPDATE userdata SET "comment"=users."name", "value"=123 FROM users WHERE users.id = userdata.user_id users.update(where = { users.id like "a%" }, limit = 1) { it[users.id] = "NewName" } -- MySQL V8 UPDATE Users SET id='NewName' WHERE Users.id LIKE 'a%' LIMIT 1

UPSERT private object Words: Table("words") { val word = varchar("name",
64).uniqueIndex() val count = integer("count").default(1) } Path: 05-exposed-dml/01-dml Source: Ex04_Upsert.kt val wordA = Words.upsert { it[word] = "A" it[count] = 10 } get Words.word Words.upsert { it[word] = wordA // "A" it[count] = 9 }

64).uniqueIndex() val count = integer("count").default(1) } Path: 05-exposed-dml/01-dml Source: Ex04_Upsert.kt -- Postgres CREATE TABLE IF NOT EXISTS words ( "name" VARCHAR(64) NOT NULL, "count" INT DEFAULT 1 NOT NULL ); ALTER TABLE words ADD CONSTRAINT words_name_unique UNIQUE ("name"); val wordA = Words.upsert { it[word] = "A" it[count] = 10 } get Words.word Words.upsert { it[word] = wordA // "A" it[count] = 9 }

64).uniqueIndex() val count = integer("count").default(1) } Path: 05-exposed-dml/01-dml Source: Ex04_Upsert.kt -- Postgres CREATE TABLE IF NOT EXISTS words ( "name" VARCHAR(64) NOT NULL, "count" INT DEFAULT 1 NOT NULL ); ALTER TABLE words ADD CONSTRAINT words_name_unique UNIQUE ("name"); val wordA = Words.upsert { it[word] = "A" it[count] = 10 } get Words.word Words.upsert { it[word] = wordA // "A" it[count] = 9 } -- Postgres INSERT INTO words ("name", "count") VALUES ('A', 10) ON CONFLICT ("name") DO UPDATE SET "count"=EXCLUDED."count"

64).uniqueIndex() val count = integer("count").default(1) } Path: 05-exposed-dml/01-dml Source: Ex04_Upsert.kt -- Postgres CREATE TABLE IF NOT EXISTS words ( "name" VARCHAR(64) NOT NULL, "count" INT DEFAULT 1 NOT NULL ); ALTER TABLE words ADD CONSTRAINT words_name_unique UNIQUE ("name"); val wordA = Words.upsert { it[word] = "A" it[count] = 10 } get Words.word Words.upsert { it[word] = wordA // "A" it[count] = 9 } -- Postgres INSERT INTO words ("name", "count") VALUES ('A', 10) ON CONFLICT ("name") DO UPDATE SET "count"=EXCLUDED."count" -- Postgres INSERT INTO words ("name", "count") VALUES ('A', 9) ON CONFLICT ("name") DO UPDATE SET "count"=EXCLUDED."count"

DELETE // DELETE FROM users WHERE users."name" LIKE '%thing' users.deleteWhere
{ users.name like "%thing" } Path: 05-exposed-dml/01-dml Source: Ex05_Delete.kt // ୭؀ 1ѐ݅ ࢏ઁೠ׮. userData.deleteWhere(limit = 1) { userData.value eq 20 }

{ users.name like "%thing" } Path: 05-exposed-dml/01-dml Source: Ex05_Delete.kt val join = users innerJoin userData val query1 = join.selectAll().where { userData.userId like "%ey" } query1.count() shouldBeGreaterThan 0L // ೯੉ ઓ੤ೠ׮. join.delete(userData) { userData.userId like "%ey" } shouldBeEqualTo 1 // ୭؀ 1ѐ݅ ࢏ઁೠ׮. userData.deleteWhere(limit = 1) { userData.value eq 20 }

{ users.name like "%thing" } Path: 05-exposed-dml/01-dml Source: Ex05_Delete.kt -- Postgres DELETE FROM userdata USING users WHERE users.id = userdata.user_id AND userdata.user_id LIKE '%ey' val join = users innerJoin userData val query1 = join.selectAll().where { userData.userId like "%ey" } query1.count() shouldBeGreaterThan 0L // ೯੉ ઓ੤ೠ׮. join.delete(userData) { userData.userId like "%ey" } shouldBeEqualTo 1 // ୭؀ 1ѐ݅ ࢏ઁೠ׮. userData.deleteWhere(limit = 1) { userData.value eq 20 }

{ users.name like "%thing" } Path: 05-exposed-dml/01-dml Source: Ex05_Delete.kt -- Postgres DELETE FROM userdata USING users WHERE users.id = userdata.user_id AND userdata.user_id LIKE '%ey' val join = users innerJoin userData val query1 = join.selectAll().where { userData.userId like "%ey" } query1.count() shouldBeGreaterThan 0L // ೯੉ ઓ੤ೠ׮. join.delete(userData) { userData.userId like "%ey" } shouldBeEqualTo 1 // ୭؀ 1ѐ݅ ࢏ઁೠ׮. userData.deleteWhere(limit = 1) { userData.value eq 20 } val towns = cities.alias("towns") val people = users.alias("people") val stats = userData.alias("stats") val aliasedJoin = Join(towns) .innerJoin(people, { towns[cities.id] }, { people[users.cityId] }) .innerJoin(stats, { people[users.id] }, { stats[userData.userId] }) val query = aliasedJoin .selectAll() .where { towns[cities.name] eq "Munich" } // ೯ਸ ࢏ઁೠ׮. aliasedJoin.delete(stats) { towns[cities.name] eq "Munich" }

{ users.name like "%thing" } Path: 05-exposed-dml/01-dml Source: Ex05_Delete.kt -- Postgres DELETE FROM userdata USING users WHERE users.id = userdata.user_id AND userdata.user_id LIKE '%ey' val join = users innerJoin userData val query1 = join.selectAll().where { userData.userId like "%ey" } query1.count() shouldBeGreaterThan 0L // ೯੉ ઓ੤ೠ׮. join.delete(userData) { userData.userId like "%ey" } shouldBeEqualTo 1 // ୭؀ 1ѐ݅ ࢏ઁೠ׮. userData.deleteWhere(limit = 1) { userData.value eq 20 } -- Postgres DELETE FROM userdata stats USING cities towns, users people WHERE towns.city_id = people.city_id AND people.id = stats.user_id AND towns."name" = 'Munich' val towns = cities.alias("towns") val people = users.alias("people") val stats = userData.alias("stats") val aliasedJoin = Join(towns) .innerJoin(people, { towns[cities.id] }, { people[users.cityId] }) .innerJoin(stats, { people[users.id] }, { stats[userData.userId] }) val query = aliasedJoin .selectAll() .where { towns[cities.name] eq "Munich" } // ೯ਸ ࢏ઁೠ׮. aliasedJoin.delete(stats) { towns[cities.name] eq "Munich" }

Window Functions Window Functions (ebook) Path: 05-exposed-dml/03-functions Source: Ex05_WindowFunctions.kt ೣࣻݺ
ࢸݺ ࠺Ҋ rowNumber() ਦب਋ ղ ࣽର ߣഐ ࠗৈ ઺ࠂ হযঠ ೡ ٸ ਬਊ rank() ҕز ࣽਤ ࠗৈ (઺ࠂ ࢤӝݶ Ѥցڭ) 1, 2, 2, 4 ࣽࢲ denseRank() ҕز ࣽਤ ࠗৈ, ߣഐ Ѥցڪ૑ ঋ਺ 1, 2, 2, 3 ࣽࢲ percentRank() ч੄ ࢚؀੸ ࣽਤ(%) (RANK - 1) / (N - 1) ҅࢑ cumeDist() ־੸ ࠺ਯ (Cumulative Distribution) i/N ഋध ࠺ਯ ntile(scale) ࠺ਯ ӝ߈ Ӓܛ ա־ӝ ؘ੉ఠܳ scale ѐ੄ Ӓܛ(bucket) ਵ۽ Ӑ١ೞѱ աׇࢲ п ೯੉ ࣘೠ Ӓܛਸ ߈ ജ lag(expr) അ੤ ೯ ӝળ ੉੹ ೯੄ ч ߈ജ ੉੹ ч ࠺Үী ࢎਊ lead(expr) അ੤ ೯ ӝળ ׮਺ ೯੄ ч ߈ജ ׮਺ ч ৘ஏ ١ firstValue() ਦب਋ ղ੄ ୐ߣ૩ ೯ ਦب਋੄ ୐ߣ૩ ೯੄ ч lastValue() ਦب਋ ղ੄ ૑Ә ೯ө૑ ઺ ݃૑݄ ೯ ਦب਋ ղ੄ അ੤ ೯ө૑੄ ݃૑݄ ೯੄ ч (૊ അ੤ ೯੄ ч)

ࢸݺ ࠺Ҋ rowNumber() ਦب਋ ղ ࣽର ߣഐ ࠗৈ ઺ࠂ হযঠ ೡ ٸ ਬਊ rank() ҕز ࣽਤ ࠗৈ (઺ࠂ ࢤӝݶ Ѥցڭ) 1, 2, 2, 4 ࣽࢲ denseRank() ҕز ࣽਤ ࠗৈ, ߣഐ Ѥցڪ૑ ঋ਺ 1, 2, 2, 3 ࣽࢲ percentRank() ч੄ ࢚؀੸ ࣽਤ(%) (RANK - 1) / (N - 1) ҅࢑ cumeDist() ־੸ ࠺ਯ (Cumulative Distribution) i/N ഋध ࠺ਯ ntile(scale) ࠺ਯ ӝ߈ Ӓܛ ա־ӝ ؘ੉ఠܳ scale ѐ੄ Ӓܛ(bucket) ਵ۽ Ӑ١ೞѱ աׇࢲ п ೯੉ ࣘೠ Ӓܛਸ ߈ ജ lag(expr) അ੤ ೯ ӝળ ੉੹ ೯੄ ч ߈ജ ੉੹ ч ࠺Үী ࢎਊ lead(expr) അ੤ ೯ ӝળ ׮਺ ೯੄ ч ߈ജ ׮਺ ч ৘ஏ ١ firstValue() ਦب਋ ղ੄ ୐ߣ૩ ೯ ਦب਋੄ ୐ߣ૩ ೯੄ ч lastValue() ਦب਋ ղ੄ ૑Ә ೯ө૑ ઺ ݃૑݄ ೯ ਦب਋ ղ੄ അ੤ ೯ө૑੄ ݃૑݄ ೯੄ ч (૊ അ੤ ೯੄ ч) ঱ઁ ࢎਊೡө? • ࣽਤ ݒӝӝ: ѱद౸ ੋӝӖ, ੼ࣻ ࣽਤ ١ → rowNumber, rank, denseRank • ߸ച ୶੸: ੉੹/׮਺ (೯੄) ч ࠺Ү → lag, lead • ా҅/࠺ਯ ࠙ࢳ: ࠙ನ ഛੋ → percentRank, cumeDist

Exposed DSL ৘द ࢎਊ ࢚ട / ݾ੸ rowNumber() rowNumber().over().orderBy(sales.amount) ࣽର ߣഐ ݒӝӝ (઺ࠂ হ਺) rank() rank().over().partitionBy(sales.year).orderBy(sales.amount.desc()) ҕز ࣽਤ ࠗৈ (1,2,2,4) denseRank() denseRank().over().partitionBy(sales.product).orderBy(sales.amount) ҕز ࣽਤ, ߣഐ Ѥցڪ૑ ঋ਺ (1,2,2,3) percentRank() percentRank().over().orderBy(users.score.desc()) ࢚؀੸ ࣽਤ ࠺ਯ (0~1) cumeDist() cumeDist().over().partitionBy(users.group).orderBy(users.score.desc()) ־੸ ࠙ನ ࠺ਯ (࢚ਤ ݻ ಌࣃ౟) lag(expr) lag(users.score).over().orderBy(users.date) ੉੹ ೯੄ ч ଵઑ (߸ച ୶੸) lead(expr) lead(users.score).over().partitionBy(users.group).orderBy(users.date) ׮਺ ೯੄ ч ଵઑ (৘ஏ, ࠺Ү)

Lightweight ORM (DAO)

DSL vs DAO о ইפۄ DSL and DAO 항목 DSL
DAO 주용도 검색/집계/조건 조합 관계 탐색/모델 표현 강점 SQL 가시성, 명시적 제어 객체 그래프 표현, 모델 가독성 주의점 쿼리 코드가 길어질 수 있음 로딩 전략/N+1 의식 필요 추천 상황 복잡한 조회, 벌크 연산 CRUD + 연관관계 탐색

TABLE, Entity, EntityClass • Table - झః݃ ੿੄ • Entity
- ё୓ ݽ؛ ઁҕ • EntityClass - ࢤࢿ/ઑഥ ೣࣻ ઁҕ

JPA, Exposed DSL, DAO ೣࣻ ࠺Ү JPA DSL DAO INSERT
em.persist(e) Table.insert { } Entity.new { } Find By Id em.find(E, id) Table.selectAll().where { id eq x } Entity.findById(id) UPDATE flush() Table.update({ઑѤ}) { } entity.prop = value DELETE em.remove(e) Table.deleteWhere { } entity.delete()

Entity - referencedOn, referrersOn Path: 05-exposed-dml/05-entities Source: Ex04_LongIdTableEntity.kt class City(id:
EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<City>(Cities) var name: String by Cities.name // one-to-many ҙ҅ val towns: SizedIterable<Town> by Town referrersOn Towns.cityId } class Town(id: EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<Town>(Towns) // many-to-one ҙ҅ var city: City by City referencedOn Towns.cityId } class Person(id: EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<Person>(People) var name: String by People.name // many-to-one ҙ҅ var city: City by City referencedOn People.cityId }

Entity - referencedOn, referrersOn Path: 05-exposed-dml/05-entities Source: Ex04_LongIdTableEntity.kt class City(id:
EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<City>(Cities) var name: String by Cities.name // one-to-many ҙ҅ val towns: SizedIterable<Town> by Town referrersOn Towns.cityId } class Town(id: EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<Town>(Towns) // many-to-one ҙ҅ var city: City by City referencedOn Towns.cityId } class Person(id: EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<Person>(People) var name: String by People.name // many-to-one ҙ҅ var city: City by City referencedOn People.cityId } @OneToMany -> ࠗݽী referrersOn @ManyToOne -> ੗धী referencedOn

Entity - referencedOn, referrersOn UUIDEntity Path: 05-exposed-dml/05-entities Source: Ex04_UUIDTableEntity.kt

Entity - referencedOn, referrersOn UUIDEntity Path: 05-exposed-dml/05-entities Source: Ex04_UUIDTableEntity.kt class
City(id: EntityID<UUID>): UUIDEntity(id) { companion object: UUIDEntityClass<City>(Cities) var name: String by Cities.name val towns: SizedIterable<Town> by Town referrersOn Towns.cityId // one-to-many } class Person(id: EntityID<UUID>): UUIDEntity(id) { companion object: UUIDEntityClass<Person>(People) var name: String by People.name var city: City by City referencedOn People.cityId } class Town(id: EntityID<UUID>): UUIDEntity(id) { companion object: UUIDEntityClass<Town>(Towns) var city: City by City referencedOn Towns.cityId // many-to-one } class Address(id: EntityID<UUID>): UUIDEntity(id) { companion object: UUIDEntityClass<Address>(Addresses) var person: Person by Person referencedOn Addresses.personId // many-to-one var city: City by City referencedOn Addresses.cityId // many-to-one var address: String by Addresses.address }

Entity - fi eld with transform Path: 05-exposed-dml/05-entities Source: Ex08_EntityFieldWithTransform.kt
object TableWithTransformss: IntIdTable() { // `transform` ೣࣻܳ ࢎਊೞৈ DB ஸۢҗ ূ౭౭ ೙٘੄ ࣻഋਸ ݏ୸׮. val value: Column<BigDecimal> = varchar("value", 50) .transform( wrap = { it.toBigDecimal() }, // colum -> field unwrap = { it.toString() }, // field -> column ) } class TableWithTransformEntity(id: EntityID<Int>): IntEntity(id) { companion object: IntEntityClass<TableWithTransformEntity>(TableWithTransforms) // `transform` ೣࣻܳ ࢎਊೞৈ DB ஸۢ (string) -> DSL ࣘࢿ (BigDecimal) -> ূ౭౭ ೙٘ (Int) ੄ ࣻഋਸ ߸ജೠ׮ var value: Int by TableWithTransforms.value .transform( wrap = { it.toInt() }, unwrap = { it.toBigDecimal() }, ) }

Advanced Features

Exposed extension library • exposed-crypt • exposed-javatime, expoed-kotlin—datetime • exposed-json
(kotlinx-serialization) • JSON, JSONB Column • exposed-money • Java Money (Moneta) - ࣻ۝җ ױਤ۽ ಴അ

Exposed custom library Bluetape4k Library • exposed-core, exposed-dao • exposed-tink
• exposed-jackson2, exposed-jackson3, exposed-fastjson2 • exposed-measured • exposed-jdbc-lettuce, exposed-jdbc-redisson • exposed-postgresql, exposed-mysql8 • exposed-bigquery, exposed-duckdb

Custom Columns private object T1: IntIdTable() { val name =
varchar("name", 50) val lz4Fory = binarySerializedBlob<Embeddable>("lz4_fory", BinarySerializers.LZ4Fory).nullable() val lz4Kryo = binarySerializedBlob<Embeddable>("lz4_kryo", BinarySerializers.LZ4Kryo).nullable() val zstdFory = binarySerializedBlob<Embeddable2>("zstd_fory", BinarySerializers.ZstdFory).nullable() val zstdKryo = binarySerializedBlob<Embeddable2>("zstd_kryo", BinarySerializers.ZstdKryo).nullable() } class E1(id: EntityID<Int>): IntEntity(id) { companion object: IntEntityClass<E1>(T1) var name by T1.name var lz4Fory by T1.lz4Fory var lz4Kryo by T1.lz4Kryo var zstdFory by T1.zstdFory var zstdKryo by T1.zstdKryo override fun equals(other: Any?): Boolean = idEquals(other) override fun hashCode(): Int = idHashCode() override fun toString(): String = entityToStringBuilder() .add("name", name) .toString() } data class Embeddable( val name: String, val age: Int, val address: String, ): Serializable Path: 06-advanced/06-custom-columns Source: BinarySerializedBlobColumnTypeTest.kt data class Embeddable2( val name: String, val age: Int, val address: String, val zipcode: String, ): Serializable

Custom Columns val embedded = Embeddable("Alice", 20, "Seoul") val embedded2
= Embeddable2("John", 30, "Seoul", "12914") val id = T1.insertAndGetId { it[T1.name] = "Alice" it[T1.lz4Fory] = embedded it[T1.lz4Kryo] = embedded it[T1.zstdFory] = embedded2 it[T1.zstdKryo] = embedded2 } entityCache.clear() val row = T1.selectAll().where { T1.id eq id }.single() row[T1.id] shouldBeEqualTo id row[T1.lz4Fory] shouldBeEqualTo embedded row[T1.lz4Kryo] shouldBeEqualTo embedded row[T1.zstdFory] shouldBeEqualTo embedded2 row[T1.zstdKryo] shouldBeEqualTo embedded2 Path: 06-advanced/06-custom-columns Source: BinarySerializedBlobColumnTypeTest.kt val embedded = Embeddable("Alice", 20, "Seoul") val embedded2 = Embeddable2("John", 30, "Seoul", “12914") val e1 = E1.new { name = "Alice" lz4Fory = embedded lz4Kryo = embedded zstdFory = embedded2 zstdKryo = embedded2 } entityCache.clear() val loaded = E1.findById(e1.id).shouldNotBeNull() loaded shouldBeEqualTo e1 loaded.lz4Fory shouldBeEqualTo embedded loaded.lz4Kryo shouldBeEqualTo embedded loaded.zstdFory shouldBeEqualTo embedded2 loaded.zstdKryo shouldBeEqualTo embedded2

Custom Sortable Id Table open class TimebasedUUIDTable(name: String = “",
columnName: String = "id"): IdTable<UUID>(name) { /** * UUID v7 ਸ Client ীࢲ ࢤࢿ೤פ׮. */ final override val id: Column<EntityID<UUID>> = javaUUID(columnName).timebasedGenerated().entityId() /** ప੉࠶ ӝࠄః ੿੄ੑפ׮. */ final override val primaryKey = PrimaryKey(id) } Path: 06-advanced/07-custom-entities Source: TimebasedUUIDTableTest.kt

columnName: String = "id"): IdTable<UUID>(name) { /** * UUID v7 ਸ Client ীࢲ ࢤࢿ೤פ׮. */ final override val id: Column<EntityID<UUID>> = javaUUID(columnName).timebasedGenerated().entityId() /** ప੉࠶ ӝࠄః ੿੄ੑפ׮. */ final override val primaryKey = PrimaryKey(id) } Path: 06-advanced/07-custom-entities Source: TimebasedUUIDTableTest.kt fun Column<UUID>.timebasedGenerated(): Column<UUID> = clientDefault { Uuid.V7.nextId() }

columnName: String = "id"): IdTable<UUID>(name) { /** * UUID v7 ਸ Client ীࢲ ࢤࢿ೤פ׮. */ final override val id: Column<EntityID<UUID>> = javaUUID(columnName).timebasedGenerated().entityId() /** ప੉࠶ ӝࠄః ੿੄ੑפ׮. */ final override val primaryKey = PrimaryKey(id) } Path: 06-advanced/07-custom-entities Source: TimebasedUUIDTableTest.kt object T1: TimebasedUUIDTable("t_timebased_uuid") { val name = varchar("name", 255) val age = integer("age") } class E1(id: TimebasedUUIDEntityID): TimebasedUUIDEntity(id) { companion object: TimebasedUUIDEntityClass<E1>(T1) var name by T1.name var age by T1.age } fun Column<UUID>.timebasedGenerated(): Column<UUID> = clientDefault { Uuid.V7.nextId() }

Custom Sortable Id Table @ParameterizedTest(name = "{0} - {1}ѐ ۨ௏٘")
@MethodSource(GET_TESTDB_AND_ENTITY_COUNT) fun `TimebasedUUID idܳ о૓ ۨ௏٘ܳ ߓ஖۽ ࢤࢿೠ׮`(testDB: TestDB, recordCount: Int) { withTables(testDB, T1) { val records = List(recordCount) { Record( name = faker.name().fullName(), age = Random.nextInt(10, 80) ) } records.chunked(100).forEach { chunk -> T1.batchInsert(chunk, shouldReturnGeneratedValues = false) { this[T1.name] = it.name this[T1.age] = it.age } } T1.selectAll().count() shouldBeEqualTo recordCount.toLong() } } Path: 06-advanced/07-custom-entities Source: TimebasedUUIDTableTest.kt

ஸۢ ঐഐച ߂ Ѩ࢝ ࠺Ѿ੿੸ ঐഐച ߑध (AEAD ߑध) val
secretTable = object: IntIdTable("tink_aead_table") { val secret = tinkAeadVarChar("secret", 512, TinkAeads.AES256_GCM).nullable() val data = tinkAeadBinary("data", 512, TinkAeads.AES256_GCM).nullable() val blob = tinkAeadBlob("blob", TinkAeads.AES256_GCM).nullable() } Path: 06-advanced/12-exposed-tink Source: TinkColumnTypeTest.kt withTables(testDB, secretTable) { val insertedSecret = faker.lorem().sentence() val insertedData = faker.lorem().sentence() val insertedBlob = faker.lorem().sentence() val id = secretTable.insertAndGetId { it[secret] = insertedSecret it[data] = insertedData.toUtf8Bytes() it[blob] = insertedBlob.toUtf8Bytes() } secretTable.selectAll().count() shouldBeEqualTo 1L val row = secretTable.selectAll().where { secretTable.id eq id }.single() row[secretTable.secret] shouldBeEqualTo insertedSecret row[secretTable.data]!!.toUtf8String() shouldBeEqualTo insertedData row[secretTable.blob]!!.toUtf8String() shouldBeEqualTo insertedBlob }

ஸۢ ঐഐച ߂ Ѩ࢝ Ѿ੿੸ ঐഐച ߑध (DAEAD ߑध) val
searchableTable = object: IntIdTable("tink_daead_table") { val email = tinkDaeadVarChar("email", 512, TinkDaeads.AES256_SIV).nullable().index() val fingerprint = tinkDaeadBinary("fingerprint", 256, TinkDaeads.AES256_SIV).nullable() val blob = tinkDaeadBlob("blob", TinkDaeads.AES256_SIV).nullable() } Path: 06-advanced/12-exposed-tink Source: TinkColumnTypeTest.kt /** * DAEAD(Ѿ੿੸ ঐഐച)ח WHERE ੺۽ Ѩ࢝੉ оמ೤פ׮. * ```sql * SELECT COUNT(*) FROM tink_daead_table WHERE tink_daead_table.email = '<ঐഐޙ>' * ``` */ searchableTable.selectAll() .where { searchableTable.email eq insertedEmail } .count() shouldBeEqualTo 1L searchableTable.selectAll() .where { searchableTable.fingerprint eq insertedFingerprint.toUtf8Bytes() } .count() shouldBeEqualTo 1L searchableTable.selectAll() .where { searchableTable.blob eq insertedBlob.toUtf8Bytes() } .count() shouldBeEqualTo 1L

Implement JPA Features

Basic JPA Entities

Basic JPA Entities CompositeId Entity object Publishers: CompositeIdTable("publishers") { val
pubId = integer("pub_id").autoIncrement().entityId() val isbn = javaUUID("isbn_code").autoGenerate().entityId() val name = varchar("publisher_name", 32) override val primaryKey = PrimaryKey(pubId, isbn) } Path: 07-jpa/01-convert-jpa-basic Source: ex04_compositeId/Ex01_CompositeId class Publisher(id: EntityID<CompositeID>): CompositeEntity(id) { companion object: CompositeEntityClass<Publisher>(Publishers) { fun new(isbn: UUID, init: Publisher.() -> Unit): Publisher { // pubId ח autoIncrement ੉޲۽, isbn ݅ਵ۽ CompositeID ܳ ࢤࢿ val compositeId = CompositeID { it[Publishers.isbn] = isbn } return Publisher.new(compositeId) { init() } } } var name: String by Publishers.name val authors: SizedIterable<Author> by Author referrersOn Authors // one-to-many val office: Office? by Office optionalBackReferencedOn Offices // one-to-one val allOffices: SizedIterable<Office> by Office optionalReferrersOn Offices // one-to-many }

Basic JPA Entities Many-To-Many Relations

Basic JPA Entities Many-To-Many Relations object BankAccountTable: IntIdTable("bank_account") { val
number = varchar("number", 255).uniqueIndex() } Path: 07-jpa/01-convert-jpa-basic Source: ex05_relations/BankSchema.kt object AccountOwnerTable: IntIdTable("account_owner") { val ssn = varchar("ssn", 255).uniqueIndex() } object OwnerAccountMapTable: Table("owner_account_map") { val ownerId = reference("owner_id", AccountOwnerTable) val accountId = reference("account_id", BankAccountTable) init { uniqueIndex(ownerId, accountId) } } class BankAccount(id: EntityID<Int>): IntEntity(id) { companion object: IntEntityClass<BankAccount>(BankAccountTable) var number: String by BankAccountTable.number // many to many with via val owners: SizedIterable<AccountOwner> by AccountOwner via OwnerAccountMapTable } class AccountOwner(id: EntityID<Int>): IntEntity(id) { companion object: IntEntityClass<AccountOwner>(AccountOwnerTable) var ssn: String by AccountOwnerTable.ssn // many to many with via val accounts: SizedIterable<BankAccount> by BankAccount via OwnerAccountMapTable }

Advanced JPA Entities Tree - Self Reference Table Path: 07-jpa/02-convert-jpa-advanced
Source: ex04_tree/TreeNodeSchema

Source: ex04_tree/TreeNodeSchema object TreeNodeTable: LongIdTable("tree_nodes") { val title = varchar("title", 255) val description = text("description").nullable() val depth = integer("depth").default(0) // ೐۽؋࣌ ӂ੢: optReference(..., onDelete = ReferenceOption.CASCADE) val parentId = optReference("parent_id", TreeNodeTable) } class TreeNode(id: EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<TreeNode>(TreeNodeTable) { override fun new(init: TreeNode.() -> Unit): TreeNode { val node = super.new { } node.init() node.depth = (node.parent?.depth ?: 0) + 1 return node } } var title by TreeNodeTable.title var description by TreeNodeTable.description var depth by TreeNodeTable.depth var parent: TreeNode? by TreeNode optionalReferencedOn TreeNodeTable.parentId // ੗ध ֢٘ ઑഥ val children: SizedIterable<TreeNode> get() = TreeNode.find { TreeNodeTable.parentId eq id // ੗ध ֢٘ܳ ੤ӈ੸ਵ۽ ࢏ઁ೤פ׮. // ೐۽؋࣌ীࢲח parentId ஸۢী onDelete = ReferenceOption.CASCADE ࢸ੿ਸ ӂ੢೤פ׮ fun deleteDescendants() { children.forEach { it.deleteDescendants() } delete() } }

Source: ex04_tree/TreeNodeSchema buildTreeNodes() val parent = TreeNodeTable.alias("parent") val child = TreeNodeTable.alias("child") val join = parent.innerJoin(child) { parent[TreeNodeTable.id] eq child[TreeNodeTable.parentId] } val titles = join .select(parent[TreeNodeTable.title], child[TreeNodeTable.title]) .where { parent[TreeNodeTable.title] eq "child1" } .map { row -> row[parent[TreeNodeTable.title]] to row[child[TreeNodeTable.title]] } titles shouldHaveSize 2 titles.forEach { log.debug { "parent: ${it.first}, child: ${it.second}" } it.first shouldBeEqualTo "child1" it.second shouldStartWith "grand" }

Source: ex04_tree/TreeNodeSchema buildTreeNodes() val parent = TreeNodeTable.alias("parent") val child = TreeNodeTable.alias("child") val join = parent.innerJoin(child) { parent[TreeNodeTable.id] eq child[TreeNodeTable.parentId] } val titles = join .select(parent[TreeNodeTable.title], child[TreeNodeTable.title]) .where { parent[TreeNodeTable.title] eq "child1" } .map { row -> row[parent[TreeNodeTable.title]] to row[child[TreeNodeTable.title]] } titles shouldHaveSize 2 titles.forEach { log.debug { "parent: ${it.first}, child: ${it.second}" } it.first shouldBeEqualTo "child1" it.second shouldStartWith "grand" } SELECT parent.title, child.title FROM tree_nodes parent INNER JOIN tree_nodes child ON (parent.id = child.parent_id) WHERE parent.title = 'child1'

Source: ex04_tree/TreeNodeSchema buildTreeNodes() val sub = TreeNodeTable.alias("sub") val subQuery = sub .select(sub[TreeNodeTable.parentId]) .where { sub[TreeNodeTable.title] like "grand%" } val query = TreeNodeTable .selectAll() .where { TreeNodeTable.id inSubQuery subQuery } val nodes = TreeNode.wrapRows(query).toList() nodes shouldHaveSize 1 nodes.single().title shouldBeEqualTo "child1"

Source: ex04_tree/TreeNodeSchema buildTreeNodes() val sub = TreeNodeTable.alias("sub") val subQuery = sub .select(sub[TreeNodeTable.parentId]) .where { sub[TreeNodeTable.title] like "grand%" } val query = TreeNodeTable .selectAll() .where { TreeNodeTable.id inSubQuery subQuery } val nodes = TreeNode.wrapRows(query).toList() nodes shouldHaveSize 1 nodes.single().title shouldBeEqualTo "child1" SELECT tree_nodes.id, tree_nodes.title, tree_nodes.description, tree_nodes."depth", tree_nodes.parent_id FROM tree_nodes WHERE tree_nodes.id IN (SELECT sub.parent_id FROM tree_nodes sub WHERE sub.title LIKE 'grand%')

Exposed with Spring Boot

Spring Boot AutoCon fi gure spring: datasource: url: jdbc:h2:mem:test driver-class-name:
org.h2.Driver exposed: generate-ddl: false # true द DatabaseInitializerо SchemaUtils.create() प೯ show-sql: true # Exposed SQL ۽Ӓ ୹۱

Spring @Transactional @Bean fun dataSource(): DataSource { val config =
HikariConfig().apply { jdbcUrl = "jdbc:h2:mem:${Base58.randomString(8)};DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL" driverClassName = "org.h2.Driver" username = "root" password = "" } return HikariDataSource(config) } /** * `@Transactional` ীࢲ ࢎਊೡ Exposed `SpringTransactionManager` ܳ ١۾೤פ׮. */ @Bean fun annotationDrivenTransactionManager(dataSource: DataSource): TransactionManager { log.info { "Create Exposed's SpringTransactionManager" } return SpringTransactionManager(dataSource, DatabaseConfig { useNestedTransactions = true }) }

Exposed Repository pattern 09-spring/04-exposed-repository @Repository class ActorExposedRepository: JdbcRepository<Long, ActorRecord> {
companion object: KLogging() override val table = ActorTable override fun extractId(entity: ActorRecord): Long = entity.id override fun ResultRow.toEntity(): ActorRecord = toActorRecord() @Transactional(readOnly = true) fun searchActors(params: Map<String, String?>): List<ActorRecord> { // build where } fun create(actor: ActorRecord): ActorRecord { val id = ActorTable.insertAndGetId { it[firstName] = actor.firstName it[lastName] = actor.lastName actor.birthday?.let { day -> it[birthday] = runCatching { LocalDate.parse(day) }.getOrNull() } } return actor.copy(id = id.value) } } JdbcRepository.kt

JdbcRepository with Cache @Cacheable(key = "'country:' + #code") fun findByCode(code:
String): CountryRecord? { log.debug { "----> Loading country with code[$code] and caching in redis ..." } // @Transactional ਸ ࢎਊೞ૑ ঋҊ, transaction {} ࠶۾ਸ ࢎਊೞৈ DBী ੽Ӕ೤פ׮. // நदী ੉޷ ч੉ ੓׮ݶ, Transactionਸ ࢎਊೞ૑ ঋҊ நदীࢲ чਸ ߈ജೞب۾ ೤פ׮. return transaction { val row = CountryTable.selectAll().where { CountryTable.code eq code }.singleOrNull() ?: return@transaction null CountryRecord( code = row[CountryTable.code], name = row[CountryTable.name], description = row[CountryTable.description] ) } } @Transactional @CacheEvict(key = "'country:' + #countryRecord.code") fun update(countryRecord: CountryRecord): Int { return CountryTable.update({ CountryTable.code eq countryRecord.code }) { it[name] = countryRecord.name it[description] = countryRecord.description } }

Exposed Anti-Patterns

Exposed Anti-Patterns • `@Transactional` җ `transaction { }` ઺୏ ޙઁ
• `transaction {}` ഐ୹ࠁ׮ `TransactionManager.current()` ࢎਊ • TransactionManager ܳ ҕਬೞ૑ ޅ೧ ߊࢤೞח ழ޿ ־ۅ • JdbcTemplate җ Exposed о ࢲ۽ ׮ܲ ழ֏࣌ਸ ଵઑ • Spring DataSourceTransactionManager ৬ োѾػ DataSource ܳ Exposed ب ࢎਊ • Rollback ੉റ Transaction ੤ࢎਊ • Rollback ੉റ زੌೠ TransactionManager ۽ ౟ے੥࣌ ੤द੘द ৘৻ ߊࢤ • ৘৻ ୊ܻ റ ࢜۽਍ ౟ے੥࣌ਵ۽ ࠙ӝೞѢա, ౟ے੥࣌ਸ ৮੹൤ ઙܐ റ ੤द੘ • Connection Pool Ҋт • Exposed ղࠗ੸ਵ۽ ழ֏࣌ਸ ҅ࣘ ೡ׼ೞҊ ߈ജೞ૑ ঋח ޙઁ (ResultRow ё୓ ਬ૑, ౟ے੥࣌ ߧਤ ਤߓ) • ౟ے੥࣌ ղীࢲ ݽٚ ୊ܻܳ ՘ղҊ, ResultRow ח DTO ۽ ߸ജೞৈ ߈ജ • ؊ ੗ࣁೠ ղਊ਷ : Exposed ࢎਊ द ؀಴੸ पࣻ৬ ѐࢶ ߑߨ ଵҊ

Resources

Resources Documents • Github Jetbrains/Exposed • Exposed Documentation • SQL
and ORM alike Database Access with Kotlin Exposed • Guide to the Kotlin Exposed • Kotlin Exposed: From Setup to Advanced Usage • Kotlin Exposed Book (Exposed 1.1.1) • Github: Exposed Workshop

Resources VIDEO • Exploring Exposed: A Kotlin Solution to Database
Access • 7+/-2 thins you didn’t know about Exposed by Alexey Soshin • Using PostgreSQL as an Append-only Datastore with Kotlin and Exposed • PostgreSQL and Exposed - Kotlin database access

хࢎ೤פ׮

Exposed 1.1.1 소개

Exposed 1.1.1 소개

Transcript

More Decks by Sunghyouk Bae (Debop)

Other Decks in Programming

Featured