Writing a Wrapper for Jooq-Transaction to use Try-With-Resources

45 views
Skip to first unread message

Bernd Huber

unread,
Mar 24, 2024, 6:09:54 AMMar 24
to jOOQ User Group
Hello guys,

many of us possibly work on SaaS server applications,
where we often have the following layers:

- Manager/Services Layer (Opens up transactions)
- DAO Layer (Interacts with the database)

i now would like to make use of jooqs nested transactions, as they are described in: https://blog.jooq.org/nested-transactions-in-jooq/ but i also would like to pass this "jooq-context" of each transaction (with additional data typical for SaaS like tenantId, languageId, ...) into the dao-layer, so the dao-instances will only work on this transaction and have the additional data (tenantId,...) available for filtering etc.

i have already written a class that implements the "Try-With-Resources" pattern, which works correctly. See: https://github.com/funkrusher/fk-framework-quarkus-jooq/blob/main/fk_core/src/main/java/org/fk/core/jooq/JooqContext.java

i can use it as follows in the manager layer of my app:

```java
public void deleteAndInsert(List<AuthorRecord> insertAuthors) {

  // transaction1
  try (JooqContext t1JooqContext = jooqContextFactory.createJooqContext(requestContext)) {

    AuthorDao t1AuthorDao = daoFactory.createAuthorDao(t1JooqContext);
    List<AuthorRecord> existingAuthors = t1AuthorDao.selectSomeAuthors(insertAuthors);

    // transaction2
    try (JooqContext t2JooqContext = jooqContextFactory.createJooqContext(requestContext)) {
      AuthorDao t2AuthorDao = daoFactory.createAuthorDao(t2JooqContext);
      t2AuthorDao.deleteByAuthorIds(existingAuthors);
      t2JooqContext.commit();
    }

    // transaction3
    try {
      try (JooqContext t3JooqContext = jooqContextFactory.createJooqContext(requestContext)) {
        AuthorDao t3AuthorDao = daoFactory.createProductRecordDAO(t3JooqContext);
        t3AuthorDao.insert(insertAuthors);
        t3JooqContext.commit();
      }
    } catch (Exception e) {
      LOGGER.info(e);
    }

  } catch (Exception e) {
    e.printStackTrace();
  }
}
```

within the JooqContext class i work with jooqs:
- dsl.startTransaction (at start of try-with-resources)
- dsl.commit (must be called explicit from outside)
- dsl.rollback when the try-block has not been completed with a "commit" call.

the only problem i currently have is, that i explicitly need to "commit" every transaction at the end of my Try-With-Resources block.

I now want to ask you guys:
- is there a better way to achieve what i need (Manager-Layer and Dao-Layer separation with shared request-data like tenantId + transaction management in Manager-Layer)

and if its ok what i do,
is there a way to make the try-with-resources call commit automatically at the end of the Try-Block.

best regards,

Bernd

Bernd Huber

unread,
Mar 24, 2024, 12:23:26 PMMar 24
to jOOQ User Group
i found a solution,

i just wrap jooqs `ctx.transaction` lambda-exception

the code looks ok that way i guess.

```
...
jooqContextFactory.withinTransaction(requestContext, jooqContext -> {
  // transaction1

  jooqContextFactory.withinTransaction(requestContext, subContextA -> {
    // transaction2
    ProductRecordDAO aProductRecordDAO = daoFactory.createProductRecordDAO(subContextA);
    aProductRecordDAO.deleteDTOs(queryResult);
  });

  try {
    jooqContextFactory.withinTransaction(requestContext, subContextB -> {
      // transaction3
      ProductRecordDAO bProductRecordDAO = daoFactory.createProductRecordDAO(subContextB);
      bProductRecordDAO.insertDTOs(inserts);
      // Integer x = Integer.valueOf("test");
    });
  } catch (Exception e) {
    LOGGER.info(e);
  }
}
```

by the way, i totally love the approach of jooq here.
I got baffled today when i realized that the JTA Transactional Annotation does not catch checked exceptions per default and does not rollback in that case.
Maybe they do it for a good reason, but in such a situation i think it can be a good idea to just ditch JTA transaction to write something by oneself.

I only sometimes fear that that quarkus guys, build some kind of Developer UI on the existing Stuff like Transactional, and when i use jooq transactions,
i may not be able to profit from it, but the additional control in regards to the code is worth it i think.

Lukas Eder

unread,
Mar 25, 2024, 3:40:21 AMMar 25
to jooq...@googlegroups.com
Hi Bernd,

The try-with-resources approach will not work unless you're OK with the explicit commit. I've explored this as well in the past:

But you don't really need any of that, as jOOQ's DSLContext.transaction() API already supports this nesting style using lambda expressions, rather than try-with-resources. I take that you wrote your own utility because you need to attach additional semantics with each transaction?

Best Regards,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/18163c0d-8cbe-4f92-93dd-d98ab3f4eda0n%40googlegroups.com.

Bernd Huber

unread,
Mar 25, 2024, 6:34:57 AMMar 25
to jOOQ User Group
Hello Lukas,

thanks for following up.

exactly like you said, i wrote the own utility because i want to pass the following data from the Manager/Services-Layer into the DAO-Layer for each "daoFactory.createXyzDao()" call:
- tenantId
- langId of the user
- the dsl of jooq

when i use the DSLContext.transaction() API directly, the could be not as slim, i think. See:

```java

DSLContext ctx = DslContextFactory.createDSLContext(request);

ctx.transaction(trx -> {
   
    AuthorDao authorDao = daoFactory.createAuthorDao(requestContext, trx)

    trx.dsl().transaction(trx1 -> {
        // ..
            BookDao bookDao = daoFactory.createBookDao(requestContext, trx1)
    });

```

the instantiation of the DSLContext necessarily needs to be given the "request" object which contains the "tenantId" and the "langId",
so it can construct the DSLContext by attaching a custom "DefaultExecuteListenerProvider" to the configuration of the DSLContext,
so all Queries executed by jooq will be appended a "WHERE tenantId = xyz", to automatically make sure that a user of tenant=xyz never
sees or accesses data from an other tenant.

The DAOs must be instantiated with the transaction and data from the request context, that can help the abstraction of the DAOs to make stuff,
like using the "langId" of the user, to make sure all queries that select something on "Lang" tables than contain a column "langId" to always get the
language-entry of the users "langId" as default-language.

But yes, you are right, it would also be totally fine to directly use the jooqs transaction api, but then the developers need to pass more variables to
the constructors of the dao and type a bit more...

Lukas Eder

unread,
Mar 25, 2024, 6:46:20 AMMar 25
to jooq...@googlegroups.com
On Mon, Mar 25, 2024 at 11:35 AM 'Bernd Huber' via jOOQ User Group <jooq...@googlegroups.com> wrote:
Hello Lukas,

thanks for following up.

exactly like you said, i wrote the own utility because i want to pass the following data from the Manager/Services-Layer into the DAO-Layer for each "daoFactory.createXyzDao()" call:
- tenantId
- langId of the user
- the dsl of jooq

Note that all jOOQ Scope types as well as the Configuration have a generic Map<Object, Object>, which can be used to pass around such context objects. Perhaps that would be useful here.
 
when i use the DSLContext.transaction() API directly, the could be not as slim, i think. See:

```java

DSLContext ctx = DslContextFactory.createDSLContext(request);

ctx.transaction(trx -> {
   
    AuthorDao authorDao = daoFactory.createAuthorDao(requestContext, trx)

    trx.dsl().transaction(trx1 -> {
        // ..
            BookDao bookDao = daoFactory.createBookDao(requestContext, trx1)
    });

```

the instantiation of the DSLContext necessarily needs to be given the "request" object which contains the "tenantId" and the "langId",
so it can construct the DSLContext by attaching a custom "DefaultExecuteListenerProvider" to the configuration of the DSLContext,
so all Queries executed by jooq will be appended a "WHERE tenantId = xyz", to automatically make sure that a user of tenant=xyz never
sees or accesses data from an other tenant.

How did you implement this from an ExecuteListener? With a regular expression or VisitListener? Or with the policy feature?

Though policies are registered separately from an ExecuteListener in the Configuration...

Bernd Huber

unread,
Mar 25, 2024, 1:04:24 PMMar 25
to jOOQ User Group
Hello Lukas,

the generic Map<Object, Object> is indeed a good solution. 
I have changed my code accordingly, and the result looks better!
I can use the default nested-transactions the way they are described in the jooq documentation 
and with help of "dsl.data(x,y)" i still have my context-specific data available in my dao-layer.

---
i implemented the tenantId-check for all SELECT-Statements with a custom ExecuteListener,
that checks for the table if it contains a column "clientId" and will append the clientId from the request.

I will keep the Policy Feature in my mind, for a reason in my company to get a commercial license someday!
one thing would be helpful for the policy feature in my opinion:
- having all Tables generated by the Codegen present when the Policy is set, to automatically generate the list of all tables that contain a "tenantId", to not need to write them manually
- criterias depending on the existence of columns in a table (add criteria x, if table contains field "y")

but that only as an idea.

Lukas Eder

unread,
Mar 26, 2024, 3:31:12 AMMar 26
to jooq...@googlegroups.com
Hi Bernd,

On Mon, Mar 25, 2024 at 6:04 PM 'Bernd Huber' via jOOQ User Group <jooq...@googlegroups.com> wrote:
Hello Lukas,

the generic Map<Object, Object> is indeed a good solution. 
I have changed my code accordingly, and the result looks better!
I can use the default nested-transactions the way they are described in the jooq documentation 
and with help of "dsl.data(x,y)" i still have my context-specific data available in my dao-layer.

---
i implemented the tenantId-check for all SELECT-Statements with a custom ExecuteListener,
that checks for the table if it contains a column "clientId" and will append the clientId from the request.

Thanks for sharing that FkExecuteListener.

This will work for simple cases indeed. But it doesn't take into account:

- DML (INSERT, UPDATE, DELETE, MERGE), both read / write use-cases (you mustn't be able to write a record that you won't be able to read, i.e. the SQL view WITH CHECK OPTION semantics)
- Subqueries (scalar, derived tables, set operations like UNION, etc.)
- CTE
- Queries where your clientId isn't projected
- Queries where your clientId is aliased
- Implicit join
- Policies on parent tables should affect child tables as well
- And probably a lot more, all of which the policies will do for you


jOOQ has around 80 integration tests for policies covering this functionality
 
I will keep the Policy Feature in my mind, for a reason in my company to get a commercial license someday!

It'll pay off in no time compared to the time spent maintaining your ExecuteListener ;-). All commercial features like this are available already in the jOOQ Express Edition.
 
one thing would be helpful for the policy feature in my opinion:
- having all Tables generated by the Codegen present when the Policy is set, to automatically generate the list of all tables that contain a "tenantId", to not need to write them manually
- criterias depending on the existence of columns in a table (add criteria x, if table contains field "y")

These are interesting ideas to keep in mind in the long run:

I won't rush a solution here, waiting for more requirements. I'm not convinced it's necessary. You have a generated Schema or even Catalog instance, which you can query for all its tables via Schema::getTables, and then Table::fields to look up all the qualified TENANT_ID fields in your schema (including cases where the naming convention isn't followed, or whatever other kind of policy pattern emerges in your case). So, your generic PolicyProvider seems only take 3-4 lines of code, IMO?
 

Bernd Huber

unread,
Mar 26, 2024, 4:23:15 AMMar 26
to jOOQ User Group
Hello Lukas,

yes, the FkExecuteListener i wrote is a "poor-mans-solution" :)

thanks for mentioning all the points that Policies are covering. 
I will keep this in mind to mention in my team as soon as jooq is used in our product for some time 
(if i can convince my colleagues to use jooq instead of hibernate that is / in my old company we already used jooq),
and make sure it is clear, that my current solution does not cover those cases.

I checked the generated Schema / Catalog instances and found no problem in getting all generated tables, and searching for the field in them,
so i'm conviced that the Policy Feature would be good to use as it is for me. If you want you can close your ticket, as it seems everything i need
would work with the Policies.

While saying this, i take into account that in a multi-module setup, there would be separated project for the Codegen, Core, Subprojects ,...

- codegen-project (generates code for database1, database, ...)
- core-project (defines a common DSLFactory that creates the Jooq Configuration and from that, the Jooq DSLContext object)
- project1 (uses the core-project and only database1, and would need to write a Project1DSLFactory, that creates Policies specific for database1 / project1)
- project2 (uses the core-project and only database2, and would need to write a Project2DSLFactory, that creates Policies specific for database2 / project2)
- server1 (uses project1 and provides rest-endpoints and defines evolutions with liquibase for database1)
- server2 (uses project2 and provides rest-endpoints and defines evolutions with liquibase for database2)

with outlining such (example) dependencies between projects i want to say, that the codegen-project containing the Schema / Catalog ist often
defined separately from the projects that are depending on its generated Schema / Catalog instances, and that we normally strive to keep database-specific
generated classes out of the database-independent core-projects, but its often not possible and also not really necessary.

Bernd Huber

unread,
Mar 26, 2024, 7:39:20 PMMar 26
to jOOQ User Group
Hello Lukas,

one point about the generic Map<Object, Object> on all Scope Types, that you have mentioned...

im currently constructing a new Jooq Configuration and DSLContext for every incoming Request / Thread in my blocking quarkus application,
and had planned to remove this requirement and to share the Jooq Configuration and DSLContext and inject it everywhere instead of constructing it many times,

When i now use the generic Map<Object, Object> via "configuration.data(x, y)", like you mentioned it seems that i may run into Thread-Safety issues?

- if thread A changed configuration.data(), will thread B also see this changed configuration.data() in a multi-threading environment?

If yes, then:
- a) maybe the "data()" function for Scope-specific data may not be the right way to do what i need (passing around thread-specific context-objects in a multi-threaded environment)
- b) i need to keep Constructing new DSLContext for every Thread to keep them separated, so i can use "data()" in an isolated manner.

best regards,

Bernd

Lukas Eder

unread,
Mar 27, 2024, 5:26:20 AMMar 27
to jooq...@googlegroups.com
The DefaultConfiguration::data map is a ConcurrentHashMap, so readers and writers won't corrupt the Map when accessing it concurrently. But indeed, the Map is shared if you share the Configuration. You can either use tenant-specific Map keys (like "com.example.tenant-1", etc.) or maintain a Configuration per tenant, or use a "lower level" scoped map, see below. I don't know what makes most sense to you.

On Wed, Mar 27, 2024 at 12:39 AM 'Bernd Huber' via jOOQ User Group <jooq...@googlegroups.com> wrote:
- if thread A changed configuration.data(), will thread B also see this changed configuration.data() in a multi-threading environment?

Yes 

If yes, then:
- a) maybe the "data()" function for Scope-specific data may not be the right way to do what i need (passing around thread-specific context-objects in a multi-threaded environment)

Each Scope has its own map, though. You don't have to register stuff to the "top level" scope. You could implement a TransactionListener and populate the TransactionContext.data() map instead of TransactionContext.configuration().data(). The same is true with an ExecuteListener, which can populate an ExecuteContext.data() map
 
- b) i need to keep Constructing new DSLContext for every Thread to keep them separated, so i can use "data()" in an isolated manner.

The main drawback (currently) of this is that reflection caches won't be shared. Those caches are used when you call Result.into(MyClass.class), where the DefaultRecordMapper is looked up only once if you share the Configuration, or every time, if you don't. There may be other caches in the future.

Bernd Huber

unread,
Mar 27, 2024, 6:42:20 AMMar 27
to jOOQ User Group
Hello Lukas,

thank you for the additional information.
There is some misunderstanding about the tenant-specific data. I will explain further...

My requirement would be:

- Each REST-Endpoint would need to be able to set a Policy/ExecuteListener that is specific to the Authentication/Authorization Credentials (Permissions) of the Bearer in this request.
As the Policy/ExecuteListener must be set on the Jooq-Configuration, i really must create the DSLContext for each REST-Request uniquely and can not share it globally.

For example:

Peter authenticates himself with the Backend and gets a Cookie that contains the Roles/Tenant that he is allowed to access.
Peter now sends this Cookie in all REST-Requests to the Backend and the Credentials within the Cookie will be passed into the REST-Endpoint of the Framework (Spring, Quarkus,...).

So i really have the need to create a Request-Specific Jooq-Configuration when i want to make use of Jooqs Policy/ExecutionListener here?

In that regards the only way i would profit from Caching of the DSLContext would be within a REST-Request Scope.

So if from Jooqs Side there is nothing really problematic (only slower runtimes because of not caching stuff), i would like to stick with recreating the DSLContext for each REST-Request.

Bernd Huber

unread,
Mar 27, 2024, 6:44:32 AMMar 27
to jOOQ User Group
sorry, i overread the part where you explained about adding data() to the TransactionContext instead. 
I will think about how i could make use of that!


lukas...@gmail.com schrieb am Mittwoch, 27. März 2024 um 09:26:20 UTC:

Bernd Huber

unread,
Mar 27, 2024, 1:23:44 PMMar 27
to jOOQ User Group
i think i have a solution now, that should be ok

i load the Configuration globally, and only once for the application, and without any request-specific configs.

I changed the Execution-Listener so it uses the "ctx.dsl().data" object to get the tenantId.

i create the DSLContext instances on demand, and add a given tenantId to the "ctx.dsl().data".

all Dao-Instances will be given the DSLContext or dsl.transaction instances to work with, and have also access to "dsl().data" to get the tenantId if needed.
Reply all
Reply to author
Forward
0 new messages