Confusing mapping behaviour when using Tables as SelectField

184 views
Skip to first unread message

Yafl Wabei

unread,
Sep 12, 2023, 2:24:54 PM9/12/23
to jOOQ User Group
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

Lukas Eder

unread,
Sep 13, 2023, 4:00:18 AM9/13/23
to jooq...@googlegroups.com
Hi Yafl,

Thanks for your message.

On Tue, Sep 12, 2023 at 8:24 PM Yafl Wabei <yafl....@gmail.com> 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:
 
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:

At this point, it's imperative to mention that projecting all columns is usually a mistake:

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

Yafl Wabei

unread,
Sep 13, 2023, 8:50:24 AM9/13/23
to jOOQ User Group
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:
 
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:

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

Lukas Eder

unread,
Sep 13, 2023, 9:34:38 AM9/13/23
to jooq...@googlegroups.com
On Wed, Sep 13, 2023 at 2:50 PM Yafl Wabei <yafl....@gmail.com> wrote:
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:
 
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.

The conversion of values to other values happens via the ConverterProvider SPI. The conversion of records to custom types happens via the RecordMapperProvider SPI. The DefaultRecordMapper has a special case for Record1<T> to Class<E> conversion, which it delegates to the ConverterProvider
 
 
// 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 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.
 

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)...?

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.

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:

None of the approaches is really better. Obviously, though, reflection might be more surprising, occasionally, because not type safe. But if type safety isn't necessary because ultimately, the consumer is a UI written in JavaScript, then you could even produce JSON directly from within the database!

jOOQ won't judge you.
 
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:

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.

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.
 
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".
 
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)?

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.

Personally, I don't understand why B.1) is perceived as a problem by folks. You either want type safety (then B.1 is the way to go), or you don't (then List<Map<String, Object>> is good enough). And then, there are the middle grounds of B.2) and B.3) and other approaches, which can work, too.

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.

I hope this helps
Lukas

Yafl Wabei

unread,
Sep 14, 2023, 11:00:50 AM9/14/23
to jOOQ User Group
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.
 
 
 
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. 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.

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? 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 :)


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:

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 :)

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. :)

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);
 
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 :)

Lukas Eder

unread,
Sep 15, 2023, 5:11:21 AM9/15/23
to jooq...@googlegroups.com
On Thu, Sep 14, 2023 at 5:00 PM Yafl Wabei <yafl....@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:

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:

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:

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:
 
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:

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+...@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.

Yafl Wabei

unread,
Sep 15, 2023, 8:10:38 PM9/15/23
to jOOQ User Group
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. 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:

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:

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:

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:

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. ;)]

Lukas Eder

unread,
Sep 18, 2023, 8:01:18 AM9/18/23
to jooq...@googlegroups.com
On Sat, Sep 16, 2023 at 2:10 AM Yafl Wabei <yafl....@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.
Thanks for the suggestion. I'll think about it

Yafl Wabei

unread,
Sep 28, 2023, 8:14:36 AM9/28/23
to jOOQ User Group
On Wednesday, 13 September 2023 at 10:00:18 UTC+2 Lukas Eder wrote:

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:

I investigated this more and after some analysis I think it's not a jooq bug, but rather I build a non correct SQL statement. To keep things separated, I started a new conversation about it:



Yafl Wabei

unread,
Sep 28, 2023, 8:19:54 AM9/28/23
to jOOQ User Group
After some thought and some more usage, I do agree.
The question of this thread is one good example for that were it silently went wrong even without a runtime error (in the jooq query– the null values in the resulting record produced a runtime exception in my business logic code).

I also see that if I want to add a new column to a query, it can happen that I add it to the query, but forget to add it in the Dto that I map into, or the other way around. Then:
With reflection, this will go wrong silently again.
With a type safe constructor reference mapping, both cases would produce a compile error.
A type safe mapping using a custom mapper method where I call setters on a Dto is somewhere in between. Adding it to the query will produce a compile error on using my mapper method (argument RecordN instead of RecordN+1). I can still fix the mapper signature and might forget to call the setter on the Dto and this won't produce a compile error. The other way around, if I add the column to the Dto and set it in the mapper (but forget to change the mapper argument from RecordN to RecordN+1), using record.get(N, Type.class) or record.get(TABLE.FIELD) will at least produce a runtime error in the mapper.
So it's not as good as the constructor reference, but at least there's less mistakes than with reflection.

I guess there are more examples and cases how reflection can go wrong silently.

Since I realized this, I'm using the reflection mapping less and the type safe ones more and it does feel better to do it this way.


I have no more questions regarding this topic. I gained a lot of understanding from this conversation. Thanks very much for all the advice, Lukas!
 
Reply all
Reply to author
Forward
0 new messages