Generate @Transactional on DAOImpl and generated DAOs query-methods

[AnghelLeonard]

Use case:

Consider a Spring Boot service method that contains some time-consuming tasks that wrap a call of a built-in query-method such as findById(). These time-consuming tasks don't interact with the database.

public void foo() {
    // some time-consuming task (1)
    // call findById()
    // more time consuming task (2)
}

Because findById() is annotated with @Transactional(readOnly=true) the above method run a short-transaction. The time-consuming tasks run outside the transactional-context.

On the other hand, calling the jOOQ findById() inherited from DAOImpl is not transactional by default.
This means that the above method should be annotated explicitly with @Transactional(readOnly=true) as follows:

@Transactional(readOnly=true)
public void foo() {
    // some time-consuming task (1)
    // call findById()
    // more time-consuming task (2)
}

But, this time, we obtain a long-running transaction. Spring opens the transaction when foo() is called and closes it at the end of this method. The time-consuming tasks (e.g., some functional operations) run inside the transaction that holds the database connection open.

Possible solution you'd like to see:

Optionally, jOOQ annotates DAOImpl and generated DAOs with @Transactional in the same manner as the built-in (SimpleJpaRepository). Basically, jOOQ generated code aligns to Spring Boot built-in code.

Possible workarounds:

As workarounds, the code can be split into multiple methods to isolate the findById() call in a method or rely on Hibernate 5.2.10 that allows us to delay the connection acquisition by disabling auto-commit mode (of course more time-consuming task (2) is not affected by this setting).

The challenge that I see is how to optionally annotate DAOImpl. Having another version of it especially for Spring Boot?! For generated DAOs, probably a new tag under <generate/> will do the trick.

Versions:

• jOOQ: 3.13.5
• Java:
• Database (include vendor):
• OS:
• JDBC Driver (include name if inofficial driver):

[lukaseder]

Thanks a lot for your suggestion. It makes perfect sense the way you explain it. We already support generating some Spring annotations on generated DAOs, we could also add the @Transactional annotation on other generated methods.

I prefer not to annotate DAOImpl and create a dependency in our core library. But I can see at least 3 viable options of implementing this:

1. All the DAOImpl methods are already non-final, so we could override them on each generated DAO, and add the annotation there.
2. We could generate a SpringDAOImpl class extending the DAOImpl class, and put the annotations there
3. We could offer a jOOQ-spring-extensions module and implement the SpringDAOImpl class there

I personally favour option 2. 1) produces an unnecessary amount of repetitive code (which is likely to be scanned by Spring, so slow startup times), and 3) might be a bit premature.

[AnghelLeonard]

Option 2 looks the more reasonable indeed.

[AnghelLeonard]

Maybe, instead of a class as SpringDaoImpl will be better to generate repository-interfaces for the DAO classes (including DAOImpl). This way, Spring Data users can optionally take advantage of @Transactional and they can glue (if they want) SpringData DAO, jOOQ DAO (user-defined and generated) in a single interface. Practically, inject a single interface in service-class and access the joined-DAO layers as a single DAO layer. Probably, this will require to rename DAOImpl.findAll() and DAOImpl.findById() to avoid conflicts with Spring Data DAO that has methods with similar names.

[lukaseder]

If someone doesn't want to let go of Spring Data, they probably don't get much value out of jOOQ's DAOs. They should use spring repositories directly. They can still use the jOOQ DSL for SQL generation of complex dynamic queries, of course, but why use jOOQ DAOs here and Spring repositories there? Where would a line be drawn? I don't have a clear picture here. Your original feature request was about being able to have more fine grained transactions, which makes sense.

In other words (and keeping in mind that DAOs are 1) quite the bikeshed, 2) not at all jOOQ's core value proposition): What kind of architecture would benefit from an approach where people mix jOOQ DAOs and Spring repositories?

[lukaseder]

Looking into this now for jOOQ 3.17.0

[lukaseder]

The generation of the new AbstractSpringDAOImpl class has been rather painful, given that

• All methods need to be overridden (the default @Transactional annotation on the class doesn't affect methods from super types, unless they're overridden)
• We need to generate this class in Java, Scala, Kotlin

But it's done now. In hindsight, option 3 might have been better. I might still switch this in the future.