@SequenceGenerator with DerbyEmbedded Problem

Question

I m actually trying to learn JPA, and i'm having some trouble with @SequenceGenerator.
I'm using the derby Embedded database with EclipseLink and I'm trying to apply the SequenceGenerator annotation to the id of a Person Entity.
Here's the code i'm using for that:

    @Id
    @SequenceGenerator(name="Person_SEQ", allocationSize=5, initialValue=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="Person_SEQ")
    private int id;

now the problem is that whenever i launch the application "for the first time" (that means that i remove the previously created data-base and retry), i get this Exception:

[EL Warning]: 2011-09-17 22:25:03.649--ServerSession(31484215)--Exception       [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: SEQUENCE 'PERSON_SEQ' does not exist.
Error Code: 30000
Call: VALUES(NEXT VALUE FOR Person_SEQ)
Query: ValueReadQuery(sql="VALUES(NEXT VALUE FOR Person_SEQ)")

Is this behavior normal?
______________________________

I ve checked the generated createDLL.jdbc and here's what it gives concerning the Person Table:

 CREATE TABLE PERSON (ID INTEGER NOT NULL, FIRSTNAME VARCHAR(255), LASTNAME VARCHAR(255), NONSENSEFIELD VARCHAR(255),PRIMARY KEY (ID))
 CREATE SEQUENCE Person_SEQ INCREMENT BY 5 START WITH 5

which makes me wonder why did it start with 5 instead of 1?

____________________________________________

I've figured out also that when restarting the application (this time with the same database) and inserting some new persons the id jumps so far from the allocationSize. To make my self clear here's the scenario i've passed through:

i've inserted 29 persons in the first execution, which created a pool of ids starting from 1 to 29.
in the second execution (after shutting down the application and relaunching it) i also inserted 29 persons, which created a pool of ids starting from 71 (instead of 30 !!) to 99

is this problem due to the derby embedded driver? or am i missing something else?

score 2 · Accepted Answer · answered Sep 19 '11 at 15:33

There error is most likely just a warning, EclipseLink is checking if the sequence exists, and probably creating it when it does not.

The start with of 5 is used because the first time a sequence is alloacted it should return 5, meaning that JPA can use 1-5, because of the pre-allocation size of 5.

The jump to 71 is most likely caused by Derby caching sequence ids on the server. You can probably also set a cache option in the DDL if you wish, but there should not be any issues with holes, normally the bigger the pre-allocation size the better. Just ensure you use a long for your id, not an int.

The application i'm about to develop is not mean't to use the same data base for a long term, instead the data base will be re-generated each new year, and hense the ids won't reach very high values, do you think i really need to use the long type? your answer is what i was expecting ! thanks — George Casttrey, Sep 19 '11 at 16:55

@SequenceGenerator with DerbyEmbedded Problem

1 Answers1