Hi Akash, Thanks for sharing your ideas.
Indeed, the generated POJOs (as well as DAOs, records, etc.) model a 1:1 relationship with the underlying table's raw data. jOOQ does not attempt to implement an object graph representation of your relational model, just like your relational database doesn't actually model the object graph (it has constraints, and those can map to a graph representation, but it really doesn't implement a graph, inside of your RDBMS). Should we evolve more into the direction of a true ORM? That's been discussed on this list and elsewhere many times in the past. It's a tradeoff. By not doing this, and not going into the weird edge cases of populating object graphs from SQL queries (deduplication, N+1, caching, identity management etc. etc.) jOOQ could invest more into much much better SQL support. jOOQ is exceptionally strong at dynamic SQL and type safe mapping, for example. You won't find a match out there. jOOQ is weak at object graph persistence, because that has never been the focus. Starting from jOOQ 3.14 (with SQL/JSON and SQL/XML support) as well as jOOQ 3.15 (with MULTISET and nested record support), jOOQ embraces more advanced ORDBMS features. It is now easy to project nested collections and data structures in a type safe way. This is extremely powerful both in jOOQ and in standard SQL, and I have not yet seen this power in other ORMs, to this extent. You can now populate object trees (not graphs) from SQL queries in a type safe way. The idea here is that you want to map arbitrary queries to arbitrary tree structures. Some ORMs claim that this is an edge case, and people mostly want to always fetch the entirety of the graph as stored in the database, without any fancy ad-hoc projections or anything. I think this is wrong. The edge case is to fetch *everything* (which is mostly being done out of laziness, and leads to inefficiencies, because the projections are too big, and the joins can't be eliminated, etc.), the common case is to fetch exactly what you need, and push as much computation into the database as possible, e.g. using aggregation, etc. So, jOOQ aims to offer as many powerful querying primitives as possible, and once they're stable, adds more convenience to simplify the case where you do want to project everything. It's possible, of course, to generate nested child collections and references to parent entities also in POJOs in the future, to allow users to opt in to mapping everything all the time. This hasn't been explored yet. Even if this was implemented, it probably won't work the way you're expecting now, certainly not via JPA annotations. The biggest problem in any such approach is the fact that jOOQ lets you work only with data (tuples, values, etc.) whereas the expectation of using an ORM is to work with graphs of objects (classes with identity). The concept of an identity is very weird in SQL. It was introduced in ORDBMS via REF types, but apart from Oracle, I haven't seen any mature implementations in the wild, and even within Oracle, hardly anyone uses those ORDBMS extensions. PostgreSQL and Informix (the other 2 ORDBMS) didn't go this far, and stayed in tuple/value land, where nesting of data structures is possible, but only in tree form, not in graph form. For example, if you have a many-to-many relationship and wish to materialise that in graph form, then you'll expect the graph to be consistent (A1 references multiple B, and each B must also reference the same A1 instance). JPA implementations don't implement this correctly, if I'm not mistaken? They leave collection consistency management to the user. They don't have many-to-many capable collection types, such as Eclipse EMF, for example. That's a tradeoff to improve usability, I guess, you can use any ArrayList or HashSet to model your associations. The point I'm trying to make is that the more you think about object graph persistence, the hairier it gets. Thus, my claim that you can already do what you need to do with MULTISET, etc. It's just not going to work the way you expected (from JPA). I can't tell you whether you're right or whether jOOQ is right. jOOQ has this vision. You have your expectations. Up to you to decide whether jOOQ's visions align with your expectations. On Tue, Oct 18, 2022 at 6:33 PM akash verma <[email protected]> wrote: > Okay, I have to generate CRUD API and for this project input is SQL > Script and from the SQL script we have to generate (POJOS, DAOs, > Controller and service layer). with JOOQ we can generate DAOs and POJOS and > from the POJOs we are generating Controller and Service layer with help of > templating . > > > ISSUE: > If we are using the Pojos generated by the JOOQ there is nothing in > Generated Pojos through which we know the Relationship in the two tables. > Example:- I have two tables one is AUTHOR and other is BOOK, AUTHOR can > have Multiples BOOK, so here the relationship is OneToMany. Now the AUTHOR > pojos have nothing through which we know the relationship with BOOK and > AUTHOR and BOOK has relationship of OneToMany. > > AUTHOR POJO:- > > @Id > @Column(name = "ID", nullable = false, precision = 7) > private Integer Id; > @Column(name = "FIRST_NAME", length = 50) > private String FirstName; > @Column(name = "LAST_NAME", nullable = false, length = 50) > private String LastName; > @Column(name = "DATE_OF_BIRTH", precision = 10) > private LocalDate DateOfBirth; > @Column(name = "YEAR_OF_BIRTH", precision = 7) > private Integer YearOfBirth; > @Column(name = "BOOK_ID", precision = 7) > private Integer BookId; > > public Author() {} > > public Author(Author value) { > this.Id = value.Id; > this.FirstName = value.FirstName; > this.LastName = value.LastName; > this.DateOfBirth = value.DateOfBirth; > this.YearOfBirth = value.YearOfBirth; > this.BookId = value.BookId; > } > > @ConstructorProperties({ "Id", "FirstName", "LastName", "DateOfBirth", > "YearOfBirth", "BookId" }) > public Author( > Integer Id, > String FirstName, > String LastName, > LocalDate DateOfBirth, > Integer YearOfBirth, > Integer BookId > ) { > this.Id = Id; > this.FirstName = FirstName; > this.LastName = LastName; > this.DateOfBirth = DateOfBirth; > this.YearOfBirth = YearOfBirth; > this.BookId = BookId; > } > > > In this we have nothing through which we know that AUTHOR have a > relationship with BOOK pojo here the (book_Id is foreign Key). > > So now we are using the pojos in the controller layer as a DTO and now if > we want to insert in the AUTHOR table we have to also have to pass the BOOK > object in the post BODY and in the pojos we don't have object of BOOK. (For > insertion we use the store() method). > > All the things here are AUTO GENERATED filling templates so we can't hard > code anything. > > I am going through the Docs you shared. > Thank you for Docs. > > Hope this detail will help you to get my questions. > > > > > On Tuesday, 18 October 2022 at 15:33:58 UTC+5:30 [email protected] wrote: > >> I'm pretty sure jOOQ can map all of the things you want to map! But as >> long as you don't ask the question of what you want to do *specifically*, I >> won't be able to answer it. I mean, you can't use JPA annotations to map >> things (the Y in your question). But you don't want to use JPA annotations, >> you want to map things. You can map nested collections and nested records >> (the X in your question), but not with JPA annotations. How about an >> example? What specific example would illustrate how you'd like to map >> things? >> >> On the other hand, perhaps I can just direct you to the relevant docs of >> how to do things in jOOQ? >> >> - >> https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/ >> - >> https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/multiset-value-constructor/ >> - >> https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/nested-records/ >> - >> https://www.jooq.org/doc/latest/manual/sql-execution/fetching/ad-hoc-converter/ >> >> Also useful: >> >> - Coming from JPA: >> https://www.jooq.org/doc/latest/manual/coming-from-jpa/ >> - OneToOne and ManyToOne: >> https://www.jooq.org/doc/latest/manual/coming-from-jpa/from-jpa-manytoone/ >> - OneToMany and ManyToMany: >> https://www.jooq.org/doc/latest/manual/coming-from-jpa/from-jpa-onetomany/ >> >> It will be helpful to let go of "It must work just like in JPA" and >> embrace "How can I make it work with jOOQ?" >> >> Hope this helps, >> Lukas >> >> On Tue, Oct 18, 2022 at 11:27 AM akash verma <[email protected]> wrote: >> >>> Yes, you are right I want some sort of mapping and JOOQ is not able to >>> do so as of now. >>> >>> Thank you. >>> >>> On Tuesday, 18 October 2022 at 12:50:58 UTC+5:30 [email protected] >>> wrote: >>> >>>> We're clearly deep into https://xyproblem.info land. You want to do X >>>> (some sort of mapping?), and you think you can achieve X using Y (JPA >>>> annotations). But that's not how jOOQ works. >>>> >>>> Let's start over again: What do you want to do? >>>> >>>> On Tue, Oct 18, 2022 at 6:50 AM akash verma <[email protected]> wrote: >>>> >>>>> if able to do then we can use that POJOs and DAOs for the CRUD Api, if >>>>> two tables have relationship like (OneToMany). >>>>> As of now we are not able to get anything from the Pojos to know the >>>>> realtionship. >>>>> >>>>> On Tuesday, 18 October 2022 at 00:08:56 UTC+5:30 [email protected] >>>>> wrote: >>>>> >>>>>> And why would jOOQ do that? >>>>>> >>>>>> Am Montag, 17. Oktober 2022 schrieb akash verma <[email protected]>: >>>>>> >>>>>>> I got my answer. >>>>>>> >>>>>>> Thank you so much, Lukas. I'm eagerly waiting for the JOOQ update in >>>>>>> which JOOQ is able to do mapping annotations in POJO classes like >>>>>>> (@OneToOne, @OneToMany). >>>>>>> >>>>>>> On Monday, 17 October 2022 at 22:16:45 UTC+5:30 [email protected] >>>>>>> wrote: >>>>>>> >>>>>>>> jOOQ-meta runs its own queries against the information schema / >>>>>>>> catalogs / dictionary views, etc. JDBC DatabaseMetaData is >>>>>>>> insufficient. >>>>>>>> >>>>>>>> I think the manual links I've shown sufficiently explain how the >>>>>>>> "connection free code generation" works? >>>>>>>> >>>>>>>> You might have a *specific* question, but I didn't see it yet... >>>>>>>> >>>>>>>> On Mon, Oct 17, 2022 at 6:12 PM akash verma <[email protected]> >>>>>>>> wrote: >>>>>>>> >>>>>>>>> I want to know how the JOOQ get the Metadata from the Database in >>>>>>>>> the Connection code generation mode. Does it use the >>>>>>>>> connection.getMetaData() method? >>>>>>>>> >>>>>>>>> and In connection-free code generation modes how JOOQ Get the >>>>>>>>> MetaData from the Script OR XML file? >>>>>>>>> >>>>>>>>> >>>>>>>>> On Monday, 17 October 2022 at 20:37:12 UTC+5:30 [email protected] >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> I'm not sure what exactly you're looking for, but these are some >>>>>>>>>> out of the box alternatives to connecting to a live database >>>>>>>>>> instance for >>>>>>>>>> code generation: >>>>>>>>>> >>>>>>>>>> - >>>>>>>>>> https://www.jooq.org/doc/latest/manual/code-generation/codegen-jpa/ >>>>>>>>>> - >>>>>>>>>> https://www.jooq.org/doc/latest/manual/code-generation/codegen-xml/ >>>>>>>>>> - >>>>>>>>>> https://www.jooq.org/doc/latest/manual/code-generation/codegen-ddl/ >>>>>>>>>> - >>>>>>>>>> https://www.jooq.org/doc/latest/manual/code-generation/codegen-liquibase/ >>>>>>>>>> >>>>>>>>>> Note that it's not uncommon to spin up a "live" database using >>>>>>>>>> testcontainers just for code generation (and integration testing): >>>>>>>>>> >>>>>>>>>> - >>>>>>>>>> https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/ >>>>>>>>>> >>>>>>>>>> I hope this helps, >>>>>>>>>> Lukas >>>>>>>>>> >>>>>>>>>> On Mon, Oct 17, 2022 at 9:07 AM akash verma <[email protected]> >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> Hello lukas, >>>>>>>>>>> >>>>>>>>>>> First of all, thank you for all your replies and for such quick >>>>>>>>>>> support. I am really thankful. >>>>>>>>>>> >>>>>>>>>>> I want to ask as of now JOOQ is generating the POJOS from the >>>>>>>>>>> SQL Script and Databases. >>>>>>>>>>> Did JOOQ have different methods for reading from scripts and >>>>>>>>>>> from databases? >>>>>>>>>>> >>>>>>>>>>> for example:- >>>>>>>>>>> "Assume that as of now, JOOQ is generating Pojos only from the >>>>>>>>>>> Databases and we want to generate it from the Script now, how and >>>>>>>>>>> where we >>>>>>>>>>> should inject our script in JOOQ?" >>>>>>>>>>> >>>>>>>>>>> -- >>>>>>>>>>> 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 [email protected]. >>>>>>>>>>> To view this discussion on the web visit >>>>>>>>>>> https://groups.google.com/d/msgid/jooq-user/cf4068f3-0cf3-4ec3-89f5-3985b5448af1n%40googlegroups.com >>>>>>>>>>> <https://groups.google.com/d/msgid/jooq-user/cf4068f3-0cf3-4ec3-89f5-3985b5448af1n%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 [email protected]. >>>>>>>>> >>>>>>>> To view this discussion on the web visit >>>>>>>>> https://groups.google.com/d/msgid/jooq-user/6d80a3ce-327d-4c76-89c4-5e3918cc251cn%40googlegroups.com >>>>>>>>> <https://groups.google.com/d/msgid/jooq-user/6d80a3ce-327d-4c76-89c4-5e3918cc251cn%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 [email protected]. >>>>>>> To view this discussion on the web visit >>>>>>> https://groups.google.com/d/msgid/jooq-user/c61eea28-2172-4357-9755-93917200e5cbn%40googlegroups.com >>>>>>> <https://groups.google.com/d/msgid/jooq-user/c61eea28-2172-4357-9755-93917200e5cbn%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 [email protected]. >>>>> >>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/jooq-user/ad87caeb-07fe-4bba-b5b6-426d769eb87en%40googlegroups.com >>>>> <https://groups.google.com/d/msgid/jooq-user/ad87caeb-07fe-4bba-b5b6-426d769eb87en%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 [email protected]. >>> >> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/jooq-user/11ffeaf7-79c2-4bcf-bac2-c928af69cd15n%40googlegroups.com >>> <https://groups.google.com/d/msgid/jooq-user/11ffeaf7-79c2-4bcf-bac2-c928af69cd15n%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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/3e2596e1-1a29-47a4-af6a-bf0cc4e97673n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/3e2596e1-1a29-47a4-af6a-bf0cc4e97673n%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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4t8Yr_oG9bH7YNnh4i90OjRg3wx-Yis%3DSWRgB%2BmyFnSQ%40mail.gmail.com.
