Spring Boot中如何使查询结果以JSON返回并兼容OpenAPI 3.0

问题分析 #

当前代码存在三个核心问题：

1. SQL执行逻辑缺失，未调用正确的查询方法获取结果
2. 直接拼接表名/ schema存在SQL注入风险
3. 返回类型List<Map<String, Object>>不符合直接返回JSON的需求，也不利于OpenAPI文档生成

重构方案 #

以下是完善后的实现，兼顾安全性、功能完整性和OpenAPI兼容性：

1. Service层方法重构

@Override
public String getTableAsJson(String tableName, String schemaName) {
    // 先校验表和schema的合法性，阻断SQL注入风险
    validateTableAndSchemaExists(tableName, schemaName);
    
    // 构造安全的SQL语句，用引号包裹标识符避免关键字冲突
    final String sql = String.format(
        "SELECT coalesce(array_to_json(array_agg(t)), '[]'::json) FROM %s.%s t",
        quoteIdentifier(schemaName),
        quoteIdentifier(tableName)
    );
    
    Query query = em.createNativeQuery(sql);
    // 直接获取单个JSON字符串结果（array_to_json返回单值）
    return (String) query.getSingleResult();
}

// 校验表和schema是否存在于数据库中
private void validateTableAndSchemaExists(String tableName, String schemaName) {
    try {
        DatabaseMetaData metaData = em.getEntityManagerFactory()
                                      .getDataSource()
                                      .getConnection()
                                      .getMetaData();
        
        // 检查schema存在性
        try (ResultSet schemaRs = metaData.getSchemas(null, schemaName)) {
            if (!schemaRs.next()) {
                throw new IllegalArgumentException("Schema " + schemaName + "不存在");
            }
        }
        
        // 检查表是否属于目标schema
        try (ResultSet tableRs = metaData.getTables(null, schemaName, tableName, new String[]{"TABLE"})) {
            if (!tableRs.next()) {
                throw new IllegalArgumentException("表" + tableName + "不存在于schema" + schemaName + "中");
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException("校验表和schema失败", e);
    }
}

// 给PostgreSQL标识符添加双引号，处理特殊字符和关键字
private String quoteIdentifier(String identifier) {
    return "\"" + identifier.replace("\"", "\"\"") + "\"";
}

2. Controller层适配OpenAPI 3.0

@GetMapping("/table-json")
@ApiResponse(
    responseCode = "200",
    description = "返回指定表的JSON格式数据",
    content = @Content(mediaType = MediaType.APPLICATION_JSON_VALUE)
)
public ResponseEntity<String> getTableJson(
    @RequestParam String tableName,
    @RequestParam String schemaName
) {
    String jsonData = yourService.getTableAsJson(tableName, schemaName);
    return ResponseEntity.ok()
            .contentType(MediaType.APPLICATION_JSON)
            .body(jsonData);
}

关键说明 #

• SQL注入防护：通过数据库元数据校验表和schema的真实存在性，同时用双引号包裹标识符，避免恶意输入构造注入语句
• 空数据处理：使用coalesce函数，当表为空时返回空数组[]，符合JSON规范
• OpenAPI兼容性：Controller层明确指定响应的mediaType为application/json，结合@ApiResponse注解，OpenAPI 3.0能正确识别返回类型为JSON结构
• 结果直接性：直接返回数据库生成的JSON字符串，避免二次序列化开销，同时保证输出结果与PG Admin中执行的SQL结果完全一致

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