简体中文 | English
编辑本页

定义表结构

在使用 SQL DSL 之前,我们首先要让 Ktorm 能够了解我们的表结构。假设我们有两个表,他们分别是部门表 t_department 和员工表 t_employee, 它们的建表 SQL 如下,我们要如何描述这两个表呢?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table t_department(
id int not null primary key auto_increment,
name varchar(128) not null,
location varchar(128) not null
);

create table t_employee(
id int not null primary key auto_increment,
name varchar(128) not null,
job varchar(128) not null,
manager_id int null,
hire_date date not null,
salary bigint not null,
department_id int not null
);

表对象

一般来说,Ktorm 使用 Kotlin 中的 object 关键字定义一个继承 Table 类的对象来描述表结构,上面例子中的两个表可以像这样在 Ktorm 中定义:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
object Departments : Table<Nothing>("t_department") {
val id = int("id").primaryKey()
val name = varchar("name")
val location = varchar("location")
}

object Employees : Table<Nothing>("t_employee") {
val id = int("id").primaryKey()
val name = varchar("name")
val job = varchar("job")
val managerId = int("manager_id")
val hireDate = date("hire_date")
val salary = long("salary")
val departmentId = int("department_id")
}

可以看到,DepartmentsEmployees 都继承了 Table,并且在构造函数中指定了表名,Table 类还有一个泛型参数,它是此表绑定到的实体类的类型,在这里我们不需要绑定到任何实体类,因此指定为 Nothing 即可。

表中的列则使用 val 关键字定义为表对象中的成员属性,列的类型使用 int、long、varchar、date 等函数定义,它们分别对应了 SQL 中的相应类型,这些类型定义函数的普遍特征如下:

  • 它们是 Table 类的扩展函数,只能在定义表对象时使用
  • 它们的名称一般对应于其实际的 SQL 类型的名称
  • 它们都接收一个字符串的参数,在这里我们需要把列的名称传入
  • 它们的返回值都是 Column<C>,C 为该列的类型,我们可以链式调用 primaryKey 扩展函数,将当前列声明为主键

通常我们都会将表定义为 Kotlin 单例对象,但我们其实不必拘泥于此。例如,在某些情况下,我们有两个结构完全相同的表,只是表名不同(在数据备份的时候比较常见),难道我们一定要在每一个表对象中都写一遍完全相同的字段定义吗?当然不需要,这里我们可以使用继承重用代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
sealed class Employees(tableName: String) : Table<Nothing>(tableName) {
val id = int("id").primaryKey()
val name = varchar("name")
val job = varchar("job")
val managerId = int("manager_id")
val hireDate = date("hire_date")
val salary = long("salary")
val departmentId = int("department_id")
}

object RegularEmployees : Employees("t_regular_employee")

object FormerEmployees : Employees("t_former_employee")

再比如,有时我们的某个表只需要使用一次,因此没有必要将其定义为全局对象,以免污染命名空间。这时,我们甚至可以在函数内部使用匿名对象定义一个表:

1
2
3
4
5
6
7
val t = object : Table<Nothing>("t_config") {
val key = varchar("key").primaryKey()
val value = varchar("value")
}

// Get all configs as a Map<String, String>
val configs = database.from(t).select().associate { row -> row[t.key] to row[t.value] }

灵活使用 Kotlin 的语法特性可以帮助我们减少重复代码、提高项目的可维护性。

SqlType

SqlType 是一个抽象类,它为 SQL 中的数据类型提供了统一的抽象,基于 JDBC,它封装了从 ResultSet 中获取数据,往 PreparedStatement 设置参数等通用的操作。在前面定义表中的字段时,我们曾使用了表示不同类型的 int、varchar 等列定义函数,这些函数的背后其实都有一个特定的 SqlType 的子类。比如 int 函数的实现是这样的:

1
2
3
4
5
6
7
8
9
10
11
12
13
fun BaseTable<*>.int(name: String): Column<Int> {
return registerColumn(name, IntSqlType)
}

object IntSqlType : SqlType<Int>(Types.INTEGER, typeName = "int") {
override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: Int) {
ps.setInt(index, parameter)
}

override fun doGetResult(rs: ResultSet, index: Int): Int? {
return rs.getInt(index)
}
}

IntSqlType 的实现特别简单,它只是使用了 ResultSet.getInt 函数获取来自结果集中的数据,使用 PreparedStatement.setInt 设置传递给数据库的参数而已。

Ktorm 默认支持的数据类型如下表:

函数名Kotlin 类型底层 SQL 类型JDBC 类型码 (java.sql.Types)
booleankotlin.BooleanbooleanTypes.BOOLEAN
intkotlin.IntintTypes.INTEGER
shortkotlin.ShortsmallintTypes.SMALLINT
longkotlin.LongbigintTypes.BIGINT
floatkotlin.FloatfloatTypes.FLOAT
doublekotlin.DoubledoubleTypes.DOUBLE
decimaljava.math.BigDecimaldecimalTypes.DECIMAL
varcharkotlin.StringvarcharTypes.VARCHAR
textkotlin.StringtextTypes.LONGVARCHAR
blobkotlin.ByteArrayblobTypes.BLOB
byteskotlin.ByteArraybytesTypes.BINARY
jdbcTimestampjava.sql.TimestamptimestampTypes.TIMESTAMP
jdbcDatejava.sql.DatedateTypes.DATE
jdbcTimejava.sql.TimetimeTypes.TIME
timestampjava.time.InstanttimestampTypes.TIMESTAMP
datetimejava.time.LocalDateTimedatetimeTypes.TIMESTAMP
datejava.time.LocalDatedateTypes.DATE
timejava.time.TimetimeTypes.TIME
monthDayjava.time.MonthDayvarcharTypes.VARCHAR
yearMonthjava.time.YearMonthvarcharTypes.VARCHAR
yearjava.time.YearintTypes.INTEGER
enumkotlin.EnumenumTypes.VARCHAR
uuidjava.util.UUIDuuidTypes.OTHER

扩展更多的类型

有时候,Ktorm 内置的这些数据类型可能并不能完全满足你的需求,比如你希望在数据库中存储一个 json 字段,许多关系数据库都已经支持了 json 类型,但是原生 JDBC 并不支持,Ktorm 也并没有默认支持。这时你可以自己提供一个 SqlType 的实现:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
class JsonSqlType<T : Any>(
val objectMapper: ObjectMapper,
val javaType: JavaType
) : SqlType<T>(Types.VARCHAR, "json") {

override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: T) {
ps.setString(index, objectMapper.writeValueAsString(parameter))
}

override fun doGetResult(rs: ResultSet, index: Int): T? {
val json = rs.getString(index)
if (json.isNullOrBlank()) {
return null
} else {
return objectMapper.readValue(json, javaType)
}
}
}

上面这个类使用 Jackson 框架进行 json 与对象之间的转换,提供了 json 数据类型的支持。有了 JsonSqlType 之后,怎样使用这个类型定义一个列呢?在前面 int 函数的实现中,我们注意到其中调用了 registerColumn 函数,这正是其中的秘诀,registerColumn 函数正是 Ktorm 提供的用来支持类型扩展的入口。我们可以写一个这样的扩展函数:

1
2
3
4
5
6
inline fun <reified C : Any> BaseTable<*>.json(
name: String,
mapper: ObjectMapper = sharedObjectMapper
): Column<C> {
return registerColumn(name, JsonSqlType(mapper, mapper.constructType(typeOf<C>())))
}

使用方式如下:

1
2
3
object Foo : Table<Nothing>("foo") {
val bar = json<List<Int>>("bar")
}

这样,Ktorm 就能无缝支持 json 字段的存取,事实上,这正是 ktorm-jackson 模块的功能之一。如果你真的需要使用 json 字段,请直接在项目中添加依赖即可,不必再写一遍上面的代码,这里仅作示范。

Maven 依赖:

1
2
3
4
5
<dependency>
<groupId>org.ktorm</groupId>
<artifactId>ktorm-jackson</artifactId>
<version>${ktorm.version}</version>
</dependency>

或者 gradle:

1
compile "org.ktorm:ktorm-jackson:${ktorm.version}"

最后,Ktorm 2.7 版本还新增了一个 transform 函数,使用这个函数,我们可以基于现有的数据类型进行扩展,增加一些自定义的转换行为,得到新的数据类型,而不必手动写一个 SqlType 的实现类。

例如下面的例子,我们定义了一个类型为 Column<UserRole> 的列,但是在数据库中保存的还是 int 值,只是在获取结果及设置参数到 PreparedStatement 时执行了一定的转换:

1
val role = int("role").transform({ UserRole.fromCode(it) }, { it.code })

需要注意的是,这个转换在获取每条结果时都会执行一次,所以在这里不要有太重的行为,以免对性能造成影响。