How can this statement be written in JOOQ?

Question

I have a table that has ID varchar(255) as PK. I have managed to create an auto increment temporary column rowNumber to use with a SELECT, as follows:

SELECT
   (@cnt := @cnt + 1) AS rowNumber, ID  
  from table
  CROSS JOIN (SELECT @cnt := 0) AS n 
where 
  (
    some conditions
    )
  )
  and date > {ts '2020-08-06 08:51:23.08'}
  ORDER BY ID
  LIMIT 10
;

How can the above query be expressed in JOOQ? Especially, the (@cnt := @cnt + 1) AS rowNumber part.

Answer 1

SQL variable support in jOOQ

jOOQ currently (version 3.13) doesn't support session variables like the ones you've shown. There's a pending feature request for this: https://github.com/jOOQ/jOOQ/issues/2558

Whenever jOOQ doesn't support a vendor specific feature, you can use it via plain SQL templating, instead. Write:

ctx
  .select(
    field("(@cnt := @cnt + 1)", SQLDataType.BIGINT).as("rowNumber"),
    TABLE.ID)
  .from(TABLE)
  .crossJoin(table(select(field("@cnt := 0", SQLDataType.BIGINT))).as("n"))
  .where(...)
  .orderBy(TABLE.ID)
  .limit(10)
  .fetch();

Assuming, as always, this static import:

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

Starting from MySQL 8

Just a side-note for future visitors who might be using MySQL 8: In those cases, you'd be using the DSL.rowNumber() window function instead of the approach using variables