Efficiently Producing and Consuming CSV in Java Using HTTP-RPC

In a previous article, I discussed how the open-source HTTP-RPC framework can be used to efficiently transform JDBC query results into JSON. However, while JSON is an extremely common and well-supported data format, it may, in some cases, be preferable to return a CSV document instead. Because field keys are specified only at the beginning of a CSV document rather than being duplicated for every record, CSV generally requires less bandwidth than JSON. Additionally, consumers can begin processing CSV as soon as the first record arrives, rather than waiting for the entire document to download.

The previous article demonstrated how HTTP-RPC's ResultSetAdapter and JSONEncoder classes could be used to easily generate a JSON response from 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
);

The following service method returns the same results encoded as CSV. The only difference in this version is the use of HTTP-RPC's CSVEncoder class instead of JSONEncoder:

@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()) {
                CSVEncoder csvEncoder = new CSVEncoder();

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

The response might look something like this, where each record in the document represents a row from the result set (date values are represented using epoch time):

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

Consuming a CSV Response

On the other end, the HTTP-RPC's CSVDecoder class can be used to efficiently process the contents of a CSV document. Rather than reading the entire payload into memory and returning the data as a random-access list of map values, CSVDecoder returns a cursor over the records in the document. This allows a client to read records as they are being produced, reducing memory consumption and improving throughput.

For example, the following client code could be used to consume the response generated by the web service:

WebServiceProxy webServiceProxy = new WebServiceProxy("GET", new URL("http://localhost:8080/httprpc-test/pets"));

webServiceProxy.getArguments().put("owner", "Gwen");
webServiceProxy.getArguments().put("format", "csv");

webServiceProxy.invoke((inputStream, contentType) -> {
    CSVDecoder csvDecoder = new CSVDecoder();

    CSVDecoder.Cursor pets = csvDecoder.readValues(inputStream);

    for (Map<String, String> pet : pets) {
        System.out.println(String.format("%s is a %s", pet.get("name"), pet.get("species")));
    }

    return null;
});

This code produces the following output:

Claws is a cat
Chirpy is a bird
Whistler is a bird

Additionally, the adapt() method of the CSVDecoder.Cursor class can be used to facilitate typed iteration of CSV data. This method produces an Iterable sequence of values of a given type representing the rows in the document. The returned adapter uses dynamic proxy invocation to map properties declared by the interface to map values in the cursor. A single proxy instance is used for all rows to minimize heap allocation.

For example, the following interface might be used to model the pet records shown above:

public interface Pet {
    public String getName();
    public String getOwner();
    public String getSpecies();
    public String getSex();
    public Date getBirth();
}

This code uses adapt() to create an iterable sequence of Pet values from the CSV response:

CSVDecoder csvDecoder = new CSVDecoder();

CSVDecoder.Cursor pets = csvDecoder.readValues(inputStream);

for (Pet pet : pets.adapt(Pet.class)) {
    System.out.println(String.format("%s is a %s", pet.getName(), pet.getSpecies()));
}

The output is identical to the previous example; however, in this case, the fields of each record are retrieved in a type-safe manner (a feature whose value becomes much more obvious when handling CSV documents containing numeric or boolean values).

Final Thoughts

This article provided an example of how HTTP-RPC's CSVEncoder and CSVDecoder classes can be used to efficiently stream and process JDBC query results as CSV. For more information, see the project README.