Filling a Scala immutable Map from a database table

Question

I have a SQL database table with the following structure:

create table category_value (
  category varchar(25),
  property varchar(25)
);

I want to read this into a Scala Map[String, Set[String]] where each entry in the map is a set of all of the property values that are in the same category. I would like to do it in a "functional" style with no mutable data (other than the database result set).

Following on the Clojure loop construct, here is what I have come up with:

def fillMap(statement: java.sql.Statement): Map[String, Set[String]] = {
    val resultSet = statement.executeQuery("select category, property from category_value")

    @tailrec
    def loop(m: Map[String, Set[String]]): Map[String, Set[String]] = {
      if (resultSet.next) {
        val category = resultSet.getString("category")
        val property = resultSet.getString("property")
        loop(m + (category -> m.getOrElse(category, Set.empty)))
      } else m
    }

    loop(Map.empty)
}

Is there a better way to do this, without using mutable data structures?

Answer 1

If you like, you could try something around

def fillMap(statement: java.sql.Statement): Map[String, Set[String]] = {
  val resultSet = statement.executeQuery("select category, property from category_value")
  Iterator.continually((resultSet, resultSet.next)).takeWhile(_._2).map(_._1).map{ res =>
    val category = res.getString("category")
    val property = res.getString("property")
    (category, property)
  }.toIterable.groupBy(_._1).mapValues(_.map(_._2).toSet)
}

Untested, because I don’t have a proper sql.Statement. And the groupBy part might need some more love to look nice.

Edit: Added the requested changes.