O/R Broker is an interesting tool which aim to simplify relational database operations from scala language. Many interesting features such as no need of external configuration files (the mapping is done in scala), sql requests can be stored in plain files which make sql requests tuning easier, no leak (connections, statements, resultsets,... are all "managed"), ...

Let's first install & prepare a mysql service on a gentoo linux system : 

As root user : 
$ emerge mysql
     ==> To install the latest mysql
$ emerge --config =dev-db/mysql-5.1.56  
     ==> initialize / prepare the database !! Ask for a root password
     ==> choose 'mysql2011root' for example
     ==> You may have to change the release to the precise one that have been installed
$ eselect rc add mysql  default
     ==> For automatic startup as system starts 
$ /etc/init.d/mysql start
     ==> Manual start
$ mysql -u root -h localhost -p            (Will ask you the root password  = 'mysql2011root')
     SHOW DATABASES;
     CREATE DATABASE science;
     USE science;
     CREATE TABLE SCIENTIST (
             SCIENTIST_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
             FIRST_NAME VARCHAR(128) NOT NULL,
             LAST_NAME VARCHAR(128) NOT NULL,
              BIRTH_DATE DATE);
     INSERT INTO SCIENTIST (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('Albert', 'Einstein', STR_TO_DATE('1879-03-18','%Y-%m-%d'));
     INSERT INTO SCIENTIST (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('Thomas', 'Edison', STR_TO_DATE('1847-02-11','%Y-%m-%d'));
     INSERT INTO SCIENTIST (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('Isaac', 'Newton', STR_TO_DATE('1643-01-16','%Y-%m-%d'));
     INSERT INTO SCIENTIST (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('Samuel', 'Morse', STR_TO_DATE('1781-04-27','%Y-%m-%d'));
     INSERT INTO SCIENTIST (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('Kurt', 'Gödel', STR_TO_DATE('1906-04-28','%Y-%m-%d'));
     select * from SCIENTIST;
     GRANT ALL ON SCIENTIST.* TO 'guest'@'localhost' IDENTIFIED BY 'guest2011';
$ mysql -u guest -h localhost -D science -p          (Password = guest2011)
     select * from SCIENTIST;

Let's create the SBT build configuration file (Build.scala) with all required dependencies : 

import sbt._
import Keys._
object SQLSandboxBuild extends Build { 
  val ssbsettings = Defaults.defaultSettings  ++ Seq(
    name         := "sqlSandbox",
    version      := "1.0",
    scalaVersion := "2.9.1",
    libraryDependencies ++= Seq (
       "org.orbroker"   % "orbroker"             % "3.1.1"    % "compile",
       "mysql"          % "mysql-connector-java" % "5.1.6"    % "compile",
       "org.scalatest"  %% "scalatest"           % "1.6.1"    % "test",
       "c3p0"           % "c3p0"                 % "0.9.1.2"  % "compile"
    ),     
    resolvers += "GEOTools" at "http://download.osgeo.org/webdav/geotools/",
    resolvers += "JBoss Repository" at "http://repository.jboss.org/maven2",
    resolvers += "Typesafe Repository" at "http://repo.typesafe.com/typesafe/releases/"
  )
  lazy val ssbproject = Project("ssbproject",file("."), settings = ssbsettings)
}
Now the next step is to define the mapping for our Scientists database : 

case class Scientist(firstName:String, lastName:String, birthDate:Option[Date]=None, id:Option[Int]=None)

object ScientistExtractor extends RowExtractor[Scientist] {
  val key = Set("SCIENTIST_ID")  
  def extract(row: Row) = {
    val id = row.integer("SCIENTIST_ID")
    val List(firstName,lastName) = List("FIRST_NAME", "LAST_NAME") map {row.string(_).get}
    val birthDate = row.date("BIRTH_DATE")
    Scientist(firstName, lastName, birthDate, id)
  }
}

object Tokens extends TokenSet(true) {
  val scientistSelectAll   = Token('scientistSelectAll, ScientistExtractor)
  val scientistSelectById  = Token('scientistSelectById, ScientistExtractor)
  val scientistInsert      = Token[Int]('scientistInsert)
  val scientistDelete      = Token('scientistDelete)
  val scientistUpdate      = Token('scientistUpdate)
}

So we defined a Scientist case class, and its extractor which tell how to build a Scientist instance from a row of "SCIENTIST" table data.

We store our sql request inside plain text files : 

 $ find src/main/resources/sql/ -name "*.sql"
src/main/resources/sql/scientistSelectAll.sql
src/main/resources/sql/scientistDelete.sql
src/main/resources/sql/scientistSelectById.sql
src/main/resources/sql/scientistUpdate.sql
src/main/resources/sql/scientistInsert.sql

for example, scientistSelectById looks like :
SELECT *
FROM SCIENTIST
where SCIENTIST_ID = :id

In order to be able to execute sql requests, you first need to create a "broker" : 

  val broker =  {
    val url = "jdbc:mysql://localhost/science"
    val username = "guest"
    val password = "guest2011"  
    val ds = getPooledDataSource(url,username,password) //new SimpleDataSource(url)
    val builder = new BrokerBuilder(ds)
    val res:Map[Symbol,String] = Tokens.idSet map {sym => sym -> "/sql/%s.sql".format(sym.name)} toMap;
    ClasspathRegistrant(res).register(builder)
    builder.verify(Tokens.idSet)
    builder.setUser(username, password)
    builder.build
  }

Get a scientist : 

    val scientist = broker.readOnly() {
      _.selectOne(scientistSelectById, "id"->1)
    }
    scientist should not equal(None)

Get all scientists : 

    val scientists = broker.readOnly() {
      _.selectAll(scientistSelectAll)
    }

Browse scientists : 

    broker.readOnly() {
      _.select(scientistSelectAll) { scientist =>
        println(scientist)
        true
      }

And now the classical CRUD operations : 

    // Create
    val newscientist = broker.transaction() { txn =>
      val scientist = Scientist("Benoist","Mandelbrot")
      val newid = txn.executeForKey(scientistInsert, "scientist"->scientist)
      scientist.copy(id = newid)
    }
    // Update
    val updatedScientist = newscientist.copy(birthDate = "1924-11-20")
    broker.transaction() { txn =>
      txn.execute(scientistUpdate, "scientist"->updatedScientist)
    }
    // Read
    val foundScientist = broker.readOnly() {
      _.selectOne(scientistSelectById, "id"->updatedScientist.id).get
    }
    foundScientist should equal(updatedScientist)
    // Delete
    broker.transaction() { txn =>
      txn.execute(scientistDelete, "id" -> updatedScientist.id.get)
    }
Notice here that once the new scientist has been created in the database we got back the automatically created numeric ID, so we return a new instance of scientist which contains the id chosen by the database, the original instance has not been modified, we want to keep all the benefits of immutability !

And now let's generate some load (40*10000 sql requests executed in parallel) on the database, and check the c3p0 database connection pool using an external jconsole : 

    import actors.Actor._
    val howmany=40
    val creator = self
    for (n <- 1 to howmany) {
      actor {
        try {
          for(i<-1 to 10000) {
            val scientists = broker.readOnly() {
               _.selectAll(scientistSelectAll)
            }
          }
        } finally {
          creator ! "finished"
        }
      }
    }    
    for (i<- 1 to howmany) receive {
      case _ =>
    }
  }

The full SBT project is available here : sqlsandbox.tar.gz.

CONTEXT : Linux Gentoo / Scala 2.9.1 / Java 1.6.0_26 / SBT 0.10 / ScalaTest 1.6.1 / ORBroker 3.1.1