On Fri, Feb 7, 2020 at 11:50 AM Thorsten Schöning <[email protected]>
wrote:
> 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.
>
Sure. But since this is a public list, with a few folks lurking, I always
like to open up discussions to broader topics, in case that particular
approach would have been viable for someone else who did not know about
these features...
> 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.
>
Let's wait until we have text blocks! :)
> 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);
> > }
> > }
>
You'll have to provide a constructor without arguments. Typically, you'll
reference the singleton CustomTable. This should definitely be added to the
documentation and Javadoc:
https://github.com/jOOQ/jOOQ/issues/9811
> Does it need to be like the following instead?
>
> > @SuppressWarnings("serial")
> static class TimePeriodRec extends CustomRecord<TimePeriodRec> implements
> Record2<OffsetDateTime, OffsetDateTime>
>
It doesn't have to. The main benefit is that occasionally, you can use such
a TimePeriodRec when a Record2<OffsetDateTime, OffsetDateTime> would be
required, e.g. in unions, or with row() constructor predicates. Usually,
you don't need that.
> That would be what the generator does:
>
> > public class CltCmdRecord extends UpdatableRecordImpl<CltCmdRecord>
> implements Record8<[...]> {
>
Only if <recordsImplementingRecordN/> is activated (it is by default)
> 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);
> > }
>
You don't need those, either.
> 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))));
>
Note that you can use Field.getUnqualifiedName().
But yes indeed, I haven't finished thinking this through. You will
eventually want to rely on https://github.com/jOOQ/jOOQ/issues/1969 , which
has not yet been implemented.
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?
>
No, but it makes working with them easier, as they now behave like ordinary
tables / views. You could (but perhaps that does not appear to be very
clean) use your CTE definition in the WITH clause, but then reference your
CustomTable in the FROM clause and its columns in all other clauses. In
principle, you could even automate the addition of the CTE definitions as
soon as you reference any of your CustomTable instances in some query.
--
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/CAB4ELO5YRTqX52xN%2BGGsLp1YRMX%2B5S_k9pjETer-rR6DteEcvw%40mail.gmail.com.
