Kotlin 数据库操作

使用 JDBC

基本操作

import java.sql.*

fun main() {
    val url = "jdbc:mysql://localhost:3306/mydb"
    val user = "root"
    val password = "password"
    
    Connection.use { conn ->
        conn.driverClassName = "com.mysql.cj.jdbc.Driver"
        
        // 查询
        val stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?")
        stmt.setInt(1, 1)
        val rs = stmt.executeQuery()
        
        while (rs.next()) {
            println("${rs.getInt("id")}: ${rs.getString("name")}")
        }
        
        // 插入
        val insert = conn.prepareStatement("INSERT INTO users (name) VALUES (?)")
        insert.setString(1, "Tom")
        insert.executeUpdate()
    }
}

使用 Exposed

Kotlin 官方 ORM 框架。

配置

// build.gradle.kts
dependencies {
    implementation("org.jetbrains.exposed:exposed-core:0.41.1")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.41.1")
    implementation("com.mysql:mysql-connector-j:8.0.33")
}

定义表

import org.jetbrains.exposed.sql.*

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

CRUD 操作

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

fun main() {
    Database.connect("jdbc:mysql://localhost:3306/mydb", "com.mysql.cj.jdbc.Driver", "root", "password")
    
    transaction {
        // 插入
        val userId = Users.insert {
            it[name] = "Tom"
            it[email] = "[email protected]"
        } get Users.id
        
        // 查询
        val users = Users.select { Users.name like "%Tom%" }
        
        // 更新
        Users.update({ Users.id eq userId }) {
            it[name] = "Tom Updated"
        }
        
        // 删除
        Users.deleteWhere { Users.id eq userId }
    }
}

使用 Room (Android)

// build.gradle.kts
dependencies {
    implementation("androidx.room:room-runtime:2.5.2")
    implementation("androidx.room:room-ktx:2.5.2")
    kapt("androidx.room:room-compiler:2.5.2")
}

@Entity
data class User(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val name: String,
    val email: String
)

@Dao
interface UserDao {
    @Query("SELECT * FROM user")
    suspend fun getAll(): List<User>
    
    @Insert
    suspend fun insert(user: User)
    
    @Delete
    suspend fun delete(user: User)
}

@Database(entities = [User::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}