Scala: Exposing a JDBC ResultSet through a generator (iterable)

Question

I've got a set of rows in a database, and I'd like to provide an interface to spin through them like this:

def findAll: Iterable[MyObject]

Where we don't require having all the instances in memory at once. In C# you can easily create generators like this using yield, the compiler takes care of converting code that loops through the recordset into an iterator (sort of inverting it).

My current code looks like this:

def findAll: List[MyObject] = {
  val rs = getRs
  val values = new ListBuffer[MyObject]
  while ( rs.next() ) 
    values += new valueFromResultSet(rs)
  values.toList
}

Is there a way I could convert this to not store the entire set in memory? Perhaps I could use a for comprehension?

Answer 1

I came across the same problem and based on the ideas above I created the following solution by simply writing an adapter class:

class RsIterator(rs: ResultSet) extends Iterator[ResultSet] {
    def hasNext: Boolean = rs.next()
    def next(): ResultSet = rs
}

With this you can e.g. perform map operations on the result set - which was my personal intention:

val x = new RsIterator(resultSet).map(x => {
    (x.getString("column1"), x.getInt("column2"))
})

Append a .toList in order to force evaluation. This is useful if the database connection is closed before you use the values. Otherwise you will get an error saying that you cannot access the ResultSet after the connection was closed.