On Thu, Sep 14, 2023 at 5:00 PM Yafl Wabei <yafl.wa...@gmail.com> wrote:
> Hey Lukas,
>
> thanks a lot for taking time to answer! It helps me a lot to develop a
> better understanding for jooq (and, indirectly, for SQL too).
>
>
> // 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.
The key takeaway here being what I said earlier: "This may or may not have
been a good idea." (the Record1<T1> specific ValueTypeMapper)
> 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.
> 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.
>
> 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.).
> To make matters even more interesting, you're using the reflective
> DefaultRecordMapper. But you could also just pass lambdas (or DTO
> constructor references) around. Some cool examples here:
>
> https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/
>
>
> Yeah, it's cool to have this flexibility to decide on using a custom
> mapper or the reflective approach. In general, I really like the very
> extensive (and hence agnostic) APIs in jooq!
>
> And wow, the multiset possibilites in this blog post are just purely
> awesome! A whole new world is opening up for me :)
>
There's no way back now :)
> A) When I want to update a record with a new value, I assume that I need
> to load the POJO or the record from the database, change the affected
> values and then store the POJO or record to the database again. Is that
> correct? Or can I just do the following?
>
> BookRecord bookRecord = create().select(BOOK.ID
> ).from(BOOK).fetchInto(BookRecord.class);
> bookRecord.setTitle("New Title");
> bookRecord.update(); // or .store();
>
> I wonder if this only updates the title or if it would override all the
> other columns (except id) with null values, because they are not present in
> the Record.
>
>
>
> UpdatableRecord operations act only on columns whose changed() flag is
> set. Not only will you not want "accidental" explicit NULL values in your
> INSERT / UPDATE statements, but you also don't want "accidental" explicit
> DEFAULT values in those statements, because trigger and locking behaviour
> might depend on the presence of a column in a statement.
>
>
> That's great, thanks for clarifying!
>
>
> 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
> 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".
>
>
> B) In my program, I have to e.g. query tables and send their data to the
> frontend quite often. Or I query a table and need it for complicated
> business logic. If I write select(C1, C2, C3, ...) queries retrieving only
> the necessary columns, I'm facing the issue of either:
>
> B.1) defining, for each query, a specific Dto (custom type) which
> contains exactly the fields that were queried. That produces a lot of Dto's
> and therefore might make my project structure messy. I can try to use
> encapsulation, but it will still produce a Dto for each (different) query.
> B.2) exposing the fetched record(s) to the caller method, which can then
> retrieve values from it. This doesn't feel great, because I'd rather want
> to have the select and mapping within the same method or class (or at least
> package). Also at the calling point in my code, I don't (immediately) know
> which fields are contained in the record and which not. I'd have to go to
> and take a look at the the jooq-query to find out. This problem does not
> occur with B.1.
> B.3) or mapping into a TableRecord, POJO or similar (to avoid B.1) and
> exposing this. That yields the problem that the caller method cannot know
> which fields in the POJO were actually retrieved from the DB table and
> whether a null value is null, because it wasn't selected or because it's
> actually null in the database. This problem does not occur with B.1 or B.2.
>
> If, on the other hand, I fetch all columns into the POJO or TableRecord,
> these problems all disappear. It does produce the overhead, as you
> mentioned, though, which I don't like either.
>
> To me, all these options have caveats. Actually, after writing it down, I
> now do prefer B.1 or B.2 (or using both depending on the use case) stronger
> than before.
>
> It does depend on the use case of course, but do you (from your
> experience) have an advice how to deal with that in a nice and manageable
> way? What turned out good to be practice for that or could you recommend
> something to me?
>
>
> Well, jOOQ can only offer you the perfect tool for *each* of these
> approaches, and it will never judge you for choosing one of them :) The
> burden of making the choice is yours. Is a JOIN or a correlated subquery
> the better way to express something in SQL, or even a set operation, like
> UNION ALL? Luckily, SQL isn't opinionated and offers you multiple tools, so
> you can freely express yourself. Is a for loop better than a while loop?
> Choose your own! Is it better to write String.indexOf(x) != -1 or
> String.contains(x)?
>
>
> Yeah, I agree. Unfortunately, I don't know more than (very) basic SQL
> (yet), but by using jooq I'm already learning a lot about it. And all the
> explanations and examples in the jooq manual and on the blog are super
> helpful. Thanks for putting so much care into this, it really helps using
> jooq and simplifying my queries and code!
>
>
> I can give you another example from previous work I did. An API was
> completely defined in WSDL (those were the days!). We generated Java
> classes from those WSDL files using Axis (I think). So we did have a set of
> DTOs for each service. The DTOs had shared nested data structures (which
> were also shared nested XSD structures in the WSDL). Everything was crystal
> clear, well documented, and type safe. Now, SQL queries could just populate
> the generated DTOs for each service. Some coworkers would have preferred
> writing Java code first and generating the WSDL. That wasn't *my* style,
> though. In my opinion, the hand-written WSDL formed a better API contract
> than the generated one, and I didn't care about hand-writing Java classes
> all that much. Which approach was better? None. I had to run this project
> so I did it "my way". I don't know what "your way" is, here.
>
>
> Thanks for sharing! I like your agnostic attitude! :) And I can relate,
> because in a different project, I am also actually (still) working in a
> very similar context: An API that we use is defined in XSD, so we (in an
> automatic way) create WSDL files and then Java classes out of that and use
> these to build our code. It works pretty well and I'm happy I don't have to
> write all these classes by hand :D
>
>
> But since you're "sending data to the frontend quite often," why not just
> design a formal API of some sorts? Either with an API language (swagger?
> I'm not up to date) and generate the Java classes, or with Java classes,
> and generate the API spec. Or, you don't do that, and embrace the lack of
> type safety, in case of which you don't need any DTOs or Records. The
> question what you're doing with jOOQ arises automatically from this
> decision of how you want to design your API.
>
>
> Honestly, I'm not experienced enough to know how to do any of that or even
> completely understand what your ideas exactly mean. So that would be a task
> for a more senior colleague ;)
> Thanks, for the ideas, though. I might come back to them when I gathered
> enough knowledge to enter that path.
>
>
> I hope this helps
>
>
> Yes, it does, a lot! Thanks :)
>
> --
> 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/ed05e5f7-03ef-4dca-be01-1500877f1c3dn%40googlegroups.com
> <https://groups.google.com/d/msgid/jooq-user/ed05e5f7-03ef-4dca-be01-1500877f1c3dn%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
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/CAB4ELO66gCTc3ghv2b09GM975y7AQ%3Doj2%2Br2%3DQ4WtFobM-%3D7Cg%40mail.gmail.com.
