Over a million developers have joined DZone.

Efficiently Transforming JDBC Query Results to JSON

DZone's Guide to

Efficiently Transforming JDBC Query Results to JSON

Let's face it: JSON is the dominating output format for most RESTful services. So what's an easy way to convert your SQL results to this popular format? Read on to find out how.

· Integration Zone
Free Resource

Migrating from On-Prem to Cloud Middleware? Here’s what Aberdeen Group says leading companies should be considering. Brought to you in partnershp with Liaison Technologies

NOTE 10/22/2016 Support for implementing REST services was dropped from the HTTP-RPC project in version 3.6. However, most of the features described in this article have been migrated to the JTemplate project.

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 simplifying development of REST applications. It allows developers to create and access web services using a convenient, RPC-like metaphor while preserving fundamental REST principles such as statelessness and uniform resource access. The project currently includes support for implementing REST services in Java and consuming services in Java, Objective-C/Swift, or JavaScript.

HTTP-RPC services are accessed by applying an HTTP verb such as GET or POST to a target resource. Arguments are provided either via the query string or in the request body, like an HTML form. Results are generally returned as JSON, although operations that do not return a value are also supported.

For example, the following request might retrieve the sum of two numbers, whose values are specified by the a and b query arguments:

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

The service would return the value 6 in response.

WebService Class

WebService is an abstract base class for HTTP-RPC web services. Service operations are defined by adding public methods to a concrete service implementation.

The @RPC annotation is used to flag a method as remotely accessible. This annotation associates an HTTP verb and a resource path with the method. All public annotated methods automatically become available for remote execution when the service is published.

For example, the following class might be used to implement the simple addition operation discussed in the previous section:

public class MathService extends WebService {
    @RPC(method="GET", path="sum")
    public double getSum(double a, double b) {
        return a + b;

Arguments may be any numeric or boolean type,  String ,  java.net.URL , or  java.util.List.  URL arguments represent binary content and can only be used with POST requests. List arguments represent multi-value parameters. They may be used with any request type, but elements must be a supported simple type; e.g. List<Double> or List<URL>.

Methods may return any numeric or boolean type, CharSequence, java.util.List or java.util.Map. Results are mapped to their JSON equivalents as follows:

  • numeric primitive/ Number: number
  • boolean/Boolean: true/false
  • CharSequence: string
  • java.util.List: array
  • java.util.Map: object

Methods may also return void to indicate that they do not produce a value.

List and Map types are not required to support random access; iterability is sufficient. This allows service implementations to stream collection data rather than buffering it in memory before it is written. Additionally, collection types that implement the AutoCloseable interface will be automatically closed after their contents have been written to the output stream, ensuring that system resources are not leaked.

ResultSetAdapter Class

The ResultSetAdapter class allows the result of a SQL query to be efficiently returned from a service method. This class implements the List interface and makes each row in a JDBC result set appear as an instance of Map, rendering the data suitable for serialization to JSON. It also implements AutoCloseable, to ensure that the underlying result set is closed once all of the response data has been written.

Further, ResultSetAdapter is forward-scrolling only; its contents are not accessible via the get() and size() methods. This allows query results to be returned to the caller directly, without any intermediate buffering.

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

    productCode VARCHAR(50) NOT NULL,
    productName VARCHAR(70) NOT NULL,
    productLine VARCHAR(50) NOT NULL,
    productScale VARCHAR(10) NOT NULL,
    productVendor VARCHAR(50) NOT NULL,
    productDescription TEXT NOT NULL,
    quantityInStock SMALLINT NOT NULL,
    buyPrice DOUBLE NOT NULL,
    PRIMARY KEY (productCode)

The service can simply execute the query, pass the result set to the adapter’s constructor, and return the adapter instance:

@RPC(method="GET", path="products")
public ResultSetAdapter getProducts() throws SQLException {
    Statement statement = getConnection().createStatement();

    return new ResultSetAdapter(statement.executeQuery("SELECT * FROM Products"));

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

    "productCode": "S10_1678",
    "productName": "1969 Harley Davidson Ultimate Chopper",
    "productLine": "Motorcycles",
    "productScale": "1:10",
    "productVendor": "Min Lin Diecast",
    "productDescription": "This replica features working kickstand...",
    "quantityInStock": 7932,
    "buyPrice": 48.81,
    "MSRP": 95.7

With just a few lines of code, the query results are quickly and efficiently returned to the caller, with the framework ensuring that the underlying database resources are properly disposed of once the response has been sent.

More Information

This article introduced the HTTP-RPC framework and provided an example of how the ResultSetAdapter class can be used to efficiently transform JDBC query results into JSON.

The latest version of HTTP-RPC can be downloaded here. For more information, see the project README.

Is iPaaS solving the right problems? Not knowing the fundamental difference between iPaaS and iPaaS+ could cost you down the road. Brought to you in partnership with Liaison Technologies.

web services ,data ,query ,services ,jdbc ,results ,json ,convert ,transformation ,java

Published at DZone with permission of Greg Brown, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}