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:
- Typesafe querying, meaning that your SQL syntax is compiled
- 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 
December 12th, 2011 at 23:35
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….
December 13th, 2011 at 00:52
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!
December 13th, 2011 at 10:08
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
December 13th, 2011 at 13:35
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
December 13th, 2011 at 18:07
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!
December 13th, 2011 at 20:51
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…
December 14th, 2011 at 20:16
Hello Lukas,
Can you suggest some pro sql books to learn ?
December 14th, 2011 at 22:15
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
December 20th, 2011 at 17:57
This looks pretty cool, I’m definitely going to try this out. Agree with the other poster this is close to the LINQ coolness
December 20th, 2011 at 18:13
Thanks! Feel free to provide feedback also on the newsgroup!
https://groups.google.com/forum/#!forum/jooq-user
December 23rd, 2011 at 15:48
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.
December 23rd, 2011 at 15:55
That’s great news! Again, any feedback on the news group is very welcome. Also, a comparison between jOOQ and http://scalaquery.org/ would be quite interesting.
Cheers
Lukas
December 24th, 2011 at 11:30
[...] http://lukaseder.wordpress.com/2011/12/11/the-ultimate-sql-dsl-jooq-in-scala/ [...]
April 15th, 2012 at 23:07
[...] affiliated with the CUBRID database)http://www.jooq.orgSee some examples of jOOQ in Scala here:http://blog.jooq.org/2011/12/11/…With Scala's language features (such as type inference, avoiding method parentheses, etc), [...]
May 17th, 2012 at 16:07
[...] PartiallyProof of concept [...]
August 9th, 2012 at 09:32
[...] a reminder, see my previous blog post about how jOOQ integrates with Scala, and how you can write almost-SQL queries in Scala using jOOQ against 13 popular databases. It [...]
October 19th, 2012 at 11:46
[...] DSL authoring support is becoming a more and more interesting topic in various platforms. Eclipse is developing Xtext, Scala is experimenting with even more powerful Macros. While Xtext doesn’t allow to mix the DSL grammar with Java, Scala’s Macros do precisely that. They allow for simple compile-time checked Scala syntax extension, if you have a powerful enough CPU to compile such source code. Where this could go for jOOQ was discusssed on this blog before [...]