Efficiently Transforming JDBC Query Results to JSON

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.

For example, the following service method (built using the open-source Kilo framework) retrieves all rows from an employees table and returns the results to the caller as JSON:

@RequestMethod("GET")
public List getEmployees() throws SQLException {
    var queryBuilder = QueryBuilder.select(Employee.class);

    try (var connection = getConnection();
        var statement = queryBuilder.prepare(connection);
        var results = queryBuilder.executeQuery(statement)) {
        return results.stream().map(result -> BeanAdapter.coerce(result, Employee.class)).toList();
    }
}

Unfortunately, this solution, while straightforward, is not terribly efficient. The entire data set must be traversed twice – once to read it from the database, and again to write it to the output stream. Additionally, the caller will not begin receiving a response until all of the rows have been read, and none of the elements will become eligible for garbage collection until all of the results have been written.

For small result sets, the latency and memory implications associated with this approach might be acceptable. However, for larger data volumes the following alternative may be preferable. The query is executed on a background thread, and the transformed results are streamed back to the caller via a pipe:

@RequestMethod("GET")
@ResourcePath("stream")
public Iterable getEmployeesStream() {
    var pipe = new Pipe(4096, 15000);

    executorService.submit(() -> {
        var queryBuilder = QueryBuilder.select(Employee.class);

        try (var connection = getConnection();
            var statement = queryBuilder.prepare(connection);
            var results = queryBuilder.executeQuery(statement)) {
            pipe.accept(results.stream().map(result -> BeanAdapter.coerce(result, Employee.class)));
        } catch (SQLException exception) {
            throw new RuntimeException(exception);
        }
    });

    return pipe;
}

From the Kilo documentation:

The Pipe class provides a vehicle by which a producer thread can submit a sequence of elements for retrieval by a consumer thread. It implements the Iterable interface and returns values as they become available, blocking if necessary.

The pipe in this example is configured with a capacity of 4K elements and a timeout of 15s. Limiting the capacity ensures that the producer does not do more work than necessary if the consumer fails to retrieve all of the data. Similarly, specifying a timeout ensures that the consumer does not wait indefinitely if the producer stops submitting data.

This implementation of the method is slightly more verbose than the first one. However, because no intermediate buffering is required, results are available to the caller sooner, and CPU and memory load is reduced.

Note that while the examples above use Kilo’s QueryBuilder and ResultSetAdapter classes to process results, the same approach can be used with an ORM framework such as Hibernate:

@RequestMethod("GET")
@ResourcePath("hibernate")
public List getEmployeesHibernate() throws SQLException {
    var configuration = new Configuration();

    configuration.addAnnotatedClass(HibernateEmployee.class);

    try (var connection = getConnection();
        var sessionFactory = configuration.configure().buildSessionFactory();
        var session = sessionFactory.withOptions().connection(connection).openSession()) {
        var criteriaQuery = session.getCriteriaBuilder().createQuery(Employee.class);
        var query = session.createQuery(criteriaQuery.select(criteriaQuery.from(HibernateEmployee.class)));

        return query.list();
    }
}

@RequestMethod("GET")
@ResourcePath("hibernate-stream")
public Iterable getEmployeesHibernateStream() {
    var pipe = new Pipe(4096, 15000);

    executorService.submit(() -> {
        var configuration = new Configuration();

        configuration.addAnnotatedClass(HibernateEmployee.class);

        try (var connection = getConnection();
            var sessionFactory = configuration.configure().buildSessionFactory();
            var session = sessionFactory.withOptions().connection(connection).openSession()) {
            var criteriaQuery = session.getCriteriaBuilder().createQuery(Employee.class);
            var query = session.createQuery(criteriaQuery.select(criteriaQuery.from(HibernateEmployee.class)));

            try (var stream = query.stream()) {
                pipe.accept(stream);
            }
        } catch (SQLException exception) {
            throw new RuntimeException(exception);
        }
    });

    return pipe;
}

The performance of the Hibernate versions is comparable to the preceding examples, although they do result in slightly higher memory consumption and GC activity. Hibernate also requires approximately 18MB of external dependencies, compared to the 150KB required by Kilo.

The complete source code for this example is available here. An earlier version of this article can be found here.