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 :-)

Tags: , , , , ,

17 responses to “The ultimate SQL-DSL: jOOQ in Scala”

  1. virtualeyes says :

    Looks better than sliced bread

    Scala needs some love in the JDBC/SQL/ORM departments, way behind the curve.

    JooQ looks like a very nice solution.

    Any chance of query result to case class coercion? Have gotten spoiled by groovy and:
    def pogo = someQueryResult as Pogo

    In Scala the compiler is much less lenient (as in Java) as you know. At any rate JooQ query syntax is close to the LINQ-to-SQL nirvana I have been seeking on the JVM (pesky parens and periods be gone!)

    I’ll dig around, if JooQ can roll in Scala this might just fit the bill….

    • lukaseder says :

      Thanks for the encouragement. I’ve just restarted to look into Scala again. I’m not aware of the full potential of such an integration, but from what I’ve seen, there probably isn’t any other product that makes SQL available to Scala to a similar extent.

      Keep digging! :-)

      • virtualeyes says :

        I’ve dug clear through to China and still no Scala ORM in site ;-)

        Early days for Scala ORMs. The main players, Squeryl and ScalaQuery have their rough edges, and the rest are crude blocks yet to be carved.

        On other platforms the ORM solutions are so comprehensive and elegant (Rails AR, Grails GORM, M$ LINQ) that the latest and greatest in Scala looks pretty unappealing.

        ScalaQuery has been taken over by TypeSafe; they are working on a full blown LINQ-to-SQL solution. Looks very promising, but as you must know, a large endeavor that is. I have to imagine they are months away from releasing anything to the masses.

        BTW, JOOQ, is LINQ-to-SQL for Java, that is quite clear, impressive body of work ;-)

    • lukaseder says :

      Thanks for your hints. I’m getting in touch with the Scala / Typesafe crew, as I have some connections to the EPFL. I’m sure some sort of cooperation is possible…

      It’s hard to say if any non-SQL language can fully implement SQL in a readable way. When you start building constructs such as Common Table Expressions, advanced aliasing, ad-hoc typing, then even Scala might not be powerful enough (yet)…

      Let’s see

      • virtualeyes says :

        Yes, hookup with TypeSafe, great idea, get back to your roots ;-)

        re: “It’s hard to say if any non-SQL language can fully implement SQL in a readable way”, Microsoft already did, LINQ, and Specifically LINQ-to-SQL are engineering masterpieces. I’m partial to Linux and the JVM; if it were otherwise, the C#/.NET/Entity Framework stack would be very appealing.

        Anyway, Scala is probably the best suited strongly typed JVM language for the task. Dynamically, Groovy + custom ASTs is getting quite close:

        http://gaelyk.appspot.com/tutorial/app-engine-shortcuts#query

        def entities = datastore.execute {
        select all from savedscript
        sort desc by dateCreated
        where author == params.author
        limit 10
        }

        I believe Microsoft had to evolve C# itself in order to create LINQ (don’t have it handy, but there’s an excellent StackOverflow topic by John Skeet on the massive undertaking it was to create LINQ)

        I’ve been following ScalaQuery on Git and it looks like they may be scrapping for comprehensions in favor of more SQL-esque syntax (at least for the end user it will appear as standard-ish SQL)

        Have to say, what JOOQ does in Java is incredible, a labor of love, you’re madman!

    • lukaseder says :

      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. As you said, I guess “madman” is the right word… :)

  2. www.drorhan.com says :

    Hello Lukas,

    Can you suggest some pro sql books to learn ?

    • lukaseder says :

      Hello,

      I’m actually going to write one some time soon, with lots of examples in various SQL dialects, jOOQ and JPA queries, but that’s not going to be published in a while. Otherwise, unfortunately, I don’t know any good books on SQL…

      Cheers
      Lukas

  3. JustSomeOne says :

    This looks pretty cool, I’m definitely going to try this out. Agree with the other poster this is close to the LINQ coolness

  4. Juan Miguel Cejuela (@jmcejuela) says :

    Amazing, great integration, so readable.

    This post has a perfect timing for me. I’m actually now starting with Scala and I was looking for a nice jOOQ-like library! jOOQ itself of course!

    I will experiment soon with this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,716 other followers

%d bloggers like this: