The best way to Fetch Sequence Values with jOOQ – Java, SQL and jOOQ.

A variety of RDBMS assist commonplace SQL sequences of some type. The usual SQL syntax to create a sequence is:

The next is how you might fetch a worth from this sequence, utilizing jOOQ, assuming you’re utilizing the code generator:

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

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

The sequence expression interprets to quite a lot of dialects:

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

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

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

It’s also possible to embed the S.nextval() area expression in another location the place a Discipline<?> will be situated, together with fetching the worth from inside a SELECT:

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

Or, if you have to fetch a number of sequence values in a single go, use nextvals():

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

This prints one thing like:

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

And the question being executed makes use of jOOQ’s GENERATE_SERIES emulation to fetch all of the values in a single go, e.g. for H2:

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