Filling Scala immutable map from database table

Question

I have an SQL database table with the following structure:

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

I want to read this in Scala Map[String, Set[String]] , where each record on the map is a set of all property values ​​that are in the same category. I would like to do this in a “functional” style without any modified data (except for the database result set).

Following the Clojure loop construct, here is what I came 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 can 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) } 

Not indexed because I do not have proper sql.Statement . And parts of groupBy might need even more love to look beautiful.

Edit : The requested changes have been added.

Answer 2

There are two parts to this problem.

Retrieving data from a database and a list of strings.

I would use Spring SimpleJdbcOperations to access the database, so that things at least seem functional, although the ResultSet is changing behind the scenes.

Firstly, some simple conversion that allows us to use closure to display each row:

 implicit def rowMapper[T<:AnyRef](func: (ResultSet)=>T) = new ParameterizedRowMapper[T]{ override def mapRow(rs:ResultSet, row:Int):T = func(rs) } 

Then we define the data structure for storing the results. (You can use a tuple, but defining my own case class has the advantage of being a little more understandable with respect to the names of things.)

 case class CategoryValue(category:String, property:String) 

Now select from the database

 val db:SimpleJdbcOperations = //get this somehow val resultList:java.util.List[CategoryValue] = db.query("select category, property from category_value", { rs:ResultSet => CategoryValue(rs.getString(1),rs.getString(2)) } ) 

Convert data from a list of rows to the desired format

 import scala.collection.JavaConversions._ val result:Map[String,Set[String]] = resultList.groupBy(_.category).mapValues(_.map(_.property).toSet) 

(You can omit type annotations. I have included them to make it clear what is happening.)

Answer 3

Builders are built for this purpose. Get one through your desired collection type companion, for example. HashMap.newBuilder[String, Set[String]] .

Answer 4

This solution is basically the same as my other solution, but it does not use Spring, and the logic for converting ResultSet to some list is simpler than Debilski's solution.

 def streamFromResultSet[T](rs:ResultSet)(func: ResultSet => T):Stream[T] = { if (rs.next()) func(rs) #:: streamFromResultSet(rs)(func) else rs.close() Stream.empty } def fillMap(statement:java.sql.Statement):Map[String,Set[String]] = { case class CategoryValue(category:String, property:String) val resultSet = statement.executeQuery(""" select category, property from category_value """) val queryResult = streamFromResultSet(resultSet){rs => CategoryValue(rs.getString(1),rs.getString(2)) } queryResult.groupBy(_.category).mapValues(_.map(_.property).toSet) } 

Answer 5

There is only one approach that I can think of that does not include either mutable state or extensive copying *. This is actually a very simple method, which I learned in my first term of studying CS. Here is the abstraction from the database:

 def empty[K,V](k : K) : Option[V] = None def add[K,V](m : K => Option[V])(k : K, v : V) : K => Option[V] = q => { if ( k == q ) { Some(v) } else { m(q) } } def build[K,V](input : TraversableOnce[(K,V)]) : K => Option[V] = { input.foldLeft(empty[K,V]_)((m,i) => add(m)(i._1, i._2)) } 

Usage example:

 val map = build(List(("a",1),("b",2))) println("a " + map("a")) println("b " + map("b")) println("c " + map("c")) > a Some(1) > b Some(2) > c None 

Of course, the resulting function is not of type Map (and none of its advantages) and has linear search costs. I assume that you can implement something in the same way that mimics simple search trees.

(*) I am talking about concepts here. In fact, things like exchanging valuable data can include, for example, variable list designs without memory overhead.