Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
Dynamic SQL
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
In most cases, table expressions, column expressions, and conditional expressions as introduced in the previous chapters will be embedded into different SQL statement clauses as if the statement were a static SQL statement (e.g. in a view or stored procedure):
create.select(
AUTHOR.FIRST_NAME.concat(AUTHOR.LAST_NAME),
count()
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.groupBy(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.orderBy(count().desc())
.fetch();
It is, however, interesting to think of all of the above expressions as what they are: expressions. And as such, nothing keeps users from extracting expressions and referencing them from outside the statement. The following statement is exactly equivalent:
SelectField<?>[] select = {
AUTHOR.FIRST_NAME.concat(AUTHOR.LAST_NAME),
count()
};
Table<?> from = AUTHOR.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID));
GroupField[] groupBy = { AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME };
SortField<?>[] orderBy = { count().desc() };
create.select(select)
.from(from)
.groupBy(groupBy)
.orderBy(orderBy)
.fetch();
Each individual expression, and collection of expressions can be seen as an independent entity that can be
- Constructed dynamically
- Reused across queries
Dynamic construction is particularly useful in the case of the WHERE clause, for dynamic predicate building. For instance:
public Condition condition(HttpServletRequest request) {
Condition result = noCondition();
if (request.getParameter("title") != null)
result = result.and(BOOK.TITLE.like("%" + request.getParameter("title") + "%"));
if (request.getParameter("author") != null)
result = result.and(BOOK.AUTHOR_ID.in(
select(AUTHOR.ID).from(AUTHOR).where(
AUTHOR.FIRST_NAME.like("%" + request.getParameter("author") + "%")
.or(AUTHOR.LAST_NAME .like("%" + request.getParameter("author") + "%"))
)
));
return result;
}
// And then:
create.select()
.from(BOOK)
.where(condition(httpRequest))
.fetch();
The dynamic SQL building power may be one of the biggest advantages of using a runtime query model like the one offered by jOOQ. Queries can be created dynamically, of arbitrary complexity. In the above example, we've just constructed a dynamic WHERE clause. The same can be done for any other clauses, including dynamic FROM clauses (dynamic JOINs), or adding additional WITH clauses as needed.
The above example made use of an optional condition, a mechanism that is explained in the following sections.
References to this page
- Using column indexes in the GROUP BY clause
- Inline derived tables
- TRUE and FALSE condition
- Optional column expressions used for dynamic SQL
- Optional conditional expressions used for dynamic SQL
- Optional tables used for dynamic SQL
- Duplicate Statements
- Overriding where()
- Don't do this with jOOQ: referencing the step types
- Don't do this in SQL: ORDER BY [column index]
Feedback
Do you have any feedback about this page? We'd love to hear it!