Shortly Attempting out jOOQ with Jbang! – Java, SQL and jOOQ.

jbang is a comparatively new utility that …

… lets college students, educators {and professional} builders create, edit and run self-contained source-only Java packages with unprecedented ease.

Sounds thrilling. How does it work with jOOQ? Very straightforward! Set it up like this (different set up choices right here):

curl -Ls https://sh.jbang.dev | bash -s - app setup

After which, mess around with this jOOQ instance:

git clone https://github.com/jOOQ/jbang-example
cd jbang-example
jbang Instance.java

And it is best to see one thing like this:

[jbang] Resolving dependencies...
[jbang]     Resolving org.jooq:jooq:RELEASE...Accomplished
[jbang]     Resolving com.h2database:h2:RELEASE...Accomplished
[jbang] Dependencies resolved
[jbang] Constructing jar...
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF8
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF8
Juni 24, 2021 5:06:35 PM org.jooq.instruments.JooqLogger information
INFO:

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@  @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@        @@@@@@@@@@
@@@@@@@@@@@@@@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@  @@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@        @@  @  @  @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@  @@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  Thanks for utilizing jOOQ 3.14.4

Question:
choose
  "alias_99346637"."FIRST_NAME",
  "alias_99346637"."LAST_NAME",
  "alias_12744250"."TITLE",
  rely(distinct "BOOK_TO_BOOK_STORE"."BOOK_ID") over (partition by "alias_12744250"."AUTHOR_ID") "books written by creator",
  rely(distinct "BOOK_TO_BOOK_STORE"."NAME") over (partition by "alias_12744250"."AUTHOR_ID") "bookstores itemizing creator",
  "alias_86071072"."CD",
  "alias_134125752"."NAME"
from (
  "BOOK_TO_BOOK_STORE"
    be part of (
      "BOOK" "alias_12744250"
        be part of "AUTHOR" "alias_99346637"
          on "alias_12744250"."AUTHOR_ID" = "alias_99346637"."ID"
        be part of "LANGUAGE" "alias_86071072"
          on "alias_12744250"."LANGUAGE_ID" = "alias_86071072"."ID"
    )
      on "BOOK_TO_BOOK_STORE"."BOOK_ID" = "alias_12744250"."ID"
    be part of "BOOK_STORE" "alias_134125752"
      on "BOOK_TO_BOOK_STORE"."NAME" = "alias_134125752"."NAME"
  )
order by
  1,
  2,
  3

End result:
+----------+---------+------------+-----------------------+-------------------------+----+-------------------------+
|FIRST_NAME|LAST_NAME|TITLE       |books written by creator|bookstores itemizing creator|CD  |NAME                     |
+----------+---------+------------+-----------------------+-------------------------+----+-------------------------+
|George    |Orwell   |1984        |                      2|                        2|en  |Orell Füssli             |
|George    |Orwell   |1984        |                      2|                        2|en  |Ex Libris                |
|George    |Orwell   |Animal Farm |                      2|                        2|en  |Orell Füssli             |
|Paulo     |Coelho   |O Alquimista|                      1|                        3|pt  |Orell Füssli             |
|Paulo     |Coelho   |O Alquimista|                      1|                        3|pt  |Ex Libris                |
|Paulo     |Coelho   |O Alquimista|                      1|                        3|pt  |Buchhandlung im Volkshaus|
+----------+---------+------------+-----------------------+-------------------------+----+-------------------------+

No Maven, Gradle, or some other hassles concerned. All dependencies are resolved routinely, compilation is completed as properly.

To re-generate the jOOQ generated code, if you happen to modify the code era configuration, or the database schema modifications, simply run:

jbang codegen@jooq db.xml

You may as well use the opposite 2 CLI APIs very merely, from jOOQ, e.g. the DiffCLI:

jbang diff@jooq -T MYSQL -1 "create desk t (i int);" -2 "create desk t (i int, j int);"

To get this output:

alter desk t add j int null;

Or, the ParserCLI

jbang parser@jooq -T MYSQL -s "create desk t (i int generated at all times as id);"

And also you’ll get:

create desk t (i int not null auto_increment);

Wish to work with the industrial distributions? Assuming you’ve already downloaded the suitable trial or professional distribution from right here: https://www.jooq.org/obtain/variations

Then, simply add the standard suffix to your script from the jbang-catalog (the complete and up-to-date catalog could be seen right here)

jbang parser-trial@jooq -T SQLSERVER -s "create desk t (i int generated at all times as id);"

And now, you may also entry the commerical dialects to get:

create desk t (i int id(1, 1) not null);

Obtainable suffixes embrace:

• -trial
• -trial-java-8
• -trial-java-11 (ranging from jOOQ 3.15)
• -pro
• -pro-java-8
• -pro-java-11 (ranging from jOOQ 3.15)

For extra set up, please discuss with the jbang documentation: