大多數系統的資料庫查詢操作比寫入多樣化且複雜，後端工程師要花比較多的心力撰寫查詢 API，以下列出常見的幾個問題
綜觀以上問題，後端是否能對於每一張資料表都使用相同的實作方式，只需要寫一個查詢 API 就可以滿足前端 「僅回傳指定欄位資料」、「多個查詢條件任意組合」、「分頁」、「排序」、「僅查詢筆數」的查詢需求，而且這種實作方式要開發快速，只需要寫少許程式碼就能立即套用於任一張資料表。
以查詢 Club 子專案的 User 資料表為範例，因為我在 q_fields 有指定 devices，查詢資料庫時就會 join 另一張 infra_user_device 資料表，一併取得使用者的多個 device 資料
QueryDSL
GET http://localhost:8080/club/users?q_fields=name,devices&q_filter=[birthYear > 1970 and enabled = true and role in (Admin, Member)]&q_orderBy=createdAt-
底層轉換為 SQL
SELECT club_user.id, infra_user_device.id, club_user."name", infra_user_device.enabled, infra_user_device.enabled_at, infra_user_device.os_version, infra_user_device.push_token, infra_user_device.source_type, infra_user_device.user_agent, infra_user_device.user_id FROM club_user LEFT JOIN infra_user_device ON club_user.id = infra_user_device.user_id WHERE (club_user.birth_year > 1970) AND (club_user.enabled = true) AND (club_user."role" IN ('Admin', 'Member')) ORDER BY club_user.created_at DESC
我習慣在 Swagger 操作比較清楚，因為有 API 文件說明如何撰寫 QueryDSL
查詢結果
繼續以上面查詢 Club 子專案的 User 資料表為範例，實作上，我們只要使用自定義的 dynamicQuery route function，然後指定 UserDTO 型態，只要 3 行程式碼就可以快速為某張資料表，完成一個支援 REST QueryDSL 的 API
authorize(ClubAuth.Admin) {
dynamicQuery<UserDTO>(ClubOpenApi.FindUsers) { dynamicQuery ->
call.respond(dynamicQuery.queryDB<UserDTO>())
}
}
需要在 UserDTO 的 ResultRowDTOMapper 定義如何 join UserDeviceDTO 的 infra_user_device 資料表，這樣轉換 QueryDSL 為 SQL 時，才知道要怎麼 join。
@Serializable
data class UserDTO(@JvmField @Serializable(with = UUIDSerializer::class) val id: UUID) : EntityDTO<UUID> {
var account: String? = null
var enabled: Boolean? = null
var role: ClubUserRole? = null
var name: String? = null
var gender: Gender? = null
var birthYear: Int? = null
var email: String? = null
var mobile: String? = null
var lang: Lang? = null
@Transient
var password: String? = null
@Serializable(with = InstantSerializer::class)
var createdAt: Instant? = null
var devices: List<UserDeviceDTO>? = null
override fun getId(): UUID = id
companion object {
val mapper: ResultRowDTOMapper<UserDTO> = ResultRowDTOMapper(
UserDTO::class, ClubUserTable,
joins = listOf(DynamicDBJoinPart(JoinType.LEFT, UserDeviceTable, ClubUserTable.id, UserDeviceTable.userId))
)
}
}
@Serializable
data class UserDeviceDTO(@JvmField @Serializable(with = UUIDSerializer::class) val id: UUID) : EntityDTO<UUID> {
@Serializable(with = UUIDSerializer::class)
var userId: UUID? = null
var sourceType: PrincipalSourceType? = null
var enabled: Boolean? = null
var pushToken: String? = null
var osVersion: String? = null
var userAgent: String? = null
@Serializable(with = InstantSerializer::class)
var enabledAt: Instant? = null
override fun getId(): UUID = id
companion object {
val mapper: ResultRowDTOMapper<UserDeviceDTO> = ResultRowDTOMapper(UserDeviceDTO::class, UserDeviceTable)
}
}
考量需要為很多資料庫資料表實作 REST QueryDSL API，所以我自定義 dynamicQuery route function 取代原始的 http get，隱藏實作細節，就可以減少 route 部分需要撰寫的程式碼
authorize(ClubAuth.Admin) {
dynamicQuery<UserDTO>(ClubOpenApi.FindUsers) { dynamicQuery ->
call.respond(dynamicQuery.queryDB<UserDTO>())
}
}
@ContextDsl
inline fun <reified RESPONSE : EntityDTO<*>> Route.dynamicQuery(
operation: OpenApiOperation,
noinline body: suspend PipelineContext<Unit, ApplicationCall>.(DynamicQuery) -> Unit
): Route {
operation.bindRoute(
this, null, HttpMethod.Get,
typeOf<Unit>(), typeOf<RESPONSE>(), DynamicQueryLocation::class
)
return locationGet<DynamicQueryLocation> {
it.validate()
body(this, DynamicQuery.from(it))
}
}
實作上是使用 Ktor Locations Plugin，先把 querystring 轉換為 DynamicQueryLocation 物件，然後再轉為 DynamicQuery 物件。因為我不限制只能透過 querystring 指定 QueryDSL，我也想透過 POST requestBody 的 DynamicQueryForm 物件，甚至是只要傳入 DSL 字串即可。例如 [Day 28] 實作 Multi-Channel Notifications 文章中的 Ops 子專案實作後台匯出 Excel 報表寄送 Email 範例，就是在 request body 的 query 欄位撰寫 QueryDSL
{
"dataType": "OpsUser",
"email": "admin@test.abc.com",
"query": "q_fields=account,name&q_filter=[role = AppTeam and enabled = true]&q_orderBy=createdAt"
}
查詢 SQL => SELECT ops_user.id, ops_user.account, ops_user."name" FROM ops_user WHERE (ops_user."role" = 'AppTeam') AND (ops_user.enabled = true) ORDER BY ops_user.created_at ASC
@Serializable
class DynamicQuery(
val fields: List<String>? = null,
val filter: Predicate? = null,
val orderByList: List<OrderBy>? = null,
val offsetLimit: OffsetLimit? = null,
val count: Boolean? = false,
private val paramMap: MutableMap<String, String>? = null
) {
fun from(form: DynamicQueryForm): DynamicQuery {...}
fun from(request: ApplicationRequest): DynamicQuery{...}
fun from(text: String): DynamicQuery{...}
}
@io.ktor.locations.Location("")
data class DynamicQueryLocation(
val q_fields: String? = null,
val q_filter: String? = null,
val q_orderBy: String? = null,
val q_offset: Long? = null,
val q_limit: Int? = null,
val q_pageIndex: Long? = null,
val q_itemsPerPage: Int? = null,
val q_count: Boolean? = null
) : Location()
class DynamicQueryForm(
val fields: List<String>? = null,
val filter: String? = null,
val orderBy: String? = null,
val offset: Long? = null,
val limit: Int? = null,
val pageIndex: Long? = null,
val itemsPerPage: Int? = null,
val count: Boolean?,
val paramMap: MutableMap<String, String>? = null
) : Form<DynamicQueryForm>()
雖然我現在底層是查詢 RMDB 資料庫，但 QueryDSL 的概念也可以套用在其它資料庫，說不定以後會需要查詢 MongoDB。所以實作上，DynamicQuery 我是放在 infra.base.query package，然後在 infra.database.util package 實作 DynamicDBQuery，透過 kotlin extension function 在 DynamicQuery 類別增加 queryDB() 方法，讓兩邊的程式碼完全切開
inline fun <reified T : EntityDTO<*>> DynamicQuery.queryDB(): ResponseDTO {
return transaction {
if (offsetLimit != null && offsetLimit.isPaging) {
val dbCountQuery = toDBCountQuery<T>()
val total = dbCountQuery.count()
val items = if (total > 0) {
val dbQuery = toDBQuery<T>()
dbQuery.toList<T>()
} else listOf()
PagingDataResponseDTO.dtoList(offsetLimit, total, items)
} else {
if (count == true) {
val dbCountQuery = toDBCountQuery<T>()
val total = dbCountQuery.count()
DataResponseDTO(JsonObject(mapOf("total" to JsonPrimitive(total))))
} else {
val dbQuery = toDBQuery<T>()
DataResponseDTO(dbQuery.toList<T>())
}
}
}
}
這部分的實作內容過於細節且複雜，所以就不再此解說，有興趣的讀者可以到 Github 看完整程式碼
GraphQL 的確功能非常強大，可以應付多樣複雜的查詢需求，但是我沒有使用是因為
iThome鐵人賽
看影片追技術