Efficiently Transforming JDBC Query Results to JSON

3/6/2021 Updated for HTTP-RPC 8.1

A lot of enterprise data is stored in relational databases and accessed via SQL queries. Many web services are little more than HTTP-based wrappers around such queries.

Unfortunately, transforming query results to JSON so it can be consumed by a client application often involves numerous inefficient steps, such as binding each row to a data object and loading the entire data set into memory before serializing it back to the caller. This type of approach has a negative impact on performance and scalabilty. Each row requires multiple heap allocations and constructor invocations, increasing latency and CPU load. Worse, the caller does not receive a response until the entire data set has been processed.

Further, since each response is loaded entirely into memory, high-volume applications require a large amount of RAM, and can only scale through the addition of more physical hardware. Eventually, the garbage collector has to run, slowing down the entire system.

A much more efficient approach is to stream response data. Instead of copying the query results into an in-memory data structure before sending the response, the web service can write a row of data to the output stream each time a row is read from the result set. This allows a client to begin receiving the data as soon as it is available, significantly reducing latency. Also, because no intermediate data structures are created, CPU and memory load is reduced, allowing each server to handle a higher number of concurrent requests. Finally, because fewer heap allocations are required, the garbage collector needs to run much less frequently, resulting in fewer system pauses.

Introducing HTTP-RPC

HTTP-RPC is an open-source framework for implementing REST services in Java. It is extremely lightweight and requires only a Java runtime environment and a servlet container. The entire framework is distributed as a single JAR file that is about 100KB in size, making it an ideal choice for applications where a minimal footprint is desired.


HTTP-RPC’s WebService type provides an abstract base class for REST-based web services. It extends the similarly abstract HttpServlet class provided by the servlet API.

Service operations are defined by adding public methods to a concrete service implementation. Methods are invoked by submitting an HTTP request for a path associated with a servlet instance. Arguments are provided either via the query string or in the request body, like an HTML form. WebService converts the request parameters to the expected argument types, invokes the method, and writes the return value to the output stream as JSON.

The RequestMethod annotation is used to associate a service method with an HTTP verb such as GET or POST. The optional ResourcePath annotation can be used to associate the method with a specific path relative to the servlet. For example, the following class might be used to implement a web service that performs a simple addition operation:

public class MathService extends WebService {
    public double getSum(double a, double b) {
        return a + b;

The following request would cause the method to be invoked, and the service would return the value 6 in response:

GET /math/sum?a=2&b=4


The JSONEncoder class, which is used internally by WebService to serialize response data, converts return values to their JSON equivalents as follows:

  • CharSequence: string
  • Number: number
  • Boolean: true/false
  • Enum: string
  • java.util.Date: number representing epoch time in milliseconds
  • java.time.TemporalAccessor: string
  • java.net.URL: string
  • Iterable: array
  • java.util.Map: object

Note that collection types are not required to support random access; iterability is sufficient. This is an important feature, as it allows service implementations to stream result data rather than buffering it in memory before it is written.


HTTP-RPC’s ResultSetAdapter class implements the Iterable interface and makes each row in a JDBC result set appear as an instance of Map, allowing query results to be efficiently serialized as an array of JSON objects.

For example, consider a web service that returns the result of a SQL query on this table, taken from the MySQL sample database:

    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE

A method to retrieve a list of all pets belonging to a given owner might be implemented as shown below. Note that the example uses HTTP-RPC’s Parameters class to simplify query execution using named parameters rather than positional values. Also note that the method uses JSONEncoder to explicitly write the results to the output stream rather than simply returning the adapter instance, to ensure that the underlying result set is closed and system resources are not leaked (see this article for more information on QueryBuilder):

public void getPets(String owner, String format) throws SQLException, IOException {
    Parameters parameters = Parameters.parse(QueryBuilder.select("name", "species", "sex", "birth")
        .where("owner = :owner").toString());

    try (Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(parameters.getSQL())) {
        parameters.apply(statement, mapOf(
            entry("owner", owner)

        try (ResultSetAdapter resultSetAdapter = new ResultSetAdapter(statement.executeQuery())) {

            JSONEncoder jsonEncoder = new JSONEncoder();

            jsonEncoder.write(resultSetAdapter, getResponse().getOutputStream());

A response produced by the method might look something like this, where each object in the array represents a row from the result set:

    "name": "Claws",
    "species": "cat",
    "sex": "m",
    "birth": 763880400000
    "name": "Chirpy",
    "species": "bird",
    "sex": "f",
    "birth": 905486400000
    "name": "Whistler",
    "species": "bird",
    "sex": null,
    "birth": 881643600000

With just a few lines of code, query results can be quickly and efficiently returned to the caller, with no intermediate buffering required.

More Information

Complete source code for this example can be found here. For more information, see the project README.