Regarding generating the pojos from script and from database.

[akash verma]

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?"

[Lukas Eder]

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

--
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/cf4068f3-0cf3-4ec3-89f5-3985b5448af1n%40googlegroups.com.

[akash verma]

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?

[Lukas Eder]

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...

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/6d80a3ce-327d-4c76-89c4-5e3918cc251cn%40googlegroups.com.

[akash verma]

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).

[Lukas Eder]

And why would jOOQ do that?

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/c61eea28-2172-4357-9755-93917200e5cbn%40googlegroups.com.

[Rob Sargent]

On 10/17/22 12:38, Lukas Eder wrote:

And why would jOOQ do that?

 As I read the "eagerly awaiting" I was tempted to say "Don't hold your breath".  Perhaps JPA-confused?

[akash verma]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/c61eea28-2172-4357-9755-93917200e5cbn%40googlegroups.com.

[akash verma]

Thanks for your concern.

[Lukas Eder]

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?

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/ad87caeb-07fe-4bba-b5b6-426d769eb87en%40googlegroups.com.

[akash verma]

Yes, you are right I want some sort of mapping  and  JOOQ is not able to do so as of now.

Thank you.

[Lukas Eder]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/11ffeaf7-79c2-4bcf-bac2-c928af69cd15n%40googlegroups.com.

[akash verma]

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.

[Lukas Eder]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/3e2596e1-1a29-47a4-af6a-bf0cc4e97673n%40googlegroups.com.

[Alf Lervåg]

Thanks for this writeup Lukas, good to have these texts to refer to. Hopefully this will be a blog post as well?

I just want to say that we’re having great success in autogenerating API-implementations based on API specifications combined with some annotations that tie the APIs to our autogenerated jOOQ-library. 

While we could have done this without jOOQ, we find that it is better for us to stand on the shoulder of giants here.

Alf Lervåg

19. okt. 2022 kl. 11:38 skrev Lukas Eder <lukas...@gmail.com>:



To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4t8Yr_oG9bH7YNnh4i90OjRg3wx-Yis%3DSWRgB%2BmyFnSQ%40mail.gmail.com.

[Lukas Eder]

Alf,

On Wed, Oct 19, 2022 at 11:59 AM Alf Lervåg <a...@lervag.net> wrote:

Thanks for this writeup Lukas, good to have these texts to refer to. Hopefully this will be a blog post as well?

Could be an interesting idea, thanks. The post would focus on the graph (identity) vs. tree (value) discussion.

 

I just want to say that we’re having great success in autogenerating API-implementations based on API specifications combined with some annotations that tie the APIs to our autogenerated jOOQ-library. 

That also sounds like a blog post! Or even an open source library?

I kinda sense that you did things like what has been discussed here:

https://github.com/jOOQ/jOOQ/issues/13069

I'm still very keen on facilitating the most common use-cases, without getting lured into any too opinionated framework. I.e. most tree structures are probably quite similar, and hand-rolling the exact query to materialise a specific tree structure is too boring, so it's quite possible that generating the query from the tree structure is an interesting approach.

Maybe, you re-invented GraphQL to some extent? :)

[akash verma]

Thank you for this detail explanation, clearing the things and I think according to my requirements, I have to use any other tools. Again, thankyou for your guidance.  

[Alf Lervåg]

On Wed, Oct 19, 2022 at 12:53 PM Lukas Eder <lukas...@gmail.com> wrote:

Alf,

On Wed, Oct 19, 2022 at 11:59 AM Alf Lervåg <a...@lervag.net> wrote:

Thanks for this writeup Lukas, good to have these texts to refer to. Hopefully this will be a blog post as well?

Could be an interesting idea, thanks. The post would focus on the graph (identity) vs. tree (value) discussion.

Yes, that's the part I found enlightening. I haven't really thought about things in this way before, however it's obvious that you've done so and that it gives a nice framework for understanding the benefits and downsides of the direction we've chosen. That is to accept and lean into SQL.

 

I just want to say that we’re having great success in autogenerating API-implementations based on API specifications combined with some annotations that tie the APIs to our autogenerated jOOQ-library. 

That also sounds like a blog post! Or even an open source library?

We're still early here but the results are promising. A blog post sounds like a good idea. Making this into an open source library is in line with our plans, however we'll need a few more iterations internally first.

 

I'm still very keen on facilitating the most common use-cases, without getting lured into any too opinionated framework. I.e. most tree structures are probably quite similar, and hand-rolling the exact query to materialise a specific tree structure is too boring, so it's quite possible that generating the query from the tree structure is an interesting approach.

Maybe, you re-invented GraphQL to some extent? :)

We're betting big on GraphQL actually. Our approach is to design the GraphQL schema first and then add custom directives that maps our GraphQL objects and fields to tables, fields and methods in our jOOQ layer. Based on this we're able to generate GraphQL Resolvers, including all the queries against our database.

Here we lean on jOOQ as much as possible. Implicit joins are great, `to-many`-path expressions [#13639] will be amazing. ForcedTypes and virtual calculated columns provide us with great places to do more complex transformations. We're still experimenting and learning how to make best use of the functionality available to us. :)

I'm happy to go into more detail later, maybe in the form of a blog post as you suggest.

 

--
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/CAB4ELO6_g-AqrsTAW1ZmEzP3wATX_f_1kYtFZxEhAWiO8podoQ%40mail.gmail.com.

[Lukas Eder]

On Wed, Oct 19, 2022 at 9:14 PM Alf Lervåg <a...@lervag.net> wrote:

On Wed, Oct 19, 2022 at 12:53 PM Lukas Eder <lukas...@gmail.com> wrote:

Could be an interesting idea, thanks. The post would focus on the graph (identity) vs. tree (value) discussion.

Yes, that's the part I found enlightening. I haven't really thought about things in this way before, however it's obvious that you've done so and that it gives a nice framework for understanding the benefits and downsides of the direction we've chosen. That is to accept and lean into SQL.

Well, here we go, time to upgrade to Oracle 23c to get even more out of this vision! (it was announced this week in Las Vegas Oracle Cloud World)

https://blogs.oracle.com/post/json-relational-duality-app-dev

Seems like a truly fantastic approach to map between trees and relations. Will play with their 23c beta in the near future. A lot of their ideas can probably be mapped to jOOQ, and of course, jOOQ will support this particular Oracle feature as well as possible via: https://github.com/jOOQ/jOOQ/issues/14011