Your every day SQL job

Fetch exists

final Authors tbl = Tables.AUTHORS;
jooqx.fetchExists(dsl -> dsl.selectOne().whereExists(dsl.selectFrom(tbl).where(tbl.NAME.eq("zero88")))) (1)
     .onSuccess(isExisted -> {
         assert Objects.equals("Existed", (isExisted ? "Existed" : "Non-existed"));
     })                                                                 (2)
     .onFailure(Throwable::printStackTrace);                            (3)

Fetch count

final Authors tbl = Tables.AUTHORS;
jooqx.fetchCount(dsl -> dsl.selectCount().from(tbl).where(tbl.COUNTRY.eq("USA")))   (1)
     .onSuccess(count -> {
         assert Objects.equals("Count 10", "Count " + count);
     })                                                                 (2)
     .onFailure(Throwable::printStackTrace);                            (3)

Fetch many

jooqx.fetchMany(dsl -> dsl.selectFrom(Tables.BOOKS).where(Tables.BOOKS.TITLE.like("%Great%")))  (1)
     .onSuccess(books -> {
         assert Objects.equals(books.toString(), """
             [+----+-----------------+
             |  id|title            |
             +----+-----------------+
             |  *4|*The Great Gatsby|
             +----+-----------------+
             , +----+-------------------------------------------+
             |  id|title                                      |
             +----+-------------------------------------------+
             |  *8|*Using Jooq with Vert.x is Great experience|
             +----+-------------------------------------------+
             ]
             """);
     })                                                             (2)
     .onFailure(Throwable::printStackTrace);                        (3)

Fetch one

final Authors tbl = Tables.AUTHORS;
jooqx.fetchOne(dsl -> dsl.selectOne().from(tbl).where(tbl.NAME.eq("zero88")).limit(1))  (1)
     .onSuccess(author -> {
         assert Objects.equals(author.toString(), """
             +----+-------+-------+
             |  id|name   |country|
             +----+-------+-------+
             |  *8|*zero88|*VN    |
             +----+-------+-------+
             """);
     })                                                             (2)
     .onFailure(Throwable::printStackTrace);                        (3)

Join query

jooqx.fetchJsonArray(
         dsl -> dsl.select(Tables.AUTHORS.asterisk(), Tables.BOOKS_AUTHORS.BOOK_ID, Tables.BOOKS.TITLE)
                   .from(Tables.AUTHORS)
                   .join(Tables.BOOKS_AUTHORS)
                   .onKey()
                   .join(Tables.BOOKS)
                   .onKey()
                   .where(Tables.AUTHORS.NAME.eq("F. Scott. Fitzgerald")))   (1)
     .onSuccess(records -> {
         assert Objects.equals(records.encode(), """
             [
               {"id":2,"name":"F. Scott. Fitzgerald","country":"USA","book_id":4,"title":"The Great Gatsby"},
               {"id":2,"name":"F. Scott. Fitzgerald","country":"USA","book_id":5,"title":"Tender id the Night"}
             ]
             """);
     })                                                             (2)
     .onFailure(Throwable::printStackTrace);                        (3)

INSERT statement

jooqx.execute(dsl -> dsl.insertInto(Tables.BOOKS, Tables.BOOKS.ID, Tables.BOOKS.TITLE)
                        .values(Arrays.asList(DSL.defaultValue(Tables.BOOKS.ID), "Hello jOOQ.x"))
                        .returning(), DSLAdapter.fetchOne(Tables.BOOKS))                            (1)
     .onSuccess(book -> {
         assert Objects.equals(book.toString(), """
             +----+-------------+
             |  id|title        |
             +----+-------------+
             | *10|*Hello jOOQ.x|
             +----+-------------+
             """);
     })                                                     (2)
     .onFailure(Throwable::printStackTrace);                (3)

SELECT FOR UPDATE statement

jooqx.fetchOne(dsl -> dsl.selectFrom(Tables.BOOKS).where(Tables.BOOKS.ID.eq(1)).forUpdate())    (1)
     .flatMap(book -> jooqx.execute(dsl -> dsl.update(Tables.BOOKS)
                                              .set(book.setTitle("Hello jOOQ.x"))
                                              .where(Tables.BOOKS.ID.eq(book.getId()))
                                              .returning(), DSLAdapter.fetchOne(Tables.BOOKS)))         (2)
     .onSuccess(book -> {
         assert Objects.equals(book.toString(), """
             +----+-------------+
             |  id|title        |
             +----+-------------+
             |  *1|*Hello jOOQ.x|
             +----+-------------+
             """);
     })                                                     (3)
     .onFailure(Throwable::printStackTrace);                (4)

UPDATE statement

final Authors tbl = Tables.AUTHORS;
jooqx.execute(dsl -> dsl.update(tbl).set(DSL.row(tbl.COUNTRY), DSL.row("USA")).where(tbl.COUNTRY.eq("US")),
              DSLAdapter.fetchCount())                                                                          (1)
     .flatMap(ignore -> jooqx.fetchMany(dsl -> dsl.selectFrom(tbl).where(tbl.COUNTRY.eq("USA"))))       (2)
     .onSuccess(book -> {
         assert Objects.equals(book.toString(), """
              [+----+--------------+-------+
              |  id|name          |country|
              +----+--------------+-------+
              |  *1|*J.D. Salinger|*USA   |
              +----+--------------+-------+
              , +----+---------------------+-------+
              |  id|name                 |country|
              +----+---------------------+-------+
              |  *2|*F. Scott. Fitzgerald|*USA   |
              +----+---------------------+-------+
              ]
             """);
     })                                                     (3)
     .onFailure(Throwable::printStackTrace);                (4)

DELETE statement

final Authors table = Tables.AUTHORS;
jooqx.execute(dsl -> dsl.deleteFrom(table).where(table.COUNTRY.eq("US")), DSLAdapter.fetchCount())      (1)
     .flatMap(ignore -> jooqx.fetchMany(dsl -> dsl.selectFrom(table).where(table.COUNTRY.eq("US"))))    (2)
     .onSuccess(books -> {
         assert Objects.equals(books.toString(), "[]");
     })                                                     (3)
     .onFailure(Throwable::printStackTrace);                (4)