I have a small application written in scala that sends a request to mysql, receives the result, then convert it to json and sends to some http server. I use java jdbc and mysql connector to connect to a database and spray-json for scala collection to json conversion. So, I create a connection to db, execute a query and then get a result with getResultSet(). Then I iterate through it, and copy result to a mutable map:
while(result.next()) {
val SomeExtractor(one, two) = result
map.update(one, map.getOrElse(one, List()) ::: List(two))
}
This works fine, but then I have to convert the result to immutable map, cause spray-json can't convert mutable collections to json, AFAIK. Is there a good way to convert the jdbc result here to an immutable collection without coping it to temporary mutable map? Maybe it is possible to do using streams somehow? I'm asking cause it looks like there must be some cool functional pattern to do it, that I have no idea about.
p.s. By the way, I can't just use Slick, cause it doesn't support stored procedures, AFAIK.
Short answer: You can't do significantly better than what you've got. Under the hood of Scala's functional cleverness is code that looks a lot like yours. Also, don't forget that mutable Maps have a toMap method that returns an immutable Map.
Long Answer: You're looking to make JDBC code interface with Scala code. JDBC's API is not designed for use from functional languages, so you'll definitely need some mutable/imperative code to help bridge the gap. It's really just a question of the path of least resistance.
If you were simply building a one-to-one map, you'd be well served by a MapBuilder. Scala includes Builder classes for most of its data structures, which use temporary, private, mutable structures to build an immutable structure as efficiently as possible. The code would look something like:
val builder = Map.newBuilder[Int, Int]
while(result.next()) {
val SomeExtractor(one, two) = result
builder += one -> two
}
return builder.result
However, you're really building a MultiMap - a map from keys to multiple values. Scala does have a MultiMap trait in its standard library, but it's not ideal for your use case. It's mutable, and stores values in mutable Sets rather than Lists, so we'll ignore it for now.
Scala's standard library does have a groupBy method on its Traversable trait, which does more-or-less what you're looking for. We've got a ResultSet rather than a Traversable, but in principle we could write some glue code to wrap the ResultSet in a Traversable, and take advantage of this existing code. Something like the following:
// strm has side effects, caused by rs.next - only ever call it once, and re-use result if needed.
def strm: Stream[(Int, Int)] = if (rs.next) SomeExtractor.unapply(rs).get #:: strm else Stream.empty
return strm.groupBy(_._1)
This will work, but we've got a scary warning about side effects, and we haven't actually gained any performance. If you look at the source code for Traversable.groupBy (see code on GitHub), it's actually doing much the same thing you are - building a mutable Map with our data, and then converting it to an immutable Map at the end.
I think the approach you've already got is close to optimal - just return map.toMap.
Oh, and I've assumed that SomeExtractor extracts a pair of Ints.