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.
lukas...@gmail.com schrieb am Samstag, 31. August 2024 um 08:18:49 UTC:
> Sure, with other formats, Java data representation may be a better choice.
> I was mostly focusing on the REST/JSON aspect.
>
> On Fri, Aug 30, 2024 at 5:06 PM 'Bernd Huber' via jOOQ User Group <
> jooq...@googlegroups.com> wrote:
>
>> 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...@gmail.com schrieb am Freitag, 30. August 2024 um 06:04:09 UTC:
>>
>>> 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/
>>>
>>> On Thu, Aug 29, 2024 at 8:27 PM 'Bernd Huber' via jOOQ User Group <
>>> jooq...@googlegroups.com> wrote:
>>>
>>>> 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.
>>>>
>>>>
>>>>
>>>> dominik...@gmail.com schrieb am Donnerstag, 29. August 2024 um
>>>> 18:13:08 UTC:
>>>>
>>>>> 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
>>>>>
>>>>> 5kil...@googlemail.com schrieb am Donnerstag, 29. August 2024 um
>>>>> 18:26:44 UTC+2:
>>>>>
>>>>>> 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 :)
>>>>>>
>>>>>> lukas...@gmail.com schrieb am Donnerstag, 29. August 2024 um
>>>>>> 07:07:32 UTC:
>>>>>>
>>>>>>> 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?
>>>>>>>
>>>>>>> On Wed, Aug 28, 2024 at 8:47 PM 'Bernd Huber' via jOOQ User Group <
>>>>>>> jooq...@googlegroups.com> wrote:
>>>>>>>
>>>>>>>> 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.
>>>>>>>>
>>>>>>>> --
>>>>>>>>
>>>>>>> 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
>>>>>>>>
>>>>>>>> <https://groups.google.com/d/msgid/jooq-user/c2fa6bd1-d999-452c-8a15-80a481680d90n%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>>>>>> .
>>>>>>>>
>>>>>>> --
>>>> 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/42e071be-71d0-45e6-a3af-501c7a64cc91n%40googlegroups.com
>>>>
>>>> <https://groups.google.com/d/msgid/jooq-user/42e071be-71d0-45e6-a3af-501c7a64cc91n%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>> .
>>>>
>>> --
>> 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/b2047d50-fde0-4557-bb30-5744df6d5159n%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/jooq-user/b2047d50-fde0-4557-bb30-5744df6d5159n%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>
--
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+unsubscr...@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.
