如何将数据库查询结果封装为JSON:从基础到实践的完整指南
在现代软件开发中,JSON(JavaScript Object Notation)已成为数据交换的主流格式,因其轻量、易读、易解析的特性,被广泛应用于前后端交互、API接口、缓存存储等场景,而数据库作为系统的数据核心,如何将查询结果高效、规范地封装为JSON,是开发者必须的技能,本文将从基础概念出发,结合不同开发语言的实践方案,详细讲解数据库数据封装为JSON的完整流程与最佳实践。
理解数据库数据与JSON的映射关系
在封装之前,需先明确数据库数据类型与JSON数据类型的对应关系,这是确保数据转换准确性的基础:
| 数据库数据类型 | JSON数据类型 | 说明 |
|---|---|---|
| INT/BIGINT/SERIAL | Number | 整数类型直接转为JSON数字 |
| DECIMAL/DOUBLE/FLOAT | Number | 浮点数类型转为JSON数字(注意精度问题) |
| VARCHAR/CHAR/TEXT | String | 字符串类型转为JSON字符串(需处理特殊字符,如引号、换行符) |
| BOOLEAN | Boolean (true/false) | 布尔类型直接映射 |
| DATE/DATETIME/TIMESTAMP | String | 日期时间类型转为ISO 8601格式字符串(如"2023-10-01T12:00:00Z") |
| NULL | null | 数据库NULL值转为JSON的null |
| JSON/JSONB | Object/Array | 原生JSON字段可直接嵌入JSON对象或数组 |
| BLOB/BINARY | Base64 String | 二进制数据需转为Base64编码字符串 |
理解这种映射关系后,才能避免数据转换过程中的类型错误或信息丢失。
封装JSON的核心步骤
无论使用何种开发语言或框架,将数据库数据封装为JSON的核心步骤可归纳为以下四步:
执行数据库查询,获取原始数据
通过SQL语句从数据库中查询目标数据,获取结果集(ResultSet),结果集可能是单条记录、多条记录,甚至是关联查询的复杂结果。
遍历结果集,逐条转换数据结构
遍历结果集中的每一行数据,将每行记录的列值按照上述映射关系,转换为JSON支持的键值对(Key-Value Pair),键名通常使用数据库列名(或通过别名指定),值为对应的JSON类型数据。
构建JSON层次结构(可选)
若数据存在关联关系(如一对多、多对多),需在JSON中体现层次结构,用户表与订单表关联,可封装为用户对象包含订单数组的形式。
序列化为JSON字符串
将构建好的数据结构(如对象、数组)通过JSON序列化工具转换为JSON字符串,便于传输或存储。
不同开发语言的实践方案
(一)Java:使用JDBC + 手动/库序列化
基础方案(手动封装)
通过JDBC获取ResultSet后,手动遍历并构建JSON对象。
import java.sql.*;
import org.json.JSONArray;
import org.json.JSONObject;
public class DatabaseToJson {
public static JSONArray convertResultSetToJson(ResultSet rs) throws SQLException {
JSONArray jsonArray = new JSONArray();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
JSONObject jsonObject = new JSONObject();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
Object value = rs.getObject(i);
// 处理NULL值
jsonObject.put(columnName, value != null ? value : JSONObject.NULL);
}
jsonArray.put(jsonObject);
}
return jsonArray;
}
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id, name, age, create_time FROM users")) {
JSONArray jsonResult = convertResultSetToJson(rs);
System.out.println(jsonResult.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
}
优化方案(使用Jackson/Gson)
手动封装代码冗余,可通过JSON库简化流程,以Jackson为例:
import com.fasterxml.jackson.databind.ObjectMapper;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DatabaseToJsonWithJackson {
public static List<Map<String, Object>> convertResultSetToList(ResultSet rs) throws SQLException {
List<Map<String, Object>> list = new ArrayList<>();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
Map<String, Object> row = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
row.put(columnName, rs.getObject(i));
}
list.add(row);
}
return list;
}
public static void main(String[] args) {
// ... 同上获取ResultSet ...
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id, name, age FROM users")) {
ObjectMapper objectMapper = new ObjectMapper();
List<Map<String, Object>> resultList = convertResultSetToList(rs);
String jsonString = objectMapper.writeValueAsString(resultList);
System.out.println(jsonString);
} catch (Exception e) {
e.printStackTrace();
}
}
}
优点:代码简洁,支持复杂对象序列化,Jackson可直接将POJO转为JSON。
(二)Python:使用sqlite3/psycopg2 + json库
Python内置json库,且数据库驱动通常支持直接转换为字典,封装非常便捷。
示例(SQLite)
import sqlite3
import json
def convert_to_json(db_path, query):
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
cursor.execute(query)
# 获取列名
columns = [description[0] for description in cursor.description]
results = []
for row in cursor.fetchall():
# 将行数据与列名组合为字典
row_dict = dict(zip(columns, row))
results.append(row_dict)
conn.close()
return json.dumps(results, ensure_ascii=False, indent=2)
# 使用示例
db_path = "test.db"
query = "SELECT id, name, age, create_time FROM users"
json_result = convert_to_json(db_path, query)
print(json_result)
示例(PostgreSQL + psycopg2)
import psycopg2
import json
def postgres_to_json(host, database, user, password, query):
conn = psycopg2.connect(host=host, database=database, user=user, password=password)
cursor = conn.cursor()
cursor.execute(query)
# 使用cursor.fetchall()直接返回字典列表(需设置row_factory)
cursor.row_factory = psycopg2.extras.RealDictCursor
results = cursor.fetchall()
conn.close()
return json.dumps([dict(row) for row in results], ensure_ascii=False)
# 使用示例
json_result = postgres_to_json(
host="localhost",
database="test",
user="postgres",
password="password",
query="SELECT id, name, age FROM users"
)
print(json_result)
优点:Python字典与JSON天然契合,代码量少,json.dumps()支持自定义格式(如日期处理)。
(三)Node.js:使用mysql2/promise + JSON.stringify
Node.js中,数据库查询通常返回Promise,可直接通过JSON.stringify()转换。
示例(MySQL)
const mysql = require('mysql2/promise');
async function getJsonFromDb() {
const connection = await mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password',
database: 'test'
});
const [rows, fields] = await connection.execute('SELECT id, name, age FROM users');
await connection.end();
// 直接转换为JSON字符串
const jsonString = JSON.stringify(rows, null, 2);
console.log(jsonString);
return rows; // 返回对象数组,后续可手动处理
}
getJsonFromDb().catch(console.error);
处理日期时间(自定义序列化)
若数据库返回的Date对象需转为字符串,可通过replacer参数处理:
const jsonString = JSON.stringify(rows, (key, value) => {
if (value instanceof Date) {
return value.toISOString(); // 转为ISO格式字符串
}
return value;
}, 2);
优点:异步支持友好,可直接使用原生JSON方法,适合RESTful API开发。
(四)C#:使用Dapper + Newtonsoft.Json
C#中,



还没有评论,来说两句吧...