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));
    }
  };
}

Java: Mocking a ResultSet using Mockito

This post shows how you can mock a java.sql.ResultSet using Mockito. It can be used to help unit test code which performs operations on ResultSets (such as a ResultSetExtractor) without relying on an external datasource.

You can create a MockResultSet by providing a list of column names and a 2D array of data. For example:

var rs = MockResultSet.create(
           new String[] { "name", "age" }, //columns
           new Object[][] { // data
             { "Alice", 20 },
             { "Bob", 35 },
             { "Charles", 50 }
           });

The code for MockResultSet is shown below (also available in my GitHub Repository). Note that I have only mocked a few methods such as next, getString and getObject but it is quite easy to mock the rest by following the same pattern.

public class MockResultSet {

  private final Map<String, Integer> columnIndices;
  private final Object[][] data;
  private int rowIndex;

  private MockResultSet(final String[] columnNames,
                        final Object[][] data) {
    // create a map of column name to column index
    this.columnIndices = IntStream.range(0, columnNames.length)
        .boxed()
        .collect(Collectors.toMap(
            k -> columnNames[k],
            Function.identity(),
            (a, b) ->
              { throw new RuntimeException("Duplicate column " + a); },
            LinkedHashMap::new
            ));
    this.data = data;
    this.rowIndex = -1;
  }

  private ResultSet buildMock() throws SQLException {
    final var rs = mock(ResultSet.class);

    // mock rs.next()
    doAnswer(invocation -> {
      rowIndex++;
      return rowIndex < data.length;
    }).when(rs).next();

    // mock rs.getString(columnName)
    doAnswer(invocation -> {
      final var columnName = invocation.getArgumentAt(0, String.class);
      final var columnIndex = columnIndices.get(columnName);
      return (String) data[rowIndex][columnIndex];
    }).when(rs).getString(anyString());

    // mock rs.getObject(columnIndex)
    doAnswer(invocation -> {
      final var index = invocation.getArgumentAt(0, Integer.class);
      return data[rowIndex][index - 1];
    }).when(rs).getObject(anyInt());

    final var rsmd = mock(ResultSetMetaData.class);

    // mock rsmd.getColumnCount()
    doReturn(columnIndices.size()).when(rsmd).getColumnCount();

    // mock rs.getMetaData()
    doReturn(rsmd).when(rs).getMetaData();

    return rs;
  }

  /**
   * Creates the mock ResultSet.
   *
   * @param columnNames the names of the columns
   * @param data
   * @return a mocked ResultSet
   * @throws SQLException
   */
  public static ResultSet create(
                         final String[] columnNames,
                         final Object[][] data)
                         throws SQLException {
    return new MockResultSet(columnNames, data).buildMock();
  }
}