Wednesday, July 8, 2009

Using Scala with groovy.sql.Sql

Short example of Scala using Groovy's Sql class. I'm new to Scala so I don't know if this is idiomatic. I used asInstanceOf to fully specify the type of the iterator.

import groovy.sql.Sql

val db = Sql.newInstance("jdbc:oracle:thin:@myhost:1521:mysid",
"myuser", "mypassword", "oracle.jdbc.driver.OracleDriver")

val sql = "select * from accounthistory where entrydate > sysdate - 100 and amount <> 0"

val iter = db.rows(sql).iterator().asInstanceOf[java.util.Iterator[java.util.Map[String,String]]]

while(iter.hasNext) {
val row =
printf("%13s\t%12s\n", row.get("ACCOUNTID"), row.get("AMOUNT"))


Guillaume Laforge said...

The pure Groovy version is more compelling IMHO ;-)

Daniel Spiewak said...

Here's an arguably better way of doing the iteration. You still need that intensely-ugly cast, but you get to use the Iterator as a proper Scala Seq:

import java.util.{Iterator, Map}

val str = {
import Stream._

val iter = db.rows(sql).iterator.asInstanceOf[Iterator[Map[String, String]]

def gen: Stream[Map[String, String] = {
if (iter.hasNext)
cons(, gen)


for (row <- str) {
printf("%13s\t%12s\n", row.get("ACCOUNTID"), row.get("AMOUNT"))

(the above is more readable when indentation is *not* eaten)

The other advantage to this approach is it is easy to chain computations on the individual rows, reducing things to a more managable level:

val str = { ... } // elided
val balances = str map { row =>
(row.get("ACCOUNTID"), row.get("AMMOUNT"))

for ((act, amt) <- balances) {
printf("%13s\t%12s\n, act, amt)

Guillaume is right though that the Groovy version is more compelling. However, this shows that Scala is capable of easily wrapping the Iterator in a lazy list (turning it into a real sequence rather than just a page) and more amenable to functional idioms like map. For non-trivial stuff, I think Scala would win out even with a Groovy API like this one.