Hello Christopher,
I have now implemented the pretty printing functionality in jOOQ:
https://sourceforge.net/apps/trac/jooq/ticket/161
There is a flag that can be set to true in the jOOQ runtime settings. By
default it is set to false, as today. When set to true, every QueryPart can
decide for themselves, how they want to format their rendered SQL. I chose
not to re-use the logic used in the jOOQ console, as it is non-trivial to
correctly parse SQL text for formatting without a sophisticated lexer. It
is also hard to extend such a parser manually without breaking it. For the
console, this is less critical, as the SQL statement doesn't have to be
executed.
Here are some sample statements, rendered with jOOQ's new pretty printing
feature:
-- Nested selects in predicates
--------------------------------------
from "TEST"."T_BOOK"
where "TEST"."T_BOOK"."ID" = any (select "array_table"."COLUMN_VALUE"
from (select 1 "COLUMN_VALUE"
from dual) "array_table")
order by "TEST"."T_BOOK"."ID" asc
-- Nested selects in LIMIT .. OFFSET clauses
--------------------------------------
select * from (
select limit_104581173.*, rownum as rownum_104581173
from (
select
"TEST"."T_AUTHOR"."ID",
"TEST"."T_AUTHOR"."FIRST_NAME",
"TEST"."T_AUTHOR"."LAST_NAME",
"TEST"."T_AUTHOR"."DATE_OF_BIRTH",
"TEST"."T_AUTHOR"."YEAR_OF_BIRTH",
"TEST"."T_AUTHOR"."ADDRESS"
from "TEST"."T_AUTHOR"
order by "TEST"."T_AUTHOR"."ID" asc
) limit_104581173
)
where rownum_104581173 > 0
and rownum_104581173 <= (0 + 1)
-- Nested JOIN clauses
--------------------------------------
select
"TEST"."T_BOOK"."TITLE",
"MULTI_SCHEMA"."T_BOOK_SALE"."BOOK_STORE_NAME",
sum("MULTI_SCHEMA"."T_BOOK_SALE"."SOLD_FOR")
from "TEST"."T_BOOK"
join ("TEST"."T_BOOK_TO_BOOK_STORE"
join "MULTI_SCHEMA"."T_BOOK_SALE" on
("MULTI_SCHEMA"."T_BOOK_SALE"."BOOK_STORE_NAME" =
"TEST"."T_BOOK_TO_BOOK_STORE"."BOOK_STORE_NAME" and
"MULTI_SCHEMA"."T_BOOK_SALE"."BOOK_ID" =
"TEST"."T_BOOK_TO_BOOK_STORE"."BOOK_ID")) on
"TEST"."T_BOOK_TO_BOOK_STORE"."BOOK_ID" = "TEST"."T_BOOK"."ID"
group by
"TEST"."T_BOOK"."ID",
"TEST"."T_BOOK"."TITLE",
"MULTI_SCHEMA"."T_BOOK_SALE"."BOOK_STORE_NAME"
order by
"TEST"."T_BOOK"."ID" asc,
sum("MULTI_SCHEMA"."T_BOOK_SALE"."SOLD_FOR") desc
--------------------------------------
This formatter could be re-used in jOOQ console, as the console has a
reference to org.jooq.Query. For formatting, use
String sql = new Factory(
connection,
dialect,
new Settings().withRenderFormatted(true)).render(query);
Cheers
Lukas
