The ultimate SQL-DSL: jOOQ in Scala
Join the DZone community and get the full member experience.
Join For Freei’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
from http://lukaseder.wordpress.com/2011/12/11/the-ultimate-sql-dsl-jooq-in-scala/
Opinions expressed by DZone contributors are their own.
Comments