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 address as elegantly as possible. Aliasing sits next to derived tables, arithmetic expressions, and casting as one of the main challenges for a DSL, so let's see exactly what we should know about it.
The examples from the following sections are available via SimpleAliases and AliasesSamples.
Expressing simple aliased tables and columns
Independent of how you like to use SQL aliases, when you want to express them in jOOQ, you must be aware of several methods, including as() and asTable(), which come in many flavors, such as as(String alias), as(Name alias), as(Name as, Name... fieldAliases), asTable(), asTable(String alias), asTable(Name alias), asTable(Table<?> alias), and so on. Commonly, we must deal with aliased tables and fields. Here is a quick sample of using aliased tables in jOOQ:
ctx.select(field(name("t", "first_name")), field(name("t", "last_name"))) .from(EMPLOYEE.as("t")).fetch();
ctx.select(field(name("t", "product_id")), field(name("t", "product_name")),field(selectCount()
.from(PRODUCT)
.where(PRODUCT.PRODUCT_ID.eq(
field(name("t", "product_id"), Long.class)))).as("count")) .from(PRODUCT.as("t")).fetch();
The following is an example of using some aliased fields (used here to take full control of the column names that are generated in your SQL):
ctx.select(EMPLOYEE.FIRST_NAME.as("fn"), EMPLOYEE.LAST_NAME.as("ln")).from(EMPLOYEE).fetch();
ctx.select(concat(EMPLOYEE.FIRST_NAME,
inline(" "), EMPLOYEE.LAST_NAME).as("name"), EMPLOYEE.EMAIL.as("contact"), EMPLOYEE.REPORTS_TO.as("boss_id")).from(EMPLOYEE).fetch();
Next, we'll look at some more complex examples of using aliases.
Aliases and JOINs
One of the common cases where we see SQL aliases at work is in JOIN statements. Instead of repeating the table names, people prefer to associate aliases with the joined tables and refer to them via these aliases. For instance, in the following screenshot, we have a JOIN between two MySQL tables (OFFICE and DEPARTMENT) expressed without aliases (top) and with aliases (bottom):
Figure 16.1 – JOIN with and without aliases
If we express the first SQL in jOOQ (without using aliases) then we obtain this – in jOOQ, whenever you can omit the usage of aliases, do it! This way, you have better a chance to obtain clean expressions, as shown here:
ctx.select(OFFICE.CITY, DEPARTMENT.NAME, DEPARTMENT.PROFIT)
.from(OFFICE)
.join(DEPARTMENT)
.on(OFFICE.OFFICE_CODE.eq(DEPARTMENT.OFFICE_CODE))
.fetch();
This is a clean and readable jOOQ snippet of code. Since jOOQ generates the SQL on our behalf, we don't feel the need to add some aliases to increase readability or reduce the typing time.
Nevertheless, next, let's add the proper aliases to obtain the second SQL. As our first attempt, we may have written this:
ctx.select(field("t1.city"), field("t2.name"), field("t2.profit")) .from(OFFICE.as("t1")) .join(DEPARTMENT.as("t2")) .on(field("t1.office_code").eq(field("t2.office_code"))).fetch();
So, we have associated the t1 alias with the OFFICE table via OFFICE.as("t1"), and the t2 alias with the DEPARTMENT table via DEPARTMENT.as("t2"). Furthermore, we used our aliases via the field() method as t1 and t2, respectively. Besides losing some readability in the jOOQ code, have you spotted other issues in this code compared to the jOOQ code without aliases? Sure you did – it's not type-safe and it renders unquoted identifiers.
When we say field("t1.city"), jOOQ renders t1.city, not `t1`.`city` (in MySQL). However, it is advisable to strive for qualified and quoted identifiers to avoid name conflicts and potential errors (for instance, using a keyword such as ORDER as an unquoted table name leads to errors). Generally speaking, quoted identifiers allow us to use reserved names as object names (for instance, ORDER), use special characters in object names (whitespaces and so on), and instructs (most databases) us to treat case-insensitive identifiers as case-sensitive ones (for example, "address" and "ADDRESS" are different identifiers, whereas address and ADDRESS are not).
However, jOOQ can render qualified and quoted identifiers if we rely on explicitly using DSL.name(), which is a very handy static method that comes in several flavors and it is useful for constructing SQL-injection-safe, syntax-safe SQL identifiers for use in plain SQL. It is commonly used in the table() and field() methods – for example, name(table_name, field_name) – but you can check out all the flavors in the documentation. The following table represents what jOOQ renders for different usages of the name() method and different databases:
Figure 16.2 – Using jOOQ name()
When an identifier occurs several times, it can be extracted in a local variable as a Name and reused in queries as needed, like so:
Name orderId = name("ORDER", "ORDER_ID");Field orderId = field(name("ORDER", "ORDER_ID"));Table t = table(name("ORDER"));When jOOQ evaluates name("ORDER", "ORDER_ID") (for MySQL), it renders `ORDER`.`ORDER_ID`. Of course, ORDER_ID doesn't necessarily need the back ticks – only ORDER does. Playing with quotations for identifiers can be done via quotedName() and unquotedName() of the DSL class, like so:
// `ORDER`.ORDER_ID
Name orderId = name(quotedName("ORDER"), unquotedName("ORDER_ID"));Moreover, jOOQ allows us to control (globally or at the query level) how identifiers are quoted via the RenderQuotedNames setting and cases via the RenderNameCase setting. For instance, we can instruct jOOQ to quote all the identifiers in the upper part of the current query, as follows:
For MySQL, jOOQ render this SQL:
select `T`.`FIRST_NAME` as `FN`, `T`.`LAST_NAME` as `LN`
from `CLASSICMODELS`.`EMPLOYEE` as `T`
ctx.configuration().derive(new Settings()
.withRenderQuotedNames(RenderQuotedNames.ALWAYS)
.withRenderNameCase(RenderNameCase.UPPER))
.dsl()
.select(field(name("t", "first_name")).as("fn"), field(name("t", "last_name")).as("ln")) .from(EMPLOYEE.as("t")).fetch();
While you can find more details about these settings in the documentation (https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-name-style/), keep in mind that they only affect identifiers that are expressed via Java-based schemas or name(). In other words, they have no effect on field("identifier") and table("identifier"). These are rendered exactly as you provide them.
jOOQ doesn't force us in any way to use quoting, qualifications, and cases consistently in the same query or across multiple queries (since jOOQ renders by default). However, juggling these aspects may lead to issues, from inconsistent results to SQL errors. This happens because, in some databases (for instance, SQL Server), identifiers are always case insensitive. This means that quoting only helps to allow special characters or escape keywords in identifiers. In other databases (for instance, Oracle), identifiers are only case insensitive if they are unquoted, while quoted identifiers are case sensitive. However, there are also databases (for instance, Sybase ASE) where identifiers are always case sensitive, regardless of them being quoted. Again, quoting only helps to allow special characters or escape keywords in identifiers. And, let's not forget the dialects (for instance, MySQL) that mix the preceding rules, depending on the operating system, object type, configuration, and other events.
So, pay attention to how you decide to handle quoting, qualification, and case sensitivity aspects. The best/safest way is to express queries via the Java-based schema, use aliases only when they are mandatory, and always use name() if you have to refer to identifiers as plain strings. From that point on, let jOOQ do the rest.
That being said, if we apply name() to our query, we obtain the following code:
ctx.select(field(name("t1", "city")), field(name("t2", "name")), field(name("t2", "profit"))) .from(OFFICE.as(name("t1"))) .join(DEPARTMENT.as(name("t2"))) .on(field(name("t1", "office_code")) .eq(field(name("t2", "office_code")))).fetch();
This time, the rendered identifiers correspond to our expectations, but this snippet of jOOQ code is still not type-safe. To transform this non-type-safe query into a type-safe one, we must extract the aliases and define them in local variables before using them, as follows (notice that there is no reason to explicitly use name()):
Office t1 = OFFICE.as("t1");Department t2 = DEPARTMENT.as("t2");ctx.select(t1.CITY, t2.NAME, t2.PROFIT)
.from(t1)
.join(t2)
.on(t1.OFFICE_CODE.eq(t2.OFFICE_CODE))
.fetch();
Alternatively, you may prefer a minimalist aliasing approach, as follows:
Department t = DEPARTMENT.as("t"); // or, Department t = DEPARTMENT;
ctx.select(OFFICE.CITY, t.NAME, t.PROFIT)
.from(OFFICE)
.join(t)
.on(OFFICE.OFFICE_CODE.eq(t.OFFICE_CODE))
.fetch();
Calling the as() method on the generated tables (here, on OFFICE and DEPARTMENT) returns an object of the same type as the table (jooq.generated.tables.Office and jooq.generated.tables.Department). The resulting object can be used to dereference fields from the aliased table in a type-safe way. So, thanks to as(), Office, and Department, we are type-safe again while using the desired table aliases. And, of course, the identifiers are implicitly rendered, quoted, and qualified.
Important Note
As a rule of thumb, in jOOQ, strive to extract and declare aliases in local variables before using them in queries. Do this especially if your aliases refer to the generated tables, are aliases that should be reused across multiple queries, you wish to increase the readability of the jOOQ expression and/or avoid typos, and so on. Of course, if your jOOQ expression simply associates some aliases to columns (to take full control of the column names that are generated in your SQL), then extracting them as local variables won't produce a significant improvement.
Let's look at a table that's been aliased as follows:
Table<Record1<String>> t3 =
ctx.select(t1.CITY).from(t1).asTable("t3");In this case, we can refer to fields in a non-type-safe manner via field(Name name), as shown in the following example:
ctx.select(t3.field(name("city")), CUSTOMERDETAIL.CUSTOMER_NUMBER)
.from(t3)
.join(CUSTOMERDETAIL)
.on(t3.field(name("city"), String.class).eq(CUSTOMERDETAIL.CITY))
.fetch();
The same field() method can be applied to any type-unsafe aliased table as Table<?> to return Field<?>.
In this case, we can make these fields look type-safe via the <T> Field<T> field(Field<T> field) method as well (introduced in Chapter 14, Derived Tables, CTEs, and Views). The t3.field(name("city")) expression indirectly refers to the t1.CITY field, so we can rewrite our queries in a type-safe manner, as follows:
ctx.select(t3.field(t1.CITY), CUSTOMERDETAIL.CUSTOMER_NUMBER)
.from(t3)
.join(CUSTOMERDETAIL)
.on(t3.field(t1.CITY).eq(CUSTOMERDETAIL.CITY))
.fetch();
However, remember that Table.field(Field<T>):Field<T> just looks type-safe. It's as good as an unsafe cast in Java because the lookup only considers the identifier, not the type. Nor does it coerce the expression.
So far, so good! You can practice these examples in AliasesSamples. Now, let's take some time to cover several fundamental aspects of jOOQ aliases and practice some simple but essential exercises.
Aliases and GROUP BY/ORDER BY
Let's consider the following SQL expressed in SQL Server:
SELECT [classicmodels].[dbo].[product].[product_line] [pl]
FROM [classicmodels].[dbo].[product]
GROUP BY [classicmodels].[dbo].[product].[product_line]
ORDER BY [pl]
This query uses an alias named pl for the PRODUCT_LINE column. Attempting to express this query via jOOQ, based on what we learned earlier, may result in something like this:
Field<String> pl = PRODUCT.PRODUCT_LINE.as("pl"); ctx.select(pl)
.from(PRODUCT)
.groupBy(pl)
.orderBy(pl)
.fetch();
But this isn't correct! The problem here is related to our expectations. We expect PRODUCT.PRODUCT_LINE.as("pl") to produce [pl] in ORDER BY, [classicmodels].[dbo].[product].[product_line] in GROUP BY, and [classicmodels].[dbo].[product].[product_line] [pl] in SELECT. In other words, we expect that the three usages of the local pl variable will magically render the output that makes more sense for us. Well, this isn't true!
Think about the jOOQ DSL more as an expression tree. So, we can store both PRODUCT.PRODUCT_LINE and PRODUCT.PRODUCT_LINE.as("pl") in separate local variables, and explicitly reuse the one that makes the most sense:
Field<String> pl1 = PRODUCT.PRODUCT_LINE.as("pl"); Field<String> pl2 = PRODUCT.PRODUCT_LINE;
ctx.select(pl1)
.from(PRODUCT)
.groupBy(pl2)
.orderBy(pl1)
.fetch();
This time, it is correct!
Important Note
Reusing the x.as("y") expression in a query and thinking that it "magically" produces x or y, whatever makes more sense, is a really bad understanding of jOOQ aliases. Thinking that x.as("y") generates x in GROUP BY, y in ORDER BY, and x.as("y") in SELECT is dangerous logic that will give you headaches. The aliased expression, x.as("y"), produces y "everywhere" outside of SELECT, and it produces the alias declaration in SELECT (but only immediately in SELECT). It "never" produces only x.
You can practice these examples in AliasesSamples.
Aliases and bad assumptions
Next, let's consider the examples shown in the following screenshot:
Figure 16.3 – Aliases use case
What can you say about (A) and (B)? If you said that (A) is correct while (B) is wrong, then you are right. Congratulations! Speaking about (B), since we assigned an alias to the [office] table, the [office].[city] column becomes unknown. The rendered SQL highlights the following aspect:
SELECT [classicmodels].[dbo].[office].[city]
FROM [classicmodels].[dbo].[office] [t]
So, one simple and straightforward solution is to simply remove the alias. Now, let's examine a few bad choices. First, let's explore this one:
// SELECT t FROM [classicmodels].[dbo].[office] [t]
ctx.select(field("t", "city")) .from(OFFICE.as("t")).fetch();
This construction is based on the assumption that jOOQ exposes a method, field(String table_name, String field_name), but there is no such method! Then why does the preceding code compile? Because DSL exposes a field(String sql, Object... bindings) that is used for SQL templating, it's being used in the wrong context. Pay attention to such silly mistakes! Who didn't feel lucky and tried to use an API without reading the documentation?!
Now, how about this one?
// SELECT [t].[office_code], [t].[city], ..., [t].[location]
// FROM [classicmodels].[dbo].[office] [t]
ctx.select(table("t").field("city")) .from(OFFICE.as("t")).fetch();
This is just another example built on wrong assumptions. While jOOQ exposes a table(String sql), which is useful for returning a table that wraps the given plain SQL, this example assumes the existence of a table(String alias) that returns a table that wraps an alias and is aware of its fields.
Going further, let's try this approach:
// SELECT [city] FROM [classicmodels].[dbo].[office] [t]
ctx.select(field(name("city"))) .from(OFFICE.as("t")).fetch();
This approach works just fine but you must be aware that the unqualified [city] is prone to ambiguities. For instance, let's say that we enrich this query, as follows:
ctx.select(field(name("city"))) .from(OFFICE.as("t1"), CUSTOMERDETAIL.as("t2")).fetch();
This leads to an ambiguous column, [city], because it's unclear if we are referring to OFFICE.CITY or CUSTOMERDETAIL.CITY. In this case, table aliases can help us express this clearly:
ctx.select(field(name("t1", "city")).as("city_office"), field(name("t2", "city")).as("city_customer")) .from(OFFICE.as("t1"), CUSTOMERDETAIL.as("t2")).fetch();
It's much better to declare aliases before using them:
Office t1 = OFFICE.as("t1");Customerdetail t2 = CUSTOMERDETAIL.as("t2");ctx.select(t1.CITY, t2.CITY)
.from(t1, t2)
.fetch();
Field<String> c1 = t1.CITY.as("city_office");Field<String> c2 = t2.CITY.as("city_customer");ctx.select(c1, c2)
.from(t1, t2)
.fetch();
Now, let's look at another case and start with two wrong approaches. So, what's wrong here?
ctx.select()
.from(OFFICE
.leftOuterJoin(DEPARTMENT)
.on(OFFICE.OFFICE_CODE.eq(DEPARTMENT.OFFICE_CODE)))
.innerJoin(EMPLOYEE)
.on(EMPLOYEE.OFFICE_CODE.eq(
field(name("office_code"), String.class))).fetch();
After joining OFFICE and DEPARTMENT, the result contains two columns named office_code – one from OFFICE and another from DEPARTMENT. Joining this result with EMPLOYEE reveals that the office_code column in the ON clause is ambiguous. To remove this ambiguity, we can use aliased tables:
ctx.select()
.from(OFFICE.as("o") .leftOuterJoin(DEPARTMENT.as("d")) .on(field(name("o","office_code")) .eq(field(name("d","office_code"))))).innerJoin(EMPLOYEE)
.on(EMPLOYEE.OFFICE_CODE.eq(OFFICE.OFFICE_CODE))
.fetch();
Is this correct? This time, we have associated aliases with our OFFICE.as("o") and DEPARTMENT.as("d") tables. While joining OFFICE with DEPARTMENT, we correctly used the aliases, but when we joined the result to EMPLOYEE, we didn't use the OFFICE alias – we used the un-aliased OFFICE.OFFICE_CODE. This is rendered in MySQL as `classicmodels`.`office`.`office_code` and it represents an unknown column in the ON clause. So, the correct expression is as follows:
ctx.select()
.from(OFFICE.as("o") .leftOuterJoin(DEPARTMENT.as("d")) .on(field(name("o","office_code")) .eq(field(name("d","office_code"))))).innerJoin(EMPLOYEE)
.on(EMPLOYEE.OFFICE_CODE
.eq(field(name("o","office_code"), String.class))).fetch();
Can we write this more compact and type-safe? Sure we can – via local variables:
Office o = OFFICE.as("o");Department d = DEPARTMENT.as("d");ctx.select()
.from(o.leftOuterJoin(d)
.on(o.OFFICE_CODE.eq(d.OFFICE_CODE)))
.innerJoin(EMPLOYEE)
.on(EMPLOYEE.OFFICE_CODE.eq(o.OFFICE_CODE))
.fetch();
Again, local variables help us express aliases and obtain elegant code.
Aliases and typos
Next, let's look at another way to extract aliases in local variables. Check out the following code:
ctx.select(field("s1.msrp"), field("s2.msrp")) .from(PRODUCT.as("s1"), PRODUCT.as("s2")) .where(field("s1.msrp").lt(field("s2.msrp")) .and(field("s1.product_line").eq("s2.product_line"))) .groupBy(field("s1.msrp"), field("s2.msrp")) .having(count().eq(selectCount().from(PRODUCT.as("s3")) .where(field("s3.msrp").eq(field("s1.msrp")))) .and(count().eq(selectCount().from(PRODUCT.as("s4")) .where(field("s4.msrp").eq(field("s2.msrp")))))).fetch();
There is a mistake (a typo) in this expression. Can you spot it? (It isn't easy!) If not, you'll end up with valid SQL that returns inaccurate results. The typo snuck into the .and(field("s1.product_line").eq("s2.product_line"))) part of the code, which should be .and(field("s1.product_line").eq(field("s2.product_line")))). But if we extract the aliases in local variables, then the code eliminates the risk of a typo and increases the readability of the expression (notice that s1, s2, s3, and s4 are not equal objects and that they cannot be used interchangeably):
Product s1 = PRODUCT.as("s1");Product s2 = PRODUCT.as("s2");Product s3 = PRODUCT.as("s3");Product s4 = PRODUCT.as("s4");ctx.select(s1.MSRP, s2.MSRP)
.from(s1, s2)
.where(s1.MSRP.lt(s2.MSRP)
.and(s1.PRODUCT_LINE.eq(s2.PRODUCT_LINE)))
.groupBy(s1.MSRP, s2.MSRP)
.having(count().eq(selectCount().from(s3)
.where(s3.MSRP.eq(s1.MSRP)))
.and(count().eq(selectCount().from(s4)
.where(s4.MSRP.eq(s2.MSRP)))))
.fetch();
You can practice these examples in AliasesSamples.
Aliases and derived tables
Let's look at another example that starts with the following snippet of code:
ctx.select().from(
select(CUSTOMER.CUSTOMER_NUMBER,
CUSTOMER.CUSTOMER_NAME, field("t.invoice_amount")).from(CUSTOMER)
.join(select(PAYMENT.CUSTOMER_NUMBER,
PAYMENT.INVOICE_AMOUNT)
.from(PAYMENT).asTable("t")) .on(field("t.customer_number").eq(CUSTOMER.CUSTOMER_NUMBER)))
.fetch();
So, what's wrong here?! Let's inspect the generated SQL (this is for MySQL):
SELECT `alias_84938429`.`customer_number`,
`alias_84938429`.`customer_name`,
`alias_84938429`.t.invoice_amount
FROM
(SELECT `classicmodels`.`customer`.`customer_number`,
`classicmodels`.`customer`.`customer_name`,
t.invoice_amount
FROM `classicmodels`.`customer`
JOIN
(SELECT `classicmodels`.`payment`.`customer_number`,
`classicmodels`.`payment`.`invoice_amount`
FROM `classicmodels`.`payment`) AS `t` ON
t.customer_number =
`classicmodels`.`customer`.`customer_number`)
AS `alias_84938429
As you can see, jOOQ has automatically associated an alias with the derived table (alias_84938429) that was obtained from JOIN and used this alias to reference customer_number, customer_name, and invoice_amount. While customer_number and customer_name are correctly qualified and quoted, invoice_amount has been incorrectly rendered as t.invoice_amount. The problem is in field("t.invoice_amount"), which instructs jOOQ that the column name is t.invoice_amount, not invoice_amount, so the resulting `alias_84938429`.t.invoice_amount is an unknown column.
There are a few solutions to this problem, and one of them consists of using name() for proper quoting and qualifying:
ctx.select().from(select(CUSTOMER.CUSTOMER_NUMBER,
CUSTOMER.CUSTOMER_NAME, field(name("t", "invoice_amount"))).from(CUSTOMER)
.join(
select(PAYMENT.CUSTOMER_NUMBER,
PAYMENT.INVOICE_AMOUNT)
.from(PAYMENT).asTable("t")) .on(field(name("t", "customer_number")).eq(CUSTOMER.CUSTOMER_NUMBER)))
.fetch();
This time, jOOQ renders `alias_10104609`.`invoice_amount`. In the bundled code, you can see four more solutions to this problem.
To understand this context, let's check out the following example:
ctx.select()
.from(select(EMPLOYEE.EMPLOYEE_NUMBER.as("en"), EMPLOYEE.SALARY.as("sal")).from(EMPLOYEE)
.where(EMPLOYEE.MONTHLY_BONUS.isNull()))
.innerJoin(SALE)
.on(field(name("en")).eq(SALE.EMPLOYEE_NUMBER))
.fetch();
Here, we have explicitly associated column aliases with the inner SELECT, but we did not associate an alias with the derived table produced by JOIN. These aliases are further used to reference the columns outside this SELECT (in the outer SELECT). Notice that we let jOOQ qualify these aliases to the generated alias for the divided table accordingly:
SELECT `alias_41049514`.`en`,
`alias_41049514`.`sal`,
`classicmodels`.`sale`.`sale_id`,
...
FROM
(SELECT `classicmodels`.`employee`.`employee_number`
AS `en`, `classicmodels`.`employee`.`salary` AS `sal`
FROM `classicmodels`.`employee`
WHERE `classicmodels`.`employee`.`monthly_bonus` IS NULL
) AS `alias_41049514`
JOIN `classicmodels`.`sale` ON `en` =
`classicmodels`.`sale`.`employee_number`
If we want to control the alias of the derived table as well, then we can do the following:
ctx.select(SALE.SALE_, SALE.FISCAL_YEAR,
field(name("t", "sal"))) .from(select(EMPLOYEE.EMPLOYEE_NUMBER.as("en"), EMPLOYEE.SALARY.as("sal")).from(EMPLOYEE)
.where(EMPLOYEE.MONTHLY_BONUS.isNull())
.asTable("t")).innerJoin(SALE)
.on(field(name("t", "en")).eq(SALE.EMPLOYEE_NUMBER))
.fetch();
This time, the rendered SQL uses our table alias:
SELECT `classicmodels`.`sale`.`sale`,
`classicmodels`.`sale`.`fiscal_year`,
`t`.`sal`
FROM
(SELECT `classicmodels`.`employee`.`employee_number`
AS `en`, `classicmodels`.`employee`.`salary` AS `sal`
FROM `classicmodels`.`employee`
WHERE `classicmodels`.`employee`.`monthly_bonus`
IS NULL) AS `t`
JOIN `classicmodels`.`sale` ON `t`.`en` =
`classicmodels`.`sale`.`employee_number`
Finally, here is a more verbose example of using aliases:
ctx.select(field(name("t2", "s")).as("c1"), field(name("t2", "y")).as("c2"), field(name("t2", "i")).as("c3")) .from(select(SALE.SALE_.as("s"), SALE.FISCAL_YEAR.as("y"), field(name("t1", "emp_sal")).as("i")) .from(select(EMPLOYEE.EMPLOYEE_NUMBER.as("emp_nr"), EMPLOYEE.SALARY.as("emp_sal")).from(EMPLOYEE)
.where(EMPLOYEE.MONTHLY_BONUS.isNull())
.asTable("t1")).innerJoin(SALE)
.on(field(name("t1","emp_nr")) .eq(SALE.EMPLOYEE_NUMBER)).asTable("t2")).fetch();
Take your time to analyze this expression and the generated SQL:
SELECT `t2`.`s` AS `c1`,
`t2`.`y` AS `c2`,
`t2`.`i` AS `c3`
FROM
(SELECT `classicmodels`.`sale`.`sale` AS `s`,
`classicmodels`.`sale`.`fiscal_year` AS `y`,
`t1`.`emp_sal` AS `i`
FROM
(SELECT `classicmodels`.`employee`.`employee_number`
AS `emp_nr`,
`classicmodels`.`employee`.`salary`
AS `emp_sal`
FROM `classicmodels`.`employee`
WHERE `classicmodels`.`employee`.`monthly_bonus`
IS NULL) AS `t1`
JOIN `classicmodels`.`sale` ON `t1`.`emp_nr` =
`classicmodels`.`sale`.`employee_number`) AS `t2`
Now, let's look at a few more examples of using aliases.
Derived column list
When column names are not known in advance (but the table's degree is!), we can use the so-called derived column list. You saw many examples of using this feature with unnested tables, so here are two more for the VALUES() table constructor and a regular table:
ctx.select().from(values(row("A", "John", 4333, false)) .as("T", "A", "B", "C", "D")).fetch();The following code is for a regular table:
ctx.select(min(field(name("t", "rdate"))).as("cluster_start"), max(field(name("t", "rdate"))).as("cluster_end"), min(field(name("t", "status"))).as("cluster_status")).from(select(ORDER.REQUIRED_DATE, ORDER.STATUS,
rowNumber().over().orderBy(ORDER.REQUIRED_DATE)
.minus(rowNumber().over().partitionBy(ORDER.STATUS)
.orderBy(ORDER.REQUIRED_DATE)))
.from(ORDER)
.asTable("t", "rdate", "status", "cluster")) .groupBy(field(name("t", "cluster"))).orderBy(1)
.fetch();
If you are not familiar with these kinds of aliases, take your time to inspect the rendered SQL and read some documentation.
Aliases and the CASE expression
Aliases can be used with CASE expressions as well. Here is an example:
ctx.select(EMPLOYEE.SALARY,
count(case_().when(EMPLOYEE.SALARY
.gt(0).and(EMPLOYEE.SALARY.lt(50000)), 1)).as("< 50000"),count(case_().when(EMPLOYEE.SALARY.gt(50000)
.and(EMPLOYEE.SALARY.lt(100000)), 1)).as("50000 - 100000"),count(case_().when(EMPLOYEE.SALARY
.gt(100000), 1)).as("> 100000")).from(EMPLOYEE)
.groupBy(EMPLOYEE.SALARY)
.fetch();
They can also be used in FILTER WHERE expressions:
ctx.select(EMPLOYEE.SALARY,
count().filterWhere(EMPLOYEE.SALARY
.gt(0).and(EMPLOYEE.SALARY.lt(50000))).as("< 50000"),count().filterWhere(EMPLOYEE.SALARY.gt(50000)
.and(EMPLOYEE.SALARY.lt(100000))).as("50000 - 100000"),count().filterWhere(EMPLOYEE.SALARY
.gt(100000)).as("> 100000")) .from(EMPLOYEE)
.groupBy(EMPLOYEE.SALARY)
.fetch();
As you can see, using aliases in CASE/FILTER expressions is quite handy since it allows us to express the meaning of each case better.
Aliases and IS NOT NULL
Aliases can be used with IS NOT NULL (and companions) if we wrap our Condition in field() to obtain a Field<Boolean>:
ctx.select(EMPLOYEE.FIRST_NAME,
EMPLOYEE.LAST_NAME, EMPLOYEE.COMMISSION,
field(EMPLOYEE.COMMISSION.isNotNull()).as("C")).from(EMPLOYEE)
.fetch();
Finally, let's take a quick look at aliases and CTEs.
Aliases and CTEs
In Chapter 14, Derived Tables, CTEs, and Views, we looked at tons of examples of using aliases in CTEs and derived tables, so please consider that chapter if you wish to become familiar with this topic. Next, let's talk about SQL templating.
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(String sql, DataType<T> type, QueryPart... parts)
field/table(String sql, QueryPart... parts)
from/where/join …(String string)
from/where/join …(String string, Object... os)
from/where/join …(String string, QueryPart... qps)
So, we can pass SQL as follows:
- Plain SQL strings
- Plain SQL strings and bindings (?)
- Plain SQL strings and QueryPart ({0}, {1}, …)
Binding and the query part argument overloads use the so-called Plain SQL Templating Language.
Here are several examples of using plain SQL with bind values (these examples are available in SQLTemplating):
ctx.fetch("""SELECT first_name, last_name
FROM employee WHERE salary > ? AND job_title = ?
""", 5000, "Sales Rep");
ctx.resultQuery("""SELECT first_name, last_name
FROM employee WHERE salary > ? AND job_title = ?
""", 5000, "Sales Rep")
.fetch();
ctx.query("""UPDATE product SET product.quantity_in_stock = ?
WHERE product.product_id = ?
""", 0, 2)
.execute();
ctx.queries(query(""), query(""), query("")).executeBatch();
Now, let's look at some examples to help you become familiar with the technique of mixing plain SQL with SQL expressed via DSL. Let's consider the following MySQL query:
SELECT `classicmodels`.`office`.`office_code`,
...
`classicmodels`.`customerdetail`.`customer_number`,
...
FROM `classicmodels`.`office`
JOIN `classicmodels`.`customerdetail`
ON `classicmodels`.`office`.`postal_code` =
`classicmodels`.`customerdetail`.`postal_code`
WHERE not((
`classicmodels`.`office`.`city`,
`classicmodels`.`office`.`country`)
<=> (`classicmodels`.`customerdetail`.`city`,
`classicmodels`.`customerdetail`.`country`))
If you are a jOOQ novice and you're trying to express this query via the jOOQ DSL, then you'll probably encounter some issues in the highlighted code. Can we express that part via the DSL? The answer is yes, but if we cannot find the proper solution, then we can embed it as plain SQL as well. Here is the code:
ctx.select()
.from(OFFICE)
.innerJoin(CUSTOMERDETAIL)
.on(OFFICE.POSTAL_CODE.eq(CUSTOMERDETAIL.POSTAL_CODE))
.where("""not(
(
`classicmodels`.`office`.`city`,
`classicmodels`.`office`.`country`
) <=> (
`classicmodels`.`customerdetail`.`city`,
`classicmodels`.`customerdetail`.`country`
)
)
""")
.fetch();
Done! Of course, once you become more familiar with the jOOQ DSL, you'll be able to express this query 100% via the DSL, and let jOOQ emulate it accordingly (much better!):
ctx.select()
.from(OFFICE)
.innerJoin(CUSTOMERDETAIL)
.on(OFFICE.POSTAL_CODE.eq(CUSTOMERDETAIL.POSTAL_CODE))
.where(row(OFFICE.CITY, OFFICE.COUNTRY)
.isDistinctFrom(row(
CUSTOMERDETAIL.CITY, CUSTOMERDETAIL.COUNTRY)))
.fetch();
But sometimes, you'll need SQL templating. For instance, MySQL defines a function, CONCAT_WS(separator, exp1, exp2, exp3,...), that adds two or more expressions together with the given separator. This function doesn't have a jOOQ correspondent, so we can use it via SQL templating (here, plain SQL and query parts), as follows:
ctx.select(PRODUCT.PRODUCT_NAME,
field("CONCAT_WS({0}, {1}, {2})", String.class, val("-"), PRODUCT.BUY_PRICE, PRODUCT.MSRP))
.from(PRODUCT)
.fetch();
Since the number of parts to concatenate can vary, it will be more practical to rely on the convenient DSL.list(QueryPart...), which allows us to define a comma-separated list of query parts in a single template argument:
ctx.select(PRODUCT.PRODUCT_NAME,
field("CONCAT_WS({0}, {1})", String.class, val("-"), list(PRODUCT.BUY_PRICE, PRODUCT.MSRP)))
.from(PRODUCT)
.fetch();
This time, the template argument, {1}, has been replaced with the list of strings that should be concatenated. Now, you can simply pass that list.
The jOOQ DSL also doesn't support MySQL variables (@variable). For instance, how would you express the following MySQL query, which uses the @type and @num variables?
SELECT `classicmodels`.`employee`.`job_title`,
`classicmodels`.`employee`.`salary`,
@num := if(@type = `classicmodels`.`employee`.
`job_title`, @num + 1, 1) AS `rn`,
@type := `classicmodels`.`employee`.`job_title` AS `dummy`
FROM `classicmodels`.`employee`
ORDER BY `classicmodels`.`employee`.`job_title`,
`classicmodels`.`employee`.`salary`
Here's SQL templating to the rescue:
ctx.select(EMPLOYEE.JOB_TITLE, EMPLOYEE.SALARY,
field("@num := if(@type = {0}, @num + 1, 1)", EMPLOYEE.JOB_TITLE).as("rn"), field("@type := {0}", EMPLOYEE.JOB_TITLE).as("dummy")).from(EMPLOYEE)
.orderBy(EMPLOYEE.JOB_TITLE, EMPLOYEE.SALARY)
.fetch();
You can practice these examples, along with others, in SQLTemplating.
SQL templating is also useful when we need to work with certain data types, such as the PostgreSQL HSTORE data type. We know that jOOQ allows us to define converters and bindings, especially for dealing with such types. In Chapter 7, Types, Converters, and Bindings, we wrote an org.jooq.Converter and an org.jooq.Binding for the HSTORE data type. Moreover, the jooq-postgres-extensions module supports HSTORE as well.
However, using SQL templating can represent a quick solution as well – for instance, you may only need to write a few queries and you don't have time to write a converter/binding. We can insert this into our HSTORE (PRODUCT.SPECS) via SQL templating, as follows:
ctx.insertInto(PRODUCT, PRODUCT.PRODUCT_NAME,
PRODUCT.PRODUCT_LINE, PRODUCT.CODE, PRODUCT.SPECS)
.values("2002 Masserati Levante", "Classic Cars", 599302L, field("?::hstore", String.class, HStoreConverter.toString(Map.of("Length (in)","197", "Width (in)", "77.5", "Height (in)",
"66.1", "Engine", "Twin Turbo Premium Unleaded
V-6"))))
.execute();
We can select everything from a HSTORE like so:
List<Map<String, String>> specs =
ctx.select(PRODUCT.SPECS.coerce(String.class))
.from(PRODUCT)
.where(PRODUCT.PRODUCT_NAME.eq("2002 Masserati Levante")) .fetch(rs -> {return HStoreConverter.fromString(
rs.getValue(PRODUCT.SPECS).toString());
});
Notice that both examples rely on org.postgresql.util.HStoreConverter.
Other operations that are performed against an HSTORE rely on vendor-specific operators. Using such operators is a perfect job for SQL templating. For instance, getting an HSTORE entry by its key can be done by respecting the PostgreSQL syntax, as shown here:
ctx.select(PRODUCT.PRODUCT_ID, PRODUCT.PRODUCT_NAME,
field("{0} -> {1}", String.class, PRODUCT.SPECS, val("Engine")).as("engine")).from(PRODUCT)
.where(field("{0} -> {1}", String.class, PRODUCT.SPECS, val("Length (in)")).eq("197")).fetch();
Alternatively, we can delete entries by key, as shown here:
ctx.update(PRODUCT)
.set(PRODUCT.SPECS, (field("delete({0}, {1})", Record.class, PRODUCT.SPECS, val("Engine")))).execute();
We can also convert an HSTORE into JSON:
ctx.select(PRODUCT.PRODUCT_NAME,
field("hstore_to_json ({0}) json", PRODUCT.SPECS)).from(PRODUCT)
.fetch();
More examples are available in the bundled code – SQLTemplating for PostgreSQL. If you need these operators more often, then you should retrieve their SQL templating code in static/utility methods and simply call those methods. For example, a get-by-key method can be expressed as follows:
public static Field<String> getByKey(
Field<Map<String, String>> hstore, String key) { return field("{0} -> {1}", String.class, hstore, val(key));}
We can also define CTE via SQL templating. Here is an example of defining a CTE via ResultQuery and SQL templating:
Result<Record1<BigDecimal>> msrps = ctx.resultQuery(
"with "updatedMsrp" as ({0}) {1}",update(PRODUCT).set(PRODUCT.MSRP,
PRODUCT.MSRP.plus(PRODUCT.MSRP.mul(0.25)))
.returning(PRODUCT.MSRP),
select().from(name("updatedMsrp"))).coerce(PRODUCT.MSRP)
.fetch();
This code still uses ResultQuery and SQL templating, but this time, the plain SQL looks as follows:
Result<Record1<BigDecimal>> msrps = ctx.resultQuery(
"with "updatedMsrp" as ({0}) {1}", resultQuery("""update
"public"."product"
set
"msrp" = (
"public"."product"."msrp" + (
"public"."product"."msrp" * 0.25
)
) returning "public"."product"."msrp"
"""),
resultQuery("""select *
from "updatedMsrp"
"""))
.coerce(PRODUCT.MSRP)
.fetch();
More examples are available in SQLTemplating for PostgreSQL.
How about calling some SQL Server functions? Let's try to call a function that returns an integer that measures the difference between the SOUNDEX() values of two different character expressions. Yes – the DIFFERENCE() function:
ctx.select(field("DIFFERENCE({0}, {1})", SQLDataType.INTEGER, "Juice", "Jucy"))
.fetch();
How about calling the FORMAT() function?
ctx.select(field("FORMAT({0}, {1})", 123456789, "##-##-#####"))
.fetch();
Now, let's try the following SQL Server batch, which uses SQL Server local variables:
DECLARE @var1 VARCHAR(70)
select @var1=(select
[classicmodels].[dbo].[product].[product_name]
from [classicmodels].[dbo].[product]
where [classicmodels].[dbo].[product].[product_id] = 1)
update [classicmodels].[dbo].[product]
set [classicmodels].[dbo].[product].[quantity_in_stock] = 0
where [classicmodels].[dbo].[product].[product_name] = @var1
Again, combining SQL and SQL templating comes to the rescue:
ctx.batch(
query("DECLARE @var1 VARCHAR(70)"), select(field("@var1=({0})", select(PRODUCT.PRODUCT_NAME).from(PRODUCT).where(PRODUCT.PRODUCT_ID.eq(1L)))),
update(PRODUCT).set(PRODUCT.QUANTITY_IN_STOCK, 0)
.where(PRODUCT.PRODUCT_NAME
.eq(field("@var1", String.class)))).execute();
You can practice these examples in SQLTemplating for SQL Server.
So far, we've looked at examples that are specific to MySQL, PostgreSQL, and SQL Server. Finally, let's add one for Oracle. For instance, if you plan to update/delete records that are referenced by a SELECT FOR UPDATE statement, you can use a WHERE CURRENT OF statement. The following example uses SQL templating to build such a SQL sample:
String sql = ctx.resultQuery("{0} WHERE CURRENT OF cur", deleteFrom(PRODUCT)).getSQL();
The SQL is as follows:
delete from "CLASSICMODELS"."PRODUCT" WHERE CURRENT OF cur
You can practice these examples in SQLTemplating for Oracle.
Moreover, especially for those corner cases that require complex SQL clauses, jOOQ exposes a set of classes that are very well exemplified in the official documentation: https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/.
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.