spring - Sequence's current value advances to next 100 upon reconnection in derby

Question

I am having an issue with derby sequences with embedded database. When I first connect to the data base it gives me the correct sequence next value with the following statement.

VALUES (NEXT VALUE FOR :seqNm)

But when restart my desktop application, there by reconnecting to the database, the next value gives me the next hundredth value. It seems like derby uses some caching for preallocating the sequence numbers.

e.g. if my sequence starts with 100, First connection to the data base gives me sequences as 100, 101, 102 and so on.

Second connection to the data base gives me sequences as 200, 201, 202, 203 and so on.

Third connection to the data base gives me sequences as 300, 301, 302, 303 and so on.

Is there any workaround for this issue? I am using spring to connect to the database.

Thanks!!

Answer 1

According to https://db.apache.org/derby/docs/10.9/ref/rrefproperpreallocator.html, yes there is a sequence preallocator:)

In configuration just enter:

derby.language.sequence.preallocator=number

where number is a prealocated number of sequences OR

derby.language.sequence.preallocator=className

where className leads to a class implementing SequencePreallocator if you want some more advanced sequence alocation...

Hope it helps!