Hi Yafl, Thanks for your message.
On Tue, Sep 12, 2023 at 8:24 PM Yafl Wabei <[email protected]> wrote: > Hi! > > I'm looking for ways to fetch all columns of a table without using an > asterisk (see > https://groups.google.com/g/jooq-user/c/4QPSmTf9Wvs/m/GZKt-sMOBAAJ). > While doing this, I got confused by some mapping behaviour when I played a > bit around with queries of the form dslContext.select(BOOK).from(BOOK), > explained in the manual: > > https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/select-clause/select-clause-table/ > > I created an mcve (using Java postgres) with log outputs: > https://github.com/yaflw/jOOQ-mcve > > In this mcve context, the following fetches behave differently with > respect to the result object containing the correct values or not: > > > // 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? > > // 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. // 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 // 4. Fetching into the Table > // WORKS NOT! VALUES ARE NULL > TestRecord fetchOneInto_Table = > ctx.select(TEST).from(TEST).where(TEST.ID.eq(id)).fetchOneInto(TEST); > Same as before. You're nesting a record, but expect it to behave like the flat representation. > // 5. Using get, mapping into the TableRecord > // WORKS! > TestRecord fetchOne_Get0_TableRecord = select.fetchOne().get(0, > TestRecord.class); > TestRecord fetchOne_GetName_TestRecord = > > ctx.select(TEST).from(TEST).where(TEST.ID.eq(id)).fetchOne().get(TEST.getQualifiedName(), > TestRecord.class); > Here, you're unnesting the nested record again explicitly, by accessing the nested value at position 0 > 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? > 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. > > e) If I need to select all columns of a Table while using a join (and > possibly also select specific columns from the joined table), what's the > safest and most readable way to do that? With "select(BOOK, > AUTHOR.ID).from(BOOK).join(AUTHOR)..." > I ran into problems when using it together with .unionAll ("column doesn't > exist" error), but that might be a different problem (maybe I'll produce an > mcve later). > Yeah, please report the UNION ALL issue as a bug here: https://github.com/jOOQ/jOOQ/issues/new/choose 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 -- 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/CAB4ELO64L_LDG0_T%3D19rMkbaEMiwtQaayF%2BvGWW%3DY3FVcOhs4A%40mail.gmail.com.
