Guten Tag Lukas Eder, am Donnerstag, 6. Februar 2020 um 14:43 schrieben Sie:
> 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/ If I understood things correctly, that doesn't help me too much: My CTEs reference tables managed by the jOOQ code generator at some point and I want to use those in favour of plain strings. If those tables change, my CTEs instantly break during compile time, which doesn't happen when using plain SQL-strings. That was one of the major drawbacks of what I had implemented before quick&dirty. So in my opinion things break down to if anything from the code generator is used or not. If so, one wouldn't want to deal with plain strings anymore at any place and express everything using names, fields etc. from jOOQ instead I guess. Even without using results from the code generator, using jOOQ instead of plain SQL make things easier readable using methods and Java-indentation than embedding complex strings in Java. Therefore I thought of supporting SQL in pre-formatted files and those might be forwarded to the jOOQ-parser then. But inlining anything else than trivial SQL in Java doesn't feel right anymore. > 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/ Thanks, that's what I was actually looking for, as I wanted to mimic the results of the generator as much as possible. So I ported one of my tables to CustomTable and ran into some questions again: How to handle the necessary CustomRecord? Does it need to implement RecordX-interfaces with concrete types as well? Looks the following currently: > @SuppressWarnings("serial") > static class TimePeriodRec extends CustomRecord<TimePeriodRec> > { > protected TimePeriodRec(Table<TimePeriodRec> table) > { > super(table); > } > } Does it need to be like the following instead? > @SuppressWarnings("serial") > static class TimePeriodRec extends CustomRecord<TimePeriodRec> implements > Record2<OffsetDateTime, OffsetDateTime> That would be what the generator does: > public class CltCmdRecord extends UpdatableRecordImpl<CltCmdRecord> > implements Record8<[...]> { Or is it about simply adding getters like the generator adds? > /** > * Getter for <code>clt_cmd.id</code>. > */ > public Integer getId() { > return (Integer) get(0); > } The second question is, how do I get an actual CTE using CustomTable? I thought that I might be able to use the table instance where I use the CTE, but that doesn't seem to be the case. Instead, I still need to work with CTE-names and fields manually: > return DSL.name(TIME_PERIOD.getName()) > .fields(TIME_PERIOD.START_AT.getName(), > TIME_PERIOD.END_AT .getName()) > .as(DSL.selectFrom(DSL.values(DSL.row(startAt, endAt)))); vs. before: > return TIME_PERIOD.NAME > .fields(TIME_PERIOD.COL_START_AT.getName(), > TIME_PERIOD.COL_END_AT .getName()) > .as(DSL.selectFrom(DSL.values(DSL.row(startAt, endAt)))); I thought I could save especially the ".fields(...)"-thing after reading your following statement: > The main benefit is that your tables will know their fields, and you don't > have to register them again. But didn't get it to work and looking at the docs, CTEs can only be created by names etc. I don't see how to come from a table to a CTE. So, by using CustomTable it seems I only follow the approach of the code generator, but it actually doesn't make creating CTEs themself easier? If so, I might stick with my former approach of names and fields only, because that way it's easier to not misuse as table what acutally is only a container for CTE-names and fields. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: [email protected] AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...........05151- 9468- 55 Fax...............05151- 9468- 88 Mobil..............0178-8 9468- 04 AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow -- 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/273439007.20200207115037%40am-soft.de.
CustomeTable.java
Description: Binary data
