1. Overview

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.  

In this tutorial, we’ll see multiple ways to convert a JDBC ResultSet object to the JSON format.

2. Code Example

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:

Username,Id,First name,Last name
doe1,7173,John,Doe
smith3,3722,Dana,Smith
john22,5490,John,Wang

The line of code to form the ResultSet looks like this:

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:

<dependency>
    <groupId>org.json</groupId>
    <artifactId>json</artifactId>
    <version>20240303</version>
</dependency>

3. Using No External Dependencies

The JDBC API predates modern Java collection frameworks.  Therefore, we cannot use the likes of for-each iteration and Stream methods.

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.

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:

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. 

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. 

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:

[
   {
      "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

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:

<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:

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:

{
   "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

In case we don’t like the default structure of the JSON object produced by jOOQ, there is room to customize it.

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.

We then feed the RecordMapper as input to the map() method of the jOOQ result class:

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.

The resulting JSON looks like this, similar to Section 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

In this article, we explored three different ways to convert a JDBC ResultSet to a JSON object.

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.

As always, the source code for the examples is available over on GitHub.

Course – LSD (cat=Persistence)

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

>> CHECK OUT THE COURSE
res – Persistence (eBook) (cat=Persistence)
2 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Comments are closed on this article!