All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.12 | Unsupported versions: 3.6 | 3.5 | 3.4 | 3.3 | 3.2
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 = trueCondition();
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(
selectOne().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) as needed.
