Hi Lukas, Thank you very much for your detailed elaborations, greatly appreciated!
Well, when I finally find someone interested in what I have to say, I don’t let him go that easily ;) My usual advice here is to do what also some of the JPA advocates advise to > do: Mix both worlds. Write the query with jOOQ, but execute and map it with > JPA: > > https://www.jooq.org/doc/latest/manual/sql-execution/alternative-execution-models/using-jooq-with-jpa/using-jooq-with-jpa-entities > I had forgotten this “native query” stuff. I can indeed use JPA with SQL, so with jOOQ! Glad you told me. I was starting to wonder how to use jOOQ to generate JPQL queries... This was published just now: > > https://arnaudroger.github.io/blog/2017/03/02/jooq-one-to-many-without-dto.html > Hey that’s interesting! My fk-based property generation on records would fit nicely with this 1-N mapping feature! In theory, and in my opinion, the perceived impedance mismatch is only a > SQL language implementation detail, or missing feature: > > https://blog.jooq.org/2015/08/26/there-is-no-such-thing-as-object-relational-impedance-mismatch/ > Interesting thoughts… This will make me think for a while… Oh oh... That's a recursive tree structure. […] you could work around > things by tricking and using recursive SQL, and then materialising the tree > only in the client. Errr… what? That’s just a very simple tree… Three tables, two relations (one 1-1 and one 1-N). In what way is it recursive? I also don’t get the recursive SQL… I know how it works for a hierarchy of elements of the same type (i.e inside one table), but with 3 different tables, without any self-reference… Am I missing something? Or maybe you misunderstood my schematic? That would explain your enthusiasm for this ;) because modelling an actual recursive tree, *that* would be more of a challenge… No need for actual foreign or unique keys here so it can be used with any >> schema. > > Be careful. That might kill your performance. These constraints are very > useful when querying, especially when you query the entire tree. Agreed, and I don’t intend to do without keys. I just prefer to start with the simplest and most generic possible implementation, to see what’s what. Also it provides a way out in case constraints are not generated by jooq. For example I’m using, among others, a strange IBM database which seems not to have any concept of keys nor indices. For actual use, it would be the ReflectNode, or maybe some intermediate implementation where you manually give a parent table and a list of FKs. Also, unfortunately only the first implementation can provide type-safety on the keys… You should definitely support materialising your tree from a flattened > result set, which you produce using recursive CTE. Again, don’t see the recursion… But yes, for retrieval there could be different sources, it doesn’t have to be DB -> Tree -> Graph. Could be used with anything. Actually I’m only focusing on the tree: classes to model it, methods to create it, helpers to walk it, objects to hold graph data (TableRecords with extra properties, Records with collection-valued fields, Tuples…). Then there is plenty of ways to use the tree. Writing the queries seems very database- and schema-dependent to me (i.e merge action specific to H2, handling of generated keys not needed if you only use natural PKs, ability to use deferred constraints…). So when (if) I package it in a library, I think SQL generation from a tree will be left to the user. Or at the very least be packaged separately. My thinking is (for insert): > - Insert the entire tree flattened in a bulk INSERT .. RETURNING > - Retreive all the generated IDs and associate them with the individual > tree nodes in the client model > - Update the parent references in a bulk UPDATE or MERGE by joining an > in-memory table containing the relationships to the actual table Left to the user… or left to you! Seems you have lots of very good ideas ;) When you say “bulk”, do you mean batch operations? I was under the impression that batches cannot return results… Also this definitely needs deferred constraints to work, right? That should be rather "easy." Yeah I know, I said that writing fetch/merge/delete “is quite simple”… I think the complete sentence would be “is quite simple when you don’t give a damn about performance” ;) I’m mainly developing prototype applications, so I confess performance is not much of a concern to me… But I may share once properly packaged in a more standalone lib, if you're >> interested. > > Absolutely interested! Glad you are. But maybe I jumped the gun here. Not sure when I’ll find time to do it properly. Not sure how to share it properly either… creating a GitHub repo may be too much… Anyway, thanks again for the useful feedback! Thomas -- 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]. For more options, visit https://groups.google.com/d/optout.
