15

I need to do the equivalent of this in JOOQ.

SELECT
  name,
  'companyType' AS resultType
FROM company
UNION ALL
SELECT
  name,
  'userType' AS resultType
FROM "user";

I have figured out how to do unionall in JOOQ fine but I cannot figure out how to select a constant value for results in either select of the union.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
benstpierre
  • 32,833
  • 51
  • 177
  • 288

1 Answers1

26

You're looking for DSL.inline(), which is used for precisely this:

inline("companyType").as("resultType")

Or, the full query:

using(configuration)
  .select(COMPANY.NAME, inline("companyType").as("resultType"))
  .from(COMPANY)
  .unionAll(
   select(USER.NAME, inline("userType").as("resultType"))
  .from(USER))
  .fetch();

Both examples assume you have this static import:

import static org.jooq.impl.DSL.*;

More information can be found here: http://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Why not use DSL.val("companyType").as("resultType") and get the benefit of bind variables? – Amit Goldstein May 11 '21 at 14:22
  • 1
    @AmitGoldstein: Well, what exactly *is* the benefit of a bind variable, specifically in this case? 1) The OP didn't seem to want bind values. Look at the title, it says *"constant"*, 2) there are many cases where bind values are actually the wrong choice. This particular example (union discriminators) is one of them. More info: https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/ (look at item 10, predicate pushdown for a related example), or https://blog.jooq.org/2017/05/30/when-to-use-bind-values-and-when-to-use-inline-values-in-sql/ – Lukas Eder May 11 '21 at 16:20
  • 1
    Such a great read, and also the comment section, I learned so much - thank you! – Amit Goldstein May 12 '21 at 04:47