CODEWITHSUNDEEP
scalajdbciterable
waterlooalex
waterlooalex

Reputation: 13912

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

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?

Upvotes: 14

Views: 7822

Answers (4)

chaotic3quilibrium
chaotic3quilibrium

Reputation: 5924

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
    })

Upvotes: 0

TimT
TimT

Reputation: 1684

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.

Upvotes: 7

Björn Jacobs
Björn Jacobs

Reputation: 4272

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.

Upvotes: 15

Rex Kerr
Rex Kerr

Reputation: 167901

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.

Upvotes: 13

Related Questions