Ktor接收URL参数作为WHERE条件查询动态表返回关联JSON

在Ktor中根据用户输入创建了带外键的动态表，需要实现以下需求：

• 接收URL参数（如api/account?key=value）作为WHERE条件查询该表
• 返回包含外键关联所有数据的嵌套JSON结果
• 保证查询高效，避免全量获取数据后在Ktor中处理（数据量超1万条且外键关联多，此方式耗时过长）

已尝试的方案及问题：

• 使用Raw SQL方案仅适用于按ID查询的场景，不支持动态WHERE条件
• 全量获取后在应用层处理关联数据，性能极差

动态表生成代码 #

fun createTable(tableName: String = "account", columns: List<Pair<String, String>>): UIntIdTable {
    return object : UIntIdTable(tableName) {
        val userId = varchar("user_id", 255).nullable().uniqueIndex("user_id")
        val userPassword = varchar("user_password", 255).nullable()
        val team = (uinteger("team") references TeamTable.id)
        
        init {
            columns.forEach { (name, type) ->
                when (type.lowercase()) {
                    "string" -> varchar(name, 255)
                    "int" -> integer(name)
                    "long" -> long(name)
                    "double" -> double(name)
                    "float" -> float(name)
                    "boolean" -> bool(name)
                    "uinteger" -> uinteger(name)
                    else -> throw IllegalArgumentException("Unsupported column type: $type")
                }
            }
        }
    }
}

// create table using Exposed
SchemaUtils.create(createTable("account", emptyList()))

目标返回JSON格式 #

[
    {
        "id": 1,
        "userId": "abc",
        "userPassword": "password",
        "team": {
            "id": 23,
            "name": "green"
        }
    }
]

待补充实现的路由代码 #

fun Application.configureAccountRouting(database: Database, project: String) {
    routing {
        route("${project}/api") {
            get("account") {
                val whereCondition = call.request.queryParameters

                val result = transaction(database) {
                    // TODO return json from db
                    exec("")
                }
                
                call.respond(result)
            }
        }
    }
}

注：由于表是动态创建的，无法使用kotlinx.serialization（要求精确声明所有列）

核心思路 #

利用PostgreSQL原生JSON函数在数据库层直接生成嵌套JSON，同时动态构建参数化WHERE条件，既保证查询效率，又支持动态过滤需求。

完整实现代码 #

import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.Database
import io.ktor.server.application.*
import io.ktor.server.response.*
import io.ktor.server.routing.*
import io.ktor.server.request.*
import org.postgresql.util.PGobject
import java.util.regex.Pattern

fun Application.configureAccountRouting(database: Database, project: String) {
    routing {
        route("${project}/api") {
            get("account") {
                val queryParams = call.request.queryParameters
                val tableName = "account" // 若需支持多动态表，可改为URL参数传入

                val result = transaction(database) {
                    // 1. 构建安全的WHERE子句与参数
                    val whereClauses = mutableListOf<String>()
                    val params = mutableListOf<Any>()
                    val camelToUnderscore = Pattern.compile("([a-z])([A-Z])")
                    
                    queryParams.forEach { (key, value) ->
                        // 将URL参数的驼峰命名转换为数据库下划线命名（如userId → user_id）
                        val dbColumn = camelToUnderscore.matcher(key).replaceAll("$1_$2").lowercase()
                        whereClauses.add("$dbColumn = ?")
                        params.add(value)
                    }
                    val wherePart = if (whereClauses.isNotEmpty()) "WHERE ${whereClauses.joinToString(" AND ")}" else ""

                    // 2. 编写关联查询并生成嵌套JSON的SQL
                    val sql = """
                        SELECT json_agg(
                            json_build_object(
                                'id', a.id,
                                'userId', a.user_id,
                                'userPassword', a.user_password,
                                'team', json_build_object(
                                    'id', t.id,
                                    'name', t.name
                                )
                            )
                        ) AS result
                        FROM $tableName a
                        LEFT JOIN team t ON a.team = t.id
                        $wherePart
                    """.trimIndent()

                    // 3. 执行查询并提取JSON结果
                    exec(sql, params) { rs ->
                        if (rs.next()) {
                            (rs.getObject("result") as PGobject).value
                        } else {
                            "[]"
                        }
                    }
                }

                // 4. 返回JSON响应
                call.respondText(result ?: "[]", contentType = io.ktor.http.ContentType.Application.Json)
            }
        }
    }
}

关键细节说明 #

• 性能优化：所有关联逻辑和JSON序列化都在数据库完成，避免应用层加载海量数据，大幅降低内存占用与处理时间。
• SQL注入防护：使用参数化查询，所有用户输入都作为参数传入，而非直接拼接SQL字符串。
• 动态适配：自动处理URL参数的命名转换，若需支持更多动态列，可通过查询information_schema.columns获取表结构，动态生成json_build_object的字段列表。
• 外键扩展：若有其他外键关联，只需在json_build_object中添加对应的关联表查询（如'department', json_build_object('id', d.id, 'name', d.name)）即可。

注意事项 #

• 确保使用的PostgreSQL版本≥9.4（支持json_build_object函数）。
• 若动态表的结构完全不确定，可通过查询数据库元信息动态生成SQL中的字段部分，实现完全通用的动态表查询。

内容的提问来源于stack exchange，提问作者Gureumi