SQL transaction

jooqx simplify supports SQL transaction by using Pool client (e.g: JDBCPool, PgPool, etc…) to borrows a connection from the pool, begins the transaction and releases the connection to the pool when the transaction ends.

Transaction

Transaction success

jooqx.transaction().run(tx -> {
    InsertResultStep<BooksRecord> q1 = tx.dsl()
                                         .insertInto(Tables.BOOKS, Tables.BOOKS.ID, Tables.BOOKS.TITLE)
                                         .values(Arrays.asList(DSL.defaultValue(Tables.BOOKS.ID), "hello guys"))
                                         .returning();
    InsertResultStep<BooksRecord> q2 = tx.dsl()
                                         .insertInto(Tables.BOOKS, Tables.BOOKS.ID, Tables.BOOKS.TITLE)
                                         .values(Arrays.asList(DSL.defaultValue(Tables.BOOKS.ID), "It's jooqx"))
                                         .returning();
    SelectOne<BooksRecord> selectOne = DSLAdapter.fetchOne(Tables.BOOKS);
    // Avoid using the scope from outside the transaction:
    // jooqx.execute(...);

    // ...but using context within the transaction scope:
    return tx.execute(q1, selectOne).flatMap(r1 -> tx.execute(q2, selectOne).map(r2 -> Arrays.asList(r1, r2)));
}).onSuccess(books -> {
    assert Objects.equals(books.toString(), """
        [+----+------------+
        |  id|title      |
        +----+-----------+
        |  *1|*hello guys|
        +----+-----------+
        , +----+-----------+
        |  id|title      |
        +----+-----------+
        |  *2|*It's jooqx|
        +----+-----------+
        ]
         """);
}).onFailure(Throwable::printStackTrace);

Handle rollback transaction

jooqx.transaction().run(tx -> {
    final Books table = Tables.BOOKS;
    final SelectOne<BooksRecord> selectOne = DSLAdapter.fetchOne(table);
    return tx.execute(dsl -> dsl.update(table)
                                .set(DSL.row(table.TITLE), DSL.row("something"))
                                .where(table.ID.eq(1))
                                .returning(), selectOne)                                            (1)
             .flatMap(r1 -> tx.execute(dsl -> dsl.update(table)
                                                 .set(DSL.row(table.TITLE), DSL.row((String) null))
                                                 .where(table.ID.eq(2))
                                                 .returning(), selectOne)                           (2)
                              .map(r2 -> Arrays.asList(r1, r2)));
}).recover(cause -> {
    assert cause instanceof DataAccessException;                                                    (3)
    assert Objects.equals(((DataAccessException) cause).sqlStateClass(),
                          SQLStateClass.C23_INTEGRITY_CONSTRAINT_VIOLATION);
    return Future.succeededFuture(Collections.emptyList());                                         (4)
});

1	First update is ok
2	Second update will be failed because of table constraint
3	Assert transaction exception
4	You can handle transaction rollback in here

Session

The different between transaction and session is:

transaction rollback if any error
session still continue executing the sequence SQL statements regardless if any SQL statement has an error.

final Authors tbl = Tables.AUTHORS;
jooqx.session()
     .perform(session -> {
         AuthorsRecord i1 = new AuthorsRecord().setName("n1").setCountry("AT");
         AuthorsRecord i2 = new AuthorsRecord().setName("n2");
         SelectOne<AuthorsRecord> selectOne = DSLAdapter.fetchOne(tbl);

         // Avoid using the scope from outside the session:
         // jooqx.execute(...);

         // ...but using context within the session scope:
         return session.execute(dsl -> dsl.insertInto(tbl).set(i1).returning(), selectOne)
                       .flatMap(r1 -> session.execute(dsl -> dsl.insertInto(tbl).set(i2).returning(), selectOne)
                                             .map(r2 -> Arrays.asList(r1, r2)));
     })
     .onFailure(t -> {
         assert t instanceof DataAccessException;
         assert t.getMessage().contains("null value in column \"country\" violates not-null constraint");
     })
     .eventually(unused -> jooqx.fetchExists(dsl -> dsl.selectFrom(tbl).where(tbl.NAME.eq("n1")))
                                .onSuccess(isExisted -> { assert isExisted; })
                                .onFailure(Throwable::printStackTrace));

Block

jooqx.block(dsl -> BlockQuery.create()
                             .add(dsl.selectFrom(Tables.AUTHORS).limit(2), DSLAdapter.fetchMany(Tables.AUTHORS))
                             .add(dsl.selectFrom(Tables.BOOKS).limit(2), DSLAdapter.fetchMany(Tables.BOOKS)))
     .onSuccess(blockResult -> {
         assert blockResult.size() == 2;

         final List<AuthorsRecord> authors = blockResult.get(0);
         assert Objects.equals(authors.toString(), """
             [+----+--------------+-------+
             |  id|name          |country|
             +----+--------------+-------+
             |  *1|*J.D. Salinger|*USA   |
             +----+--------------+-------+
             , +----+---------------------+-------+
             |  id|name                 |country|
             +----+---------------------+-------+
             |  *2|*F. Scott. Fitzgerald|*USA   |
             +----+---------------------+-------+
             ]
              """);

         final List<BooksRecord> books = blockResult.get(1);
         assert Objects.equals(books.toString(), """
             [+----+-----------------------+
             |  id|title                  |
             +----+-----------------------+
             |  *1|*The Catcher in the Rye|
             +----+-----------------------+
             , +----+-------------+
             |  id|title        |
             +----+-------------+
             |  *2|*Nine Stories|
             +----+-------------+
             ]
              """);
     })
     .onFailure(Throwable::printStackTrace);