Jdbi and Streaming

About half a year has this post been in my draft stash. Finally I found some time and motivation to finish it. And here it is! :)

The streaming of data block by block from your web service can have significant advantages in comparision to collecting all the data on the server side and then sending it to the client.

1. less memory consumption
2. faster processing time

Less Memory Consumption

In most cases when it comes to web services the data will be collected on the server and then sent to the client. And this is totally ok and works pretty well as in most cases the data transfered from and to web services is not so big.

But when it comes to transfering several hundreds of MB in one request it may not be so efficient and may even kill the server because of consuming all the available memory … ever seen the OutOfMemoryError? ;-)

But what if you just process one entity at a time and send that chunk of data to the client? Memory consumption wouldn’t be any problem at all.

Having a solution for the server is great. But you also have to take care of the client side (if it is in your hands) so that you don’t just push the memory consumption problem from the server to the client. You need to process the data on the client also block by block.

Faster Processing Time

When streaming the data from the server to the client without having to first collect the whole data on the server side the client gets the first block of data much faster and thus can process the data earlier. Thus the whole processing time is shorter.

Jakarta JAX-RS Streaming Support

JAX-RS supports the streaming of data via the interface jakarta.ws.rs.core.StreamingOutput which can be returned as the response entity.

return Response.ok(myStreamingOutput).build();

Jdbi Streaming Support

Jdbi supports the streaming of data directly from an SQL query out-of-the-box in the Core API and also in the SQL Objects module with the interface org.jdbi.v3.core.result.ResultIterable. This interface can be used instead as a return type just like java.util.List.

The interface ResultIterable comes with multiple methods to work with the data. Just keep in mind that almost all these methods work similar as in java.util.stream.Stream as they are almost all terminal operations. With executing these terminal operations you will load and process all data from your query and cannot process the data again with the same ResultIterable object.

From the Jdbi docs:

Almost all operations on the ResultIterable interface are terminal operations. When they finish, they close and release all resources, especially the ResultSet and PreparedStatement objects.

DAO

The DAO interface (using Jdbi SQL Objects) just returns a ResultIterable instead of a list.

@SqlQuery("SELECT ... FROM ... WHERE ...")
ResultIterable<Location> iterate();

Data Service

You can use the method stream() to get a java.util.stream.Stream object from the ResultIterable. After processing the data from the stream all the allocated resources should be freed but the Handle instance is still open. You need to pass the handle to the StreamingOutput instance so that you can call close() on the Handle instance after the processing. One way of returning both the stream and the handle is by using the Pair interface of the Apache Commons Lang project.

Pair.of(resultIterable.stream(), handle);

All this can be encapsulated into a service class which is then used by the web service resource class.

JAX-RS + Jdbi

Both parts (JAX-RS and Jdbi) support the streaming of data. Now we just have to bring those two together. We need to implement StreamingOutput for this and have to have access to the stream and the Jdbi Handle object.

This implementation will output a stream of objects to the client. The objects are deserialized by using an ObjectMapper instance from the Jackson Data Bind library. It also lets you limit the number of objects you want to return to the client by passing the size parameter to the constructor.

The stream and the Jdbi handle will be passed to the implementation by using the Pair interface from the Apache Commons Lang project.

The Jdbi handle will be closed after processing all the objects in the stream.

public class JdbiStreamingOutput<T> implements StreamingOutput {

    private ObjectMapper mapper;
    private Pair<Stream<T>, Handle> streamHandle;
    private Long size;

    public JdbiStreamingOutput(Pair<Stream<T>, Handle> streamHandle, ObjectMapper mapper) {
        this.streamHandle = streamHandle;
        this.mapper = mapper;
    }

    public JdbiStreamingOutput(Pair<Stream<T>, Handle> streamHandle, ObjectMapper mapper, Long size) {
        this.streamHandle = streamHandle;
        this.mapper = mapper;
        this.size = (size != null && size < 0) ? 0 : size;
    }

    @Override
    public void write(OutputStream out) throws IOException, WebApplicationException {
        AtomicInteger i = new AtomicInteger();
        byte[] comma = ",".getBytes(StandardCharsets.UTF_8);

        out.write("[".getBytes(StandardCharsets.UTF_8));

        try {
            Stream<T> stream = streamHandle.getLeft().sequential();
            if (size != null) stream = stream.limit(size);

            stream.forEach(c -> {
                try {
                    if (i.get() > 0) out.write(comma);
                    mapper.writeValue(out, c);

                    i.incrementAndGet();
                } catch (Exception e) {
                    throw new RuntimeException("Object deserialization failed.", e);
                }
            });
        } catch (Exception e) {
            throw new RuntimeException("Error on streaming data at element " + (i.get()+1), e);
        } finally {
            if (!streamHandle.getRight().isClosed()) streamHandle.getRight().close();
        }

        out.write("]".getBytes(StandardCharsets.UTF_8));

        out.flush();
    }
}

Web Service Resource

In the web service resource class a JdbiStreamingOutput instance is returned as the resposne entity … and that’s it :).

public Response stream() {
    Pair<Stream<Location>, Handle> result = locationService.stream();
    return Response.ok(new JdbiStreamingOutput<Location>(result, mapper)).build();
}

An instance of ObjectMapper is passed for deserialization to the constructor.

Final Thoughts and Hints

Of course there are some limitation to this simple implementation. F. e. you can only return an array of objects (which is mostly the case when you stream JSON data ;-) ).

It is also tied to the libraries Jdbi and Jackson Data Bind. But this is intended and pretty easy to abstract away if needed.

Hint

@UseRowReducer cannot be used in this case in the Jdbi DAO interface as it results in Jdbi collecting all the data on the server before streaming it and thus … no streaming. But there is a solution which will be presented in another post.

Wrap Up

This post demonstrates how to stream data from the database to the client using JAX-RS and Jdbi. There will be a follow up post about the other side … the client side.

Happy streaming!

Mihael