JOOQ Tooling

[Kevin Jones]

Hey Lukas,

is there a way to take a JOOQ expression (and a dialect) and see what the generated SQL looks like without actually running the 'real' code? i.e. some tool or API I can use to decode the DSL into SQL,

Thanks

Kevin

[Simon Martinelli]

Hi Kevin,

Simon here :-)

You can call getSQL on the query. 

Check out the documentation:

https://www.jooq.org/doc/latest/manual/getting-started/use-cases/jooq-as-a-sql-builder-without-codegeneration/

Simon

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/d6a8a25b-2db4-48a4-a23f-7aae58566af4n%40googlegroups.com.

[Lukas Eder]

Hi Kevin,

Keep in mind that *every* jOOQ query is a dynamic SQL query, even if jOOQ's API tries hard not to make it look this way by mimicking actual SQL syntax.

By dynamic, I mean:

- jOOQ (or an IDE) doesn't really know if any QueryParts are composed dynamically, e.g. like (something ? A.eq(1) : noCondition())

- There can be VisitListener, ExecuteListener, or any other kind of transformation going on

- Even many Settings influence the outcome of a query (e.g. quoting, qualifying, etc. of identifiers)

Of course, it would be possible to write a tool with tons of heuristics about what is a quasi-static query. jOOQ's manual does this, for example. See the "dialect support" section on most manual pages:

https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/string-functions/substring-function/

You can write a simple script like this yourself. Here's one I'm frequently using:

public class Dialects {

public static void main(String[] args) throws Exception {

List<SQLDialect> dialects = Stream

.of(families())

.filter(f -> f != DEFAULT)

.filter(Unchecked.predicate(f -> !SQLDialect.class.getField(f.name()).isAnnotationPresent(Deprecated.class)))

.filter(f -> f.supported())

.sorted(comparing(SQLDialect::name))

.collect(toList());

System.out.println("All dialects");

System.out.println("------------");

System.out.println(dialects);

System.out.println();

System.out.println("A translation");

System.out.println("-------------");

QueryPart sql = S_AUTHOR_ID.nextval();

for (SQLDialect dialect : dialects) {

DSLContext ctx = DSL.using(dialect);

ctx.settings().setRenderFormatted(true);

System.out.println(String.format("%1$-20s", dialect) + ":" + ctx.renderInlined(sql));

}

}

}

You'll still have to "run" your query, although, you don't need your entire application for that.

I hope this helps

Lukas

--