The ultimate SQL-DSL: jOOQ in Scala

i’ve recently come across some advertising for the new upcoming version of scala ide for eclipse , which made me remember my college programming lessons at the epfl laboratoire des méthodes de programmation (lamp), the origin of the scala language . back then, scala appeared quite freaky. very elegant, a bit inefficient, somewhat dogmatic. it was much more functional than object oriented, from what i recall, and martin odersky had a hard time agreeing that the key to success is to combine the two paradigms. but scala has come a long way in the last 8 years. so i was wondering if jooq was portable to scala. the answer amazes me:

jooq is 100% scala-ready !!

obviously, this is not due to jooq’s fluent api alone. it’s mostly because of how scala was built on top of java. check out this piece of sample code:

package org.jooq.scala

import java.sql.connection
import java.sql.drivermanager

// this makes integration of java into scala easier
import scala.collection.javaconversions._

// import all relevant things from jooq
import org.jooq.impl.factory._
import org.jooq.util.maven.example.mysql.test2factory
import org.jooq.util.maven.example.mysql.tables._

object test {
  def main(args: array[string]) {

    // this is business as usual. i guess there's
    // also a "scala way" to do this...?
    class.forname("com.mysql.jdbc.driver");
    val connection = drivermanager.getconnection(
      "jdbc:mysql://localhost/test", "root", "");
    val create = new test2factory(connection);

    // fetch book titles and their respective authors into
    // a result, and print the result to the console. wow!
    // if this doesn't feel like sql to you...?
    val result = (create
      select (
          t_book.title as "book title",
          t_author.first_name as "author's first name",
          t_author.last_name as "author's last name")
      from t_author
      join t_book on (t_author.id equal t_book.author_id)
      where (t_author.id in (1, 2, 3))
      orderby (t_author.last_name asc) fetch)

    // print the result to the console
    println(result)

    // iterate over authors and the number of books they've written
    // print each value to the console
    for (r <- (create
               select (t_author.first_name, t_author.last_name, count)
               from t_author
               join t_book on (t_author.id equal t_book.author_id)
               where (t_author.id in (1, 2, 3))
               groupby (t_author.first_name, t_author.last_name)
               orderby (t_author.last_name asc)
               fetch)) {

      // accessing record data is just like in java
      print(r.getvalue(t_author.first_name))
      print(" ")
      print(r.getvalue(t_author.last_name))
      print(" wrote ")
      print(r.getvalue(count))
      println(" books ")
    }
  }
}

as expected, the console contains this data

+------------+-------------------+------------------+
|book title  |author's first name|author's last name|
+------------+-------------------+------------------+
|o alquimista|paulo              |coelho            |
|brida       |paulo              |coelho            |
|1984        |george             |orwell            |
|animal farm |george             |orwell            |
+------------+-------------------+------------------+

paulo coelho wrote 2 books
george orwell wrote 2 books

you get 2 in 1

with scala, jooq’s fluent api looks even more like sql than in java. and you get 2 in 1:

1. typesafe querying, meaning that your sql syntax is compiled
2. typesafe querying, meaning that your database schema is part of the code

the biggest drawback i can see so far is that scala ships with new reserved words, such as val, a very important method in jooq. i guess that could be sorted out somehow. so scala users and sql enthusiasts! please! feedback

from http://lukaseder.wordpress.com/2011/12/11/the-ultimate-sql-dsl-jooq-in-scala/