# Realizing a SQL ResultSet into a Map in Scala

I am trying to realize a java.sql.ResultSet into a map, in Scala.

import java.sql.{ResultSet, ResultSetMetaData}

class DbRow extends java.util.HashMap[java.lang.String, Object] {
}

object freeFunctions {

def realize(queryResult: ResultSet): Vector[DbRow] = {
val colNames = for (i <- 1 to md.getColumnCount) yield md.getColumnName(i)
var rows: Vector[DbRow] = Vector.empty
while (queryResult.next()) {
val row = new DbRow
for (n <- colNames) {
row.put(n, queryResult.getObject(n))
}
rows = rows :+ row
}
rows
}

}


I feel this could (should) be less verbose. If while/yield comprehensions existed, or some comprehension for create a map. The client code needs a java map for now, but if an elegant enough solution can produce a Scala map, I could convert it.

I don't have the time to test this, so I hope you can do that for me. I decided not to use HashMap straight away because type conversion makes things a bit more complicated than they should, but let me know if this is important for you and causes you trouble.

Here's option 1:

def realize(queryResult: ResultSet): Vector[Map[String, Object]] = {
val colNames = for (i <- 1 to md.getColumnCount) yield md.getColumnName(i)
val buildMap = () => (for (n <- colNames) yield n -> queryResult.getObject(n)).toMap
Iterator.continually(queryResult.next()).takeWhile(identity).map(_ => buildMap()).toVector
}


I don't like it much because some parts of the code rely on side effects of other parts of the code, so even if it works, it's still ugly.

Here's option 2, which I like more:

def buildMap(queryResult: ResultSet, colNames: Seq[String]): Option[Map[String, Object]] =
if (queryResult.next())
Some(colNames.map(n => n -> queryResult.getObject(n)).toMap)
else
None

def realize(queryResult: ResultSet): Vector[Map[String, Object]] = {
val colNames = (1 to md.getColumnCount) map md.getColumnName
Iterator.continually(buildMap(queryResult, colNames)).takeWhile(!_.isEmpty).map(_.get).toVector
}


Nicer, IMHO, you can save some dots over there but I decided to keep them. Maybe something like this:

 (Iterator continually (buildMap(queryResult, colNames)) takeWhile (!_.isEmpty) map (_.get)).toVector


Again, I didn't test it, I don't know how to do it without a DB and I don't feel like setting one up now, sorry for that.

Finally, I'm not sure if you realized this, but "realize" is probably not the best name for this method (or for any method, unless you are programming some epistemic system, IMHO).

• Thanks, I'll give this a try when I can (I cannot at the moment). I used "realize" to mean convert from a lazy iterable to a concrete collection. I don't know how widely used the term is, but I have seen it used with Clojure's lazy-seq. – Gnurfos Dec 19 '14 at 15:17
• Your answer is good, but it could be even better if you'd more clearly outline the differences between each solution ;) – glampert Sep 29 '15 at 19:49

It does not seem useful since you will be stuck with Objects and you won't know what to do with them.

You should either use some ORM framework, or else write yourself some specific class that corresponds to the type of the entries in the DB. For example, if each entry has two integers and one string, define some class with those members. You can then define a function that takes a DB row and transforms it to your class. You won't have to look at the metadata since you know beforehand what is in the DB.

Since a ResultSet might not fit in memory (in a Vector), you should look at Scala's Stream which is a lazy collection. You can somewhat think of a Stream as a "while/yield comprehension".

• I agree and will probably write such specific class later, but for now I'm migrating some existing java code to scala. This code does not pretend (yet) to abstract the table structure (the caller is responsible for that). The result set is also "supposed" to be small (it will get displayed in a table without scrolling facilities, so laziness would not be kept for long at this point). – Gnurfos Oct 6 '14 at 8:33

You could potentially do some variation of the following:

    val rowCount =
if (queryResult.last()) queryRestult.getRow()
else // throw exception

queryResult.beforeFirst()

val rows =
for {
i <- rowCount
} yield {
queryResult.next()
val results = colNames map (n => queryResult.getObject(n))
Map(colNames zip results:_*)
}

• Interesting. But my result is of type TYPE_FORWARD_ONLY, and this gives me things like: java.sql.SQLException: Invalid operation for forward only resultset : last – Gnurfos Oct 6 '14 at 8:28