This is a note about jOOQ SQL building based on the official jOOQ document. jOOQ use BNF-based hierarchy DSL to emulate SQL in Java. It enables compile-time checking. jOOQ build a preser internally that consists of Query, ResultQuery, Table, Field, Row, Condition and Name.
1 SQL Statements
Internally, jOOQ uses query parts that delegates SQL rendering and variable binding to child components. Conditional expressions and column expressions are immutable. The constructed statements are muttable.
1.1 The Select Statement
The selectFrom() method fetches all columns from a single table. The select() method projects data. By default, it use Table.asterisk() or DSL.asterisk() to select all columns.
Records and raw value expressions up to degree 22 are typesafe.
The FROM clause specifies a number of table expressions to select data from. selectOne() returns 1 without a from clause.
jOOQ supports many types joins: inner join, left join, right join, full outer join, cross join, natural join and ect. It can be called on org.jooq.Table types or directly after the FROM clause.
InORDER BY clause, use asc() or desc() to transform a column expression into a SortField expression. Order by field index is also supported as one().asc() or inline(2).desc(). Use nullsLast() to put null values to the end. Case expression in order is also supported.
Use limit(m).offset(n) for paging. For better paing performance, use seek().
Use forUpdate() to create a record-lock.
Use union() or unionAll() to combine result sets. Similary, use intersect(), intersectAll(), except() and exceptAll() to process multiple sets.
1.2 The Insert Statement
Use insertInto(...).values(...).values(...) to insert one or more rows. Use defaultValues() to insert default values. jOOQ supports set(Column, value) to insert values. insertInto(table).select(selectFrom(...)) is used to insert from select.
Use onDuplicateKeyUpdate() or onDuplicateKeyIgnore() to support merge (or upsert).
jOOQ supports returnning() after insert.
1.3 The Update Statement
Update statement can only modify a single table. It supports set with select or rwo values expression. Update supports returning().
2 Expressions
2.1 Table Experssion
jOOQ’s can generate all tables from db schema as type safe Java ojbects. These tables can be used in SQL from, join and other places where table can be used.
Use as('alias') method to define a table alias. use field(colnm-name) to access a table column.
A select statement can appear almost anywhere a table expression can.
jOOQ use DUAL table if there is no from clause.
2.2 Column Expression
There are different ways to create a column expression:
|
|
Columns can use aliases. Use cast(SQLDataType.VARCHAR(100)) or cast(String.class) to specify data type. Use coerce() to convert a type. Use collate() to specify a sorting order on a character set.
jOOQ suports arithmatic operations/functions, sting functions, date time functions , system functions, regular expressions, aggregate functions, window functions, group functions, and etc.
2.3 Conditional Expressions
eq(), ne(), or(), and(), lessThan(), between... and etc.
Use all, any, some, in to compare a non-scalar table suquery with a scalar value.
isNull() and isNotNull() to compare null value. For null-safe comparison, use isDistincFrom() or isNotDistinctFrom().
Use QBE (query by examaple) like condition(book) where book is a new book record.
2.4 Dynamic SQL
The table expressions, column expressions and conditional expressions can be constructed dynamically and resused across queries.
jOOQ supports using plain SQL strings in table/column/conditional expressions as well as funciton and query.
For example:
|
|