rss-bridge 2022-03-01T13:08:00+00:00

How to Fetch Sequence Values with jOOQ

A lot of RDBMS support standard SQL sequences of some form. The standard SQL syntax to create a sequence is: The following is how you could fetch a value from this sequence, using jOOQ, assuming you’re using the code generator: The sequence expression translates to a variety of dialects: You can also embed the S.nextval() … Continue reading How to Fetch Sequence Values with jOOQ →

How to Fetch Sequence Values with jOOQ

Posted on March 1, 2022February 22, 2022 by lukaseder

A lot of RDBMS support standard SQL sequences of some form. The standard SQL syntax to create a sequence is:


CREATE SEQUENCE s;

The following is how you could fetch a value from this sequence, using jOOQ, assuming you’re using the code generator:


// import static com.example.generated.Sequences.*;

System.out.println(ctx.fetchValue(S.nextval()));

The sequence expression translates to a variety of dialects:


-- CockroachDB, PostgreSQL, YugabyteDB
nextval('s')

-- Db2, HANA, Informix, Oracle, Snowflake, Sybase SQL Anywhere, Vertica
s.nextval

-- Derby, Firebird, H2, HSQLDB, MariaDB, SQL Server
NEXT VALUE FOR s

You can also embed the S.nextval() field expression in any other location where a Field<?> can be located, including fetching the value from within a SELECT:


ctx.select(S.nextval()).fetch();

Or, if you need to fetch multiple sequence values in one go, use nextvals():


System.out.println(ctx.fetchValues(S.nextvals(10)));

This prints something like:

[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

And the query being executed uses jOOQ’s GENERATE_SERIES emulation to fetch all the values in one go, e.g. for H2:


SELECT NEXT VALUE FOR s
FROM system_range(1, 10)

How to Fetch Sequence Values with jOOQ

How to Fetch Sequence Values with jOOQ

Like this: