Streaming MySQL Results Using Java 8 Streams

The article is inspired by the posts here and here.

There is a RESTful service as the infrastructure for data access in our team. It is based on Jersey/JAX-RS and runs fast. However, it consumes large memory when constructing large data set as response. Since it builds the entire response in memory before sending it.

As suggested in the above posts. Streaming is the solution. They integrated Hibernate or Spring Data for easy adoption. But I need a general purpose RESTful service, say, I do not know the schema of a table. So I decided to implement it myself using raw JDBC interface.

My class is so-called MysqlStreamTemplate:

  • It does not extend JdbcTemplate, since there is only one interface for streaming, not one series. I’m not writing a general purpose library.
  • It is MySQL only, I have no time to verify with other relation databases.
  • It does accept a DataSource as the parameter of the its constructor.
  • Staff like Hibernate session is not concerned, since it maintains Statement & Connection by itself.
  • Staff like @Transcational is not concerned, since we do not care about transactions. Actually, MySQL gives HOLD_CURSORS_OVER_COMMIT in StatementImpl#getResultSetHoldability() in its JDBC driver, saying that our ResultSet survives after commit.

So, here is my class. NOTE: closing our Statement & Connection requires explicit invoke of Stream#close():

Read inline comments for additional details. Now the response entry and controller mapping:

Complete code can be find on my GitHub repository.

My simple benchmark script looks like:

Dramatic improvements in memory usage as shown in jconsole, especially Old Gen:

Some raw data from jmap:

  • Jersey
  • Spring Boot
  • Spring Boot with Streams

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">