Exposed - Kotlin语言的轻量级SQL库和ORM框架

欢迎来到Exposed，一个适用于Kotlin的ORM框架。

Exposed是一个基于JDBC驱动的轻量级SQL库，专为Kotlin语言设计。 Exposed提供两种数据库访问方式：类型安全的SQL包装DSL和轻量级数据访问对象（DAO）。

使用Exposed，你可以选择包装DSL或轻量级DAO进行数据库访问。我们的官方吉祥物是墨鱼，以其出色的模仿能力而闻名，能够无缝融入任何环境。与我们的吉祥物类似，Exposed可以用来模仿各种数据库引擎，帮助你构建不依赖于特定数据库引擎的应用程序，并且可以在它们之间切换，几乎不需要或只需很少的更改。

支持的数据库

H2（2.x版本；1.x版本已弃用，将在未来版本中移除）
（同时支持使用pgjdbc-ng JDBC驱动的PostgreSQL）

依赖项

Maven中央仓库配置

Exposed的发布版本可在Maven中央仓库中获取。你可以在构建脚本中声明此仓库，如下所示：

Gradle Groovy和Kotlin DSL

警告： 你可能需要将Kotlin JVM目标设置为8，使用Spring时设置为17，以确保正常工作：

repositories {
    // 0.30.1之后的版本
    // 0.30.1之前的版本目前不可用
    mavenCentral()
}

Maven

Maven用户默认启用Maven中央仓库。

Exposed模块

Exposed包含以下模块：

exposed-core - 基础模块，包含DSL API和映射
exposed-crypt - 提供额外的列类型，用于在数据库中存储加密数据并在客户端进行编码/解码
exposed-dao - DAO API
exposed-java-time - 基于Java8 Time API的日期时间扩展
exposed-jdbc - 基于Java JDBC API的传输层实现
exposed-jodatime - 基于JodaTime库的日期时间扩展
exposed-json - JSON和JSONB数据类型扩展
exposed-kotlin-datetime - 基于kotlinx-datetime的日期时间扩展
exposed-money - 支持"javax.money:money-api"中MonetaryAmount的扩展
exposed-spring-boot-starter - Spring Boot的启动器，用于使用Exposed作为ORM，而不是Hibernate


<dependencies>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-core</artifactId>
        <version>0.53.0</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-crypt</artifactId>
        <version>0.53.0</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-dao</artifactId>
        <version>0.53.0</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-java-time</artifactId>
        <version>0.53.0</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-jdbc</artifactId>
        <version>0.53.0</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-jodatime</artifactId>
        <version>0.53.0</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-json</artifactId>
        <version>0.53.0</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-kotlin-datetime</artifactId>
        <version>0.53.0</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-money</artifactId>
        <version>0.53.0</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-spring-boot-starter</artifactId>
        <version>0.53.0</version>
    </dependency>
</dependencies>

Gradle Groovy

dependencies {
    implementation 'org.jetbrains.exposed:exposed-core:0.53.0'
    implementation 'org.jetbrains.exposed:exposed-crypt:0.53.0'
    implementation 'org.jetbrains.exposed:exposed-dao:0.53.0'
    implementation 'org.jetbrains.exposed:exposed-jdbc:0.53.0'
    
    implementation 'org.jetbrains.exposed:exposed-jodatime:0.53.0'
    // 或
    implementation 'org.jetbrains.exposed:exposed-java-time:0.53.0'
    // 或
    implementation 'org.jetbrains.exposed:exposed-kotlin-datetime:0.53.0'
    
    implementation 'org.jetbrains.exposed:exposed-json:0.53.0'
    implementation 'org.jetbrains.exposed:exposed-money:0.53.0'
    implementation 'org.jetbrains.exposed:exposed-spring-boot-starter:0.53.0'
}

Gradle Kotlin DSL

在build.gradle.kts中：

val exposedVersion: String by project
dependencies {
    implementation("org.jetbrains.exposed:exposed-core:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-crypt:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-dao:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-jdbc:$exposedVersion")
    
    implementation("org.jetbrains.exposed:exposed-jodatime:$exposedVersion")
    // 或
    implementation("org.jetbrains.exposed:exposed-java-time:$exposedVersion")
    // 或
    implementation("org.jetbrains.exposed:exposed-kotlin-datetime:$exposedVersion")
    
    implementation("org.jetbrains.exposed:exposed-json:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-money:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-spring-boot-starter:$exposedVersion")
}

在gradle.properties中

exposedVersion=0.53.0

示例

查看示例以快速入门。

链接

目前，Exposed可用于maven/gradle构建。查看Maven中央仓库并阅读入门指南以了解如何设置Exposed。

欲了解更多信息，请访问以下链接：

带有示例和文档的文档
为Exposed做贡献
迁移指南
重大变更和任何迁移详情
Slack频道
问题追踪

提交问题

请注意，我们正在从GitHub Issues转移到YouTrack来报告错误和功能。您必须登录才能查看和记录问题，否则将会遇到404错误。

社区

有问题吗？欢迎申请邀请加入kotlinlang slack，并在我们的#exposed频道参与项目讨论。

拉取请求

我们欢迎您的拉取请求。但是，我们更倾向于将您的工作与现有问题关联起来。

从主分支创建分支并为其命名。
为您的分支选择一个能描述您正在进行的工作的名称。例如：adds-new-thing。
如果您添加了应该被测试的代码，请添加测试并确保测试套件通过。
确保解决所有的lint警告。
如果您改进了现有代码，请在PR描述中告诉我们。

更多详细信息，请参阅贡献指南。

示例

SQL DSL

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

object Users : Table() {
    val id: Column<String> = varchar("id", 10)
    val name: Column<String> = varchar("name", length = 50)
    val cityId: Column<Int?> = (integer("city_id") references Cities.id).nullable()

    override val primaryKey = PrimaryKey(id, name = "PK_User_ID") // 这里的name是可选的
}

object Cities : Table() {
    val id: Column<Int> = integer("id").autoIncrement()
    val name: Column<String> = varchar("name", 50)

    override val primaryKey = PrimaryKey(id, name = "PK_Cities_ID")
}

fun main() {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver", user = "root", password = "")

    transaction {
        addLogger(StdOutSqlLogger)

        SchemaUtils.create(Cities, Users)

        val saintPetersburgId = Cities.insert {
            it[name] = "圣彼得堡"
        } get Cities.id

        val munichId = Cities.insert {
            it[name] = "慕尼黑"
        } get Cities.id

        val pragueId = Cities.insert {
            it.update(name, stringLiteral("   布拉格   ").trim().substring(1, 2))
        }[Cities.id]

        val pragueName = Cities.selectAll().where { Cities.id eq pragueId }.single()[Cities.name]
        println("布拉格名称 = $pragueName")

        Users.insert {
            it[id] = "andrey"
            it[name] = "安德
Users.deleteWhere{ Users.name like "%thing" }

println("所有城市:")

for (city in Cities.selectAll()) {
    println("${city[Cities.id]}: ${city[Cities.name]}")
}

println("手动连接:")

(Users innerJoin Cities)
    .select(Users.name, Cities.name)
    .where {
        (Users.id.eq("andrey") or Users.name.eq("Sergey")) and
            Users.id.eq("sergey") and Users.cityId.eq(Cities.id)
    }.forEach { 
        println("${it[Users.name]} 住在 ${it[Cities.name]}") 
    }

println("使用外键的连接:")

(Users innerJoin Cities)
    .select(Users.name, Users.cityId, Cities.name)
    .where { Cities.name.eq("St. Petersburg") or Users.cityId.isNull() }
    .forEach { 
        if (it[Users.cityId] != null) { 
            println("${it[Users.name]} 住在 ${it[Cities.name]}") 
        } 
        else { 
            println("${it[Users.name]} 没有住处") 
        } 
    }

println("函数和分组:")

((Cities innerJoin Users)
    .select(Cities.name, Users.id.count())
    .groupBy(Cities.name)
    ).forEach {
        val cityName = it[Cities.name]
        val userCount = it[Users.id.count()]

        if (userCount > 0) {
            println("$cityName 有 $userCount 个用户")
        } else {
            println("$cityName 没有人居住")
        }
    }

SchemaUtils.drop(Users, Cities)
}
}

生成的SQL:

SQL: CREATE TABLE IF NOT EXISTS Cities (id INT AUTO_INCREMENT, name VARCHAR(50) NOT NULL, CONSTRAINT PK_Cities_ID PRIMARY KEY (id))
SQL: CREATE TABLE IF NOT EXISTS Users (id VARCHAR(10), name VARCHAR(50) NOT NULL, city_id INT NULL, CONSTRAINT PK_User_ID PRIMARY KEY (id), CONSTRAINT FK_Users_city_id__ID FOREIGN KEY (city_id) REFERENCES Cities(id) ON DELETE RESTRICT ON UPDATE RESTRICT)
SQL: INSERT INTO Cities (name) VALUES ('St. Petersburg')
SQL: INSERT INTO Cities (name) VALUES ('Munich')
SQL: INSERT INTO Cities (name) VALUES (SUBSTRING(TRIM('   Prague   '), 1, 2))
SQL: SELECT Cities.id, Cities.name FROM Cities WHERE Cities.id = 3
pragueName = Pr
SQL: INSERT INTO Users (id, name, city_id) VALUES ('andrey', 'Andrey', 1)
SQL: INSERT INTO Users (id, name, city_id) VALUES ('sergey', 'Sergey', 2)
SQL: INSERT INTO Users (id, name, city_id) VALUES ('eugene', 'Eugene', 2)
SQL: INSERT INTO Users (id, name, city_id) VALUES ('alex', 'Alex', NULL)
SQL: INSERT INTO Users (id, name, city_id) VALUES ('smth', 'Something', NULL)
SQL: UPDATE Users SET name='Alexey' WHERE Users.id = 'alex'
SQL: DELETE FROM Users WHERE Users.name LIKE '%thing'
所有城市:
SQL: SELECT Cities.id, Cities.name FROM Cities
1: St. Petersburg
2: Munich
3: Pr
手动连接:
SQL: SELECT Users.name, Cities.name FROM Users INNER JOIN Cities ON Cities.id = Users.city_id WHERE ((Users.id = 'andrey') or (Users.name = 'Sergey')) and (Users.id = 'sergey') and (Users.city_id = Cities.id)
Sergey 住在 Munich
使用外键的连接:
SQL: SELECT Users.name, Users.city_id, Cities.name FROM Users INNER JOIN Cities ON Cities.id = Users.city_id WHERE (Cities.name = 'St. Petersburg') or (Users.city_id IS NULL)
Andrey 住在 St. Petersburg
函数和分组:
SQL: SELECT Cities.name, COUNT(Users.id) FROM Cities INNER JOIN Users ON Cities.id = Users.city_id GROUP BY Cities.name
St. Petersburg 有 1 个用户
Munich 有 2 个用户
SQL: DROP TABLE IF EXISTS Users
SQL: DROP TABLE IF EXISTS Cities

DAO

import org.jetbrains.exposed.dao.*
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

object Users : IntIdTable() {
    val name = varchar("name", 50).index()
    val city = reference("city", Cities)
    val age = integer("age")
}

object Cities: IntIdTable() {
    val name = varchar("name", 50)
}

class User(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<User>(Users)

    var name by Users.name
    var city by City referencedOn Users.city
    var age by Users.age
}

class City(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<City>(Cities)

    var name by Cities.name
    val users by User referrersOn Users.city
}

fun main() {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver", user = "root", password = "")

    transaction {
        addLogger(StdOutSqlLogger)

        SchemaUtils.create(Cities, Users)

        val stPete = City.new {
            name = "St. Petersburg"
        }

        val munich = City.new {
            name = "Munich"
        }

        User.new {
            name = "a"
            city = stPete
            age = 5
        }

        User.new {
            name = "b"
            city = stPete
            age = 27
        }

        User.new {
            name = "c"
            city = munich
            age = 42
        }

        println("城市: ${City.all().joinToString { it.name }}")
        println("${stPete.name}的用户: ${stPete.users.joinToString { it.name }}")
        println("成年人: ${User.find { Users.age greaterEq 18 }.joinToString { it.name }}")
    }
}

生成的SQL:

SQL: CREATE TABLE IF NOT EXISTS Cities (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL)
SQL: CREATE TABLE IF NOT EXISTS Users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, city INT NOT NULL, age INT NOT NULL, CONSTRAINT FK_Users_city__ID FOREIGN KEY (city) REFERENCES Cities(id) ON DELETE RESTRICT ON UPDATE RESTRICT)
SQL: CREATE INDEX Users_name ON Users (name)
SQL: INSERT INTO Cities (name) VALUES ('St. Petersburg')
SQL: INSERT INTO Cities (name) VALUES ('Munich')
SQL: SELECT Cities.id, Cities.name FROM Cities
城市: St. Petersburg, Munich
SQL: INSERT INTO Users (name, city, age) VALUES ('a', 1, 5)
SQL: INSERT INTO Users (name, city, age) VALUES ('b', 1, 27)
SQL: INSERT INTO Users (name, city, age) VALUES ('c', 2, 42)
SQL: SELECT Users.id, Users.name, Users.city, Users.age FROM Users WHERE Users.city = 1
St. Petersburg的用户: a, b
SQL: SELECT Users.id, Users.name, Users.city, Users.age FROM Users WHERE Users.age >= 18
成年人: b, c

贡献

在贡献之前，请参阅贡献指南。

通过向Exposed项目贡献，您同意您的贡献将根据Apache License, Version 2.0许可。