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.
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.
Perhaps there's already a low-hanging feature request that could be
> extracted from the above and moved into core jOOQ.
>
Well, I'll say the easiest part is the code generation, but not sure it's
very useful by itself...
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.
You bet. Check this out:
>
Ok, you won. I suddenly feel so pointless with my tiny helper...
I'm very curious about some examples of what you have done
>
For the purpose of demonstration, here is a simple graph:
------------
| 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.
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; // use one of the
above method to create the tree
// assumes a DSLContext field "dsl" is available
@GET
public Collection<SimuRecord> query() {
return NODE.fetch(dsl);
}
@POST
public SimuRecord merge(SimuRecord parent) {
NODE.merge(dsl, parent);
return NODE.fetchOne(dsl, condition(intoPrimaryKey(parent)));
}
@DELETE
@Path("{id}")
public void delete(@PathParam("id") Integer id) {
NODE.delete(dsl, DSL.row(id));
}
*Conclusion*
First remark: the ReflectNode doesn't have a known type for the parent
primary key (it extends TreeNode<?, Record>). It makes the NODE.delete()
method typeless and I'm not happy with that. This must be improved. But I'm
still not sure about the right way to convey typed keys (posted another
topic about that).
Then if I'm quite happy with the CUD part of the CRUD, the R part may be
too simplistic, I'll probably need filters and joins in my select one day.
So I'd like to find a way to mix this tree approach with a custom select
query. Maybe Record with additional fields (Field<Record> or
Field<List<Record>>) can replace concrete TableRecord with additional
properties. Saw some feature requests about this kind of field I'll need to
check.
But then again, let's try not to write yet another JPA engine.
I've not included the actual fetch/merge/delete code nor the generator
code, because I didn't manage to extract meaningful snippets. And this post
is already long enough anyway, seems I've been carried away by enthusiasm ;)
But I may share once properly packaged in a more standalone lib, if you're
interested.
Cheers,
Thomas
On Monday, February 27, 2017 at 5:57:01 PM UTC+1, Lukas Eder wrote:
>
> Hi Thomas,
>
> Thanks a lot for your comments. I'm very curious about some examples of
> what you have done:
>
> 2017-02-27 13:17 GMT+01:00 Thomas GILLET <[email protected]>:
>
>> What I've done for now is:
>> - extend the JavaGenerator to generate properties (i.e
>> field/getter/setter) in generated records (or POJOs) based on foreign keys
>> - write code to fetch records from a table and its "children" tables, and
>> then associate children records to their parent (that is create a graph)
>> using generated setters
>> - write code to insert/update/merge/delete a graph of record objects,
>> copying foreign key values from parent to children when needed
>>
>
> Perhaps there's already a low-hanging feature request that could be
> extracted from the above and moved into core jOOQ.
>
>
>> For fetching it's a lot less than what a MULTISET can offer I guess
>>
>
> You bet. Check this out:
> https://twitter.com/lukaseder/status/832939068933681152
>
> Getting all actors, and the categories they played in, as a nested
> collection, and the films per category they played in as a doubly nested
> collection. Beautiful! :)
>
>
>> but the idea was to have write capabilities too (so you can insert/update
>> a graph right from deserialized JSON).
>> And for now I only handles one-to-one and one-to-many relationships.
>>
>
> Just to get the complete picture: Materialising a 1:1 entity graph that is
> supposed to be written back to the database later on is exactly the main
> value proposition of JPA. What currently keeps you from using JPA?
>
> Cheers,
> 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 [email protected].
For more options, visit https://groups.google.com/d/optout.
