5
\$\begingroup\$

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 md = queryResult.getMetaData
    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.

\$\endgroup\$

4 Answers 4

5
\$\begingroup\$

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 md = queryResult.getMetaData
    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 md = queryResult.getMetaData
    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).

\$\endgroup\$
2
  • \$\begingroup\$ 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. \$\endgroup\$
    – Gnurfos
    Dec 19, 2014 at 15:17
  • 1
    \$\begingroup\$ Your answer is good, but it could be even better if you'd more clearly outline the differences between each solution ;) \$\endgroup\$
    – glampert
    Sep 29, 2015 at 19:49
1
\$\begingroup\$

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".

\$\endgroup\$
1
  • \$\begingroup\$ 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). \$\endgroup\$
    – Gnurfos
    Oct 6, 2014 at 8:33
1
\$\begingroup\$

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:_*)
      }
\$\endgroup\$
1
  • \$\begingroup\$ 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 \$\endgroup\$
    – Gnurfos
    Oct 6, 2014 at 8:28
-1
\$\begingroup\$

e.g. to get a map out of a resultset column ...

new Iterator[String] {
  def hasNext: Boolean = rs.next()
  def next(): String = rs.getString("xyz")
}.toMap
\$\endgroup\$
1
  • \$\begingroup\$ Why the downvote? \$\endgroup\$ May 30, 2020 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.