How to Fetch Sequence Values with jOOQ – Java, SQL and 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:

// 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)