Core Java

Java: Streaming a JDBC ResultSet as JSON

This post shows how you can convert a java.sql.ResultSet to JSON and stream it back to the caller. This is useful if you want to send a large dataset from a JDBC DataSource to a web application in JSON format. Streaming allows you to transfer the data, little by little, without having to load it all into the server’s memory.

For example, consider the following ResultSet:

+---------+-----+
| name    | age |
+---------+-----+
| Alice   |  20 |
| Bob     |  35 |
| Charles |  50 |
+---------+-----+

The corresponding JSON is:

[
  { "name": "Alice",   "age": 20 },
  { "name": "Bob",     "age": 35 },
  { "name": "Charles", "age": 50 },
]

The following class (also available in my GitHub Repository) can be used to convert the ResultSet to JSON. Note that this class implements Spring’s ResultSetExtractor, which can be used by a JdbcTemplate to extract results from a ResultSet.

/**
 * Streams a ResultSet as JSON.
 */
public class StreamingJsonResultSetExtractor implements ResultSetExtractor<Void> {

  private final OutputStream os;

  /**
   * @param os the OutputStream to stream the JSON to
   */
  public StreamingJsonResultSetExtractor(final OutputStream os) {
    this.os = os;
  }

  @Override
  public Void extractData(final ResultSet rs) {
    final var objectMapper = new ObjectMapper();
    try (var jg = objectMapper.getFactory().createGenerator(
                  os, JsonEncoding.UTF8)) {
      writeResultSetToJson(rs, jg);
      jg.flush();
    } catch (IOException | SQLException e) {
      throw new RuntimeException(e);
    }
    return null;
  }

  private static void writeResultSetToJson(final ResultSet rs,
                            final JsonGenerator jg)
                            throws SQLException, IOException {
    final var rsmd = rs.getMetaData();
    final var columnCount = rsmd.getColumnCount();
    jg.writeStartArray();
    while (rs.next()) {
      jg.writeStartObject();
      for (var i = 1; i <= columnCount; i++) {
        jg.writeObjectField(rsmd.getColumnName(i), rs.getObject(i));
      }
      jg.writeEndObject();
    }
    jg.writeEndArray();
  }
}

To use this in a web service with JAX-RS:

import javax.ws.rs.core.StreamingOutput;

@GET
@Path("runQuery")
public StreamingOutput runQuery() {
  return new StreamingOutput() {
    @Override
    public void write(final OutputStream os)
        throws IOException, WebApplicationException {
      jdbcTemplate.query("select name, age from person",
                   new StreamingJsonResultSetExtractor(os));
    }
  };
}
Published on Java Code Geeks with permission by Fahd Shariff, partner at our JCG program. See the original article here: Java: Streaming a JDBC ResultSet as JSON

Opinions expressed by Java Code Geeks contributors are their own.

Fahd Shariff

Fahd is a software engineer working in the financial services industry. He is passionate about technology and specializes in Java application development in distributed environments.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Kassie Militello
Kassie Militello
2 years ago

This was super helpful! Do you know if there is an equivalent for XML Generation?

Back to top button