Hi Yanko, On Mon, Jan 24, 2022 at 6:29 PM Yanko Bahchevanov < [email protected]> wrote:
> Im using custom data classes with JPA Column annotations and not using > DAOs or POJOs generated from JOOQ. Its worth saying that underneath the > wrappers plain JOOQ is used. > I think that's where the problem is. If you're using a third party library to execute jOOQ queries, then you can't profit from those advanced mapping features, which are implemented in jOOQ itself, not in any third parties. If you use jOOQ to execute SQL directly via JDBC or R2DBC, you can use the MULTISET, ROW operators out of the box. If you want to extract the SQL string and execute it elsewhere (yourself or via a third party), then you'll have to wire the generated JSON / XML back to jOOQ data structures yourself. DSLContext::fetchFromJSON could be of use, but it won't be able to access the projected type information as easily, and you don't get any type safety. There might be public API in a future jOOQ release to help construct the jOOQ Result/Record[N] type hierarchy from nested JSON / XML data structures (see https://github.com/jOOQ/jOOQ/issues/12012), but irrespective of such low level improvements, it will always be much more interesting to let jOOQ execute your query for you, rather than any third party. > All the documentation and posts I saw point to a generated SQL with > jsonb_build_object, but this is not true in my case. Is it some sort of > configuration or inferrence from my setup or something? > Can good point, that implementation has changed as a part of a series of bug fixes. It turned out that, since we can't use the JSON object key names anyway for this serialisation (ambiguous key names, lack of ordering), we might as well just use JSON arrays instead, which at least guarantee ordering, and use less space. Having said so, you shouldn't see the examples in the manual as formal specification of what jOOQ does to get MULTISET to work. This might change again in the future, as we run into some caveats, and there are numerous ones. Here's some insight: https://blog.jooq.org/standard-sql-json-the-sobering-parts/ > This is probably the only problem I need to solve, otherwise JOOQ looks > absolutely awesome, so good job to the team that is developing it! > You won't look back, especially not once you get MULTISET to work! -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5tXihbH_PX%3DFHkueq5NRFTOofzDo1gq2zK202%2B3aVLzQ%40mail.gmail.com.
