It was hard to limit ourselves to 10 Nice Examples of Writing SQL in Kotlin With jOOQ, recently, because the Kotlin language has many nice little features that really help a lot when working with Java libraries. We’ve talked about the nice with()
stdlib function, which allows to “import” a namespace for a local scope or closure:
with (AUTHOR) { ctx.select(FIRST_NAME, LAST_NAME) .from(AUTHOR) .where(ID.lt(5)) .orderBy(ID) .fetch { println("${it[FIRST_NAME]} ${it[LAST_NAME]}") } }
In the above example, the AUTHOR
table is made available as the this
reference in the closure following the with
function, which works exactly like JavaScript’s with()
. Everything in AUTHOR
is available, without dereferencing it from AUTHOR
.
Apply is very similar
A very similar feature is made available through apply()
, although with different syntactic implications. Check out this Stack Overflow question for some details about with()
vs. apply()
in Kotlin.
When using jOOQ, apply()
is most useful for dynamic SQL. Imagine you have local variables indicating whether some parts of a query should be added to the query:
val filtering = true; val joining = true;
These boolean variables would be evaluated dynamically, of course. filtering
specifies whether a dynamic filter / where clause is needed, whereas joining
specifies whether an additional JOIN is required.
So, the following query will select authors, and:
- if “filtering”, we’re selecting only author ID = 1
- if “joining”, we’ll join the books table and count the number of books per author
Both of these predicates are independent. Enter the game: apply()
:
ctx.select( a.FIRST_NAME, a.LAST_NAME, if (joining) count() else value("")) .from(a) .apply { if (filtering) where(a.ID.eq(1)) } .apply { if (joining) join(b).on(a.ID.eq(b.AUTHOR_ID)) } .apply { if (joining) groupBy(a.FIRST_NAME, a.LAST_NAME) } .orderBy(a.ID) .fetch { println(it[a.FIRST_NAME] + " " + it[a.LAST_NAME] + (if (joining) " " + it[count()] else "")) }
That’s neat! See, the jOOQ API doesn’t specify any apply()
method / function, yet you can chain the apply()
function to the jOOQ API as if it were natively supported.
Like with()
, apply()
makes a reference available to a closure as this
, so it doesn’t have to be referenced explicitly anymore. Which means, we can write neat things like
.apply { if (filtering) where(a.ID.eq(1)) }
Where a where()
clause is added only if we’re filtering!
Of course, jOOQ (or any other query builder) lends itself to this kind of dynamic SQL, and it can be done in Java too:
https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql
But the Kotlin-specific fluent integration using apply()
is exceptionally neat. Well done, Kotlin!
Side-note
This only works because the jOOQ DSL API of jOOQ 3.x is mutable and every operation returns the same this
reference as was kindly pointed out by Ilya Ryzhenkov
In the future (e.g. version 4.0), we’re planning on making the jOOQ API more immutable – mutability is a historic legacy (although, often, it’s the desired behaviour for a query builder).
Very nice!
In many places I’m adding:
.apply { if (limit != null) limit(limit) }
.apply { if (offset != null) offset(offset) }
Could something like that be extracted in a method call or something?
I’m sure it can be! What’s your expectation for client code to look like?