SQL Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 219 posts at DZone. You can read more from them at their website. View Full User Profile

The ultimate SQL-DSL: jOOQ in Scala

12.15.2011
| 3812 views |
  • submit to reddit

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/

Published at DZone with permission of Lukas Eder, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Afandi Merathi replied on Sat, 2012/03/24 - 7:53am

Very nice. I’ve recently seen Groovy’s capabilities for creating DSL’s in another context. That’s nice work by Gaelyk. I think I’ll also dig into that very soon. The complete jOOQ DSL has become quite complex, including clauses like INSERT .. ON DUPLICATE KEY UPDATE .., or INSERT .. RETURNING, not to mention the MERGE statement.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.