How can I stream/chunk database results asynchronously?

playframework, playframework-2.2, scala

Solution

Firstly, you can't do it "asynchronously", because the JDBC API is synchronous - when you call `rs.next()`, it will block synchronously. But that's ok, just need to make sure your thread pools are tuned to allow blocking operations (ie, make them large). Play has an asynchronous streaming API (which is quite different to the synchronous InputStream/OutputStream that you're most likely used to), that uses things called iteratees/enumerators. You want to create an enumerator that enumerates your ResultSet. Essentially, you want to do something that looks like this:

import play.api.libs.iteratee._

val conn = DB.getConnection()
val stmt = conn.createStatement
val resultSet = stmt.executeQuery("""select * from atable""")

Ok.stream(Enumerator.unfold(resultSet) { (rs: ResultSet) =>
  if (rs.next()) {
    val chunk = // Read the result from the ResultSet and format it in the way you want it formatted
    Some((rs, chunk))
  } else None
}.onDoneEnumerating {
  resultSet.close()
  stmt.close()
  conn.close()
})

Problem

I'd like to use an `Enumerator` (similar to the ScalaStream example) to chunk database results to the response as TSV. I may have many thousands of rows and i don't want to page the results nor do i want to accumulate the entire `ResultSet` into a single `String`. If needed, it is ok for chunks to not break at line separators. In other words, the only criteria is to chunk the results, not that it adhere to TSV delimiters. I'd like to do something like this in my Action: ``` Ok.stream(new Iterator[ResultSet] { val conn = DB.getConnection() val stmt = conn.createStatement val rs = stmt.executeQuery("""select * from atable""") def hasNext = !rs.isLast() && !rs.isClosed() def next() = { if (!rs.next()) { conn.close() null } else rs } }.toStream) ``` Unfortunately, the `Ok.stream` expects a `java.io.InputStream` and going the route of wrapping the results in an `InputStream` seems excessive. `Ok.stream` also accepts an `Enumerator` but i'm not sure how to create one in this context.

Original source