Re: Simple Relationship Mapping

[Lukas Eder]

Hello,

> What I really want in the class is:
> [...]
> @OneToOne
> private Other other;
>
> @OneToMany(...)
> private List<Child> children;
> [...]

> This is about as complex as it gets for 90% of my crud cases. Having the
> ability to populate the relationships automatically, would completely
> eliminate my need for heavy ORMs such as Hibernate.
>
> Can fetchInto already do this?

No, fetchInto() (or Result.into()) can only "flat-map" records onto
POJOs, right now.

> If not, how much work would it be to get it to do this?

This is on the roadmap as #1530:
https://github.com/jOOQ/jOOQ/issues/1530

I'm currently undecided about how to further evolve these into()
methods. While I would imagine that mapping an annotated (or
non-annotated) @OneToOne or @ManyToOne relationship is rather simple,
I guess it would get more tricky for @OneToMany or @ManyToMany.

Any implementation hints (on this user group, or directly on that
ticket) are very welcome! I think that other users would like to see
this being implemented, as well!

Cheers
Lukas

[Lukas Eder]

> I'm currently undecided about how to further evolve these into()
> methods. While I would imagine that mapping an annotated (or
> non-annotated) @OneToOne or @ManyToOne relationship is rather simple,
> I guess it would get more tricky for @OneToMany or @ManyToMany.

Some considerations about the mapping (Here, jOOQ should behave as a
partial JPA implementation):

1. Adding support for these annotations may indicate to some that lazy
fetching is possible with jOOQ. This will not be true. Fetching is
done in jOOQ, the mapping is done based on what is available in a flat
org.jooq.Result. This feature limitation must not be forgotten when
designing such extensions.
2. The fact that you join TABLE, OTHER, CHILD and the fact that you
have Table with Other and List<Child> attributes is a coincidence. JPA
has a "model-first strategy" and renders the necessary SQL depending
on what outcome you want (Table, Other, List<Child> generate the
required JPQL / SQL). With jOOQ, the inverse would be true. The SQL
generates a flat table and results will somehow have to be mapped
according to what is available through reflection. Having a
List<Child> attribute means that automatically, the resulting table
will be "grouped" by CHILD.TABLE_ID, returning only unique Table
values. This opens a lot of questions for transitivity. E.g. what if
Other has a List<Table> attribute? What if Table has a List<Table>
attribute?
3. @JoinColumn will probably be a necessary annotation as well
4. Is there any potential for a partial solution not involving JPA
annotations? Simple mappings could be handled, in principle. Should
jOOQ go down that path?

Some considerations about the code-generator:

1. Should jooq-codegen be adapted in order to generate @OneToMany and
@ManyToOne annotations automatically for generated pojos?
2. Can @OneToOne and @ManyToMany relations even be discovered automatically?
3. How would code-generation impact generated interfaces and/or
generated records?
4. How would code-generation impact generated DAOs?

Cheers
Lukas

[Lukas Eder]

> 2. I was thinking it should just be explicit. Do not modify the query. I
> will write the query. However, if possible, the ID of the relational object
> should be populated if I selected the ID from either table.

Could you explain this with an example?

> 4. I'm not sure if jOOQ should go down this path without careful thought and
> design. There are many edge cases and issues to deal with.

Yes, I'm guessing that a JPA-annotated solution should be implemented first, and a reduced version thereof might be implemented thereafter. The simple example of a BOOK table with AUTHOR_ID and CO_AUTHOR_ID is already a thing that probably cannot be handled without JPA-annotations

> 2. This would be rather difficult. It'd be hard to infer the user's
> decisions on a per model basis.

You're probably right

> Since I have to get this working in a project very soon. I'm going hack up a
> solution and modify jOOQ. However, I don't think that's the best idea in the
> long term. This seems to need more thought and consideration.

Nice! Be sure to share your experience on this matter. Pull requests are welcome, too!

Cheers

Lukas