DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Efficiently Transforming JDBC Query Results to JSON

Efficiently Transforming JDBC Query Results to JSON

Check out this tutorial that shows you a process to significally reduce the latency between translating JDBC queries into JSON.

Greg Brown user avatar by
Greg Brown
·
Jul. 03, 18 · Tutorial
Like (9)
Save
Tweet
Share
11.54K Views

Join the DZone community and get the full member experience.

Join For Free

NOTE The  DispatcherServlet class mentioned below has been renamed to  WebService since this article was originally written.

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 scalability. 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 less than 30KB in size, making it an ideal choice for applications such as microservices where a minimal footprint is desired.

DispatcherServlet

HTTP-RPC's DispatcherServlet type provides an abstract base class for REST-based web services. 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. DispatcherServlet 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:


@WebServlet(urlPatterns={"/math/*"})
public class MathServlet extends DispatcherServlet {
    @RequestMethod("GET")
    @ResourcePath("/sum")
    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

JSONEncoder

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

  • CharSequence: string
  • Number: number
  • Boolean: true/false
  • 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.

ResultSetAdapter

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:


CREATE TABLE pet (
    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:


@RequestMethod("GET")
public void getPets(String owner) throws SQLException, IOException {
    try (Connection connection = DriverManager.getConnection(DB_URL)) {
        Parameters parameters = Parameters.parse("SELECT name, species, sex, birth FROM pet WHERE owner = :owner");

        parameters.put("owner", owner);

        try (PreparedStatement statement = connection.prepareStatement(parameters.getSQL())) {
            parameters.apply(statement);

            try (ResultSet resultSet = statement.executeQuery()) {
                JSONEncoder jsonEncoder = new JSONEncoder();

                jsonEncoder.writeValue(new ResultSetAdapter(resultSet), getResponse().getOutputStream());
            }
        }
    } finally {
        getResponse().flushBuffer();
    }
}

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

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. For more information, see the project README.

Database JSON Relational database

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

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • SAST: How Code Analysis Tools Look for Security Flaws
  • Cloud Native London Meetup: 3 Pitfalls Everyone Should Avoid With Cloud Data
  • Playwright vs. Cypress: The King Is Dead, Long Live the King?
  • The Data Leakage Nightmare in AI

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: