7

In order to avoid re-creating the same part of a dynamic query over and over again, I was going to build the main part once and then reuse this part in different parts of the application. Since building the query is somewhat involved (see question jOOQ: best way to get aliased fields (from #as(alias, aliasFunction)) for one aspect of why this is the case), this should benefit performance...

Unfortunately, I've had to realize that the different "steps" in the builder pattern don't return amended copies, but modify underlying state. Thus, I've looked for a way to create an immutable copy of a specific "step" (e.g. SelectWhereStep) from which to initialize and subsequently amend a (Select-)query each time I need it. Unfortunately, I haven't been able to identify any ("legal") way of achieving this.

Can it be done? If not, what's the best alternative?

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Chris
  • 312
  • 1
  • 11
  • *"this should benefit performance..."* what performance characteristics are you worried about in particular? Compared to the SQL string generation, building the expression tree is completely negligible in all benchmarks. Likewise, compared to running the SQL statement, all jOOQ operations are quite negligible in most cases. – Lukas Eder Mar 17 '19 at 13:32
  • @LukasEder I appreciate that this will likely have a negligible effect on performance, running the same code multiple times (knowing it will yield the same result) it just feels wrong. I'm already doing as you suggest, extracting the common part of the query construction to a separate function. For me, it would "feel" better if there was a way to clone an existing query. Do you think you might add something like that (or accept a PR that does)? – Chris Mar 17 '19 at 19:27
  • 1
    *"it just feels wrong"* - Yes, we're well aware of that. *"Do you think you might add something like that"* - Yes, jOOQ 4.0 will address this. *"or accept a PR that does"* - No, this is absolutely not a simple change that we'd like to get as a PR :) – Lukas Eder Mar 18 '19 at 10:02

1 Answers1

8

Unfortunately, I've had to realize that the different "steps" in the builder pattern don't return amended copies, but modify underlying state

This is indeed a very unfortunate limitation in the current design of the jOOQ DSL / model APIs. The DSL API should be immutable whereas the model API is the mutable one. But this isn't always the case as you've noticed.

There is currently no way to clone a jOOQ query as mostly, this is not really necessary. Regardless if you're operating with a mutable or immutable API, the cleanest way to achieve what you want to do is to compose jOOQ queries in a functional way. I.e. instead of

I was going to build the main part once and then reuse this part in different parts of the application

You could just do the same thing in a functional way rather than an imperative way. Rather than assigning "the main part" to some local or global variable, you could make a theMainPart() function that returns that part on the fly. More about this in this blog post.

As a side-note, when writing dynamic SQL, there are usually better ways than referencing the XYZStep types directly.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Is the following example safe to use regarding that the result of `DSL.trunc` is used twice? `Field<...> nowAsMonth = DSL.trunc(DSL.now(), DatePart.MONTH);` `Field<...> startAt = nowAsMonth.sub(...);` `Field<...> endAt = nowAsMonth.sub(...);` Looking at code, things seem to be safe, because a new `Expression` is created always. But how do I know without looking at code? Because `DSL.trunc` returns what you call a column expression/function in your linked docs? – Thorsten Schöning Jan 30 '20 at 16:45
  • 1
    @ThorstenSchöning: As of jOOQ 3.x, if you want to be 100% sure, you can never reuse any expression. In the worst case, you have a `Param` instance in your expression tree, and someone calls the deprecated `Param::setValue`. If you're pragmatic, then you can assume that `Field` expression are reasonably immutable. – Lukas Eder Feb 03 '20 at 12:09