ORM 框架可以让开发者专注於物件的 CRUD 操作，不必直接思考 SQL 要怎麽写。如果是新增、修改、删除操作的话，通常只要写一行程序码，呼叫 save() 之类的方法就完成了，所以 ORM 的 Query API 才是开发者关注的重点。Exposed API 的使用方式非常直觉简单，官方文件也都有范例可以参考，就不在此多做介绍了，所以今天的主题是分享进阶的实作技巧，如何透过自定义 ColumnType, Operator, Expression，写出更简洁的查询程序码。
Exposed 提供2种 API 操作资料库
目前我只使用 DSL API 操作，原因是
kotlinx.serialization 无法 serialize delegated property，所以即使是最简单查询 Entity 资料的需求，我也无法把 Entity 物件直接转为 json 输出至前端，必须要定义 DTO 类别用来转换资料。既然如此，我就直接使用 DSL API 把查询出来的资料填入 DTO 物件即可。相关文件及讨论可参考以下连结
// DSL Table
object StarWarsFilms : IntIdTable() {
val sequelId = integer("sequel_id").uniqueIndex()
val name = varchar("name", 50)
val director = varchar("director", 50)
}
// DAO Entity
class StarWarsFilm(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<StarWarsFilm>(StarWarsFilms)
var sequelId by StarWarsFilms.sequelId
var name by StarWarsFilms.name
var director by StarWarsFilms.director
}
以 club_user 资料表为例，lang,contact1, address1,contact2, address2 在资料库的 columnType 都是字串。如果我们也在 ClubUserTable 的栏位变数型态宣告为字串的话，那麽每次从查询结果 ResultRow 取出栏位的字串值後，就要再自行转换为物件填入到 UserDTO。为了避免每次查询後都要自己转换，我们可以利用 kotlin extension function 的特性，在 Exposed 的 Table 类别增加 columnType。
object ClubUserTable : UUIDTable(name = "club_user") {
// string of varchar column
val name = varchar("name", USER_NAME_LENGTH)
// lang object of varchar(20) column
val lang = lang("lang").nullable()
// json of text column
val contact1 = jsonObject("contact1")
val addresses1 = jsonArray("addresses1")
// dto of text column
val contact2 = dto<Contact>("contact2")
val addresses2 = dtoList<Address>("addresses2")
}
@Serializable
data class UserDTO(@JvmField @Serializable(with = UUIDSerializer::class) val id: UUID) : EntityDTO<UUID> {
var name: String? = null
var contact1: JsonObject? = null
var addresses1: JsonArray? = null
var contact2: Contact? = null
var addresses2: List<Address>? = null
override fun getId(): UUID = id
}
以 lang 栏位为例，储存在资料库的值是字串 languageCode，我们可以实作 valueFromDB
及 notNullValueToDB 方法，告诉 Exposed 要如何与 Lang 物件做双向转换，然後 ResultRow 呼叫 get(lang) 方法取出的物件型态就会是 Lang 了。
val langObject: Lang = ClubUserTable.select { account eq "john" }.singleOrNull()?.get(lang)
@Serializable
class Lang(val locale: Locale) {
constructor(code: String) : this(Locale.Builder().setLanguageTag(code).build())
val code: String = locale.toLanguageTag()
}
fun Table.lang(name: String): Column<Lang> = registerColumn(name, object : VarCharColumnType(20) {
override fun sqlType(): String = currentDialect.dataTypeProvider.textType()
override fun valueFromDB(value: Any): Lang = Lang(value as String)
override fun notNullValueToDB(value: Any): Any = (value as Lang).code
})
资料库除了储存单纯的字串之外，储存 Json 字串也是非常常见的需求，在这里使用 kotlinx.serialization 的 parseToJsonElement 与 toString 进行转换
val contact1: JsonObject? = ClubUserTable.select { account eq "john" }.singleOrNull()?.get(contact1)
fun Table.jsonObject(name: String): Column<JsonObject> = registerColumn(name, object : StringColumnType() {
override fun sqlType(): String = currentDialect.dataTypeProvider.textType()
override fun valueFromDB(value: Any): JsonElement = json.parseToJsonElement(value as String).jsonObject
override fun notNullValueToDB(value: Any): Any = value.toString()
})
fun Table.jsonArray(name: String): Column<JsonArray> = registerColumn(name, object : StringColumnType() {
override fun sqlType(): String = currentDialect.dataTypeProvider.textType()
override fun valueFromDB(value: Any): JsonElement = json.parseToJsonElement(value as String).jsonArray
override fun notNullValueToDB(value: Any): Any = value.toString()
})
如果能再从 JSON 物件转换为自定义的 DTO 物件，那就更简洁易读且 typesafe 了
val addresses2: List<Address>? = ClubUserTable.select { account eq "john" }.singleOrNull()?.get(addresses2)
@OptIn(InternalSerializationApi::class)
inline fun <reified T : Any> Table.dto(name: String): Column<T> = registerColumn(name, object : StringColumnType() {
override fun sqlType(): String = currentDialect.dataTypeProvider.textType()
override fun valueFromDB(value: Any): T = json.decodeFromString(T::class.serializer(), value as String)
override fun notNullValueToDB(value: Any): Any = json.encodeToString(T::class.serializer(), value as T)
})
@OptIn(InternalSerializationApi::class)
inline fun <reified T : Any> Table.dtoList(name: String): Column<List<T>> = registerColumn(name, object : StringColumnType() {
override fun sqlType(): String = currentDialect.dataTypeProvider.textType()
override fun valueFromDB(value: Any): List<T> = json.decodeFromString(ListSerializer(T::class.serializer()), value as String)
override fun notNullValueToDB(value: Any): Any = json.encodeToString(ListSerializer(T::class.serializer()), value as List<T>)
})
SQL 的 Beween Operator 的 begin 与 end 都是 inclusive，然而我们还是会有 endExclusive 的需求，与其每次都要写 columnA >= begin and columnB < end，倒不如自己定义 endExclusiveRange operator 比较容易阅读且不会出错
ClubUserTable.select { createdAt endExclusiveRange (beginInstant to endInstant) }.toList()
infix fun ExpressionWithColumnType<Instant>.endExclusiveRange(pair: Pair<Instant, Instant>): AndOp = inTemporalRange(this, pair)
private fun <T, R> endExclusiveRange(column: ExpressionWithColumnType<T>, pair: Pair<R, R>): AndOp = AndOp(
listOf(
GreaterEqOp(column, QueryParameter(pair.first, column.columnType)),
LessOp(column, QueryParameter(pair.second, column.columnType))
)
)
另一个常见需求是检查两个 X, Y 的时间区间有没有重叠，也就是 Y.startInclusive <= X.endInclusive && Y.endInclusive >= X.startInclusive
ClubUserTable.select { overlap(createdAt, updatedAt, beginInstant, endInstant) }.toList()
fun <T, R> SqlExpressionBuilder.overlap(
startColumn: ExpressionWithColumnType<T>,
endColumn: ExpressionWithColumnType<T>,
rangeStart: R,
rangeEndInclusive: R
): AndOp = AndOp(
listOf(
LessEqOp(startColumn, QueryParameter(rangeEndInclusive, startColumn.columnType)),
GreaterEqOp(endColumn, QueryParameter(rangeStart, endColumn.columnType))
)
)
MySQL 支援多个栏位的 IN 查询语法 => SELECT * FROM table WHERE (x,y) IN ((1, 2), (3, 4), (5, 6))。Java 的解决方式不外乎就是写个 SQL 字串处理的 utility method，但是 kotlin 可以在 SqlExpressionBuilder 加上 multiIn extension function，覆写 toQueryBuilder 方法将组好的字串 append 到 SQL statement
ClubUserTable.select { multiIn(listOf(name, enabled), listOf(listOf("john", true), listOf("mary", false))) }.toList()
fun SqlExpressionBuilder.multiIn(columns: List<Column<*>>, values: List<List<Any>>) = object : Op<Boolean>() {
override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
append(columns.joinToString(",", prefix = "(", postfix = ")") {
it.name
})
append(" in ")
append(values.joinToString(",", prefix = "(", postfix = ")") { it1 ->
it1.joinToString(",", prefix = "(", postfix = ")") { it2 ->
when (it2) {
is String -> "'$it2'"
else -> it2.toString()
}
}
})
}
}
如果需要写比较复杂的 Raw SQL，那麽我们可以定义 RawExpression ，然後在里面 append 任何字串。这个方式的好处是不必为了一个 Exposed 未内建支援的 expression，就放弃使用 DSL API 走回字串相加的老方法
class RawExpression(private val raw: String) : Expression<String>() {
override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder { +raw }
}
class CountExpression : Expression<Long>() {
override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder { +"COUNT(*)" }
}
Exposed DSL API 的查询结果是 ResultRow 物件，需要自己转换为各种 DTO 物件，所以我在 ResultRow 加上 toDTO(dtoClass: KClass<T>): T …等 extension function，可以写出 fluent-style 的程序码 query.toList().toDTO(UserDTO::class).map {...}。另一方面，内部实作会递回处理每一个 DTO 物件属性，所以支援巢状结构的 DTO。
点我连结至完整 ResultRowDTOMapper 程序码
fun <T : EntityDTO<*>> ResultRow.toDTO(dtoClass: KClass<T>): T {
logger.debug { "========== map ResultRow to ${dtoClass.qualifiedName} begin ==========" }
val mapper = ResultRowDTOMapper.getMapper(dtoClass)
val value = mapper.mapDTO(this)!!
logger.debug { "========== map ResultRow to ${dtoClass.qualifiedName} end ==========" }
return value
}
fun <T : EntityDTO<*>> List<ResultRow>.toSingleDTO(dtoClass: KClass<T>): T? {
return takeIf { it.isNotEmpty() }?.toDTO(dtoClass)?.get(0)
}
fun <T : EntityDTO<*>> List<ResultRow>.toDTO(dtoClass: KClass<T>): List<T> {
logger.debug { "=============== map List<ResultRow> to ${dtoClass.qualifiedName} begin ===============" }
val mapper = ResultRowDTOMapper.getMapper(dtoClass)
val dtoList = mutableListOf<T>()
val dtoMap = mutableMapOf<List<*>, T>()
this.forEach { resultRow ->
val dtoIdValues = resultRow.getPKValuesOfTable(mapper.table)!!
var dto = dtoMap[dtoIdValues]
if (dto == null) {
dto = dtoClass.primaryConstructor!!.call(*dtoIdValues.toTypedArray())
mapper.mapDTO(dto, resultRow)
dtoMap[dtoIdValues] = dto
dtoList.add(dto)
} else {
// use single to avoid cross join
val nestedDTOListProperty =
dtoClass.memberProperties.single { it.returnType.isSubtypeOf(ResultRowDTOMapper.listType) } as KProperty<MutableList<T>>
val nestedDTOClass = nestedDTOListProperty.returnType.arguments[0].type!!.classifier as KClass<T>
val nestedDTO = resultRow.toDTO(nestedDTOClass)
nestedDTOListProperty.getter.call(dto).add(nestedDTO)
}
}
logger.debug { "=============== map List<ResultRow> to ${dtoClass.qualifiedName} end ===============" }
return dtoList
}
为了知道每一个 Exposed transaction block 耗时多久，还有想 catch Exposed ExposedSQLException 转换为自定义的 Exception，所以定义自己的 transaction function，内部再呼叫 Exposed transaction block
private val profilingLogger = KotlinLogging.logger("fanpoll.infra.database.sql.Profiling")
fun <T> transaction(db: Database? = null, statement: Transaction.() -> T): T {
return transaction(db) {
val begin = Instant.now()
profilingLogger.debug { "===== Transaction Profiling Begin ($id) ===== " }
try {
statement()
} catch (e: ExposedSQLException) {
throw InternalServerException(InfraResponseCode.DB_SQL_ERROR, e.toString(), e) // include caused SQL
} finally {
profilingLogger.debug {
"===== Transaction execution time: ${Duration.between(begin, Instant.now()).toMillis()} millis ($id) ====="
}
}
}
}
>>: [Day13] Slide In on Scroll
分散式 transaction 和共识 (Distributed Transactions and ...
这一个章节节我们要来介绍复合查询，当单一的查询子句无法完成需求时，为了应付这种高级查询需求，所以就产...
使用 Python Flask 架设 API 吧！ 今日学习目标 API 观念讲解 什麽是 API？...
KNN简单说明 为一种监督学习的方法，其原理就好像物以类聚一样，相同的东西会聚在一起 我们可以设定一...
1. 使用浏览器 DSM 套件中心安装需要的开发相关套件，这边我安装 Docker 跟 Node.j...