Kotlin is the next big thing. With Google announcing official support for Kotlin on Android, we’ll see a lot more traction for this lovely language.
After today's #kotlin announcement, we're betting this tag will blow up just like Swift did. #io17. https://t.co/whOcJlJfWe pic.twitter.com/2QKEJIRYVi
— Stack Overflow (@StackOverflow) May 17, 2017
We’ve already blogged about the Kotlin language recently: 10 Features I Wish Java Would Steal From the Kotlin Language.
Should you migrate your application to Kotlin? Perhaps – there are many questions. The most important question is (of course): What are you going to do with your awesome SQL code?
The answer is: Use jOOQ! Need convincing? Here are 10 nice examples of writing SQL in Kotlin with jOOQ:
(Note: All the code is available on GitHub)
1. Using Kotlin’s Map Access Literals With jOOQ Records
This is a free feature that you’re getting from Kotlin thanks to their smart move of supporting certain operators on custom types by convention. Check out how easy it is to access a value from a record using a column reference:
val a = AUTHOR
val b = BOOK
ctx.select(a.FIRST_NAME, a.LAST_NAME, b.TITLE)
.from(a)
.join(b).on(a.ID.eq(b.AUTHOR_ID))
.orderBy(1, 2, 3)
.forEach {
println("${it[b.TITLE]} by "
+ "${it[a.FIRST_NAME]} ${it[a.LAST_NAME]}")
}
The output from our sample code is this:
1984 by George Orwell Animal Farm by George Orwell Brida by Paulo Coelho O Alquimista by Paulo Coelho
We’re already using a couple of useful language features here. First off:
val for local variable type inference
We’re renaming AUTHOR to a and BOOK to b in our code to shorten table names a little bit. Using val we don’t even need to explicitly refer to the type of those tables.
As it looks right now, we’re going to get that feature in the Java language as well, so stay tuned.
(More about val in item #3)
More info about this feature here.
String interpolation
As you can see, inside of the String literal, we’re using the ${dollar.notation} to access variables from the context.
More info about this feature here.
(More about string interpolation in item #5)
Implicit lambda parameter “it”
When writing single-parameter lambda expressions, which is typical for loops, mapping, and many other standard library features, we don’t need to actually name that parameter in Kotlin. The default name is “it”. Groovy developers will rejoice, as they can profit from the same feature.
More info about this feature here.
Finally: Operator overloading
Kotlin supports a nice and pragmatic approach to operator overloading. We’re making use of the fact that the following two things mean exactly the same in Kotlin:
val x1 = anything[key];
val x2 = anything.get(key);
anything[key] = value;
anything.set(key, value);
Yeah, why not? In jOOQ, we’ve already seen this coming in version 3.9, and we’ve thus added get() and set() methods on our ubiquitous Record type, so you can access record values by:
- Field reference (with type safety)
- Column name (no type safety)
- Index (no type safety)
Note that operator overloading can be used as well with a couple of arithmetic operators:
ctx.select(TRANSACTION.AMOUNT + 10) // Translates to Field.plus(10)
.from(TRANSACTION)
.fetch();
More info about this feature here.
2. Loop Over Record Contents
Another really nice feature of the language is destructuring of “tuples” into several local variables. Quite a few languages support this now, and so does Kotlin, even without first-level language support for real tuples. But often, we don’t actually need tuples, we just need their syntax. This can be done with any Map, for instance. Consider this code:
for (r in ctx.fetch(b))
for ((k, v) in r.intoMap())
println("${r[b.ID]}: ${k.padEnd(20)} = $v")
The output from our sample code is this:
1: ID = 1 1: AUTHOR_ID = 1 1: TITLE = 1984 1: PUBLISHED_IN = 1948 2: ID = 2 2: AUTHOR_ID = 1 2: TITLE = Animal Farm 2: PUBLISHED_IN = 1945 3: ID = 3 3: AUTHOR_ID = 2 3: TITLE = O Alquimista 3: PUBLISHED_IN = 1988 4: ID = 4 4: AUTHOR_ID = 2 4: TITLE = Brida 4: PUBLISHED_IN = 1990
This is really nice! Every jOOQ record can be represented as a Map using Record.intoMap(), which can be destructured automatically in loops as can be seen above.
More info about this feature here.
3. Local Variable Type Inference
An API that makes use of generics as heavily as jOOQ can leave quite some burden on the user occasionally. Type safety is great for the DSL, e.g. in jOOQ, the Java / Kotlin compilers can type-check things like:
// Type checks: Both sides are of type string
AUTHOR.FIRST_NAME.in("Alice", "Bob");
// Type checks:
// - Both sides are of type string
// - Both sides are of degree one
AUTHOR.FIRST_NAME.in(
select(CUSTOMER.FIRST_NAME).from(CUSTOMER)
);
// Doesn't type check: Wrong degree
AUTHOR.FIRST_NAME.in(
select(CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME).from(CUSTOMER)
)
This works through fancy types like:
Select<Record2<String, String>> subselect =
select(CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME).from(CUSTOMER);
These types can get quite hairy as they go up to 22 (higher degrees are supported without type safety). So, in Kotlin, we can simply write:
val subselect =
select(CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME).from(CUSTOMER);
// Still doesn't type check:
AUTHOR.FIRST_NAME.in(subselect)
// This works:
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).in(subselect)
More info about this feature here.
4. Inline Functions for New jOOQ Features
Are you missing a feature in jOOQ, for instance the PostgreSQL ilike operator? Well, it is supported through the Field.likeIgnoreCase() method, but you might prefer the more native looking ilike operator. No problem with Kotlin. Just write an inline function (which works similar to an extension function but they’re not exactly the same thing):
inline fun <F: Field<String>> F.ilike(field: String): Condition {
return condition("{0} ilike {1}", this, field)
}
These inline functions make use of jOOQ’s plain SQL API which is heavily used by Java developers as well, but in this case, the keyword inline on the above functions indicates that the function works like an inline function. We can now write a query like this:
println("${ctx.select(b.TITLE)
.from(b)
.where(b.TITLE.ilike("%animal%"))
.fetchOne(b.TITLE)}")
The result being:
Animal Farm
Notice how we can simply write b.TITLE.ilike("..") even if the jOOQ API doesn’t have such a method!
More info about this feature here
5. String Interpolation and Multiline Strings
How many times have we wished for multiline strings in Java? So many other languages have it, even SQL and PL/SQL. When working with string-based embedded SQL, this is just a killer feature. Good for Kotlin! Not only does Kotlin support multiline strings, but also string interpolation.
Here’s how to use the jOOQ plain SQL API with Kotlin:
ctx.resultQuery("""
SELECT *
FROM (
VALUES (1, 'a'),
(2, 'b')
) AS t
""")
.fetch()
.forEach {
println("${it.intoMap()}")
}
Just copy-paste any arbitrary SQL statement right from your SQL console (in this case: H2 syntax) and you’re done. The output of the above is:
{C1=1, C2=a}
{C1=2, C2=b}
Bonus feature if you’re using the upcoming jOOQ 3.10 parser: You can standardise on SQL features that are not available from your database:
val colX = field("x")
val colY = field("y")
ctx.parser()
.parseResultQuery("""
SELECT *
FROM (
VALUES (1, 'a'),
(2, 'b')
-- This feature (derived column lists)
-- isn't really available in H2 yet it works!
) AS t(${colX.name}, ${colY.name})
""")
.fetch()
.forEach {
println("${it[colX]}, ${it[colY]}")
}
The above statement makes use of derived column lists (renaming tables and columns in one go). Unfortunately, H2 doesn’t support this feature but jOOQ can emulate it, transparently. Note that we’re again using string interpolation, this time in order to reuse column names.
Beware that string interpolation is just a fancy way of performing string concatenation! If you use this feature, you’ll expose yourself to the usual SQL injection risks!
More info about the feature here.
6. Null Safe Dereferencing
Null is the mother of all bikesheds. Of course, Kotlin is opinionated about null as well
You’re not sure if your query will produce a result? No problem with Kotlin’s various null-related operator:
println("${ctx.fetchOne(b, b.ID.eq(5))?.title ?: "not found"}")
The above will print:
not found
Why? Because fetchOne() won’t return a record from our database, it will return null. We cannot dereference ?.title from null, but the ?. operator won’t throw an exception, it will just return null again. Finally, the Elvis operator will provide a default value if the expression on the left of it is null. Perfect!
More info about the feature here.
7. Getters and setters feel like properties
Remember the last 20 years when we emulated properties in Java using the obnoxious JavaBeans convention? Tired of writing getters and setters? Well, at least, with Kotlin, it pays off. We can syntactically pretend that we have a property where there are only getters and setters. Check this out:
val author1 = ctx.selectFrom(a).where(a.ID.eq(1)).fetchOne();
println("${author1.firstName} ${author1.lastName}")
val author2 = ctx.newRecord(a);
author2.firstName = "Alice"
author2.lastName = "Doe"
author2.store()
println("${author2.firstName} ${author2.lastName}")
Isn’t that awesome? we can just dereference author.firstName, which simply translates to author.getFirstName(). Likewise, we can assign a value to author2.lastName = "Doe", which simply translates to author2.setLastName("Doe")
It seems so obvious, no?
More info about the feature here
8. With to Abbreviate Setter Calls
We can take this feature one step further and use the nice with() inline function (not a keyword!). In the above example, when storing a new author, we have to repeat the author reference all the time. This is no longer true with… with!
val author3 = ctx.newRecord(a);
with (author3) {
firstName = "Bob"
lastName = "Doe"
store()
}
All the code that is inside of the block supplied to the with() function is going to operate on the with() function’s first argument. We already know this interesting feature from JavaScript, but in Kotlin, it’s typesafe!
And notice, the store() call is also affected!
More info about this feature here
9. With to Locally Import Tables
We can use with() also with queries! In jOOQ, columns are instance members inside of tables, and as such, must be dereferenced from a concrete table. That can be tedious (SQL doesn’t have this requirement), especially when selecting only from a single table. You can turn on static member generation in jOOQ’s code generator, but then table aliasing is much less easy.
Or you use Kotlin again! The with() function can help:
with (AUTHOR) {
ctx.select(FIRST_NAME, LAST_NAME)
.from(AUTHOR)
.where(ID.lt(5))
.orderBy(ID)
.fetch {
println("${it[FIRST_NAME]} ${it[LAST_NAME]}")
}
}
Resulting in
George Orwell Paulo Coelho
Isn’t that nice? Now, all the FIRST_NAME, LAST_NAME, ID column references are dereferenced from AUTHOR in a much more lenient way.
More info about this feature here
10. Destructuring jOOQ records into sets of local variables
This is so great, we’re going to add more native support for it in jOOQ 3.10 right away.
Kotlin’s type destructuring feature is implemented following convention. This means that any type that has methods with the name component[N] can be destructured. We currently don’t have those methods, but they can be added really easily using inline operators:
operator fun <T, R : Record3<T, *, *>> R.component1() : T {
return this.value1();
}
operator fun <T, R : Record3<*, T, *>> R.component2() : T {
return this.value2();
}
operator fun <T, R : Record3<*, *, T>> R.component3() : T {
return this.value3();
}
These inline functions are added to the R type, which is any subtype of Record3, and then they can be used as such:
for ((first, last, title) in ctx
.select(a.FIRST_NAME, a.LAST_NAME, b.TITLE)
.from(a)
.join(b).on(a.ID.eq(b.AUTHOR_ID))
.orderBy(1, 2, 3))
println("$title by $first $last")
The result is again:
1984 by George Orwell Animal Farm by George Orwell Brida by Paulo Coelho O Alquimista by Paulo Coelho
As can be seen, our query returns the type Record3<String, String, String>. Our inline functions will add component1(), component2(), and component3() methods to the type, which are used by the Kotlin language to destructure the record into three local loop variables: (first, last, title).
This is incredibly useful!
(note also that we don’t have to execute the query explicitly in the loop because any jOOQ ResultQuery is Iterable)
More info about this feature here
Conclusion
Kotlin is getting increasingly popular. It contains a lot of smart yet pragmatic features that make daily work with programming on the JVM a lot easier. Kotlin’s high focus on Java interoperability makes it really really powerful, and working with Kotlin and jOOQ is a really productive way of writing SQL on the JVM.
Do give it a shot. The sources are here: