Chapter 5: Tackling Different Kinds of SELECT, INSERT, UPDATE, DELETE, and MERGE

A common scenario for jOOQ beginners originates from having a plain valid SQL that should be expressed via the jOOQ DSL API. While the jOOQ DSL API is extremely intuitive and easy to learn, the lack of practice may still lead to scenarios where we simply cannot find or intuit the proper DSL methods that should be chained to express a certain SQL.

This chapter addresses this kind of issue via a comprehensive collection of popular queries, which gives you the chance to practice jOOQ DSL syntax based on the Java-based schema. More precisely, our aim is to express, in jOOQ DSL syntax, a carefully harvested list of SELECT, INSERT, UPDATE, DELETE, and MERGE statements that are used in our day-to-day job.

This way, by the end of this chapter, you should have funneled a significant number of SQLs through the jOOQ DSL syntax and tried them out against MySQL, PostgreSQL, SQL Server, and Oracle databases in...

Technical requirements

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

Expressing SELECT statements

In this section, we will express/write via jOOQ DSL syntax a wide range of SELECT statements, including common projections, popular subqueries, scalar and correlated subqueries, unions, and row value expressions. We'll start with the commonly used projections.

Expressing commonly used projections

By commonly used projections, we understand the projections written against the well-known dummy table, DUAL. As you most probably know, the DUAL table is specific to Oracle; it's mostly unnecessary in MySQL (although jOOQ still renders it for MySQL 5.7 compatibility) and doesn't exist in PostgreSQL and SQL Server.

In this context, even if the SQL standard requires a FROM clause, jOOQ never requires such a clause and it renders the DUAL table whenever it is needed/supported. For example, selecting 0 and 1 can be done via the selectZero() and selectOne()methods (these statics are available in org.jooq.impl.DSL). The latter (selectOne())...

Expressing INSERT statements

In this section, we will express different kinds of inserts including INSERT ... VALUES, INSERT ... SET, INSERT ... RETURNING, and INSERT ...DEFAULT VALUES via the jOOQ DSL syntax. Let's start with the well-known INSERT ... VALUES insert, which is supported by most database vendors.

Expressing INSERT ... VALUES

jOOQ supports INSERT ... VALUES via the insertInto() and values() methods. Optionally, we can use the columns() method for separating the name of the table in which we insert from the list of fields/columns that we insert. To trigger the actual INSERT statement, we have to explicitly call execute(); pay attention to this aspect since jOOQ novices tend to forget this call at the end of the insert/update/delete expressions. This method returns the number of rows affected by this INSERT statement as an integer value (0), which means that nothing happened.

For example, the following jOOQ type-safe expression will render an INSERT statement...

Expressing UPDATE statements

In this section, we will express different kinds of updates, including UPDATE ... SET, UPDATE ... FROM, and UPDATE ... RETURNING, and update using row value expressions via the jOOQ DSL syntax. At the time of writing, jOOQ supports updates against a single table, while updates against multiple tables represent a work in progress task.

Expressing UPDATE ... SET

The straightforward UPDATE ... SET statement can be expressed in jOOQ via the set(field, value) method, as in the following example (don't forget to call execute() to trigger the update):

ctx.update(OFFICE)
   .set(OFFICE.CITY, "Banesti")
   .set(OFFICE.COUNTRY, "Romania")
   .where(OFFICE.OFFICE_CODE.eq("1"))
   .execute();

The rendered SQL for MySQL dialect will be as follows:

UPDATE `classicmodels`.`office`
SET `classicmodels`.`office'.`city` = ?,
    `classicmodels...

Expressing DELETE statements

Expressing DELETE statements in jOOQ can be done via the DSLContext.delete() and DSLContext.deleteFrom() API or via DSLContext.deleteQuery() and DSLContext.executeDelete(), respectively. While the first three methods receive an argument of the Table<R> type, the executeDelete()method is useful for deleting a record as TableRecord<?> or UpdatableRecord<?>. As you can see from the following example, delete() and deleteFrom() work exactly the same:

ctx.delete(SALE)
   .where(SALE.FISCAL_YEAR.eq(2003))
   .execute();
ctx.deleteFrom(SALE)
   .where(SALE.FISCAL_YEAR.eq(2003))
   .execute();

Both of these expressions render this SQL:

DELETE FROM `classicmodels`.`sale`
WHERE `classicmodels`.`sale`.`fiscal_year` = ?

Combining DELETE and row value expressions is useful for deleting via subselects, as in the following example:

ctx.deleteFrom(CUSTOMERDETAIL)
   ...

Expressing MERGE statements

The MERGE statement is quite a powerful tool; it allows us to perform INSERT/UPDATE and even DELETE on a table known as the target table from a table known as the source table. I strongly suggest you read this article, especially if you need a quick reminder of the MERGE statement: https://blog.jooq.org/2020/04/10/the-many-flavours-of-the-arcane-sql-merge-statement/.

MySQL and PostgreSQL support a MERGE flavor known as UPSERT (INSERT or UPDATE) via ON DUPLICATE KEY UPDATE, respectively via ON CONFLICT DO UPDATE clauses. You can find examples of these statements next to the well-known INSERT IGNORE INTO (MySQL) and ON CONFLICT DO NOTHING (PostgreSQL) clauses in the code bundled with this book. By the way, we can use all these statements interchangeably (for example, we can use onConflictDoNothing() with MySQL and onDuplicateKeyIgnore() with PostgreSQL), since jOOQ will always emulate the correct syntax. We can even use them with SQL Server and Oracle,...

Summary

This chapter is a comprehensive resource for examples of expressing popular SELECT, INSERT, UPDATE, DELETE, and MERGE statements in the jOOQ DSL syntax relying on the Java-based schema.

For brevity, we couldn't list all the examples here, but I strongly recommend you take each application and practice the examples against your favorite database. The main goal is to get you familiar with the jOOQ syntax and to become capable of expressing any plain SQL via the jOOQ API in a productive amount of time.

In the next chapter, we continue this adventure with a very exciting topic: expressing JOIN in jOOQ.