10 Nice Examples of Writing SQL in Kotlin With jOOQ

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.

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.

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:

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-kotlin-example

Java A’s new Local-Variable Type Inference

News could hardly get more exciting than this, for a programming language aficionado!

There is now a JEP 286 for Local-Variable Type Inference with status “Candidate”. And a request for feedback by Brian Goetz, which I would love to invite you to participate in:
http://mail.openjdk.java.net/pipermail/platform-jep-discuss/2016-March/000037.html

Please do so, the survey remains open only from March 9 to March 16!

This is not a feature that will be implemented. It might be implemented. Hence, there is no specific Java version yet, which is why I name the Java version “A” (for Awesome).

What is local-variable type inference and why is it good?

Let’s have a look at a feature that various other languages have had for quite a while. In this blog post, I’d like to discuss the general idea, not the possibly specific implementation that might be planned for Java, as that would be too early, and I certainly don’t have the big picture of how this fits into Java.

In Java, as well as in some other languages, types are always declared explicitly and verbosely. For instance, you write things like:

// Java 5 and 6
List<String> list = new ArrayList<String>();

// Java 7
List<String> list = new ArrayList<>();

Notice how in Java 7, some syntax sugar was added via the useful diamond operator <>. It helps removing unnecessary redundancy in the Java way, i.e. by applying “target-typing”, which means the type is defined by the “target”. Possible targets are:

  • Local variable declarations
  • Method arguments (both from the outside and from the inside of the method)
  • Class members

Since in many cases, the target type MUST be declared explicitly (method arguments, class members), Java’s approach makes a lot of sense. In the case of local variables, however, the target type doesn’t really need to be declared. Since the type definition is bound to a very local scope, from which it cannot escape, it may well be inferred by the compiler without the source code ever being explicit about it, from the “source type”. This means, we will be able to do things like:

// Java A as suggested in the JEP

// infers ArrayList<String>
var list = new ArrayList<String>();

// infers Stream<String>
val stream = list.stream();

In the above example var stands for a mutable (non-final) local variable, whereas val stands for an immutable (final) local variable. Notice how the type of list was never really needed, just as when we write the following, where the type is already inferred today:

stream = new ArrayList<String>().stream();

This will work no different from lambda expressions, where we already have this kind of type inference in Java 8:

List<String> list = new ArrayList<>();

// infers String
list.forEach(s -> {
    System.out.println(s);
};

Think of lambda arguments as local variables. An alternative syntax for such a lambda expression might have been:

List<String> list = new ArrayList<>();

// infers String
list.forEach((val s) -> {
    System.out.println(s);
};

Other languages have this, but is it good?

Among these other languages: C# and Scala and JavaScript, if you will ;). YAGNI is probably an common reaction to this feature. For most people, it’s mere convenience to be able not to type all types all the time. Some people might prefer to see the type explicitly written down, when reading code. Especially, when you have a complex Java 8 Stream processing pipeline, it can get hard to track all the types that are inferred along the way. An example of this can be seen in our article about jOOλ’s window function support:

BigDecimal currentBalance = new BigDecimal("19985.81");
 
Seq.of(
    tuple(9997, "2014-03-18", new BigDecimal("99.17")),
    tuple(9981, "2014-03-16", new BigDecimal("71.44")),
    tuple(9979, "2014-03-16", new BigDecimal("-94.60")),
    tuple(9977, "2014-03-16", new BigDecimal("-6.96")),
    tuple(9971, "2014-03-15", new BigDecimal("-65.95")))
.window(Comparator
    .comparing((Tuple3<Integer, String, BigDecimal> t) 
        -> t.v1, reverseOrder())
    .thenComparing(t -> t.v2), Long.MIN_VALUE, -1)
.map(w -> w.value().concat(
     currentBalance.subtract(w.sum(t -> t.v3)
                              .orElse(BigDecimal.ZERO))
));

The above implements a running total calculation that yields:

+------+------------+--------+----------+
|   v0 | v1         |     v2 |       v3 |
+------+------------+--------+----------+
| 9997 | 2014-03-18 |  99.17 | 19985.81 |
| 9981 | 2014-03-16 |  71.44 | 19886.64 |
| 9979 | 2014-03-16 | -94.60 | 19815.20 |
| 9977 | 2014-03-16 |  -6.96 | 19909.80 |
| 9971 | 2014-03-15 | -65.95 | 19916.76 |
+------+------------+--------+----------+

While the Tuple3 type needs to be declared because of the existing Java 8’s limited type inference capabilities (see also this article on generalized target type inference), are you able to track all the other types? Can you easily predict the result? Some people prefer the short style, others claim:

On the other hand, do you like to manually write down a type like Tuple3<Integer, String, BigDecimal>? Or, when working with jOOQ, which of the following versions of the same code do you prefer?

// Explicit typing
// ----------------------------------------
for (Record3<String, Integer, Date> record : ctx
    .select(BOOK.TITLE, BOOK.ID, BOOK.MODIFIED_AT)
    .from(BOOK)
    .where(TITLE.like("A%"))
) {
    // Do things with record
    String title = record.value1();
}

// "Don't care" typing
// ----------------------------------------
for (Record record : ctx
    .select(BOOK.TITLE, BOOK.ID, BOOK.MODIFIED_AT)
    .from(BOOK)
    .where(TITLE.like("A%"))
) {
    // Do things with record
    String title = record.getValue(0, String.class);
}

// Implicit typing
// ----------------------------------------
for (val record : ctx
    .select(BOOK.TITLE, BOOK.ID, BOOK.MODIFIED_AT)
    .from(BOOK)
    .where(TITLE.like("A%"))
) {
    // Do things with record
    String title = record.value1();
}

I’m sure that few of you would really like to explicitly write down the whole generic type, but if your compiler can still remember the thing, that would be awesome, wouldn’t it? And it’s an opt-in feature. You can always revert to explicit type declarations.

Edge-cases with use-site variance

There are some things that are not possible without this kind of type inference, and they’re related to use-site variance and the specifics of generics as implemented in Java. With use-site variance and wild cards, it is possible to construct “dangerous” types that cannot be assigned to anything because they’re undecidable. For details, please read Ross Tate’s paper on Taming Wildcards in Java’s Type System.

Use-site variance is also a pain when exposed from method return types, as can be seen in some libraries that either:

  • Didn’t care about this pain they’re inflicting on their users
  • Didn’t find a better solution as Java doesn’t have declaration-site variance
  • Were oblivious to this issue

An example:

interface Node {
    void add(List<? extends Node> children);
    List<? extends Node> children();
}

Imagine a tree data structure library, where tree nodes return lists of their children. A technically correct children type would be List<? extends Node> because the children are Node subtypes, and it is perfectly OK to use a Node subtype list.

Accepting this type in the add() method is great from an API design perspective. It allows people to add a List<LeafNode>, for instance. Returning it from children() is horrible, though, because the only options are now:

// Raw type. meh
List children = parent.children();

// Wild card. meh
List<?> children = parent.children();

// Full type declaration. Yuk
List<? extends Node> children = parent.children();

With JEP 286, we might be able to work around all of this and have this nice fourth option:

// Awesome. The compiler knows it's 
// List<? extends Node>
val children = parent.children();

Conclusion

Local Variable Type Inference is a hot topic. It’s entirely optional, we don’t need it. But it makes a lot of things much much easier, especially when working with tons of generics. We’ve seen that type inference is a killer feature when working with lambda expressions and complex Java 8 Stream transformations. Sure, it will be harder to track all the types across a long statement, but at the same time, if those types were spelled out, it would make the statement very unreadable (and often also very hard to write).

Type inference helps make developers more productive without giving up on type safety. It actually encourages type safety, because API designers are now less reluctant to expose complex generic types to their users, as users can use these types more easily (see again the jOOQ example).

In fact, this feature is already present in Java in various situations, just not when assigning a value to a local variable, giving it a name.

Whatever your opinion is: Do make sure to share it to the community and answer this survey:
http://mail.openjdk.java.net/pipermail/platform-jep-discuss/2016-March/000037.html

Looking forward to Java A where A stands for Awesome.