@SequenceGenerator on a database without sequences
A named @SequenceGenerator previously failed outright on every database whose dictionary defines no NextSequenceQuery - MySQL, MariaDB before 10.3, Apache Derby, Microsoft SQL Server and Sybase as configured by OpenJPA, and any other dictionary that declares none - with Error instantiating named sequence "…": Your database dictionary does
not support native sequences, and there was no property that avoided it.
Such a generator is now backed by a org.apache.openjpa.jdbc.kernel.ValueTableJDBCSeq instead: a counter row in OPENJPA_SEQUENCES_TABLE, whose initialValue and allocationSize are honoured, so the identifiers are unique and allocated in blocks as they would be from a real sequence.
This is a design decision with a compatibility dimension, not something Jakarta Persistence demands; the specification leaves the realisation of a generator to the provider, and the observable contract is the uniqueness of the identifiers.
What this means in practice:
-
The
OPENJPA_SEQUENCES_TABLEhas to exist. The schema tool andopenjpa.jdbc.SynchronizeMappingscreate it, but a hand-maintained schema has to add it, since nothing creates it otherwise. -
The counter row is keyed by the sequence name, so entities sharing one generator share one counter, as they would share a sequence object. The generator’s
schemaandcatalogqualify that key - they do not place the table, which stays in the default schema. The schema is the one a sequence object would have been created in, which is the persistence unit default schema when the generator declares none, so changing that default moves the counter just as it would move the sequence object. -
Each allocation takes a row lock on the shared counter row for the duration of its transaction. Like every
org.apache.openjpa.jdbc.kernel.TableJDBCSeqderived sequence it therefore behaves best with openjpa.ConnectionFactory2 configured, so that the allocation runs on a connection of its own. -
Some of these databases do have sequence objects even though the dictionary declares none - Microsoft SQL Server has had them since 2012. Where a generator is meant to draw from a sequence the database already has, which is also the way to keep sharing it with anything else that draws from it, configure that dictionary with a
NextSequenceQueryin openjpa.jdbc.DBDictionary, for instanceopenjpa.jdbc.DBDictionary=sqlserver(NextSequenceQuery="SELECT NEXT VALUE FOR {0}"); the generator then keeps the native path. -
The substitution is logged once per generator as a warning, and
openjpa.Compatibility=EmulateNativeSequences=falserestores the previous hard failure. -
A dictionary that only learns from the server version whether the database has sequence objects -
MariaDBDictionarydoes - is asked for a connection while the generator is resolved, so that the answer does not depend on whether anything else has touched the database first. If no connection can be obtained, resolving the generator fails with that failure as its cause rather than picking a backing store that cannot be revised later. Dictionaries that declare aNextSequenceQueryin their constructor - Oracle, DB2, PostgreSQL, H2, HSQL, Informix, Firebird, Ingres - are resolved without a connection, as before.