That sounds very interesting. Indeed, if you remove UD from CRUD, things do get much simpler. Would be very curious to see that in action!
2017-03-02 13:31 GMT+01:00 <[email protected]>: > We've solved writing back entity graphs a bit differently. The application > has a requirement for business process purposes as well as compliance > reasons to maintain a complete history of all changes. We've chosen to > support this using a bi-temporal data model. Almost every table in the > application either has a valid range and transaction time range or is > immutable and belongs to a table that has a valid and transaction time. > Because of this when an entity graph hits a repository, it's trivial to > identify what needs to be done. > > - Insert entities with a current transaction time or which belong to > an entity with a curent transaction time. > - Update the transaction end time (effectively the transaction end > time is the only piece of mutable data) of any entities with transaction > end time equal to the current transaction time. > - Deletes are not allowed. > > We use the Scala collection operations to identify these scenarios and map > them into insert or update statements that are executed in batch. Also, > since the transaction time has additional meaning we have database > constraints to ensure we're doing this correctly. At least for us this has > proven to be a very simple, explicit way to handle entity graph updates. > Very interested in any feedback or similar solutions people have come up > with. > > > On Thursday, March 2, 2017 at 5:58:43 AM UTC-6, Lukas Eder wrote: > >> Hi Thomas, >> >> Thank you very much for your detailed elaborations, greatly appreciated! >> >> 2017-02-28 16:51 GMT+01:00 Thomas GILLET <[email protected]>: >> >>> Hi Lukas, >>> >>> Just to get the complete picture [...] What currently keeps you from >>>> using JPA? >>> >>> >>> A long time ago, I used to use JPA to do very basic CRUD stuffs, and it >>> was working fine. >>> But I began to issue less and less insert/updates, and more and more >>> complex selects, and to be very frustrated with the "code-in-a-string" JPQL >>> approach, the verbosity of CriteriaBuilder, the lack of type-safety, >>> and the heaviness of JPA in general. >>> Then came jOOQ, and it was good, and then came Java 8 streams and >>> collectors, and there was nothing else left to say. >>> >> Until I needed a dumb CRUD resource on objects spanning several tables, >>> and the circle was complete. >>> >> >> Indeed - the current SQL centric approach works best as long as there is >> hardly any writing back of entity graphs to the database. >> >> >>> Hence the idea to be able to occasionally CRUD simple object graphs with >>> jOOQ - the keywords being "occasionally" and "simple". >>> But I must confess I had the nasty feeling to re-create a JPA engine >>> when writing this code, so I may well be wrong in refusing to use JPA again. >>> >> >> That describes it, and it's also the reason why I always tried to refuse >> implementing such features in jOOQ, because users tend to say: "Just >> support to-one relationships". "Just support single nested collections". >> And before we notice it, jOOQ will pass the entire JPA TCK and more :) >> >> 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/alterna >> tive-execution-models/using-jooq-with-jpa/using-jooq-with-jpa-entities >> >> It's not perfect either, sometimes a JPA JOIN FETCH might be a bit more >> optimal (their lame workaround for MULTISET support), but it might also be >> good enough. >> >> Also I'm heavily relying on Key objects, and it's bothering me that they >>> don't have any generic to represent their type. But I posted another topic >>> about that. >>> >> >> Yes, I've seen that. Will respond there. >> >> >>> You bet. Check this out: >>>> >>> >>> Ok, you won. I suddenly feel so pointless with my tiny helper... >>> >> >> Don't feel pointless :) The more people try solving this problem, the >> more probable it is that someone will find the breakthrough eventually. The >> problem is to finally find a solution that breaks the impedance mismatch. >> Because there is none. 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-o >> bject-relational-impedance-mismatch/ >> >> The "only" problem is that few databases really support nested >> collections in a way that: >> >> 1. It is easy to write them (e.g. MULTISET) >> 2. It is easy to serialise them (e.g. XML) >> 3. It is fast (only XML in SQL Server. XML in Oracle or PostgreSQL, and >> MULTISET in Oracle is much slower) >> >> So, as long as the above isn't solved by the databases, we client >> application writers will continue searching for suitable workarounds. >> >> I'm very curious about some examples of what you have done >>>> >>> >>> For the purpose of demonstration, here is a simple graph: >>> >> >> Oh oh... That's a recursive tree structure. In SQL, it could only be >> reasonably materialised with a "higher order MULTISET nested collection" >> (how awesome would that be!), or you could work around things by tricking >> and using recursive SQL, and then materialising the tree only in the client. >> >> I actually don't think that anyone has really found a thorough and >> satisfactory solution to this problem in any ORM, neither SQL based like >> jOOQ, nor entity graph based like JPA. >> >> But your approach does look very interesting! >> >> >>> >>> ------------ >>> | SIMU | >>> |------------| >>> |PK: SIMU_ID | >>> ------------ >>> 1 >>> ___________|___________ >>> / \ >>> / \ >>> 1 * >>> ------------- --------------------------- >>> | SIMU_COST | | SIMU_OPERATION | >>> |-------------| |---------------------------| >>> | PK: SIMU_ID | | PK: SIMU_ID, OPERATION_ID | >>> | FK: SIMU_ID | | FK: SIMU_ID | >>> ------------- --------------------------- >>> >>> >>> Where it is assumed that the SimuRecord class has two additional >>> properties: >>> >>> private SimuCostRecord cost; >>> private List<SimuOperationRecord> operation; >>> >>> // and related accessors (getter/setter) >>> >>> >>> These properties are generated with a special JavaGenerator in this >>> example, but they could as well be written manually by simply extending the >>> SimuRecord >>> class. >>> >>> *Tree creation* >>> >>> The base of my object tree is the TreeNode class: >>> >>> // KR: type of the PK of the parent (or root) table >>> // PR: type of the parent table >>> public abstract class TreeNode<KR extends Record, PR extends TableRecord >>> <PR>> {...} >>> >>> >>> I started with a simple tree builder to construct trees manually at >>> runtime: >>> >>> // SimpleNode assumes a Record1<K> primary key >>> TreeNode<Record1<Integer>, SimuRecord> NODE = SimpleNode >>> // parent table and its primary key >>> .one(SIMU, SIMU.SIMU_ID) >>> // one-to-one child with foreign key and accessors for a >>> SimuCostRecord property >>> .toOne(SIMU_COST, SIMU_COST.SIMU_ID, SimuRecord::getCost, SimuRecord >>> ::setCost) >>> // one-to-many child with foreign key and accessors for a >>> List<SimuOperationRecord> property >>> .toMany(SIMU_OPERATION, SIMU_OPERATION.SIMU_ID, SimuRecord:: >>> getOperation, SimuRecord::setOperation); >>> >>> >>> 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. >> >> Notice that some databases allow you to use DEFERRED mode with foreign >> key constraints, meaning that the constraint is only enforced upon commit, >> not upon INSERT / UPDATE. E.g. PostgreSQL: >> https://www.postgresql.org/docs/current/static/sql-set-constraints.html >> >> >>> Then comes the interesting part, allowing user to write as little code >>> as possible: >>> >>> // Associations are retrieved from foreign and unique keys reported by >>> jOOQ, >>> // and accessors are found by reflection. >>> TreeNode<?, SimuRecord> NODE = new ReflectNode<SimuRecord>(SIMU); >>> >>> >>> ReflectNode is very simple: it deduces relationships from the foreign >>> keys referencing the parent table primary key, and looks for unique >>> constraints to determine their cardinality: >>> >>> // Relationships targeting the Table "parentTable" >>> Collection<ForeignKey<?,?>> keys = parentTable >>> .getPrimaryKey() >>> .getReferences(); >>> >>> // Cardinality of relationship defined by ForeignKey "fk" >>> boolean unique = fk >>> .getTable() >>> .getKeys() >>> .stream() >>> .anyMatch(uk -> fk.getFields().containsAll(uk.getFields())); >>> >>> >>> This code is used in both code generation (to generate properties) and >>> at runtime to create the tree. >>> At runtime, properties are retrieved by reflecting on generated record >>> classes. >>> >>> *Usage* >>> >>> And then, after writing the appropriate code to fetch/merge/delete a >>> tree (which, with all the assumptions made on PKs and FKs, is quite >>> simple), here is how a simple CRUD resource can look like: >>> >>> private final static TreeNode<?, SimuRecord> NODE<span >>> style="color:rgb(102,102,0)" class="gmail-m_8753945 >>> >> -- > 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. > -- 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.
