Hey Lukas, Appreciate the answer! By your explanation if I switch to using a pure JDBC driver implementation, will it work with custom data classes or will I have to still use the POJOs generated from JOOQ itself? The main reason why I did not want to use those was, because I couldn't get it to serialize easily to a pure JSON data structure for HTTP output. My case is very simple request-> query -> response, so being able to just get the result and serialize it to JSON easily without much boilerplate is a bit important to me. I can still use the POJOs even without changing the driver if it will fix the issue, not using them was a conscoius decision on my side, not a limitation or anything. These third parties that I am using are more of a bridge for Vert.x reactive SQL client than anything else, so they dont really change anything and still use the JOOQ methods underneath.
This is the line after the query declaration for a bit of context: val cases = jooq.execute(casesQuery, DSLAdapter.fetchMany(casesQuery.asTable(), Case::class.java)).await() And this is the "bridge" that I am using, if it can help shed some light on the problem. https://github.com/zero88/jooqx Again thank you very much for taking the time to look into this! You rock! My bad on the private message, first time using Google Groups. On Monday, January 24, 2022 at 9:36:39 PM UTC+2 [email protected] wrote: > 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/d4ee9052-aa82-4cc0-8e9e-ad11b8f47a94n%40googlegroups.com.
