Record.into : get a null TableRecord instead of dummy null-fields TableRecord instance

[maxime....@gmail.com]

Hi,

I have the following request : 

2 tables, Object and Tag, that are related through Object.tag_id = Tag.id. An Object may have a Tag, or not.

Given the following :

Record record = getDslContext()

.select(OBJECT.OBJECT_ID, OBJECT.ALIAS)

.select(TAG.TAG_ID, TAG.TAG_LABEL)

.from(OBJECT)

.leftJoin(TAG).onKey()

[.......]

.fetchOne();

TagRecord tag = record.into(TagRecord.class);

In the case where the queried Object has no Tag, when I map the result into a TagRecord, I happen to get a TagRecord instance tag with null fields (further, if Tag would contain primitive data types, they would be filled with the default value).

I would expect to get tag==null directly..

Is it possible to have such behaviour?

Thanks in advance,

Max

[Lukas Eder]

Hi Max,

I perfectly understand that you'd expect tag to be null as a result. After all, you're probably thinking in terms of the object model, where an Object type *contains* a nullable Tag type, or even a list of tags. While the SQL standard supports nested collections through MULTISET, few databases actually support this, and jOOQ cannot emulate it (yet). This means that people often resort to using LEFT JOIN instead, but a [ LEFT ] JOIN operation doesn't create a nested collection, it denormalises the schema, flattening two tables into a single table.

Now, when you call "Record.into()" that method has absolutely no knowledge about how the Record came to be. There could be many reasons why all the TAG columns are NULL. One reason could be that you didn't select them (e.g. if TAG has a LAST_UPDATE column), so it would be unreasonable for the into() method to automatically return null in such a case, which it probably couldn't reliably detect.

I'm afraid, you'll need to either explicitly handle this case, or resort to using a more generic record mapper, e.g. modelmapper.org or simpleflatmapper.org, or roll your own:

https://www.jooq.org/doc/latest/manual/sql-execution/fetching/pojos-with-recordmapper-provider

I hope this helps,
Lukas

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Maxime Rocchia]

Hi Lukas,

Thank you for the explaination, I will try to handle that case in the mapper then.

Thanks a lot!

Max

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.