Chapter 16: Tackling Aliases and SQL Templating

This chapter covers two important topics that sustain your road to becoming a jOOQ power user: aliases and SQL templating.

The first part of this chapter tackles several practices for aliasing tables and columns via the jOOQ DSL. The goal of this part is to make you comfortable when you need to express your SQL aliases via jOOQ and to provide you with a comprehensive list of examples that cover the most common use cases.

The second part of this chapter is all about SQL templating or how to express SQL when the jOOQ DSL cannot help us. There will be rare cases when you'll have to write plain SQL or combine DSL and plain SQL to obtain some corner cases or vendor-specific features.

In this chapter, we will cover the following main topics:

• Expressing SQL aliases in jOOQ
• SQL templating

Let's get started!

Technical requirements

The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter16.

Expressing SQL aliases in jOOQ

SQL aliasing is a simple task. After all, it's just about giving some nicknames to your columns and tables and referring to them via these nicknames instead of using their real names. But as simple as this may seem, this is a rather controversial topic. Some of the open questions you may come across will sound like this: Should I only use aliases when it's mandatory (for instance, when I reference the same table twice)? Should I use meaningful names or will single letters work just fine (p, q, t1, t2, and so on)? Do they increase readability and decrease typing time? Most probably, the correct answer is that it depends… on the context, on the query, on who is writing the query (a developer, a DBA, a generator), and so on!

As you'll see shortly, using aliasing via a DSL requires us to respect several rules and to be prepared for some verbosity since the host language (here, Java) comes with several shortcomings that a DSL must...

SQL templating

When we talk about SQL templating or the Plain SQL Templating Language, we're talking about covering those cases where the DSL cannot help us express our SQL. The jOOQ DSL strives to cover SQL as much as possible by constantly adding more and more features, but it is normal to still find some corner case syntax or vendor-specific features that won't be covered by the DSL. In such cases, jOOQ allows us to express SQL as plain SQL strings with bind values or query parts via the Plain SQL API.

The Plain SQL API materializes in a set of overloaded methods that can be used where the DSL doesn't help. Here are some examples:

field/table(String sql)
field(String sql, Class<T> type)
field(String sql, Class<T> type, Object... bindings)
field(String sql, Class<T> type, QueryPart... parts)
field/table(String sql, Object... bindings)
field(String sql, DataType<T> type)
field(String sql, DataType<T> type, Object... bindings)
field...

Summary

This was a short but comprehensive chapter about jOOQ aliases and SQL templating. In jOOQ, most of the time, you can have a peaceful life without being a power user of these features, but when they come into play, it is nice to understand their basics and exploit them.

In the next chapter, we'll tackle multitenancy.