Treating an SQL ResultSet like a Scala Stream

I didn't test it, but why wouldn't it work?

new Iterator[String] {
  def hasNext = resultSet.next()
  def next() = resultSet.getString(1)
}.toStream

i needed something similar. Building on elbowich's very cool answer, I wrapped it a bit, and instead of the string, I return the result (so you can get any column)

def resultSetItr(resultSet: ResultSet): Stream[ResultSet] = {
    new Iterator[ResultSet] {
      def hasNext = resultSet.next()
      def next() = resultSet
    }.toStream
  }

I needed to access table metadata, but this will work for table rows (could do a stmt.executeQuery(sql) instead of md.getColumns):

 val md = connection.getMetaData()
 val columnItr = resultSetItr( md.getColumns(null, null, "MyTable", null))
      val columns = columnItr.map(col => {
        val columnType = col.getString("TYPE_NAME")
        val columnName = col.getString("COLUMN_NAME")
        val columnSize = col.getString("COLUMN_SIZE")
        new Column(columnName, columnType, columnSize.toInt, false)
      })

Utility function for @elbowich's answer:

def results[T](resultSet: ResultSet)(f: ResultSet => T) = {
  new Iterator[T] {
    def hasNext = resultSet.next()
    def next() = f(resultSet)
  }
}

Allows you to use type inference. E.g.:

stmt.execute("SELECT mystr, myint FROM mytable")

// Example 1:
val it = results(stmt.resultSet) {
  case rs => rs.getString(1) -> 100 * rs.getInt(2)
}
val m = it.toMap // Map[String, Int]

// Example 2:
val it = results(stmt.resultSet)(_.getString(1))

This sounds like a great opportunity for an implicit class. First define the implicit class somewhere:

import java.sql.ResultSet

object Implicits {

    implicit class ResultSetStream(resultSet: ResultSet) {

        def toStream: Stream[ResultSet] = {
            new Iterator[ResultSet] {
                def hasNext = resultSet.next()

                def next() = resultSet
            }.toStream
        }
    }
}

Next, simply import this implicit class wherever you have executed your query and defined the ResultSet object:

import com.company.Implicits._

Finally get the data out using the toStream method. For example, get all the ids as shown below:

val allIds = resultSet.toStream.map(result => result.getInt("id"))