Hi, thanks for your reply! And btw thanks for creating jooq! I'm new to it and still learning, but I really like it a lot and appreciate how much thought you put into developing it!
// 1. Fetching the plain result // WORKS - VALUES ARE PRESENT Record1<TestRecord> fetchOne = ctx.select(TEST).from(TEST).where(TEST.ID.eq(id)).fetchOne(); This probably needs no explanation? Correct :) // 2. Fetching into a custom type or a POJO // WORKS! Test fetchOneInto_POJO = ctx.select(TEST).from(TEST).where(TEST.ID.eq(id)).fetchOneInto(Test.class); This is due to a feature from the DefaultRecordMapper where single record results can be mapped to classes directly using the DefaultConverterProvider: https://github.com/jOOQ/jOOQ/issues/10071 Note that just because a POJO class is called Test (like the table), or the fact that it's generated from the table doesn't mean there's a link between the two things. Do you mean to say (with the last sentence) that it would also just work with a random custom type (like a custom Dto)? And thanks for the reference! If I see it correctly (see question below, too), then this special feature makes my call equivalent to fetchOne(r -> r.get(0, Test.class)) and that's then similar to case "5." from above. Now that I got that, I still wonder how r.get(0, Test.class) actually converts the record to a Test instance. Is it correct that this is equivalent to r.get(0).into(Test.class) and hence to testRecordInstance.into(Test.class)? And the mapping algorithm for that is then (one of) the basic mapping algorithms of jooq, i.e. the naming convention algorithm mentioned in the DefaultRecordMapper JavaDoc which compares field names, right? I guess I just gathered the understanding while thinking "out loud" here. ;) So these questions are more rhetorical (or just asking for confirmation). If (parts of) my explanation is wrong, please let me know. // 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? 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)? So my questions are: a) What makes 2 work, i.e. which kind of mapping algorithm is applied there? b) Why does that not apply to 3? c) The difference between 2 and 3 seems to make the use of select(Table) error prone. Should I avoid using this? Or should I just always use get(0) (and get(1), get(2) etc. when using more select arguments)? What's the best practice for that? There are no best practices. Map things the way you need them to be mapped. You already said you understood that you're getting a Record1<TestRecord>, but obviously, you never wanted that. And now, you're trying to unnest it again. So, the solution is just to not nest it at all! Nesting is useful when you have more complex queries. d) If select(Table) should be avoided, what's the best (safest and readable) way to get all columns of a Table? (E.g. if I need to fetch the POJO to make persistent changes on it.) Maybe selectFrom() method, that projects the desired TestRecord type. Thanks for the advices! This questions originates from me wanting to avoid asterisks (SELECT *), but we (currently) still have code where we fetch all columns. When that is necessary, I want to do this as safe as possible, so I considered using variants where jooq (or Java) knows about the record type at compile time. This applies to selectFrom(BOOK) and select(BOOK).from(BOOK), but not to select().from(BOOK). When I experimented with the variants, I stumbled upon the confusing behaviour above. But you are right, if I want all columns with jooq knowing the type, I should just go for selectFrom(BOOK) instead of select(BOOK).from(BOOK) if I don't need joins. However, when I need joins, I'd need to fall back to select(BOOK, AUTHOR.NAME).from(BOOK).join(AUTHOR)... Or I just go with select().from(BOOK).join(AUTHOR), but that will fetch to many columns from AUTHOR which I didn't want to fetch. Is select(BOOK, AUTHOR.NAME).from(BOOK).join(AUTHOR)... the best solution to avoid this? Or maybe select(BOOK.fields()).select(AUTHOR.NAME).from(BOOK).join(AUTHOR)...? As I write this, I wonder: Is it in the case of a query without join also "better" (for whatever reason) to use select(BOOK.fields()).from(BOOK) instead of selectFrom(BOOK)? Or is that mere preference? At this point, it's imperative to mention that projecting all columns is usually a mistake: https://www.jooq.org/doc/latest/manual/reference/dont-do-this/dont-do-this-sql-select-all/ Yes, it's convenient, but very often, this leads to avoidable, systematic overhead. But anyway, you can just omit any projection at all, i.e. write select(), and then you get a flat, untyped Record with all columns from the FROM clause in it Thanks for this reminder. I do want to avoid select(), especially in joins, even when I need all columns from table1, but not all from table2. I do also question retrieving all columns in general since you first gave me the hint about it, but I don't understand yet how to achieve the following use cases without it: 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. 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? 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? Thanks a lot! yafl -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/0bc8af17-a00e-4b27-a1c7-b1b1b6c25d55n%40googlegroups.com.
