Wrapping result sets in a stream

The JDBC ResultSet object plays very badly with Scala collections. The only real way of doing anything useful with it is to loop through it directly with a while loop. For instance, to get a list of the names of physicists in our database, we could write the following code:

// WARNING: poor Scala code
import Implicits._ // import implicit conversions

SqlUtils.usingConnection("test") { connection =>
  connection.withQuery("SELECT * FROM physicists") { resultSet =>
    var names = List.empty[String]
    while(resultSet.next) {
      val name = resultSet.getString("name")
      names = name :: names
    }
    names
  }
}
//=> List[String] = List(Paul Dirac, Albert Einstein, Marie Curie, Richard Feynman, Isaac Newton)

The ResultSet interface feels unnatural because it behaves very differently from Scala collections. In particular, it does not support the higher-order functions that we take for granted in Scala: no map, filter, fold, or for comprehensions...