跳至正文

💾 資料庫

📖 什麼是Exposed?​

Exposed是Kotlin的SQL函式庫。它允許您使用型別安全的DSL輕鬆安全地執行資料庫操作!

💡 設定​

build.gradle.kts​

dependencies {
    implementation("org.jetbrains.exposed:exposed-core:0.44.0")
    implementation("org.jetbrains.exposed:exposed-dao:0.44.0")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.44.0")

    // H2 데이터베이스 (개발용)
    implementation("com.h2database:h2:2.2.224")

    // PostgreSQL (프로덕션용)
    // implementation("org.postgresql:postgresql:42.6.0")
}

🎯 基本用法​

表格定義​

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

object Users : Table() {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 50)
    val email = varchar("email", 100)

    override val primaryKey = PrimaryKey(id)
}

fun main() {
    // 데이터베이스 연결
    Database.connect(
        url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
        driver = "org.h2.Driver"
    )

    transaction {
        // 로깅 활성화
        addLogger(StdOutSqlLogger)

        // 테이블 생성
        SchemaUtils.create(Users)

        // 데이터 삽입
        Users.insert {
            it[name] = "홍길동"
            it[email] = "hong@example.com"
        }

        // 조회
        Users.selectAll().forEach {
            println("${it[Users.name]}: ${it[Users.email]}")
        }
    }
}

🎨 CRUD操作​

Create(建立)​

transaction {
    // 단건 삽입
    Users.insert {
        it[name] = "김철수"
        it[email] = "kim@example.com"
    }

    // ID 받기
    val userId = Users.insert {
        it[name] = "이영희"
        it[email] = "lee@example.com"
    } get Users.id

    println("생성된 ID: $userId")
}

Read(讀取)​

transaction {
    // 전체 조회
    val allUsers = Users.selectAll()
    allUsers.forEach { row ->
        println("${row[Users.id]}: ${row[Users.name]}")
    }

    // 조건 조회
    val user = Users.select { Users.email eq "hong@example.com" }
        .singleOrNull()

    if (user != null) {
        println("찾음: ${user[Users.name]}")
    }

    // 여러 조건
    Users.select {
        (Users.name like "김%") and (Users.email like "%example.com")
    }.forEach {
        println(it[Users.name])
    }
}

Update(更新)​

transaction {
    // 조건에 맞는 행 수정
    Users.update({ Users.email eq "hong@example.com" }) {
        it[name] = "홍길동(수정)"
    }

    // 전체 수정
    Users.update {
        it[email] = concat(email, stringLiteral("_old"))
    }
}

Delete(刪除)​

transaction {
    // 조건 삭제
    Users.deleteWhere { Users.id eq 1 }

    // 여러 조건
    Users.deleteWhere {
        (Users.name like "test%") or (Users.email eq "temp@example.com")
    }
}

🔧 實戰範例​

使用者管理系統​

object Users : Table() {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 50)
    val email = varchar("email", 100).uniqueIndex()
    val age = integer("age")
    val createdAt = long("created_at")

    override val primaryKey = PrimaryKey(id)
}

class UserService {
    fun createUser(name: String, email: String, age: Int): Int {
        return transaction {
            Users.insert {
                it[Users.name] = name
                it[Users.email] = email
                it[Users.age] = age
                it[createdAt] = System.currentTimeMillis()
            } get Users.id
        }
    }

    fun findById(id: Int): UserData? {
        return transaction {
            Users.select { Users.id eq id }
                .singleOrNull()
                ?.let {
                    UserData(
                        id = it[Users.id],
                        name = it[Users.name],
                        email = it[Users.email],
                        age = it[Users.age]
                    )
                }
        }
    }

    fun findAll(): List<UserData> {
        return transaction {
            Users.selectAll().map {
                UserData(
                    id = it[Users.id],
                    name = it[Users.name],
                    email = it[Users.email],
                    age = it[Users.age]
                )
            }
        }
    }

    fun updateUser(id: Int, name: String?, email: String?, age: Int?): Boolean {
        return transaction {
            val updated = Users.update({ Users.id eq id }) {
                name?.let { value -> it[Users.name] = value }
                email?.let { value -> it[Users.email] = value }
                age?.let { value -> it[Users.age] = value }
            }
            updated > 0
        }
    }

    fun deleteUser(id: Int): Boolean {
        return transaction {
            Users.deleteWhere { Users.id eq id } > 0
        }
    }
}

data class UserData(
    val id: Int,
    val name: String,
    val email: String,
    val age: Int
)

🎯 關聯式資料​

1:N關聯​

object Posts : Table() {
    val id = integer("id").autoIncrement()
    val userId = integer("user_id").references(Users.id)
    val title = varchar("title", 200)
    val content = text("content")

    override val primaryKey = PrimaryKey(id)
}

fun getUserWithPosts(userId: Int): UserWithPosts? {
    return transaction {
        val user = Users.select { Users.id eq userId }
            .singleOrNull() ?: return@transaction null

        val posts = Posts.select { Posts.userId eq userId }
            .map {
                PostData(
                    id = it[Posts.id],
                    title = it[Posts.title],
                    content = it[Posts.content]
                )
            }

        UserWithPosts(
            id = user[Users.id],
            name = user[Users.name],
            posts = posts
        )
    }
}

data class PostData(val id: Int, val title: String, val content: String)
data class UserWithPosts(val id: Int, val name: String, val posts: List<PostData>)

JOIN查詢​

fun getPostsWithAuthors(): List<PostWithAuthor> {
    return transaction {
        (Posts innerJoin Users)
            .selectAll()
            .map {
                PostWithAuthor(
                    postId = it[Posts.id],
                    title = it[Posts.title],
                    authorName = it[Users.name]
                )
            }
    }
}

data class PostWithAuthor(
    val postId: Int,
    val title: String,
    val authorName: String
)

🔥 進階功能​

交易​

fun transferMoney(fromId: Int, toId: Int, amount: Int) {
    transaction {
        // 출금
        Accounts.update({ Accounts.id eq fromId }) {
            it[balance] = balance - amount
        }

        // 입금
        Accounts.update({ Accounts.id eq toId }) {
            it[balance] = balance + amount
        }

        // 예외 발생 시 자동 롤백
    }
}

批次插入​

fun createMultipleUsers(users: List<NewUser>) {
    transaction {
        Users.batchInsert(users) { user ->
            this[Users.name] = user.name
            this[Users.email] = user.email
            this[Users.age] = user.age
        }
    }
}

data class NewUser(val name: String, val email: String, val age: Int)

分頁​

fun getUsersPage(page: Int, size: Int): List<UserData> {
    return transaction {
        Users.selectAll()
            .limit(size, offset = ((page - 1) * size).toLong())
            .orderBy(Users.id)
            .map {
                UserData(
                    id = it[Users.id],
                    name = it[Users.name],
                    email = it[Users.email],
                    age = it[Users.age]
                )
            }
    }
}

🛠️ Ktor整合​

資料庫初始化​

fun Application.configureDatabases() {
    Database.connect(
        url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
        driver = "org.h2.Driver"
    )

    transaction {
        SchemaUtils.create(Users, Posts)
    }
}

fun Application.module() {
    configureDatabases()

    val userService = UserService()

    install(ContentNegotiation) {
        json()
    }

    routing {
        route("/users") {
            get {
                val users = userService.findAll()
                call.respond(users)
            }

            get("/{id}") {
                val id = call.parameters["id"]?.toIntOrNull()
                val user = id?.let { userService.findById(it) }

                if (user != null) {
                    call.respond(user)
                } else {
                    call.respond(HttpStatusCode.NotFound)
                }
            }

            post {
                val request = call.receive<CreateUserRequest>()
                val userId = userService.createUser(
                    request.name,
                    request.email,
                    request.age
                )
                call.respond(HttpStatusCode.Created, mapOf("id" to userId))
            }
        }
    }
}

🤔 常見問題​

Q1. H2 vs PostgreSQL?​

A: H2用於開發,PostgreSQL用於生產環境!

// H2 (개발 - 메모리)
Database.connect(
    url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
    driver = "org.h2.Driver"
)

// PostgreSQL (프로덕션)
Database.connect(
    url = "jdbc:postgresql://localhost:5432/mydb",
    driver = "org.postgresql.Driver",
    user = "user",
    password = "password"
)

Q2. 資料遷移怎麼辦?​

A: 推薦使用Flyway或Liquibase!

// Flyway
dependencies {
    implementation("org.flywaydb:flyway-core:9.22.0")
}

val flyway = Flyway.configure()
    .dataSource(url, user, password)
    .load()

flyway.migrate()

Q3. 效能優化?​

A: 使用連線池!

dependencies {
    implementation("com.zaxxer:HikariCP:5.0.1")
}

val config = HikariConfig().apply {
    jdbcUrl = "jdbc:postgresql://localhost:5432/mydb"
    username = "user"
    password = "password"
    maximumPoolSize = 10
}

val dataSource = HikariDataSource(config)
Database.connect(dataSource)

🎬 總結​

使用Exposed進行安全的資料庫操作!

關鍵要點:
✅ 型別安全的SQL DSL
✅ 簡單的CRUD操作
✅ 交易支援
✅ JOIN和關聯式資料
✅ 易於Ktor整合

下一步:在Deployment中學習如何將伺服器部署到生產環境!