jOOQ support

[pblanchardie]

Overview of the feature request

Provide an option to generate some jOOQ code.

Motivation for or Use Case

Provide a way to use jOOQ with the JHipster's generation logic and simplicity, and start writing complex yet performant dynamic queries that can't be written with JPA/specifications/QueryDSL, and avoid using SQL views and stored functions.

Related issues or PR

155 wanted to replace Spring Data JPA, but I only want to provide an additional option for complex select queries.

https://github.com/willmitchell/jooq-jhipster-generator This module doesn't seem to be maintained, but perhaps some JHipster users will want to contribute and add this module to the marketplace.

https://github.com/jhipster/generator-jhipster/issues/1762#issuecomment-124818822 @jdubois in this comment, you're saying that jOOQ can be expensive, but you added the spring-boot-starter-jooq dependency to jhipster/jhipster-dependencies/pom.xml one year ago (https://github.com/jhipster/jhipster/commit/32d6c974928a5eb9bf3536c25c7b15af7d023a99), so I thought the question was worth asking! Or is there a free alternative with all features?

• [x] Checking this box is mandatory (this is just to show you read everything)

[atomfrede]

I like jooq very much and it is quite easy to integrate with spring boot I don't think we can maintain an alternative to hibernate here.

[pblanchardie]

I agree with the ease of integration. I don't talk about an "alternative", but the option to use jOOQ as a complementary tool. Indeed, it would be nice to collaborate on a jooq-jhipster-generator module.

[cbornet]

You can indeed build a module for this. Is there more than just adding the JOOQ dependency to the POM ?

[atomfrede]

It depends what you want to do. I would like to have jooq codegen which requires maven/gradle plugin and a running database. And it is more or less not really possible to use different databases for Dev and production

[jdubois]

jOOQ can only be a module, it's far too much work for us to integrate.

A quick look at Google trends, etc, show that few people use it. It's not against the product by itself, but by experience when a technology isn't widely used we have too much trouble to find contributors. We had the same issue with Cassandra & Couchbase, but fortunately we solved those by having dedicated commiters on each: this can work if we have an engagement from someone (ideally from the company, but I understand it's a one-person company), but it's really hard.

[atomfrede]

In fact Lukas has now I think two employees but pretty sure they have other priorities than providing a jhipster integration

On Thu, Mar 28, 2019, 11:03 Julien Dubois notifications@github.com wrote:

jOOQ can only be a module, it's far too much work for us to integrate.

A quick look at Google trends, etc, show that few people use it. It's not against the product by itself, but by experience when a technology isn't widely used we have too much trouble to find contributors. We had the same issue with Cassandra & Couchbase, but fortunately we solved those by having dedicated commiters on each: this can work if we have an engagement from someone (ideally from the company, but I understand it's a one-person company), but it's really hard.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/jhipster/generator-jhipster/issues/9486#issuecomment-477525947, or mute the thread https://github.com/notifications/unsubscribe-auth/AAMaidMd4e3Ca0O6Ty3jGe2vXBOgsjgaks5vbJNpgaJpZM4cN5uV .

[pascalgrimaud]

I think we agree it should go into module, so let's close this. Don't hesitate to ask help if needed, if you decide to start a module or, ping us on Twitter to promote your module

[mraible]

Is there anyone on this thread (or otherwise) that's willing to do the work on a JOOQ module? We have some interest on Twitter - and an offer from @lukaseder to match a $500 bounty!!

[mshima]

@mraible I can take a look, I've used JOOQ for advanced searches before, I will take a look to remember what I've done. @lukaseder what goals do you have in mind?

[atomfrede]

👍

[lukaseder]

@atomfrede pretty sure they have other priorities than providing a jhipster integration

We'd like to focus on jOOQ development (many exciting features coming up), indeed. But will be happy to help with any questions that may arise.

@lukaseder what goals do you have in mind?

You folks who have used the combination can probably better identify how a module or integration can make this integration easier for you. Based on this tutorial video by @mraible: https://youtu.be/uQqlO3IGpTU, I'd say that these things would definitely add value (without knowing what parts can be done from a module, and what parts would have to be done in core JHipster):

1. In the project setup phase (see screenshot below), there could be options for additional ORMs / SQL APIs, like jOOQ / MyBatis / etc. While this issue here is only about jOOQ, it's certainly worth exploring what other options could be offered to users setting up a project, and what that would mean for the setup workflow. This could be offered as an alternative to Hibernate (probably much more work), or an optional add-on (probably less work). I'd start with the add-on use-case, where people use Spring Data JPA for most database interactions, and jOOQ for occasional queries / analytics.
2. The out of the box config would be using the jOOQ Open Source Edition, probably from the Spring Boot jOOQ starter. If users prefer the commercial distributions, they can just tweak the Maven / Gradle dependency a little bit, and they're all set: https://blog.jooq.org/2019/06/26/how-to-use-jooqs-commercial-distributions-with-spring-boot
3. A jOOQ DSLContext should be configured and injectable based on the chosen prod / dev database configuration. Spring Boot already does some work here, I guess this is mostly about generating the required Spring Boot config
4. From what I understand, JDL is used to generate JPA entities and Liquibase migration scripts? That output would be a perfect source of metadata for jOOQ's code generator, which could be configured automatically in Maven or Gradle to pick up that generated output to generate jOOQ code: https://www.jooq.org/doc/latest/manual/code-generation/codegen-liquibase/, https://www.jooq.org/doc/latest/manual/code-generation/codegen-jpa/. This intermediate meta data source would help avoid having to read JDL from the jOOQ code generator.

Please let me know what you think

[atomfrede]

Sounds like a good plan.

The most questions I have is how JPA/JOOQ are combined. I know Thorben told me often Hibernate/Jooq are a perfect fit, but I never tried. We usually use jooq and mapstruct to map records to java classes. Micronaut So if we would "just" have to create jooq setup (DSLContext, Codegen) and "just" generate the jpa entities as is and use the spring data repositories as we do now this sounds quite "easy".

EDIT: sorry didn't read carefully. Point 1 already mentions the addon case, so default crud still via hibernate/jpa as a starting point, right? In that case it seems pretty straightforward for a module which can be applied on top of the generated application in case you are in need for advanced queries with jooq by modifying the pom, adding the jooq configuration and hooking into entity creation to trigger code generation from the updated liquibase scripts.

[lukaseder]

EDIT: sorry didn't read carefully. Point 1 already mentions the addon case, so default crud still via hibernate/jpa as a starting point, right?

Yes. Both use-cases are valid, but I think that the addon use-case is much simpler to implement first, because:

• it builds on top of existing (and tested) work
• it actually serves both use-cases. If someone wants to work with jOOQ only, they have some excess JPA entities lying around, but jOOQ still works. If requested, at a later stage, the JPA entites could still be removed by the project setup, optionally (I don't know JHipster well enough to assess what that would mean)

[jdubois]

It's awesome to see everybody teaming up this. Please note that this needs to be a blueprint (it says "module" earlier in this thread - we can discuss this but I believe a blueprint is better), as maintenance is otherwise going to be a big issue. This blueprint can be hosted in the JHipster org if you want, I'll do a specific team and give you all RW access - but in that case it will need to comply to our org settings, like signing our CLA. In that case, the code would belong to the JHipster Developers Assocation, which protects it, but I know @lukaseder is having a commercial business on this, so please be careful - this is what we did for the Micronaut blueprint, and it works great, but it depends on what people want/need.

[lukaseder]

but I know @lukaseder is having a commercial business on this, so please be careful

I don't think that any code of this blueprint will affect jOOQ's own code, so I don't think CLAs or blueprint ownership will be an issue. In fact, I prefer this to be owned / licensed by the JHipster Developers Association rather than e.g. Data Geekery.

[atomfrede]

@jdubois Yes a blueprint would be better.

[atomfrede]

Should I setup a minimal jhipster application with additional jooq support to get started?

[mshima]

@mraible I can take a look, I've used JOOQ for advanced searches before, I will take a look to remember what I've done. @lukaseder what goals do you have in mind?

Here is the initial version: https://github.com/mshima/generator-jhipster-jooq

If this is ok, I could move it to https://github.com/jhipster/generator-jhipster-jooq.

For now it just adds spring-boot-starter-jooq dependency and writes an empty README.jooq.md. DSLConstext is available at spring application context.

• From what I understand, JDL is used to generate JPA entities and Liquibase migration scripts? That output would be a perfect source of metadata for jOOQ's code generator, which could be configured automatically in Maven or Gradle to pick up that generated output to generate jOOQ code: https://www.jooq.org/doc/latest/manual/code-generation/codegen-liquibase/, https://www.jooq.org/doc/latest/manual/code-generation/codegen-jpa/. This intermediate meta data source would help avoid having to read JDL from the jOOQ code generator.

Liquibase is optional, would make sense to use jpa by default. We could make both codegen as alternative.

@lukaseder how difficult is to generate our own definitions? Is this something you recommend?

[atomfrede]

Do we make liquibase optional for the generator?

As an idea how to make the DSLContext more useful we could generate for each repository we create a customized repository and the implementation has the DSLContext injected to writing queries with jooq can be done directly.

[lukaseder]

@lukaseder how difficult is to generate our own definitions? Is this something you recommend?

You shouldn't generate your own jOOQ code, as that uses internal jOOQ API which is subject for change between minor releases. But you could implement the org.jooq.meta.Database type from the jOOQ-meta module: https://github.com/jOOQ/jOOQ/blob/main/jOOQ-meta/src/main/java/org/jooq/meta/Database.java

An example to base your implementation on would be the XMLDatabase: https://github.com/jOOQ/jOOQ/blob/main/jOOQ-meta/src/main/java/org/jooq/meta/xml/XMLDatabase.java, which acts as a meta data source for XML-based meta data: https://www.jooq.org/doc/latest/manual/code-generation/codegen-xml

While this would allow for making both JPA and Liquibase optional, it would add a dependency on JDL (not sure if that's mandatory within JHipster?)

In any case, this is about finding a good default, opinionated out-of-the-box behaviour, I guess? It should always be possible to override it...

[mshima]

Do we make liquibase optional for the generator?

You are right, it's mandatory for sql databases. --skip-db-changelog is designed to let the use update their changelogs manually. Liquibase should be safe to be the default then.

As an idea how to make the DSLContext more useful we could generate for each repository we create a customized repository and the implementation has the DSLContext injected to writing queries with jooq can be done directly.

Sure, we should implement a JooqRepository for each entity and add stubs for writing/seaching (a jpaFiltering alternative?).

[mshima]

@lukaseder how difficult is to generate our own definitions? Is this something you recommend?

You shouldn't generate your own jOOQ code, as that uses internal jOOQ API which is subject for change between minor releases. But you could implement the org.jooq.meta.Database type from the jOOQ-meta module: https://github.com/jOOQ/jOOQ/blob/main/jOOQ-meta/src/main/java/org/jooq/meta/Database.java

👍

An example to base your implementation on would be the XMLDatabase: https://github.com/jOOQ/jOOQ/blob/main/jOOQ-meta/src/main/java/org/jooq/meta/xml/XMLDatabase.java, which acts as a meta data source for XML-based meta data: https://www.jooq.org/doc/latest/manual/code-generation/codegen-xml

While this would allow for making both JPA and Liquibase optional, it would add a dependency on JDL (not sure if that's mandatory within JHipster?)

Not sure why add a dependency on jdl? It's merged to generator-jhipster repository now (jhipster 7).

In any case, this is about finding a good default, opinionated out-of-the-box behaviour, I guess? It should always be possible to override it...

Ok thanks. We should go for liquibase by default and add an alternative for jpa for now.

[lukaseder]

Not sure why add a dependency on jdl?

Since you didn't state your motivation for "generating your own definitions", I was assuming the reason for your question was that you might want to bypass Liquibase/JPA and derive generated jOOQ code directly from something else, i.e. JDL. That was just an assumption on my side.

I still think that as a first step, generating code from either Liquibase and/or JPA models will be the simplest, for now.

[mshima]

Since you didn't state your motivation for "generating your own definitions", I was assuming the reason for your question was that you might want to bypass Liquibase/JPA and derive generated jOOQ code directly from something else, i.e. JDL. That was just an assumption on my side.

No problem, JDL and prompts are supported, both should work fine.

[atomfrede]

Here is a small sample https://github.com/atomfrede/jhipster-jooq-addon-sample. It adds

• All required jooq depenencies
• Code gen via maven plugin + liquibase (resulting classes are under generated, so not under source control
• Customized UserRepository which can use jooq's DSLContext

The changes are quite small, so pretty sure we can have a working blueprint in the next couple of weeks.

[mshima]

Someone could create jhipster/generator-jhipster-jooq repository?

[mshima]

JPA generator doesn't generates jooq definitions. Looking at https://www.jooq.org/doc/latest/manual/code-generation/codegen-jpa/, it seem the entities should be on a different module, so JPA generator isn't suitable for jhipster. We generate 1 module for everything. @lukaseder can you confirm?

[jdubois]

I can create jhipster/generator-jhipster-jooq and create a specific team for it, who wants to be in it?

[jdubois]

I created the project but there's some strange issue with GitHub at the moment, I can't create the team right now

[jdubois]

OK I just created it! I invited @lukaseder @mshima @atomfrede Let me add the license and the CoC. BTW we should change all our licenses soon, so it points to the JHipster Developers Association, our non-profit org

[mshima]

Should we move this issue to jhipster/generator-jhipster-jooq?

[lukaseder]

Looking at https://www.jooq.org/doc/latest/manual/code-generation/codegen-jpa/, it seem the entities should be on a different module, so JPA generator isn't suitable for jhipster. We generate 1 module for everything. @lukaseder can you confirm?

Yes indeed, see https://github.com/jOOQ/jOOQ/issues/6945. Pretty sure with Maven, you have to extract the entities in a separate module for jOOQ to be able to pick them up as the JPADatabase uses some programmatic Hibernate API to reverse engineer them once they're compiled, rather than using the APT part of the compile phase. I'm not convinced it would be easy to change that.

With Gradle, there might be more possible tweaks to get the things to be done sequentially without going completely against some hard-wired phases as Maven specifies.

[mshima]

@atomfrede @lukaseder do you want any other feature for the first version? I think we should add a service template for User and release it.

[lukaseder]

I'm all for early user feedback, so as soon as it works, releasing sounds great!

[mraible]

@mshima How's progress? Is this ready for an initial release? If it is, let's release, promote, and iterate!

[mshima]

@mraible yes, I will merge the pending PR and released this weekend. @pascalgrimaud not sure if I can create the package at npm and then transfer the ownership to jhipster or someone should create it at jhipster org.

[pascalgrimaud]

@mshima : I need to have a look how to do it, I don't remember well how works the organization in NPM :)

[mshima]

@pascalgrimaud who has admin rights to jhipster's npm organization? Someone needs to create generator-jhipster-jooq package in npm and a me as uploader.

[pascalgrimaud]

@mshima : I can create the team at npm organization, but I need to remember how to do it. Can you give me your NPM username ? I'll try to create the team after my day work, in 2 or 3 hours

[mshima]

@pascalgrimaud mshima too.

[mshima]

@pascalgrimaud take your time, just remembering about this.

[pascalgrimaud]

really sorry for the delay, it should be good for NPM organization now, @mshima

[mshima]

@pascalgrimaud thanks.

[pascalgrimaud]

As the 1st release has been done https://www.npmjs.com/package/generator-jhipster-jooq Can we close this ticket @mshima ?

Don't forget to claim your bounty @mshima : it's well deserved !

[mshima]

@pascalgrimaud I think we can close this. Next steps should be done in separated issues:

• Split filtering related code from entity's Resource into FilteredResource. (Related to https://github.com/jhipster/generator-jhipster/issues/12702)
• Override filtering resource/service with another implemented with jOOQ.

[mshima]

@pascalgrimaud bounty claimed https://opencollective.com/generator-jhipster/expenses/36150.

generator-jhipster-jooq is ready for JHipster 7, time to claim. There a bug in their side due to liquibase 4, reverting to liquibase 3 it works.

[pascalgrimaud]

@mshima : approved ;)