Quickly Trying out jOOQ with Jbang!

jbang is a relatively new utility that …

… lets students, educators and professional developers create, edit and run self-contained source-only Java programs with unprecedented ease.

Sounds exciting. How does it work with jOOQ? Very easy! Set it up like this (other installation options here):

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

And then, play around with this jOOQ example:

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

And you should see something like this:

[jbang] Resolving dependencies...
[jbang]     Resolving org.jooq:jooq:RELEASE...Done
[jbang]     Resolving com.h2database:h2:RELEASE...Done
[jbang] Dependencies resolved
[jbang] Building 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.tools.JooqLogger info
INFO:

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@  @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@        @@@@@@@@@@
@@@@@@@@@@@@@@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@  @@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@        @@  @  @  @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@  @@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  Thank you for using jOOQ 3.14.4

Query:
select
  "alias_99346637"."FIRST_NAME",
  "alias_99346637"."LAST_NAME",
  "alias_12744250"."TITLE",
  count(distinct "BOOK_TO_BOOK_STORE"."BOOK_ID") over (partition by "alias_12744250"."AUTHOR_ID") "books written by author",
  count(distinct "BOOK_TO_BOOK_STORE"."NAME") over (partition by "alias_12744250"."AUTHOR_ID") "bookstores listing author",
  "alias_86071072"."CD",
  "alias_134125752"."NAME"
from (
  "BOOK_TO_BOOK_STORE"
    join (
      "BOOK" "alias_12744250"
        join "AUTHOR" "alias_99346637"
          on "alias_12744250"."AUTHOR_ID" = "alias_99346637"."ID"
        join "LANGUAGE" "alias_86071072"
          on "alias_12744250"."LANGUAGE_ID" = "alias_86071072"."ID"
    )
      on "BOOK_TO_BOOK_STORE"."BOOK_ID" = "alias_12744250"."ID"
    join "BOOK_STORE" "alias_134125752"
      on "BOOK_TO_BOOK_STORE"."NAME" = "alias_134125752"."NAME"
  )
order by
  1,
  2,
  3

Result:
+----------+---------+------------+-----------------------+-------------------------+----+-------------------------+
|FIRST_NAME|LAST_NAME|TITLE       |books written by author|bookstores listing author|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 any other hassles involved. All dependencies are resolved automatically, compilation is done as well.

To re-generate the jOOQ generated code, if you modify the code generation configuration, or the database schema changes, just run:

jbang codegen@jooq db.xml

You can also use the other 2 CLI APIs very simply, from jOOQ, e.g. the DiffCLI:

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

To get this output:

alter table t add j int null;

Or, the ParserCLI

jbang parser@jooq -T MYSQL -s "create table t (i int generated always as identity);"

And you’ll get:

create table t (i int not null auto_increment);

Want to work with the commercial distributions? Assuming you’ve already downloaded the appropriate trial or pro distribution from here: https://www.jooq.org/download/versions

Then, just add the usual suffix to your script from the jbang-catalog (the full and up-to-date catalog can be seen here)

jbang parser-trial@jooq -T SQLSERVER -s "create table t (i int generated always as identity);"

And now, you can also access the commerical dialects to get:

create table t (i int identity(1, 1) not null);

Available suffixes include:

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

For more installation, please refer to the jbang documentation:

• Installation
• Usage

from Java, SQL and jOOQ. https://ift.tt/3xQJx5G
via IFTTT