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

Ext JS - Reading a JSON File

This post shows how you can read a JSON file in your Ext JS applications.

Let's say you have the following person.json file, which contains information about a person:

{
    "name" : "Joe Bloggs",
    "age"  : "15"
}

In order to load this file, you need to define an Ext.data.Store as shown below:

Ext.define('MyApp.store.Person', {
    extend: 'Ext.data.Store',
    fields: ['name', 'age'],
    autoLoad: true,
    proxy: {
        type: 'ajax',
        url: 'person.json',
        reader: 'json',
        pageParam: false,
        startParam: false,
        limitParam: false,
        noCache: false,
        listeners: {
            exception: function(proxy, response, operation) {
                // add appropriate error handling!
                console.log("Could not read file");
            }
        }
    },

    // return the specified field of the person e.g. name.
    get: function(field) {
        return this.first() && this.first().get(field);
    }
});

Then create the store and once it has loaded, you can access the data.

var store = Ext.create('MyApp.store.Person');
store.on('load', function() {
    console.log(store.get('name'));
});

Converting CSV to JSON in JavaScript

This post shows how you can convert a simple CSV file to JSON in JavaScript.

Consider the following sample CSV:

author,title,publishDate
Dan Simmons,Hyperion,1989
Douglas Adams,The Hitchhiker's Guide to the Galaxy,1979

The desired JSON output is:

[{"author":"Dan Simmons","title":"Hyperion","publishDate":"1989"},
{"author":"Douglas Adams","title":"The Hitchhiker's Guide to the Galaxy","publishDate":"1979"}]

The following JavaScript function transforms CSV into JSON. (Note that this implementation is quite naive and will not handle quoted fields containing commas!)

function toJson(csvData) {
  var lines = csvData.split("\n");
  var colNames = lines[0].split(",");
  var records=[];
  for(var i = 1; i < lines.length; i++) {
    var record = {};
    var bits = lines[i].split(",");
    for (var j = 0 ; j < bits.length ; j++) {
      record[colNames[j]] = bits[j];
    }
    records.push(record);
  }
  return records;
}

A simple test:

csv="author,title,publishDate\nDan Simmons,Hyperion,1989\nDouglas Adams,The Hitchhiker's Guide to the Galaxy,1979";
json = toJson(csv);
console.log(JSON.stringify(json));

To read a CSV file in JavaScript and convert it to JSON:

var rawFile = new XMLHttpRequest();
rawFile.open("GET", "books.csv", true);
rawFile.onreadystatechange = function () {
  if (rawFile.readyState === 4) {
    if (rawFile.status === 200 || rawFile.status == 0) {
      var allText = rawFile.responseText;
      var result = toJson(allText);
      console.log(JSON.stringify(result));
    }
  }
}
rawFile.send(null);

You might also like:
Converting XML to CSV using XSLT 1.0