I'd like to add comments to aid debugging large, generated SQL statements
The below works for fields, but is there a more generic form that I can use to also attach comments to joins, order-by/group-bys, etc?
fun org.jooq.Field<*>.comment(comment: String) = DSL.field("/* $comment */ {0}", this.dataType, this)
There have been some feature requests on the issue tracker for related features, including:
#2541 Add support for inserting custom clauses in a Select statement through .sql(String)
This feature request was rejected as it was focused only on the DSL API, not generically on all QueryPart types that are part of an arbitrary expression tree.
#13049 Add new API to maintain comments and other whitespace in the expression tree
This feature is described as being useful mainly for parsing and SQL dialect translation purposes, but obviously, such a feature would be useful to your use-case as well.
Doing this is a big project in jOOQ (currently version 3.17), as jOOQ maintains only the significant parts of a SQL expression tree. Comments and whitespace tend to be insignificant in SQL. Given that they can appear at any possible location, including e.g. between keywords that have no representation in the jOOQ API, it will be difficult to implement #13049 thoroughly. For example, jOOQ has a groupBy() method, but that's 2 keywords in SQL:
GROUP BY
and users might want to put a comment in between them:
GROUP /* comment */ BY
The SQL dialect translation use-case is a strong one in favour of this feature, at least to be implemented to some extent, so this might ship in a not too distant future
The 2 main ways to annotate your SQL with comments are currently:
Field, but you can wrap most popular QueryPart types in a template, including Table (i.e. joins) and others. It's ugly, but it works.ExecuteListener SPI allows you to patch your generated SQL globally with a single Configuration, rather than having to think about this on a per query basis.