1. Overview
1.概述
In some scenarios, we might need to send the result of a database query via an API call to another system or a messaging platform. For such cases, we often use JSON as the data exchange format.
在某些情况下，我们可能需要通过API调用将数据库查询的结果发送到另一个系统或一个消息平台。对于这种情况，我们经常使用JSON作为数据交换格式。
In this tutorial, we’ll see multiple ways to convert a JDBC ResultSet object to the JSON format.
在本教程中，我们将看到将JDBCResultSet对象转换成JSON格式的多种方法。
2. Code Example
2.代码示例
We’ll use the H2 database for our code example. We have a sample CSV file, which we’ve read into a table words using JDBC. Here are three lines from the sample CSV file, with the first line being the header:
我们将使用H2数据库作为我们的代码示例。我们有一个样本CSV文件，我们使用JDBC将其读入一个表words。下面是样本CSV文件的三行，第一行是标题。
Username,Id,First name,Last name
doe1,7173,John,Doe
smith3,3722,Dana,Smith
john22,5490,John,WangThe line of code to form the ResultSet looks like this:
形成ResultSet的这行代码看起来像这样。
ResultSet resultSet = stmt.executeQuery("SELECT * FROM words");For JSON processing, we use the JSON-Java (org.json) library. First, we add its corresponding dependency to our POM file:
对于JSON处理，我们使用JSON-Java（org.json）库。首先，我们将其相应的依赖性添加到我们的POM文件中。
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20220320</version>
</dependency>
3. Using No External Dependencies
3.使用无外部依赖性
The JDBC API predates modern Java collection frameworks. Therefore, we cannot use the likes of for-each iteration and Stream methods.
JDBC API早于现代Java集合框架。 因此，我们不能使用类似for-each迭代和Stream方法。
Instead, we have to rely on iterators. Moreover, we need to extract the number and list of column names from the metadata of the ResultSet.
相反，我们必须依赖迭代器。此外，我们需要从ResultSet的元数据中提取列名的数量和列表。
This leads to a basic loop consisting of forming a JSON object per row, adding objects to a List, and finally converting that List to a JSON array. All these functionalities are available in the org.json package:
这导致了一个基本的循环，包括每行形成一个JSON对象，将对象添加到List，最后将该List转换为JSON数组。所有这些功能都可以在org.json包中找到。
ResultSetMetaData md = resultSet.getMetaData();
int numCols = md.getColumnCount();
List<String> colNames = IntStream.range(0, numCols)
.mapToObj(i -> {
try {
return md.getColumnName(i + 1);
} catch (SQLException e) {
e.printStackTrace();
return "?";
}
})
.collect(Collectors.toList());
JSONArray result = new JSONArray();
while (resultSet.next()) {
JSONObject row = new JSONObject();
colNames.forEach(cn -> {
try {
row.put(cn, resultSet.getObject(cn));
} catch (JSONException | SQLException e) {
e.printStackTrace();
}
});
result.add(row);
}Here, we first run a loop to extract the name of each column. We later use these column names in forming the resulting JSON object.
在这里，我们首先运行一个循环，提取每一列的名称。之后我们使用这些列名来形成结果的JSON对象。
In the second loop, we go through the actual results and convert each one to a JSON object, using the column names we computed in the previous step. We then add all these objects to a JSON array.
在第二个循环中，我们通过实际的结果，并将每个结果转换成JSON对象，使用我们在前一步计算的列名。然后我们将所有这些对象添加到一个JSON数组中。
We have left the extraction of column names and column count out of the loop. This helps in making the execution faster.
我们把提取列名和列数的工作留在了循环中。这有助于提高执行速度。
The resulting JSON looks like this:
产生的JSON看起来像这样。
[
{
"Username":"doe1",
"First name":"John",
"Id":"7173",
"Last name":"Doe"
},
{
"Username":"smith3",
"First name":"Dana",
"Id":"3722",
"Last name":"Smith"
},
{
"Username":"john22",
"First name":"John",
"Id":"5490",
"Last name":"Wang"
}
]4. Using jOOQ with Default Settings
4.使用默认设置的jOOQ
The jOOQ framework (Java Object Oriented Querying) provides, among other things, a set of convenient utility functions to work with JDBC and ResultSet objects. First, we need to add the jOOQ dependency to our POM file:
jOOQ框架（Java Object Oriented Querying）除其他外，还提供了一组方便的实用函数，以处理JDBC和ResultSet对象。首先，我们需要将jOOQ依赖项添加到我们的POM文件中。
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
<version>3.11.11</version>
</dependency>After adding the dependency, we can actually use a single-line solution for converting a ResultSet to a JSON object:
在添加了依赖关系后，我们实际上可以使用单行方案将ResultSet转换为一个JSON对象。
JSONObject result = new JSONObject(DSL.using(dbConnection)
.fetch(resultSet)
.formatJSON());The resulting JSON element is an object consisting of two fields called fields and records, where fields have the names and types of the columns, and records contain the actual data. This is slightly different from the previous JSON object and looks like this for our example table:
产生的JSON元素是一个由两个字段组成的对象，称为fields和records，其中fields具有列的名称和类型，而records包含实际数据。这与之前的JSON对象略有不同，对于我们的示例表，看起来是这样的。
{
"records":[
[
"doe1",
"7173",
"John",
"Doe"
],
[
"smith3",
"3722",
"Dana",
"Smith"
],
[
"john22",
"5490",
"John",
"Wang"
]
],
"fields":[
{
"schema":"PUBLIC",
"name":"Username",
"type":"VARCHAR",
"table":"WORDS"
},
{
"schema":"PUBLIC",
"name":"Id",
"type":"VARCHAR",
"table":"WORDS"
},
{
"schema":"PUBLIC",
"name":"First name",
"type":"VARCHAR",
"table":"WORDS"
},
{
"schema":"PUBLIC",
"name":"Last name",
"type":"VARCHAR",
"table":"WORDS"
}
]
}5. Using jOOQ with Customized Settings
5.使用自定义设置的jOOQ
In case we don’t like the default structure of the JSON object produced by jOOQ, there is room to customize it.
如果我们不喜欢jOOQ产生的JSON对象的默认结构，那么就有空间来定制它。
We’ll do this by implementing the RecordMapper interface. This interface has a map() method that receives a Record as input and returns the desired object of an arbitrary type.
我们将通过实现RecordMapper接口来做到这一点。这个接口有一个map()方法，它接收一个Record作为输入并返回任意类型的所需对象。
We then feed the RecordMapper as input to the map() method of the jOOQ result class:
然后，我们将RecordMapper作为输入送入jOOQ结果类的map()方法。
List json = DSL.using(dbConnection)
.fetch(resultSet)
.map(new RecordMapper() {
@Override
public JSONObject map(Record r) {
JSONObject obj = new JSONObject();
colNames.forEach(cn -> obj.put(cn, r.get(cn)));
return obj;
}
});
return new JSONArray(json);
Here, we returned a JSONObject from the map() method.
在这里，我们从map()方法中返回一个JSONObject。
The resulting JSON looks like this, similar to Section 3:
产生的JSON看起来像这样，与第3节类似。
[
{
"Username":"doe1",
"First name":"John",
"Id":"7173",
"Last name":"Doe"
},
{
"Username":"smith3",
"First name":"Dana",
"Id":"3722",
"Last name":"Smith"
},
{
"Username":"john22",
"First name":"John",
"Id":"5490",
"Last name":"Wang"
}
]6. Conclusion
6.结语
In this article, we explored three different ways to convert a JDBC ResultSet to a JSON object.
在这篇文章中，我们探讨了将JDBC ResultSet转换为JSON对象的三种不同方法。
Each approach can have its own uses. What we choose depends on the required structure of the output JSON object and possible limitations on the dependency size, for example.
每种方法都可以有自己的用途。我们选择什么取决于输出的JSON对象的所需结构以及对依赖性大小的可能限制，例如。
As always, the source code for the examples is available over on GitHub.
一如既往，例子的源代码可在GitHub上获得。。