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(); // 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); // 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); // 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); // 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); 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. 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. 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? 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.) 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). All help is very appreciated! Thanks! 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 jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/e326ff81-7524-408f-90c2-1e6624be7fefn%40googlegroups.com.
