ライブラリ

Ktorm

概要

Ktormは「軽量でエレガントなKotlin ORM フレームワーク」として開発された、SQLファーストのアプローチを採用するデータベースアクセスライブラリです。関数型プログラミングとKotlin DSLの力を活用し、型安全でexpressiveなSQL操作を可能にします。Active Recordパターンの代わりにSQL DSLによる記述性の高いクエリ構築を提供し、コンパイル時型安全性、null安全性、ラムダ式による簡潔な記述により、現代的なKotlinアプリケーション開発にふさわしい軽量で高性能なデータベースアクセス層を実現します。

詳細

Ktorm 2025年版は、Kotlin 1.9とCoroutines 1.7に完全対応し、非同期処理とリアクティブプログラミングのベストプラクティスを統合しています。JDBCの上に構築された軽量なアーキテクチャにより、最小限のオーバーヘッドで高いパフォーマンスを実現。PostgreSQL、MySQL、SQLite、SQL Server、Oracle等の主要データベースをサポートし、それぞれの方言に対応したSQL生成を行います。エンティティ関係マッピング、トランザクション管理、接続プール統合、バッチ処理等の企業レベル機能も包括的にサポートしています。

主な特徴

• SQL DSL: Kotlinらしい関数型DSLによる型安全なSQL構築
• 軽量設計: 最小限の依存関係とランタイムオーバーヘッド
• Expressiveクエリ: ラムダ式による直感的で読みやすいクエリ記述
• マルチDB対応: 主要データベースの方言とfeatureサポート
• 関数型アプローチ: 関数合成によるクエリの組み立てと再利用
• Coroutines統合: 非同期処理とreactive streamsの完全サポート

メリット・デメリット

メリット

• SQL DSLによる非常に読みやすく保守しやすいコード
• 軽量で高性能、最小限のメモリフットプリント
• Kotlinの言語特性を最大限活用した自然な記述
• 関数型プログラミングとの優れた親和性
• 学習コストが低く、既存のSQL知識を活用可能
• Coroutinesとの完璧な統合による非同期処理

デメリット

• 複雑なORM機能（lazy loading、cascadeなど）の不足
• Active Recordパターンに慣れた開発者には学習コストがある
• エンティティ関係の表現が制約的な場合がある
• 大規模エンタープライズ向けの高度な機能が限定的
• ドキュメントと日本語リソースの充実度が限定的
• データベース固有の最適化機能が少ない

参考ページ

• Ktorm 公式サイト
• Ktorm GitHub リポジトリ
• Ktorm ドキュメント

書き方の例

セットアップ

// build.gradle.kts
plugins {
    kotlin("jvm") version "1.9.22"
}

dependencies {
    implementation("org.ktorm:ktorm-core:3.6.0")
    implementation("org.ktorm:ktorm-support-mysql:3.6.0")
    implementation("org.ktorm:ktorm-support-postgresql:3.6.0")
    implementation("org.ktorm:ktorm-support-sqlite:3.6.0")
    
    // Database drivers
    implementation("mysql:mysql-connector-java:8.0.33")
    implementation("org.postgresql:postgresql:42.7.1")
    implementation("org.xerial:sqlite-jdbc:3.44.1.0")
    
    // Connection pool
    implementation("com.zaxxer:HikariCP:5.1.0")
    
    // Coroutines support
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.7.3")
    
    // JSON support (optional)
    implementation("org.ktorm:ktorm-jackson:3.6.0")
}

-- Database schema example
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    location VARCHAR(128) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    job VARCHAR(128) NOT NULL,
    manager_id INT,
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2),
    department_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

CREATE TABLE projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(12, 2),
    status VARCHAR(20) DEFAULT 'planning'
);

CREATE TABLE employee_projects (
    employee_id INT,
    project_id INT,
    role VARCHAR(50),
    allocation_percentage DECIMAL(5, 2),
    PRIMARY KEY (employee_id, project_id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (project_id) REFERENCES projects(id)
);

基本的な使い方

import org.ktorm.database.*
import org.ktorm.dsl.*
import org.ktorm.entity.*
import org.ktorm.schema.*
import kotlinx.coroutines.*
import java.time.LocalDate
import java.time.LocalDateTime
import java.math.BigDecimal

// Table schema definitions
object Departments : Table<Department>("departments") {
    val id = int("id").primaryKey().bindTo { it.id }
    val name = varchar("name").bindTo { it.name }
    val location = varchar("location").bindTo { it.location }
    val createdAt = datetime("created_at").bindTo { it.createdAt }
}

object Employees : Table<Employee>("employees") {
    val id = int("id").primaryKey().bindTo { it.id }
    val name = varchar("name").bindTo { it.name }
    val job = varchar("job").bindTo { it.job }
    val managerId = int("manager_id").bindTo { it.managerId }
    val hireDate = date("hire_date").bindTo { it.hireDate }
    val salary = decimal("salary").bindTo { it.salary }
    val departmentId = int("department_id").bindTo { it.departmentId }
    val createdAt = datetime("created_at").bindTo { it.createdAt }
}

object Projects : Table<Project>("projects") {
    val id = int("id").primaryKey().bindTo { it.id }
    val name = varchar("name").bindTo { it.name }
    val description = text("description").bindTo { it.description }
    val startDate = date("start_date").bindTo { it.startDate }
    val endDate = date("end_date").bindTo { it.endDate }
    val budget = decimal("budget").bindTo { it.budget }
    val status = varchar("status").bindTo { it.status }
}

// Entity classes
interface Department : Entity<Department> {
    companion object : Entity.Factory<Department>()
    val id: Int
    var name: String
    var location: String
    var createdAt: LocalDateTime
}

interface Employee : Entity<Employee> {
    companion object : Entity.Factory<Employee>()
    val id: Int
    var name: String
    var job: String
    var managerId: Int?
    var hireDate: LocalDate
    var salary: BigDecimal?
    var departmentId: Int
    var createdAt: LocalDateTime
}

interface Project : Entity<Project> {
    companion object : Entity.Factory<Project>()
    val id: Int
    var name: String
    var description: String?
    var startDate: LocalDate?
    var endDate: LocalDate?
    var budget: BigDecimal?
    var status: String
}

// Database configuration
class DatabaseConfig {
    companion object {
        fun createDatabase(): Database {
            val dataSource = HikariDataSource().apply {
                jdbcUrl = "jdbc:mysql://localhost:3306/ktorm_demo"
                username = "root"
                password = "password"
                driverClassName = "com.mysql.cj.jdbc.Driver"
                maximumPoolSize = 10
                minimumIdle = 5
            }
            
            return Database.connect(dataSource)
        }
    }
}

// Repository implementation using SQL DSL
class EmployeeRepository(private val database: Database) {
    
    // Create employee
    suspend fun createEmployee(
        name: String,
        job: String,
        managerId: Int?,
        hireDate: LocalDate,
        salary: BigDecimal?,
        departmentId: Int
    ): Int = withContext(Dispatchers.IO) {
        database.insertAndGenerateKey(Employees) {
            set(it.name, name)
            set(it.job, job)
            set(it.managerId, managerId)
            set(it.hireDate, hireDate)
            set(it.salary, salary)
            set(it.departmentId, departmentId)
            set(it.createdAt, LocalDateTime.now())
        } as Int
    }
    
    // Find employee by ID
    suspend fun findEmployeeById(id: Int): Employee? = withContext(Dispatchers.IO) {
        database.sequenceOf(Employees).find { it.id eq id }
    }
    
    // Find all employees
    suspend fun findAllEmployees(): List<Employee> = withContext(Dispatchers.IO) {
        database.sequenceOf(Employees).toList()
    }
    
    // Find employees by department
    suspend fun findEmployeesByDepartment(departmentId: Int): List<Employee> = withContext(Dispatchers.IO) {
        database.sequenceOf(Employees)
            .filter { it.departmentId eq departmentId }
            .toList()
    }
    
    // Search employees by name pattern
    suspend fun searchEmployeesByName(namePattern: String): List<Employee> = withContext(Dispatchers.IO) {
        database.sequenceOf(Employees)
            .filter { it.name like "%$namePattern%" }
            .toList()
    }
    
    // Find employees by salary range
    suspend fun findEmployeesBySalaryRange(minSalary: BigDecimal, maxSalary: BigDecimal): List<Employee> = 
        withContext(Dispatchers.IO) {
            database.sequenceOf(Employees)
                .filter { (it.salary greaterEq minSalary) and (it.salary lessEq maxSalary) }
                .sortedBy { it.salary }
                .toList()
        }
    
    // Update employee
    suspend fun updateEmployee(
        id: Int,
        name: String,
        job: String,
        managerId: Int?,
        salary: BigDecimal?
    ): Boolean = withContext(Dispatchers.IO) {
        database.update(Employees) {
            set(it.name, name)
            set(it.job, job)
            set(it.managerId, managerId)
            set(it.salary, salary)
            where { it.id eq id }
        } > 0
    }
    
    // Delete employee
    suspend fun deleteEmployee(id: Int): Boolean = withContext(Dispatchers.IO) {
        database.delete(Employees) { it.id eq id } > 0
    }
    
    // Count employees
    suspend fun countEmployees(): Int = withContext(Dispatchers.IO) {
        database.sequenceOf(Employees).count()
    }
    
    // Find employees with pagination
    suspend fun findEmployeesWithPagination(page: Int, size: Int): List<Employee> = withContext(Dispatchers.IO) {
        database.sequenceOf(Employees)
            .drop(page * size)
            .take(size)
            .toList()
    }
    
    // Find managers (employees who manage others)
    suspend fun findManagers(): List<Employee> = withContext(Dispatchers.IO) {
        database.sequenceOf(Employees)
            .filter { 
                it.id inList database.from(Employees).select(Employees.managerId).where { Employees.managerId.isNotNull() }
            }
            .toList()
    }
}

// Usage example
suspend fun demonstrateBasicOperations() {
    val database = DatabaseConfig.createDatabase()
    val employeeRepo = EmployeeRepository(database)
    
    // Create department first
    val deptId = database.insertAndGenerateKey(Departments) {
        set(it.name, "Engineering")
        set(it.location, "Tokyo")
        set(it.createdAt, LocalDateTime.now())
    } as Int
    
    // Create employees
    val emp1Id = employeeRepo.createEmployee(
        name = "Tanaka Taro",
        job = "Software Engineer",
        managerId = null,
        hireDate = LocalDate.of(2020, 4, 1),
        salary = BigDecimal("800000"),
        departmentId = deptId
    )
    
    val emp2Id = employeeRepo.createEmployee(
        name = "Sato Hanako",
        job = "Senior Engineer",
        managerId = emp1Id,
        hireDate = LocalDate.of(2018, 10, 15),
        salary = BigDecimal("1200000"),
        departmentId = deptId
    )
    
    println("Created employees with IDs: $emp1Id, $emp2Id")
    
    // Find employee by ID
    val foundEmployee = employeeRepo.findEmployeeById(emp1Id)
    println("Found employee: $foundEmployee")
    
    // Search employees by name
    val searchResults = employeeRepo.searchEmployeesByName("Tanaka")
    println("Search results: $searchResults")
    
    // Find employees by salary range
    val highSalaryEmployees = employeeRepo.findEmployeesBySalaryRange(
        BigDecimal("1000000"), BigDecimal("2000000")
    )
    println("High salary employees: $highSalaryEmployees")
    
    // Update employee
    val updated = employeeRepo.updateEmployee(
        id = emp1Id,
        name = "Tanaka Ichiro",
        job = "Lead Engineer",
        managerId = null,
        salary = BigDecimal("1500000")
    )
    println("Employee updated: $updated")
    
    // Count employees
    val totalEmployees = employeeRepo.countEmployees()
    println("Total employees: $totalEmployees")
    
    // Find managers
    val managers = employeeRepo.findManagers()
    println("Managers: $managers")
}

複雑なクエリとJOIN

class AdvancedQueryRepository(private val database: Database) {
    
    // Join employees with departments
    suspend fun findEmployeesWithDepartments(): List<Pair<Employee, Department>> = withContext(Dispatchers.IO) {
        database.from(Employees)
            .innerJoin(Departments, on = Employees.departmentId eq Departments.id)
            .select()
            .map { row ->
                Pair(
                    Employees.createEntity(row),
                    Departments.createEntity(row)
                )
            }
    }
    
    // Complex query: Department statistics
    suspend fun getDepartmentStatistics(): List<DepartmentStats> = withContext(Dispatchers.IO) {
        database.from(Departments)
            .leftJoin(Employees, on = Departments.id eq Employees.departmentId)
            .select(
                Departments.id,
                Departments.name,
                Departments.location,
                count(Employees.id).aliased("employee_count"),
                avg(Employees.salary).aliased("avg_salary"),
                max(Employees.salary).aliased("max_salary"),
                min(Employees.salary).aliased("min_salary")
            )
            .groupBy(Departments.id, Departments.name, Departments.location)
            .map { row ->
                DepartmentStats(
                    departmentId = row[Departments.id]!!,
                    departmentName = row[Departments.name]!!,
                    location = row[Departments.location]!!,
                    employeeCount = row.getInt("employee_count"),
                    avgSalary = row.getBigDecimal("avg_salary"),
                    maxSalary = row.getBigDecimal("max_salary"),
                    minSalary = row.getBigDecimal("min_salary")
                )
            }
    }
    
    // Subquery example: Employees earning above department average
    suspend fun findEmployeesAboveDepartmentAverage(): List<Employee> = withContext(Dispatchers.IO) {
        val avgSalaryByDept = database.from(Employees)
            .select(Employees.departmentId, avg(Employees.salary).aliased("dept_avg_salary"))
            .groupBy(Employees.departmentId)
            .aliased("dept_avg")
        
        database.from(Employees)
            .innerJoin(avgSalaryByDept, on = Employees.departmentId eq avgSalaryByDept["department_id"].cast<Int>())
            .select(Employees.columns)
            .where { 
                Employees.salary greater avgSalaryByDept["dept_avg_salary"].cast<BigDecimal>()
            }
            .map { row -> Employees.createEntity(row) }
    }
    
    // Window function: Employee rank by salary within department
    suspend fun getEmployeeRankingBySalary(): List<EmployeeRanking> = withContext(Dispatchers.IO) {
        database.from(Employees)
            .innerJoin(Departments, on = Employees.departmentId eq Departments.id)
            .select(
                Employees.id,
                Employees.name,
                Employees.salary,
                Departments.name.aliased("dept_name"),
                rank().over(
                    partitionBy = Employees.departmentId,
                    orderBy = Employees.salary.desc()
                ).aliased("salary_rank")
            )
            .orderBy(Departments.name.asc(), Employees.salary.desc())
            .map { row ->
                EmployeeRanking(
                    employeeId = row[Employees.id]!!,
                    employeeName = row[Employees.name]!!,
                    salary = row[Employees.salary],
                    departmentName = row.getString("dept_name")!!,
                    salaryRank = row.getInt("salary_rank")
                )
            }
    }
    
    // CTE (Common Table Expression) example: Recursive manager hierarchy
    suspend fun getManagerHierarchy(managerId: Int): List<ManagerHierarchy> = withContext(Dispatchers.IO) {
        // Note: This is a simplified example. Actual recursive CTE implementation 
        // would require database-specific SQL generation
        val directReports = database.from(Employees)
            .select(
                Employees.id,
                Employees.name,
                Employees.job,
                Employees.managerId,
                literal(1).aliased("level")
            )
            .where { Employees.managerId eq managerId }
        
        directReports.map { row ->
            ManagerHierarchy(
                employeeId = row[Employees.id]!!,
                employeeName = row[Employees.name]!!,
                job = row[Employees.job]!!,
                managerId = row[Employees.managerId],
                level = row.getInt("level")
            )
        }
    }
    
    // Conditional aggregation
    suspend fun getDepartmentJobDistribution(): List<JobDistribution> = withContext(Dispatchers.IO) {
        database.from(Employees)
            .innerJoin(Departments, on = Employees.departmentId eq Departments.id)
            .select(
                Departments.name,
                Employees.job,
                count(Employees.id).aliased("job_count")
            )
            .groupBy(Departments.name, Employees.job)
            .orderBy(Departments.name.asc(), count(Employees.id).desc())
            .map { row ->
                JobDistribution(
                    departmentName = row[Departments.name]!!,
                    jobTitle = row[Employees.job]!!,
                    count = row.getInt("job_count")
                )
            }
    }
    
    // Complex filtering with multiple conditions
    suspend fun findEmployeesByCriteria(
        departmentNames: List<String>? = null,
        minSalary: BigDecimal? = null,
        maxSalary: BigDecimal? = null,
        jobTitles: List<String>? = null,
        hiredAfter: LocalDate? = null,
        hiredBefore: LocalDate? = null
    ): List<Employee> = withContext(Dispatchers.IO) {
        database.sequenceOf(Employees)
            .filter { employee ->
                var conditions = listOf<ColumnDeclaring<Boolean>>()
                
                departmentNames?.let { deptNames ->
                    conditions = conditions + (employee.departmentId inList database.from(Departments)
                        .select(Departments.id)
                        .where { Departments.name inList deptNames })
                }
                
                minSalary?.let { min ->
                    conditions = conditions + (employee.salary greaterEq min)
                }
                
                maxSalary?.let { max ->
                    conditions = conditions + (employee.salary lessEq max)
                }
                
                jobTitles?.let { jobs ->
                    conditions = conditions + (employee.job inList jobs)
                }
                
                hiredAfter?.let { after ->
                    conditions = conditions + (employee.hireDate greater after)
                }
                
                hiredBefore?.let { before ->
                    conditions = conditions + (employee.hireDate less before)
                }
                
                conditions.reduceOrNull { acc, condition -> acc and condition } ?: true.asExpression()
            }
            .toList()
    }
}

// Data classes for complex query results
data class DepartmentStats(
    val departmentId: Int,
    val departmentName: String,
    val location: String,
    val employeeCount: Int,
    val avgSalary: BigDecimal?,
    val maxSalary: BigDecimal?,
    val minSalary: BigDecimal?
)

data class EmployeeRanking(
    val employeeId: Int,
    val employeeName: String,
    val salary: BigDecimal?,
    val departmentName: String,
    val salaryRank: Int
)

data class ManagerHierarchy(
    val employeeId: Int,
    val employeeName: String,
    val job: String,
    val managerId: Int?,
    val level: Int
)

data class JobDistribution(
    val departmentName: String,
    val jobTitle: String,
    val count: Int
)

// Usage example for complex queries
suspend fun demonstrateComplexQueries() {
    val database = DatabaseConfig.createDatabase()
    val advancedRepo = AdvancedQueryRepository(database)
    
    // Employees with departments
    val employeesWithDepts = advancedRepo.findEmployeesWithDepartments()
    println("Employees with departments: $employeesWithDepts")
    
    // Department statistics
    val deptStats = advancedRepo.getDepartmentStatistics()
    println("Department statistics: $deptStats")
    
    // Employees above department average
    val aboveAverage = advancedRepo.findEmployeesAboveDepartmentAverage()
    println("Employees above dept average: $aboveAverage")
    
    // Employee salary rankings
    val rankings = advancedRepo.getEmployeeRankingBySalary()
    println("Employee salary rankings: $rankings")
    
    // Job distribution by department
    val jobDistribution = advancedRepo.getDepartmentJobDistribution()
    println("Job distribution: $jobDistribution")
    
    // Complex criteria search
    val searchResults = advancedRepo.findEmployeesByCriteria(
        departmentNames = listOf("Engineering", "Sales"),
        minSalary = BigDecimal("800000"),
        jobTitles = listOf("Engineer", "Senior Engineer"),
        hiredAfter = LocalDate.of(2020, 1, 1)
    )
    println("Complex search results: $searchResults")
}

トランザクションとバッチ操作

class TransactionalService(private val database: Database) {
    
    // Simple transaction
    suspend fun transferEmployee(employeeId: Int, newDepartmentId: Int, newSalary: BigDecimal): Boolean = 
        withContext(Dispatchers.IO) {
            database.useTransaction { 
                // Verify employee exists
                val employee = database.sequenceOf(Employees).find { it.id eq employeeId }
                    ?: throw IllegalArgumentException("Employee not found: $employeeId")
                
                // Verify department exists
                val department = database.sequenceOf(Departments).find { it.id eq newDepartmentId }
                    ?: throw IllegalArgumentException("Department not found: $newDepartmentId")
                
                // Update employee
                val updated = database.update(Employees) {
                    set(it.departmentId, newDepartmentId)
                    set(it.salary, newSalary)
                    where { it.id eq employeeId }
                }
                
                updated > 0
            }
        }
    
    // Batch employee creation
    suspend fun batchCreateEmployees(employeesData: List<EmployeeData>): List<Int> = 
        withContext(Dispatchers.IO) {
            database.useTransaction {
                employeesData.map { data ->
                    database.insertAndGenerateKey(Employees) {
                        set(it.name, data.name)
                        set(it.job, data.job)
                        set(it.managerId, data.managerId)
                        set(it.hireDate, data.hireDate)
                        set(it.salary, data.salary)
                        set(it.departmentId, data.departmentId)
                        set(it.createdAt, LocalDateTime.now())
                    } as Int
                }
            }
        }
    
    // Complex transaction: Department reorganization
    suspend fun reorganizeDepartment(
        oldDepartmentId: Int,
        newDepartmentId: Int,
        salaryAdjustmentRate: BigDecimal
    ): ReorganizationResult = withContext(Dispatchers.IO) {
        database.useTransaction {
            // Get employees to be transferred
            val employeesToTransfer = database.sequenceOf(Employees)
                .filter { it.departmentId eq oldDepartmentId }
                .toList()
            
            if (employeesToTransfer.isEmpty()) {
                return@useTransaction ReorganizationResult(0, BigDecimal.ZERO, emptyList())
            }
            
            // Calculate total salary adjustment
            val totalSalaryAdjustment = employeesToTransfer
                .mapNotNull { it.salary }
                .sumOf { it * salaryAdjustmentRate - it }
            
            // Update employees
            database.update(Employees) {
                set(it.departmentId, newDepartmentId)
                set(it.salary, it.salary * salaryAdjustmentRate)
                where { it.departmentId eq oldDepartmentId }
            }
            
            // Log the reorganization
            val transferredIds = employeesToTransfer.map { it.id }
            
            ReorganizationResult(
                transferredCount = employeesToTransfer.size,
                totalSalaryAdjustment = totalSalaryAdjustment,
                transferredEmployeeIds = transferredIds
            )
        }
    }
    
    // Batch update with different values
    suspend fun batchUpdateSalaries(salaryUpdates: Map<Int, BigDecimal>): Int = withContext(Dispatchers.IO) {
        database.useTransaction {
            var totalUpdated = 0
            salaryUpdates.forEach { (employeeId, newSalary) ->
                val updated = database.update(Employees) {
                    set(it.salary, newSalary)
                    where { it.id eq employeeId }
                }
                totalUpdated += updated
            }
            totalUpdated
        }
    }
    
    // Complex transaction: Project assignment with resource validation
    suspend fun assignEmployeesToProject(
        projectId: Int,
        assignments: List<ProjectAssignment>
    ): ProjectAssignmentResult = withContext(Dispatchers.IO) {
        database.useTransaction {
            // Validate project exists
            val project = database.from(Projects).select().where { Projects.id eq projectId }.map { 
                Projects.createEntity(it) 
            }.firstOrNull() ?: throw IllegalArgumentException("Project not found: $projectId")
            
            // Validate all employees exist
            val employeeIds = assignments.map { it.employeeId }
            val existingEmployees = database.sequenceOf(Employees)
                .filter { it.id inList employeeIds }
                .associateBy { it.id }
            
            val missingEmployees = employeeIds.filter { it !in existingEmployees.keys }
            if (missingEmployees.isNotEmpty()) {
                throw IllegalArgumentException("Employees not found: $missingEmployees")
            }
            
            // Validate total allocation doesn't exceed 100% per employee
            val currentAllocations = database.from(EmployeeProjects)
                .select(EmployeeProjects.employeeId, sum(EmployeeProjects.allocationPercentage))
                .where { EmployeeProjects.employeeId inList employeeIds }
                .groupBy(EmployeeProjects.employeeId)
                .associate { row ->
                    row[EmployeeProjects.employeeId]!! to (row.getBigDecimal(1) ?: BigDecimal.ZERO)
                }
            
            val overAllocatedEmployees = assignments.filter { assignment ->
                val currentAllocation = currentAllocations[assignment.employeeId] ?: BigDecimal.ZERO
                currentAllocation + assignment.allocationPercentage > BigDecimal("100.00")
            }
            
            if (overAllocatedEmployees.isNotEmpty()) {
                throw IllegalArgumentException("Over-allocated employees: ${overAllocatedEmployees.map { it.employeeId }}")
            }
            
            // Insert assignments
            assignments.forEach { assignment ->
                database.insert(EmployeeProjects) {
                    set(it.employeeId, assignment.employeeId)
                    set(it.projectId, projectId)
                    set(it.role, assignment.role)
                    set(it.allocationPercentage, assignment.allocationPercentage)
                }
            }
            
            ProjectAssignmentResult(
                projectId = projectId,
                assignedEmployees = assignments.size,
                totalAllocation = assignments.sumOf { it.allocationPercentage }
            )
        }
    }
    
    // Rollback example: Conditional operation with rollback
    suspend fun conditionalSalaryUpdate(
        departmentId: Int,
        salaryIncreaseRate: BigDecimal,
        maxBudgetIncrease: BigDecimal
    ): SalaryUpdateResult = withContext(Dispatchers.IO) {
        try {
            database.useTransaction {
                // Calculate current total salary
                val currentTotalSalary = database.from(Employees)
                    .select(sum(Employees.salary))
                    .where { Employees.departmentId eq departmentId }
                    .map { it.getBigDecimal(1) ?: BigDecimal.ZERO }
                    .first()
                
                // Calculate proposed increase
                val proposedIncrease = currentTotalSalary * salaryIncreaseRate
                
                if (proposedIncrease > maxBudgetIncrease) {
                    // This will cause a rollback
                    throw IllegalStateException("Proposed salary increase ($proposedIncrease) exceeds budget ($maxBudgetIncrease)")
                }
                
                // Apply salary increase
                val updated = database.update(Employees) {
                    set(it.salary, it.salary * (BigDecimal.ONE + salaryIncreaseRate))
                    where { it.departmentId eq departmentId }
                }
                
                SalaryUpdateResult.Success(
                    updatedEmployees = updated,
                    totalIncrease = proposedIncrease,
                    newTotalSalary = currentTotalSalary + proposedIncrease
                )
            }
        } catch (e: Exception) {
            SalaryUpdateResult.Failed(e.message ?: "Unknown error")
        }
    }
}

// Additional table definition for employee projects
object EmployeeProjects : Table<Nothing>("employee_projects") {
    val employeeId = int("employee_id")
    val projectId = int("project_id")
    val role = varchar("role")
    val allocationPercentage = decimal("allocation_percentage")
}

// Data classes for transaction operations
data class EmployeeData(
    val name: String,
    val job: String,
    val managerId: Int?,
    val hireDate: LocalDate,
    val salary: BigDecimal?,
    val departmentId: Int
)

data class ReorganizationResult(
    val transferredCount: Int,
    val totalSalaryAdjustment: BigDecimal,
    val transferredEmployeeIds: List<Int>
)

data class ProjectAssignment(
    val employeeId: Int,
    val role: String,
    val allocationPercentage: BigDecimal
)

data class ProjectAssignmentResult(
    val projectId: Int,
    val assignedEmployees: Int,
    val totalAllocation: BigDecimal
)

sealed class SalaryUpdateResult {
    data class Success(
        val updatedEmployees: Int,
        val totalIncrease: BigDecimal,
        val newTotalSalary: BigDecimal
    ) : SalaryUpdateResult()
    
    data class Failed(val reason: String) : SalaryUpdateResult()
}

// Usage example for transactions and batch operations
suspend fun demonstrateTransactionsAndBatch() {
    val database = DatabaseConfig.createDatabase()
    val transactionalService = TransactionalService(database)
    
    // Batch employee creation
    val newEmployeesData = listOf(
        EmployeeData("Yamada Taro", "Developer", null, LocalDate.of(2023, 4, 1), BigDecimal("900000"), 1),
        EmployeeData("Suzuki Jiro", "Designer", null, LocalDate.of(2023, 6, 15), BigDecimal("850000"), 1),
        EmployeeData("Watanabe Saburo", "Manager", null, LocalDate.of(2023, 8, 1), BigDecimal("1300000"), 1)
    )
    
    val createdEmployeeIds = transactionalService.batchCreateEmployees(newEmployeesData)
    println("Created employees with IDs: $createdEmployeeIds")
    
    // Transfer employee
    val transferred = transactionalService.transferEmployee(
        employeeId = createdEmployeeIds.first(),
        newDepartmentId = 2,
        newSalary = BigDecimal("1000000")
    )
    println("Employee transfer successful: $transferred")
    
    // Batch salary updates
    val salaryUpdates = mapOf(
        createdEmployeeIds[1] to BigDecimal("950000"),
        createdEmployeeIds[2] to BigDecimal("1400000")
    )
    val updatedCount = transactionalService.batchUpdateSalaries(salaryUpdates)
    println("Updated salaries for $updatedCount employees")
    
    // Department reorganization
    val reorganizationResult = transactionalService.reorganizeDepartment(
        oldDepartmentId = 1,
        newDepartmentId = 2,
        salaryAdjustmentRate = BigDecimal("1.05") // 5% increase
    )
    println("Reorganization result: $reorganizationResult")
    
    // Conditional salary update
    when (val salaryResult = transactionalService.conditionalSalaryUpdate(
        departmentId = 2,
        salaryIncreaseRate = BigDecimal("0.10"), // 10% increase
        maxBudgetIncrease = BigDecimal("500000")
    )) {
        is SalaryUpdateResult.Success -> {
            println("✓ Salary update successful: $salaryResult")
        }
        is SalaryUpdateResult.Failed -> {
            println("✗ Salary update failed: ${salaryResult.reason}")
        }
    }
}

エラーハンドリング

// Custom exceptions
sealed class KtormException(message: String, cause: Throwable? = null) : Exception(message, cause) {
    class EntityNotFoundException(entityType: String, id: Any) : KtormException("$entityType not found with id: $id")
    class DuplicateKeyException(message: String) : KtormException("Duplicate key violation: $message")
    class ValidationException(message: String) : KtormException("Validation failed: $message")
    class DatabaseException(message: String, cause: Throwable? = null) : KtormException("Database error: $message", cause)
    class TransactionException(message: String, cause: Throwable? = null) : KtormException("Transaction failed: $message", cause)
    class ConstraintViolationException(constraint: String) : KtormException("Constraint violation: $constraint")
}

// Result wrapper for safe operations
sealed class Result<out T> {
    data class Success<T>(val data: T) : Result<T>()
    data class Error(val exception: KtormException) : Result<Nothing>()
    
    inline fun <R> map(transform: (T) -> R): Result<R> = when (this) {
        is Success -> Success(transform(data))
        is Error -> this
    }
    
    inline fun <R> flatMap(transform: (T) -> Result<R>): Result<R> = when (this) {
        is Success -> transform(data)
        is Error -> this
    }
    
    fun getOrNull(): T? = when (this) {
        is Success -> data
        is Error -> null
    }
    
    fun getOrElse(defaultValue: T): T = when (this) {
        is Success -> data
        is Error -> defaultValue
    }
}

class SafeEmployeeRepository(private val database: Database) {
    
    // Safe employee creation with validation
    suspend fun createEmployeeSafely(
        name: String,
        job: String,
        managerId: Int?,
        hireDate: LocalDate,
        salary: BigDecimal?,
        departmentId: Int
    ): Result<Int> = withContext(Dispatchers.IO) {
        try {
            // Input validation
            if (name.isBlank()) {
                return@withContext Result.Error(KtormException.ValidationException("Name cannot be empty"))
            }
            if (job.isBlank()) {
                return@withContext Result.Error(KtormException.ValidationException("Job cannot be empty"))
            }
            if (hireDate.isAfter(LocalDate.now())) {
                return@withContext Result.Error(KtormException.ValidationException("Hire date cannot be in the future"))
            }
            if (salary != null && salary < BigDecimal.ZERO) {
                return@withContext Result.Error(KtormException.ValidationException("Salary cannot be negative"))
            }
            
            // Validate department exists
            val departmentExists = database.sequenceOf(Departments).any { it.id eq departmentId }
            if (!departmentExists) {
                return@withContext Result.Error(KtormException.EntityNotFoundException("Department", departmentId))
            }
            
            // Validate manager exists (if specified)
            managerId?.let { id ->
                val managerExists = database.sequenceOf(Employees).any { it.id eq id }
                if (!managerExists) {
                    return@withContext Result.Error(KtormException.EntityNotFoundException("Manager", id))
                }
            }
            
            // Check for duplicate name in same department (business rule)
            val duplicateExists = database.sequenceOf(Employees)
                .any { (it.name eq name) and (it.departmentId eq departmentId) }
            if (duplicateExists) {
                return@withContext Result.Error(
                    KtormException.DuplicateKeyException("Employee with name '$name' already exists in department $departmentId")
                )
            }
            
            // Create employee
            val employeeId = database.insertAndGenerateKey(Employees) {
                set(it.name, name)
                set(it.job, job)
                set(it.managerId, managerId)
                set(it.hireDate, hireDate)
                set(it.salary, salary)
                set(it.departmentId, departmentId)
                set(it.createdAt, LocalDateTime.now())
            } as Int
            
            Result.Success(employeeId)
            
        } catch (e: Exception) {
            when (e) {
                is KtormException -> Result.Error(e)
                else -> Result.Error(KtormException.DatabaseException("Failed to create employee", e))
            }
        }
    }
    
    // Safe employee retrieval
    suspend fun getEmployeeSafely(employeeId: Int): Result<Employee> = withContext(Dispatchers.IO) {
        try {
            val employee = database.sequenceOf(Employees).find { it.id eq employeeId }
            if (employee != null) {
                Result.Success(employee)
            } else {
                Result.Error(KtormException.EntityNotFoundException("Employee", employeeId))
            }
        } catch (e: Exception) {
            Result.Error(KtormException.DatabaseException("Failed to retrieve employee", e))
        }
    }
    
    // Safe employee update
    suspend fun updateEmployeeSafely(
        employeeId: Int,
        name: String,
        job: String,
        managerId: Int?,
        salary: BigDecimal?
    ): Result<Employee> = withContext(Dispatchers.IO) {
        try {
            // Validation
            if (name.isBlank()) {
                return@withContext Result.Error(KtormException.ValidationException("Name cannot be empty"))
            }
            if (job.isBlank()) {
                return@withContext Result.Error(KtormException.ValidationException("Job cannot be empty"))
            }
            if (salary != null && salary < BigDecimal.ZERO) {
                return@withContext Result.Error(KtormException.ValidationException("Salary cannot be negative"))
            }
            
            database.useTransaction {
                // Check if employee exists
                val existingEmployee = database.sequenceOf(Employees).find { it.id eq employeeId }
                    ?: throw KtormException.EntityNotFoundException("Employee", employeeId)
                
                // Validate manager exists (if specified and different from current)
                managerId?.let { id ->
                    if (id != existingEmployee.managerId) {
                        val managerExists = database.sequenceOf(Employees).any { it.id eq id }
                        if (!managerExists) {
                            throw KtormException.EntityNotFoundException("Manager", id)
                        }
                        
                        // Prevent circular reference (employee cannot be their own manager)
                        if (id == employeeId) {
                            throw KtormException.ValidationException("Employee cannot be their own manager")
                        }
                    }
                }
                
                // Check for duplicate name in same department (excluding self)
                val duplicateExists = database.sequenceOf(Employees)
                    .any { (it.name eq name) and (it.departmentId eq existingEmployee.departmentId) and (it.id neq employeeId) }
                if (duplicateExists) {
                    throw KtormException.DuplicateKeyException(
                        "Employee with name '$name' already exists in department ${existingEmployee.departmentId}"
                    )
                }
                
                // Update employee
                val updateCount = database.update(Employees) {
                    set(it.name, name)
                    set(it.job, job)
                    set(it.managerId, managerId)
                    set(it.salary, salary)
                    where { it.id eq employeeId }
                }
                
                if (updateCount == 0) {
                    throw KtormException.DatabaseException("Failed to update employee")
                }
                
                // Return updated employee
                database.sequenceOf(Employees).find { it.id eq employeeId }!!
            }
            
            val updatedEmployee = database.sequenceOf(Employees).find { it.id eq employeeId }!!
            Result.Success(updatedEmployee)
            
        } catch (e: KtormException) {
            Result.Error(e)
        } catch (e: Exception) {
            Result.Error(KtormException.DatabaseException("Failed to update employee", e))
        }
    }
    
    // Safe employee deletion with cascade check
    suspend fun deleteEmployeeSafely(employeeId: Int): Result<Boolean> = withContext(Dispatchers.IO) {
        try {
            database.useTransaction {
                // Check if employee exists
                val employee = database.sequenceOf(Employees).find { it.id eq employeeId }
                    ?: throw KtormException.EntityNotFoundException("Employee", employeeId)
                
                // Check if employee is a manager
                val managedEmployeesCount = database.sequenceOf(Employees)
                    .count { it.managerId eq employeeId }
                
                if (managedEmployeesCount > 0) {
                    throw KtormException.ConstraintViolationException(
                        "Cannot delete employee $employeeId: still managing $managedEmployeesCount employees"
                    )
                }
                
                // Check for project assignments
                val projectAssignments = database.from(EmployeeProjects)
                    .select(count())
                    .where { EmployeeProjects.employeeId eq employeeId }
                    .map { it.getInt(1) }
                    .first()
                
                if (projectAssignments > 0) {
                    // Remove project assignments first
                    database.delete(EmployeeProjects) { it.employeeId eq employeeId }
                }
                
                // Delete employee
                val deleteCount = database.delete(Employees) { it.id eq employeeId }
                deleteCount > 0
            }
            
            Result.Success(true)
            
        } catch (e: KtormException) {
            Result.Error(e)
        } catch (e: Exception) {
            Result.Error(KtormException.DatabaseException("Failed to delete employee", e))
        }
    }
    
    // Safe batch operation
    suspend fun batchCreateEmployeesSafely(
        employeesData: List<EmployeeData>
    ): Result<List<Int>> = withContext(Dispatchers.IO) {
        try {
            // Validate all data first
            employeesData.forEachIndexed { index, data ->
                if (data.name.isBlank()) {
                    throw KtormException.ValidationException("Name cannot be empty at index $index")
                }
                if (data.job.isBlank()) {
                    throw KtormException.ValidationException("Job cannot be empty at index $index")
                }
                if (data.hireDate.isAfter(LocalDate.now())) {
                    throw KtormException.ValidationException("Hire date cannot be in the future at index $index")
                }
                if (data.salary != null && data.salary < BigDecimal.ZERO) {
                    throw KtormException.ValidationException("Salary cannot be negative at index $index")
                }
            }
            
            // Check for duplicate names within batch
            val names = employeesData.map { it.name }
            if (names.size != names.toSet().size) {
                throw KtormException.ValidationException("Duplicate names in batch data")
            }
            
            val result = database.useTransaction {
                // Validate all departments exist
                val departmentIds = employeesData.map { it.departmentId }.toSet()
                val existingDepartments = database.from(Departments)
                    .select(Departments.id)
                    .where { Departments.id inList departmentIds }
                    .map { it[Departments.id]!! }
                    .toSet()
                
                val missingDepartments = departmentIds - existingDepartments
                if (missingDepartments.isNotEmpty()) {
                    throw KtormException.EntityNotFoundException("Departments", missingDepartments)
                }
                
                // Validate all managers exist
                val managerIds = employeesData.mapNotNull { it.managerId }.toSet()
                if (managerIds.isNotEmpty()) {
                    val existingManagers = database.from(Employees)
                        .select(Employees.id)
                        .where { Employees.id inList managerIds }
                        .map { it[Employees.id]!! }
                        .toSet()
                    
                    val missingManagers = managerIds - existingManagers
                    if (missingManagers.isNotEmpty()) {
                        throw KtormException.EntityNotFoundException("Managers", missingManagers)
                    }
                }
                
                // Batch creation
                employeesData.map { data ->
                    database.insertAndGenerateKey(Employees) {
                        set(it.name, data.name)
                        set(it.job, data.job)
                        set(it.managerId, data.managerId)
                        set(it.hireDate, data.hireDate)
                        set(it.salary, data.salary)
                        set(it.departmentId, data.departmentId)
                        set(it.createdAt, LocalDateTime.now())
                    } as Int
                }
            }
            
            Result.Success(result)
            
        } catch (e: KtormException) {
            Result.Error(e)
        } catch (e: Exception) {
            Result.Error(KtormException.DatabaseException("Batch creation failed", e))
        }
    }
    
    // Database health check
    suspend fun checkDatabaseHealth(): Result<DatabaseHealthStatus> = withContext(Dispatchers.IO) {
        try {
            val employeeCount = database.sequenceOf(Employees).count()
            val departmentCount = database.sequenceOf(Departments).count()
            
            // Test a simple query
            val latestEmployee = database.sequenceOf(Employees)
                .sortedByDescending { it.createdAt }
                .firstOrNull()
            
            val status = DatabaseHealthStatus(
                isHealthy = true,
                employeeCount = employeeCount,
                departmentCount = departmentCount,
                latestEmployeeName = latestEmployee?.name,
                lastChecked = LocalDateTime.now()
            )
            
            Result.Success(status)
        } catch (e: Exception) {
            Result.Error(KtormException.DatabaseException("Database health check failed", e))
        }
    }
}

// Data class for health status
data class DatabaseHealthStatus(
    val isHealthy: Boolean,
    val employeeCount: Int,
    val departmentCount: Int,
    val latestEmployeeName: String?,
    val lastChecked: LocalDateTime
)

// Usage example for error handling
suspend fun demonstrateErrorHandling() {
    val database = DatabaseConfig.createDatabase()
    val safeRepo = SafeEmployeeRepository(database)
    
    // Test database health
    when (val healthResult = safeRepo.checkDatabaseHealth()) {
        is Result.Success -> println("✓ Database health: ${healthResult.data}")
        is Result.Error -> println("✗ Health check failed: ${healthResult.exception.message}")
    }
    
    // Safe employee creation
    when (val result = safeRepo.createEmployeeSafely(
        name = "Test Employee",
        job = "Software Engineer",
        managerId = null,
        hireDate = LocalDate.of(2023, 6, 1),
        salary = BigDecimal("1000000"),
        departmentId = 1
    )) {
        is Result.Success -> {
            println("✓ Successfully created employee with ID: ${result.data}")
            
            // Safe update
            when (val updateResult = safeRepo.updateEmployeeSafely(
                employeeId = result.data,
                name = "Updated Test Employee",
                job = "Senior Software Engineer",
                managerId = null,
                salary = BigDecimal("1200000")
            )) {
                is Result.Success -> println("✓ Successfully updated employee: ${updateResult.data}")
                is Result.Error -> println("✗ Update failed: ${updateResult.exception.message}")
            }
        }
        is Result.Error -> println("✗ Employee creation failed: ${result.exception.message}")
    }
    
    // Test validation errors
    when (val invalidResult = safeRepo.createEmployeeSafely(
        name = "",
        job = "Invalid Job",
        managerId = 999, // Non-existent manager
        hireDate = LocalDate.now().plusDays(1), // Future date
        salary = BigDecimal("-1000"), // Negative salary
        departmentId = 999 // Non-existent department
    )) {
        is Result.Success -> println("This shouldn't happen")
        is Result.Error -> println("✓ Correctly caught validation error: ${invalidResult.exception.message}")
    }
    
    // Test safe batch creation
    val batchData = listOf(
        EmployeeData("Employee 1", "Developer", null, LocalDate.of(2023, 7, 1), BigDecimal("900000"), 1),
        EmployeeData("Employee 2", "Designer", null, LocalDate.of(2023, 8, 1), BigDecimal("850000"), 1),
        EmployeeData("", "Invalid", null, LocalDate.of(2023, 9, 1), BigDecimal("800000"), 1) // Invalid name
    )
    
    when (val batchResult = safeRepo.batchCreateEmployeesSafely(batchData)) {
        is Result.Success -> println("✓ Batch creation successful: ${batchResult.data}")
        is Result.Error -> println("✓ Correctly caught batch validation error: ${batchResult.exception.message}")
    }
    
    // Test non-existent employee retrieval
    when (val result = safeRepo.getEmployeeSafely(9999)) {
        is Result.Success -> println("This shouldn't happen")
        is Result.Error -> println("✓ Correctly caught entity not found: ${result.exception.message}")
    }
}