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).
More nice Kotlin/jOOQ tricks in this article here.
Like this:
Like Loading...
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?