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?

Ingvar

Answer 1

Try extending Iterator instead. I haven't tested it, but something like this:

def findAll: Iterator[MyObject] = new Iterator[MyObject] {
  val rs = getRs
  override def hasNext = rs.hasNext
  override def next = new valueFromResultSet(rs.next)
}

This should store rs when it's called, and otherwise just be a light wrapper to calls to rs.

If you want to save the values that you traverse, check out Stream.

Teak

I just assumed that rs actually has a "hasNext" method. If not, you should cache the next result with a (probably private) var inside the iterator and have hasNext say whether that cached result exists. – Teak

Yeah, that works, I used hasNext = rs.isLast. One trouble is though I don't have any mechanism to close the rs and connection. In my current code I have wrapped the (above) code in a "using" method which closes things for me. – Ingvar

If "isLast" means that you can't get another one, I'd think hasNext = !rs.isLast would be the thing to do. Maybe just a typo? – Teak

@Rex oops, yeah I meant hasNext = !rs.isLast – Ingvar

Answer 2

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.

Lambart

I used this solution. Thank you muchly! I did have to call toList to use the results. val externalKeys = resultSet.map { x => x.getString("external_key") }.toList – Blackguard

There is an issue with this. As per specification for hasNext it should execute without advancing the resultset. Hence either caching as suggested by @Rex Kerr or using isLast (not sure about performance) is a better choice there. – Avraham

Answer 3

A simpler (idiomatic) way to achieve the same would be

Iterator.continually((rs.next(), rs)).takeWhile(_._1).map(r => valueFromResultSet(r._2)).toList

You need the .toList to force evaluation, otherwise the underlying collection will be a stream and the ResultSet may be closed before evaluation has taken place.

Branny

Answer 4

I devised a solution in Scala 2.13 by enhancing ResultSet with an implicit class containing a .map() method.

implicit class ResultSetOps(resultSet: ResultSet) {
  def iterator: Iterator[ResultSet] =
    Iterator
      .continually(resultSet)
      .takeWhile(_.next())

  def map[T](toT: ResultSet => T): Iterator[T] =
    iterator.map(toT)
}

To be used like this for smaller quantities of rows:

val ts =
  resultSet
    .map(resultSet => {
      val marketId = resultSet.getString("market_id")
      println(s"marketId=$marketId")

      marketId
    })
    .toList

It is critically important to remember the .toList at the end as that causes the Iterator to traverse the ResultSet.

---

For larger quantities of rows where it is undesirable to use up the memory, the alternative is to do the work with a .foreach(), like this:

val ts =
  resultSet
    .iterator
    .foreach(resultSet => {
      val marketId = resultSet.getString("market_id")
      println(s"marketId=$marketId")
      
      //perform and (side-effect) persist the row here
    })

Maher