Hi Thorsten, Thanks for describing your very interesting use case.
In theory the CTE itself is the same like a table jOOQ would generate > columns for, so I as well really created one field per column per CTE. Absolutely! Eventually, we're hoping to be able to offer more powerful APIs to construct such "tables", which are really views: https://github.com/jOOQ/jOOQ/issues/1969 It doesn't matter if the views are used as derived tables, CTE, correlated subqueries, or actual views. The programming model is very promising, but it isn't easy to extract a simple enough API from it. The only alternative coming into my mind is outsourcing the individual > SELECT-statements into a view, for which jOOQ would create classes etc. > automatically again. But doing this for each and every CTE for each and > every report in question doesn't feel that right as well. Every change in > any CTE would involve a change in the database schema, where it seems far > easier to change Java-code instead. > Views are definitely a very good alternative to using jOOQ in some cases, and combine well with jOOQ for partially dynamic SQL. jOOQ's biggest value proposition is with dynamic SQL. Once SQL is static, native SQL can sometimes be easier to work with. Do note that you can also work with string-based SQL and parse that using jOOQ's DSLContext::parser API: https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/ We're going to invest a lot more into this parser API in the future, as we see it to be a very promising product on its own. See also these two useful applications here, which rely on parsing: - https://www.jooq.org/translate/ - https://www.jooq.org/diff/ > The good thing on creating fields and stuff manually for all the CTEs > is that types are available everywhere needed and one doesn't need to > handle that within the query itself. Like in "cte.field(...)" one can > simply reference an already available field definition and jOOQ takes > that name, type etc. At other places, only the name of that field > could be used as needed. > Your CteDefs class is a good start, but I would recommend you use CustomTable subtypes instead, more similar to generated code: https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/ The main benefit is that your tables will know their fields, and you don't have to register them again. Clearly, there isn't a silver bullet here. Native SQL is a bit easier to edit than jOOQ code, but in pure string form, it is not type safe. In stored view form, it is type safe and available to the code generator, but less dynamic. Usage of such non-dynamic views heavily depends on join elimination and other non-cost model SQL transformations, and you may not have that, depending on the RDBMS you're using: https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/. So, using jOOQ can lead to better performance than using native SQL, because it is much easier to make SQL logic reusable through dynamicity. - 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5OUJKyuL8Bm6e-0v7Hgh2biCJnYJTY2WsuNWJ4sMhaBA%40mail.gmail.com.
