On Sat, Sep 16, 2023 at 2:10 AM Yafl Wabei <yafl.wa...@gmail.com> wrote:
> Hi again!
>
> // 3. Fetching into the TableRecord
> // WORKS NOT! VALUES ARE NULL
> TestRecord fetchOneInto_TableRecord =
> ctx.select(TEST).from(TEST).where(TEST.ID.eq(id)).fetchOneInto(TestRecord.class);
>
>
> This is record-to-record mapping, which is governed by field equivalence.
> The nested Record1<TestRecord> record doesn't have any corresponding fields
> with TestRecord
>
>
> I do get that. But if DefaultRecordMapper would treat TestRecord just as a
> custom type and then use the same algorithm as above for the POJO (or any
> other custom type), would it then work? That's just not what happens,
> because the DefaultRecordMapper always field-equivalence for
> Record-to-Record mapping, right?
>
>
> But it doesn't treat TestRecord as a custom type, otherwise, it couldn't
> transfer record state (such as changed flags, the fetched flag, original
> values, etc.).
>
>
> I see, that makes sense! Now I also found the relevant code sections in
> the jooq source code. I still wonder if it would somehow be possible for
> the DefaultRecordMapper make the behaviour similar to the other case. E.g.
> would it be possible to determine that (in the discussed case) the usual
> record mapping doesn't fit for mapping Record1<TestRecord> to
> TestRecord.class and instead try to unnest Record1 and map the result to
> the target? I.e. in simplified pseudo code for the general case:
>
> // "Parameters":
> Record<?> row;
> Class<E> type;
>
> if (AbstractRecord.class.isAssignableFrom(type)) {
> if (row is Record1 && "no sensible mapping from row to E" possible) {
> // <-- Is it possible to determine this?
> Object v = row.get(0);
> // Try to map v to E, by (recursively) using the
> DefaultRecordMapper again
> map(v, type);
> } else {
> // Use default RecordToRecordMapper;
> }
>
> But I guess records don't have enough type information to determine if
> they "match" or not?! Even if, I just made this up and have no clue about
> the consequences for all other cases which are not like in my example.
>
>
> It would be a fun exercise to specify fully and formally what it means for
> there to be "no sensible mapping" (including any potential future "sensible
> mapping" that we may still want to add). Think about things like:
> https://github.com/jOOQ/jOOQ/issues/11148. Though, I'd rather spend my
> time on more pressing features, currently. I believe that since jOOQ 3.15's
> various changes to add more ad-hoc conversion and type safety to mapping
> (including nested collections), the reflective DefaultRecordMapper might
> become less popular.
>
>
> Ah so the order (implemented in the DefaultRecordMapper) of which Mapper
> to choose was, in fact, the other way around before! And that caused
> #11148. Now I see where the order of the conditions in the JavaDoc comes
> from and why it's not up-to-date anymore :)
>
> When I came up with this idea up there, it was more a "would this
> theoretically be possible" question than a desperate wish for an
> implementation. I do agree that there are more important features to spend
> time on.
>
> As to the new type-safe mappings: I'm always happy about more type safety
> and will definitely use them when appropriate. To me the reflective mapping
> has some advantage though, because sometimes the type-safe mapper might be
> too verbose in comparison. Also, when the target object (e.g. a Java 16
> record) has 7 String fields, using a constructor method reference might
> (more easily) lead to interchanged values just by mistakenly confusing the
> order of columns in the query-select.
>
More mistakes can happen with the reflective mapping than the type safe
one. For added type safety, you could use SQL DOMAIN types.
> Mapping values in wrong fields like this cannot happen with the reflective
> mapping (or with explicit custom mapping by using old-school very verbose
> transformation by setters and getters, at least not so easily). Because of
> that and because of its simple syntax, I'll probably continue using the
> reflective mapping in multiple situations even though I'd prefer a
> type-safe, non-reflective way.
>
> I am especially confused by the difference between 2 and 3. In 2 the
> mapping works and the POJO will contain the correct values. In 3 however,
> the TableRecord's values will be null.
>
>
> So, 2 really just is a special case. It was designed for queries like:
>
> select(SOME_STRING_COLUMN).from(T).fetchInto(SomeStringWrapper.class);
>
> This may or may not have been a good idea.
>
>
> I do understand that this kind of select query produces a
> Record1<TestRecord> and not a plain TestRecord itself. According to the
> JavaDoc of into(Table<R>), the mapping algorithm compares the Table<R>
> field names with the field names of Record1<TestRecord>, so I guess that's
> the reason why 4 doesn't work (because Record1 doesn't have the same fields
> as the table TEST). But 2 and 3 both use into(Class<E>), and although the
> JavaDoc states that different mapping algorithms are used for different
> constellations, I didn't see which one of these constellations would match
> 2 and make it work and don't make 3 work.
>
>
> I don't think any explanation is missing in the Javadoc?
> https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/impl/DefaultRecordMapper.html
> What's missing, in your opinion?
>
>
> I reckon that in "2." this constellation applies:
>
>
> "If <E> is a field "value type" and <R extends Record1<?>>, i.e. it has
> exactly one column:
>
> Any Java type available from SQLDataType qualifies as a well-known "value
> type" that can be converted from a single-field Record1. [...]"
>
>
> But I'm not sure why it applies, because there is this SQLDataType
> restriction. So my questions is:
>
> <E> is my POJO (or custom data type), right? Which SQLDataType does it
> match then? Or is the record nested in Record1 the type that needs to be an
> SQLDataType? I.e. which type needs to match an SQLDataType and why does it
> match in my example case (and which SQLDataType does it match)?
>
>
> There's SQLDataType.RECORD for your case.
>
>
> I'm confused, because E (my POJO or custom type) is not a Record in this
> case.
>
>
> You're right, that doesn't explain it here. The actual explanation is the
> one I provided earlier. If the ConverterProvider provides a Converter
> between <T1> (from Record1<T1>) and <E>, then that is used. The
> DefaultConverterProvider can convert from any Record to any POJO simply by
> using the Record's attached RecordMapperProvider again. So, we recurse.
>
>
> Great, thanks for clarifying :)
>
>
>
>
> I think I'm misunderstanding the JavaDoc. I think I should rephrase my
> question more explicitly:
>
> a) What does the term «field "value type"» mean that's referring to <E>
> at the beginning? Which properties must <E> have to make this condition
> true?
> b) The second sentence about the SQLDataType qualifying as a "value
> type" then refers to the type that is nested in Record1<?>, and not to <E>,
> correct?
>
>
> I do have a suggestion for improvement of the JavaDoc of the
> DefaultRecordMapper. Right now it reads:
>
> The mapping algorithm is this:
> If <E> is an array type:
> [...]
> If <E> is a field "value type" and <R extends Record1<?>>, i.e. it has
> exactly one column:
> [...]
> If <E> is a TableRecord type (e.g. from a generated record), then its
> meta data are used:
> [...]
>
> In the confusing example case 3. above, the second condition is fulfilled
> (I think), but the third one too. And the third condition is actually
> checked before the second one source code, so the RecordToRecordMapper is
> applied and not the mapping algorithm of the second block. So I'd suggest
> to switch the order of the 2nd and 3rd condition in the JavaDoc to make
> this clearer.
>
>
> Yes, that order should be reflected correctly, as well as what I said
> earlier about ConverterProvider being the deciding factor, not whether the
> <E> type is a "built-in" type (all built-in types are recognised by the
> DefaultConverterProvider, but it does more than that). I've created:
> https://github.com/jOOQ/jOOQ/issues/15598
>
> Thanks for being thorough here. Fresh eyes often help see flaws in ancient
> documentation that is outdated only in subtle ways. This Javadoc used to be
> correct, but then changes happened.
>
>
> Thanks for being appreciative about it! I wasn't sure if I just didn't
> understand the JavaDoc correctly. It's good to receive confirmation that
> daring to ask and expressing my confusion (instead of letting it go)
> actually helped. :)
>
>
> I don't really like to engage in a discussion about what is the "best"
> way. jOOQ is made of tons of operators. They all work in well defined ways.
> Which ones you find "best" is very subjective.
>
> Any time you nest something, you will have the effects of nesting (pros
> and cons). It's a tradeoff.
>
> Where would such a discussion even stop? Some folks prefer creating 1 view
> or (table valued function) per query. When you do that, every query will
> have its own MY_VIEW class and MyViewRecord type, and you will be able (in
> a type safe way) to project the entire record, without projecting too many
> columns. If you're careful to craft updatable views (and tell jOOQ about
> underlying primary keys using synthetic keys in the code gen), then you can
> even store data back to the underlying table via your updatable view!
> Different style, same result. Not really "better" or "worse". I've done
> this in the past, and it was wonderful for that system. A library of highly
> reusable views that build upon views, and automatic DTO generation for each
> one of them.
>
>
> Thanks for clarifying! I'm very amazed by the flexibility jooq offers.
> There is a lot to discover for me that simplifies previously tedious work,
> that's super great!
>
> I've never (explicitly) used custom views in SQL. How do I create one to
> use it with jooq?
>
>
> CREATE VIEW v AS SELECT ... in every SQL dialect
>
>
> And do I understand correctly that it's possible to let jooq automatically
> generate a MY_VIEW class and MyViewRecord for every such view? If you have
> a link to documentation about that in the manual or on your blog, I'd be
> very happy to check it out there :)
>
>
> Why would this need additional documentation, in your opinion? Views have
> been a core concept of the relational model since Codd's 12 rules:
> https://en.wikipedia.org/wiki/Codd%27s_12_rules
>
> For ages, jOOQ didn't even know something was a view or a table (or a
> materialized view) if the RDBMS listed them at the same place in the
> dictionary views (INFORMATION_SCHEMA, etc.).
>
>
> Thanks for clarifying – I think now I understand it. Views are new to me
> and I didn't know how to work with that while using SQL with jOOQ.
> Now that I know what you mean, I can see that the option of creating 1
> view for per query and generating the corresponding MY_VIEW and
> MyViewRecord classes for each of them does definitely not fit my use case ^^
>
> If that doesn't work, I could of course write an ad-hoc
> create().update(...) statement, but I would rather use the easily
> understandable POJOs or TableRecords API to store and update records.
>
> So what is "the recommended way" to do this without selecting all columns?
>
>
> I would probably run an explicit update. From your example, it isn't clear
> why you're running 2 round trips in the first place (SELECT, then UPDATE).
> But other than that, there's no "recommended way".
>
>
> Yeah, that's true. I do have some use cases where I need to fetch data, do
> some logical checks in Java, and then update, but in most cases an update
> would be sufficient.
>
> I do now see the pros of a plain update. It didn't feel natural to me,
> because I'm just so used to having an ORM and working directly on the BOs
> instead of using SQL. But I do like the direct approach via jooq and SQL
> and will use that more often now. :)
>
> Both have their place. SQL fails at making repetitive CRUD simple (it gets
> too verbose). But a lot of CRUD isn't "just" CRUD. DML statements can be
> very complex. A lot of times, moving more logic into a single SQL statement
> will drastically accelerate *and* simplify your system.
>
> I guess this talk I made some years ago will give you a bit more insight
> into the wonderful world of doing more with just SQL:
> https://www.youtube.com/watch?v=wTPGW1PNy_Y
>
>
> Yeah, I saw it two weeks ago and it was pretty mind-blowing for me too.
> The domain that I'm working in doesn't have a lot of use cases for more
> complex SQL queries, but I guess that even in our code I will stumble upon
> more possible applications for this kind of optimisation than I can think
> of right now. And for the cases that do come to my mind, I really wanna go
> there and change them right now. :D Unfortunately, new features have higher
> priority at the moment ...
>
>
> Btw I made a mistake when I mentioned "POJOs or TableRecords API". I meant
> "DAO or UpdateableRecord API". While the UpdateableRecord only updates
> changed values (which is very cool), that doesn't apply to DAOs and POJOs,
> right? So the following
>
> Book book =
> create().select(BOOK.ID).from(BOOK).where(BOOK.ID.eq(5)).fetchInto(Book.class);
> // Book.class = POJO generated by jooq
> book.setTitle("New Title");
> bookDao.update(book);
>
> would update all columns, because the POJO cannot track which fields were
> changed (and neithe can the DAO, I guess)?! ..... Ok I just saw the
> documentation
> https://www.jooq.org/doc/3.18/manual/sql-execution/fetching/pojos/#interaction-with-daos.
> I guess, the above is just not the way to work with the POJOs. Looking at
> the example in the manual, it looks like I should rather use the DAO for
> querying too, if I really want to update the table by using the whole POJO:
>
> Book book = bookDao.findById(5; // Book.class = POJO generated by jooq
> book.setTitle("New Title");
> bookDao.update(book);
>
>
> Yeah, sigh.
>
> The DAO is my biggest regret. It was so super easy to implement, so I just
> went ahead and did it, hoping folks will find it useful. And they did. And
> requested 500 feature requests from the DAO, which they could have simply
> implemented with SQL, but the "Spring Repository mindset" made them prefer
> the DAO as the single source of database interaction. But of course, the
> DAO (and POJOs) can't easily model dirty flags, as little as they can model
> the simplest of joins or nested collections, or whatever.
>
> I said there's no "right way" of doing things, and jOOQ usually isn't very
> opinionated. But the DAO is opinionated in a narrow way, and opinions only
> lead to regrets. I completely underestimated the user's desire for The One
> True Way™ to do things, and users who discover the DAO (and who don't
> reject it) will now want to do *everything* with the DAO. I was going to
> deprecate the DAO but was overruled. Look at its history here, if you have
> time:
> https://github.com/jOOQ/jOOQ/issues/5984
>
> I personally recommend not using it. Look at the jOOQ manual. 99.9% of all
> pages are *not* about the DAO. The DAO is just a simple utility for the
> most boring database interactions. It's not even really good at it. It only
> covers the most trivial queries, e.g. the ones you usually see in some
> content marketer authored "beginner's guide tutorial." or Spring Pet Clinic
> style example projects.
>
> SQL is vast and powerful and really good at what it does. DAOs (or ORMs in
> general) try to hide this for no really good reason other than "someone
> else also has this, so we must, too".
>
>
> Thanks for the advice and the background! I did read through the linked
> issue and it helped me getting your point about DAOs and grasp a better
> understanding of jooq's rationale towards this aspect.
>
> I do agree (with other people in the thread) that some 3ish lines of
> explicit comment in the manual (on the DAO and POJO pages), explaining that
> DAOs are not the recommended way to use jooq, could really prevent people
> from using them or make them aware of the drawbacks, at least. It would be
> good to also give a reason so that people understand why they shouldn't use
> jooq's DAOs – you could e.g. put a link (to one of the comprehensive
> explanatory comments in the issue above) in this 3 line paragraph, if the
> reasoning would be too long for the manual page.
>
> For me this definitely would have been helpful. I took over a project from
> a colleague who started building it and used jooq, so I was thrown into
> jooq without knowing it before. He decided to set up jooq to generate POJOs
> and DAOs and he also used them in some cases (I haven't seen all the code
> yet, so I don't know how much they are being used). Hence, for me this
> looked like an appropriate (or even desired) way to store (insert, update)
> database entries.
> I even looked up the sections about POJOs and DAOs in the manual to see
> how to use them in the right way. I agree that they have very little space
> in the manual, but I wasn't consciously aware of that – I just didn't
> notice, because I (as a noobie) was not analyzing the manual (or its table
> of contents) as a whole when searching for explanations in there. I just
> wanted to know how to use DAOs and POJOs. Maybe later on I would have
> noticed, but now I didn't. An explicit remark within the sections about
> POJOs and DAOs would have helped me to realize this earlier.
> [I'm sharing this point of view here because I could imagine that it might
> help you to gain some insight from this "fresh eyes" jooq-noobie
> perspective of mine. ;)]
>
Thanks for the suggestion. I'll think about it
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAB4ELO6K%3D2MZBntRxhvmSf6pCnQPU1PmsZ72e3KO6n_E3dsCFw%40mail.gmail.com.
