Jooq Codegenerator - Question about Java Records and Schema-Changes

[Bernd Huber]

I want to ask about the ".withRecords(true)" Functionality of the Jooq-Codegenerator...

- is it possible to let those Pojos (which are generated as java14-records) adhere to some specification, that makes sure that whenever the db-table-schema changes i get a error in my IDE to see that i need to:

  - add a field

  - remove a field

  - rename a field

With specification a Java-Interface would be optimal.

I guess the Java-Interface should work if it defines the default-getters of the java14-records.

For example:

public interface PersonSpec { String name(); int age(); }

public record Person(String name, int age) implements PersonSpec {
   // No additional methods needed; the record already provides them.
}


---

i currently think about using java14-Records instead of normal Pojos (with getters/setters) because the getters/setters can be harder to review in merge-requests. The getters/setters are often so much code that the review-person in the merge-request has much to review, while it would only be fields.

But i don't want to loose the advantage of letting the Pojo implement and interface, which makes sure that if my db-table changes its schema i am forced to also fix the Pojo for that.

[Dominik Hirt]

Why your JOOQ generated source files (as e.g. your POJOS) are within a git merge request ?

No JOOQ generated source file needs to be checked into your repo due to it's generated automatically for every build.

And if you call 

    mvn generate-sources or mvn clean generate-sources

after every DB Schema modification, then your (record based) POJOS alway match your DB Schema.

Such workflow did also a great job together with some DB migration tolls like Liquibase or Flyway: simple call 

    mvn liquibase:updateTestingRollback generate-sources

for every modification of your DB Schema. If the output of the two maven plugins is too noisy, you can reduce the clutter by

    mvn liquibase:updateTestingRollback generate-sources -Dorg.slf4j.simpleLogger.log.org.liquibase=warn -Dorg.slf4j.simpleLogger.log.org.jooq=warn

Hope that helps

Dominik

[Lukas Eder]

The question whether to put generated code in version control is a controversial one, with no clear winner. There are tradeoffs, pros and cons, see also:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-version-control/

See also this interesting discussion:

https://twitter.com/lukaseder/status/1805209257295393167

I agree though that if generated code is being checked in, any potential conflict can be ignored as it is just derived from a different conflict elsewhere (e.g. the DDL).

--
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/c84d38d0-d478-46b5-bf34-717c92bfcbe4n%40googlegroups.com.

[Lukas Eder]

Thanks for your message, Bernd,

Well that "specification" that you're talking about is just the previous version of your schema. You could generate interfaces from your schema with jOOQ (use the <interfaces/> flag) in a code generation run 1, then in code generation run 2, re-generate the records, and try to compile them against the previously generated interfaces. You can add interfaces to any generated class using generator strategies, e.g.:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy/

This does what you're asking for.

But I'm not really sure I understand the underlying use-case here. Is this because you would like to hand-write your POJOs in a way to match the generated code, but for some reason, *not* use the code generator for this?

--

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/c2fa6bd1-d999-452c-8a15-80a481680d90n%40googlegroups.com.

[Bernd Huber]

Hello Dominik and Lukas,

you are both right about that if i use the generated code as-is
(either by not checking it into git, or by letting it reside in the "generated"-folder)

i would not run into my described problem

- If checked in, the mergers could just assume that the code in the "generated"-folder is ok as auto-generated
and would not need to be reviewed.

- If not checked in, there would be no problem as it does not show in the merge at all.

---

Lukas assumption in his last comment is correct.

- My use-case is, that i need to hand-write my POJOs in a way to match the generated code

My use-case is described as follow as example with a new database-table:

- 1. i create a liquibase-migration that creates the new database-table(s)

- 2. i run the jooq-codegenerator which creates the Pojos for those new database-table(s)

- 3. i copy the Pojos from the "generated"-folder into the project "source"-folder

- 4. i now change the Pojos in the "source"-folder by adding relationships between the Pojos to create a "nested" structure that reflects the database-relationships (i rename the Pojos to DTOs also)

- 5. The so created DTOs need to be Serializable by Jackson to create JSON and return this nested-json to the Frontend for consumption.

This would be one of the typical use-cases why i need to hand-write my POJOs (DTOs) and not use the generated code for all my use-cases (only for some).

- If The database-table(s) are changed now, i need a way to detect that i need to change my handwritten Pojos to be noticed of those changes.

- For this i let my handwritten Pojos implement the jooq generated Interfaces, which work great so far!

- Sadly the Getters / Setters in my handwritten Pojos need to be reviewed by the Mergers and i search for a way to make my handwritten pojos more simple, while still be noticed of changes.

----

The best solution would be to use as less handwritten code as possible, but for now i see no way to get rid of the hand-written pojos, so i seek for a way to get rid of the getters / setters.
Java14 Records of Frameworks like "Lombok" seem to be able to help here.

But it's not really an important problem to me, -  it's only a small nuisance, which is also totally fine to work with :)

[Dominik Hirt]

Hello Bernd,

I now understand your problem better.
So the cause lies in the target structure into which you want to fetch your DB results. You need nested structures to map 1:n relationships...

I also had this problem. I came up with the following solutions:

I also define target DTOs that meet my requirements and can be structured / nested as required. I don't use a REST API, so I don't have to serialize to JSON, but I also want to define the DB results very precisely for my Vaadin UIs.


1) JOOQ multiset

This is of course the most elegant way to map 1:n database relationships directly into a DTO. Lukas has provided plenty of examples and tips, it's really great. You can use it to build DTO structures that are nested as deeply as you like and fill them directly with a single JOOQ query, e.g. something like this

class MyProjectListDTO {
private Integer projectId;
private List<Order> orders;

class Order {
private Integer id;
private List<OrderPosition> positions;
}
class OrderPosition {
private Integer id;
private String name;
private CustomerAddress address;
}
}

etc.

2) composition of pojos
Here you can build a DTO that contains any number of JOOQ pojos in a flat structure.

e.g.
class MyProjectListDTO {
private Project project;
private Order order;
private Customer customer;
}

When fetching, you can get help from a 3rd party library (modelmapper.org), which fills the class attributes of your DTO automatically with the help of prefixes of the DB result columns.

3) DTO extends POJO
You can of course also derive your DTO from a POJO and extend it accordingly.
Unfortunately, this does not work for JOOQ Records, as Lukas has already explained here in the group.

I hope this helps you a bit, because I have the feeling, without knowing all the details of course, that manual readjustment of the POJOS is not really necessary.

Kind regards

Dominik

[Bernd Huber]

Hello Dominik,

yes i exactly have such a use-case and your example fits well.

- I also use Jooq Multiset to fill the DTOs with data from the database. Really great!

- The DTOs in your example are missing Getters/Setters ? (Those are my problem)

I have a private pet-project that i use as private reference. I can show you an example DTO in this pet project:

- https://github.com/funkrusher/fk-framework-quarkus-jooq/blob/main/_modules/fk_product/src/main/java/org/fk/product/dto/ProductDTO.java

I fill this DTO with data (by using multiset) in this Repository-Class:

- https://github.com/funkrusher/fk-framework-quarkus-jooq/blob/main/_modules/fk_product/src/main/java/org/fk/product/repository/ProductRepository.java

Now as you can see the ProductDTO here is a very big class with many getters / setters.

The getters and setters are boilerplate that makes the ProductDTO fat and hart to quickly judge if everything is correct.

It works and is ok, but take for example a java14 record.

For the java14 record you would not need any Getters / Setters and only need to define the fields

So it would be much shorter and easier to look at for correctness.

[Lukas Eder]

Folks, while I don't have too many opinions on various DTO designs, have you considered skipping this step entirely? You're using MULTISET to serialise a SQL JSON result to DTOs, which you serialise back to JSON using Jackson. What for? Why not just serialise the JSON from the DB directly?

https://blog.jooq.org/stop-mapping-stuff-in-your-middleware-use-sqls-xml-or-json-operators-instead/

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/42e071be-71d0-45e6-a3af-501c7a64cc91n%40googlegroups.com.

[Bernd Huber]

Hi Lukas,

that sounds interesting!

i will give that i try and get myself accustomed with the JSON Serialisation directly by jooq.

- For the typical REST-Queries that expect nested JSON this should work!

There are some use-cases in my app i will need to think a bit how to solve them though.

For example:

- We have Exports (PDF; XML, JSON) with specific requirements

- We need to create those exports in a streaming manner (chunks of 250 items)

- We use MULTISET to prepare the nested DTOs that those Exports need to work with

I guess i can think about preparing the nested data that is needed for the export into a Row-Like Format (maybe with help of db VIEWS).

For PDF Creation i need to have a Pojo that i can push into my HTML template rendering engine (Quarkus Qute) and aferwards use Flyingsaucer to generate the PDF from the HTML.

I guess a flat Pojo should be good for the Exports, i just need to find a way to Flatten my Nested data into such a flat Pojo (with help of VIEWS).

---

But i think i should put effort into evaluating which use-cases are really unsolveable without the nested DTOs ..

So i will try with my pet-project to kick away all self-written DTOs and try to work with the Jooq-Records (Table-Records) instead
and also (as you suggested) try out the JSON-Serialization of jooq.

[Lukas Eder]

Sure, with other formats, Java data representation may be a better choice. I was mostly focusing on the REST/JSON aspect.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/b2047d50-fde0-4557-bb30-5744df6d5159n%40googlegroups.com.

[Bernd Huber]

Hi Lukas,

ah yes, i understand.

i tried around with generating pure JSON for REST-Responses with jooq and got it working.

If it was sure that my team would only need to return REST-Responses to Frontends, i would use this approach.

---

But having a Nested Model that represents the Datastructure available in the Backend,
seems to be a "Silver Bullet", or: "if you have a hammer, then everything is a nail" for all our needs :)

The Developers can also expect for all our Use-Cases to be able to use the same approach (which reduces the cognitive load).

So it seems i'm still good with searching for an approach to make my DTOs easier by removing getters/setters
or any other approach that helps me create a nested linkage between Pojos in my Backend.

---

i also needed to put a bit of effort to create the JSON-Response, and i was not able to get it with using MULTISET.
Code-Examples are a bit hard here to post, but i will just post it to show my approach.

It seems that i needed to use "jsonObject", "key", "jsonArrayAgg" instead of the multiset-approach.

            .select(
                jsonObject(
                    key("productId").value(PRODUCT.PRODUCTID),
                    key("price").value(PRODUCT.PRICE),

                    key("userId").value(USER.USERID),
                    key("email").value(USER.EMAIL),
                    key("firstName").value(USER.FIRSTNAME),
                    key("lastName").value(USER.LASTNAME),
                    key("roles").value(
                        select(
                            jsonArrayAgg(
                                jsonObject(
                                    key("role").value(ROLE.ROLEID)
                                )
                            )
                        ).from(USER).join(USER_ROLE).on(USER_ROLE.USERID.eq(USER.USERID))
                            .join(ROLE).on(ROLE.ROLEID.eq(USER_ROLE.ROLEID))
                            .where(USER.USERID.eq(PRODUCT.CREATORID))
                    ),
                    key("langs").value(
                        select(
                            jsonArrayAgg(
                                jsonObject(
                                    key("name").value(PRODUCT_LANG.NAME),
                                    key("langId").value(PRODUCT_LANG.LANGID)
                                )
                            )
                        ).from(PRODUCT_LANG).where(PRODUCT_LANG.PRODUCTID.eq(PRODUCT.PRODUCTID))
                    )
                )
            )

i somehow still think about ways i may use the jooq autogenerated pojos, by maybe extending them or something similar.

Then the getters/setters would only need to stay in the code-generated pojos and not in the hand-written.

[Bernd Huber]

i also asked chatgpt about some clues, and i guess i will focus on the "Inheritance" solution and evaluate
if i can create DTOs that extend the jooq-generated pojos/dtos to build the nesting.

"composition" also looked interesting

See the chatgpt:

- https://chatgpt.com/share/523d9852-8ee8-42ef-b162-0b32edc83bbf

[Alf Lervåg]

Just out of curiosity, what are you gaining by having POJOs in addition to the Record-classes that jOOQ generates?

I’ve found that maintaining deeply nested business entities and keeping these in sync with the database is a lot of work and leads to designs where it’s harder to use the database efficiently.

By skipping the repository pattern completely and letting business logic access the tables directly (through jOOQ that is) we seem to end up with simpler and faster solutions.

Re-use is also much easier since we can make strongly typed methods on our tables that can be shared at a much more granular level than I’ve ever seen before outside of views, stored procedures, macros etc inside the database.

For example we can have methods like:

- public static Collection<OrderField<?>> getOrderByPriorityFields(Issue table)

- public static Collection<OrderField<?>> getOrderByPriorityFields(Repository table)

- public static Collection<OrderField<?>> getOrderByPriorityFields(Milestone table)

These methods encapsulate the business logic of what it means to order the different entities in our system by priority in a way that is very re-usable and easy to test.

Alf Lervåg

31. aug. 2024 kl. 10:47 skrev 'Bernd Huber' via jOOQ User Group <jooq...@googlegroups.com>:



To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/48bfcb25-d438-44f4-8273-ea1b43f2ec71n%40googlegroups.com.

[Bernd Huber]

Hello Alf,

about your first question: "what are you gaining by having POJOs in addition to the Record-classes that jOOQ generates?":

i'm using the jooq-codegenerator with following flags:

.withInterfaces(true)
.withPojos(true)

The jooq-codegenerator generated following folders for me that contain the generated code:

- interfaces

- pojos

- records

The "records" folder contains the classes that i use primarily for CRUD-operations on their associated db-tables.
- I can not use the "record"-classes in this folder as frontend-results though.

- they contain jooq-specific code, are not simple pojos and Tools like Swagger also have problem with them as results

The "pojos" folder contains the classes that i can also use as REST-Request and REST-Response data to get and return from/to the frontend (serialized as json)

The classes in the "pojos" folder have no relationship between each other and each pojo is describing the db-table its assoicated with.

When i now take those pojos and add nesting to them i am able to satisfy business use-cases like the following:

- a frontend grid that needs to be remote-paginated and filterable and needs to show data in the grid that consists of data from a db-table but also of data from related tables of this db-table.

for example:

we have a requirement that each Item has language-specific texts in multiple languages (de, fr, it, en, ...)

So there is always a main-table containing data that does not contain language-specific texts, and a directly related table that contains the language-specific texts in the according language.

For example

Table Product (productId, price, stock)

Table ProductLang (productId, langId, name, description)

1 Product has 1-N ProductLang entries associated.

When the user changes the display-language the Grid in the frontend should show the "name", "description" columns in the grid with this display-language.

A different Example would be a "UserModel" where each "User" would have "Roles" associated as well as "Features". 

So 1 User would have N Roles and N Features.

It is just convenient to deliver the User-Object to the frontend as a JSON-Object that also contains the Roles and Features of the User.

So to conclude:

- The records do not contain relationships and can not be serialized in a good way to share with the frontend

- The pojos can contain relationships to associated pojos and can be serialized to be shared with the frontend

---------------------------------

MULTISET in the Repository has helped me write a Remote-Pagination Logic in the Backend where the Filters, Sorters on all associated tables

can be given by the frontend to the backend, and the Repository can make Filtering and Sorting possible while also supporting Streaming the Results and Paginating the results.

Im not sure if i understand what you mean by "skipping the repository pattern completely".

---------------------------------

I can understand the wish to drop "maintaining deeply nested business entities and keeping these in sync with the database" alltogether,
if that is possible.

A simplification or removal of business use-cases can help here, to make the app so easy and simple that comlex use-cases are not needed,

but as soon as that seems to be the case, then it may be better to use Tools like "Supabase" or similar things where the Backend is so slim and logic-less,

that its just a passthrough directly to the database.

In my opinion jooq has a very big pro, where such "deeply nested business entities" are necessary and needed,
because there are harder business-use-cases that need backend-logic, backend-scheduling etc.

[Alf Lervåg]

Thank you, that’s a good explanation and I see better where you’re coming from.

I definitely see the need for nested models for user interfaces and APIs that cater to these. As a matter of fact we’re currently building a large GraphQL API in addition to our existing OpenAPI-based REST APIs.

My point though, is that:

1. these nested models are just representations of our normalized relational models

2. the nested models are less stable (change more frequently) than the normalized models

3. we need to maintain more than one nested models because of things like versioning and legacy support of old clients etc

In effect, I believe we’re better off writing our business logic closer to the normalized models.

The main issue then is mapping between models. For this we’ve ended up with a declarative approach combined with code generation. In short, we’re adding directives/custom metadata in our GraphQL and OpenAPI specifications that map our API models and fields to our relational tables and fields. Since jOOQ allows us to write methods such as mentioned earlier, it’s easy to write extra business logic and calculations and stuff that can be used here in addition to the actual tables and columns in the database.

Based on this configuration it’s possible to generate the entire implementation of the API.

As you say, tools like Supabase and Hasura etc can do a lot of this as well, but I find that jOOQ lets us take this to the next level. At the same time it allows us to keep our business logic safely on what I consider a very stable technology stack. 

Alf Lervåg

31. aug. 2024 kl. 16:29 skrev 'Bernd Huber' via jOOQ User Group <jooq...@googlegroups.com>:



To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/b46c3813-259e-4c90-9dce-715aff3b961dn%40googlegroups.com.