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 :-)
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
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….
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! :-)
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 ;-)
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
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!
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… :)
Hello Lukas,
Can you suggest some pro sql books to learn ?
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
This looks pretty cool, I’m definitely going to try this out. Agree with the other poster this is close to the LINQ coolness
Thanks! Feel free to provide feedback also on the newsgroup!
https://groups.google.com/forum/#!forum/jooq-user
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.
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