EclipseLink and Sequence Generator Preallocation

I have an issue I can't get my head around. In hibernate I have no problem with the following:

@GeneratedValue( strategy = GenerationType.AUTO, generator = "email-seq-gen" )
@SequenceGenerator( name="email-seq-gen", sequenceName="EMAIL_SEQ_GEN", allocationSize=500 )

Then in my schema.ddl I have this:

CREATE SEQUENCE EMAIL_SEQ_GEN START 1 INCREMENT 500;

Not much to see here. Everything works as expected. However, if I switch my provider to EclipseLink I get this error:

The sequence named [EMAIL_SEQ_GEN] is setup incorrectly.  Its increment does not match its pre-allocation size.

So of course I google around and see something about EclipseLink creating a negative number if the initial value is 1 and that it should equal the allocationSize.

So, okay, so adding "initialValue=500" and updating my DDL scripts to "START 500" fixes this but now my numbering starts at 500 instead of 1. What gives? Is this an EclipseLink bug or is there something I am not understanding. I would like to generate sequences that start at 1 and have allocation sizes that are tuned to the entity (in this case 500). How would I do that with EclipseLink?

Thanks!

Another way to ask this is....given this DDL:

CREATE SEQUENCE EMAIL_SEQ_GEN START 1 INCREMENT 500;

What is the correct way to annotate my entity to use it with EclipseLink?

If I let EclipseLink generate my DDL then this:

@GeneratedValue( strategy = GenerationType.AUTO, generator = "email-seq-gen" )
@SequenceGenerator( name="email-seq-gen", sequenceName="EMAIL_SEQ_GEN", initialValue=1, allocationSize=500 )

Will generate this:

CREATE SEQUENCE EMAIL_SEQ_GEN INCREMENT BY 500 START WITH 500;

Which kind of implies that it is IMPOSSIBLE to create a DDL with a "START WITH 1" using EclipseLink.

Solution

By default entities annotated with @SequenceGenerator use initialValue=1 and alocationSize=50.

public @interface SequenceGenerator {
    /** 
     * (Optional) The value from which the sequence object 
     * is to start generating.
     */
    int initialValue() default 1;

    /**
     * (Optional) The amount to increment by when allocating 
     * sequence numbers from the sequence.
     */
    int allocationSize() default 50;
}

A "sequential" entity id seems to be calculated by EclipseLink with the following formula:

entityId = initialValue - allocationSize + INCREMENT_BY

or in case of using DDL:

entityId = START_WITH - allocationSize + INCREMENT_BY

Going back to your particular cases:

@SequenceGenerator( 
    name="email-seq-gen", 
    sequenceName="EMAIL_SEQ_GEN", 
    allocationSize=500
) // initialValue=1 (default)

CREATE SEQUENCE EMAIL_SEQ_GEN START WITH 1 INCREMENT BY 500;

produces

entityId = 1 - 500 + 1 = -500 // EclipseLink error

@SequenceGenerator( 
    name="email-seq-gen", 
    sequenceName="EMAIL_SEQ_GEN", 
    initialValue=1, 
    allocationSize=500 )

CREATE SEQUENCE EMAIL_SEQ_GEN START WITH 1 INCREMENT BY 500;

produces

entityId = 1 - 500 + 1 = -500 // EclipseLink error

@SequenceGenerator( 
    name="email-seq-gen", 
    sequenceName="EMAIL_SEQ_GEN", 
    initialValue=500, 
    allocationSize=500
)

CREATE SEQUENCE EMAIL_SEQ_GEN START WITH 500 INCREMENT BY 500;

produces

entityId = 500 - 500 + 500 = 500 // fine, but inappropriate
entityId = 500 - 500 + 1000 = 1000 // incremented by 500
entityId = 500 - 500 + 1500 = 1500 // incremented by 500
...

To meet your requirements the following one should be used:

@SequenceGenerator( 
    name="email-seq-gen", 
    sequenceName="EMAIL_SEQ_GEN", 
    allocationSize=500 
) // initialValue=1 (default) but 'START WITH'=500

CREATE SEQUENCE EMAIL_SEQ_GEN START WITH 500 INCREMENT BY 1;

produces

entityId = 500 - 500 + 1 = 1
entityId = 500 - 500 + 2 = 2
entityId = 500 - 500 + 3 = 3
...

An existing sequence can be removed from the underlying database with the following SQL command:

DROP SEQUENCE email_seq_gen RESTRICT;

I hope it helps.