Generated primary keys start form 9951 instead of the 10000

I make use of:

NetBeans IDE 6.7.1, GlassFish v2.1, Oracle 10g XE, JAVA 6 SE, JAVA 5 EE,

From inside a stateless EJB I persist entities of type customer I have the annotation: @SequenceGenerator(name="seq", sequenceName="cust_id_seq") in the class customer so the primary keys are autogenerated in the database from the sequence cust_id_seq but when I persist the first customer the primary key is 9951 instead of 10000, the primary key of the second customer is 9952. The output of the GlassFish v2.1 after I persisted two customers is:

Application server startup complete.

My id is: 0

TopLink, version: Oracle TopLink Essentials - 2.1 (Build b31g-fcs (10/19/2009))

Server: unknown file:/C:/Documents%20and%20Settings/IOANNIS_PAPAIOANNOU/My%20Documents/NetBeansProjects/VideoClub/dist/gfdeploy/VideoClub-ejb_jar/-vc_pu login successful

My id is: 9951

My id is: 0

My id is: 9952

@Entity
@Table(name = "customer")
@SequenceGenerator(name="seq", sequenceName="cust_id_seq")
public class Customer implements Serializable
{

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="seq")
@Column(name="CUST_ID")
private int id;

@Column(name = "phone_number")
private int phoneNumber;

@Column(name = "first_name")
private String firstName;

@Column(name = "last_name")
private String lastName;

@Column(name = "credit")
private int credit;

@OneToMany    
private Collection<CustRentMovie> rents = new ArrayList<CustRentMovie>();

public int getCredit()
{
    return credit;
}

public void setCredit(int credit)
{
    this.credit = credit;
}

public String getFirstName()
{
    return firstName;
}

public void setFirstName(String firstName)
{
    this.firstName = firstName;
}

public int getId()
{
    return id;
}

public void setId(int id)
{
    this.id = id;
}

public String getLastName()
{
    return lastName;
}

public void setLastName(String lastName)
{
    this.lastName = lastName;
}

public int getPhoneNumber()
{
    return phoneNumber;
}

public void setPhoneNumber(int phoneNumber)
{
    this.phoneNumber = phoneNumbe开发者_JAVA技巧r;
}

public Collection<CustRentMovie> getRents()
{
    return rents;
}

public void setRents(Collection<CustRentMovie> rents)
{
    this.rents = rents;
}


}

the code of the EJB:

@Stateless
public class ClerkSessionBean implements ClerkSessionRemote
{

@PersistenceContext(unitName = "vc_pu")
private EntityManager em;

public int writeCustomer(AlmostCustomer almostCustomer)
{

    Customer customer = new Customer();
    System.out.println("My id is: " + customer.getId());
    customer.setFirstName(almostCustomer.getFirstName());
    customer.setLastName(almostCustomer.getLastName());
    customer.setPhoneNumber(almostCustomer.getPhoneNumber());
    em.persist(customer);
    System.out.println("My id is: " + customer.getId());
    return customer.getId();
}
}

part of the script that creates the database:

CREATE table customer
(
cust_id NUMBER(5),
phone_number NUMBER(10) NOT NULL,
first_name VARCHAR2(12) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
-- Τα χρήματα που έχει ένας πελάτης στο λογαριασμό του.
credit NUMBER(5, 2) DEFAULT 0 NOT NULL,
CONSTRAINT cust_pk PRIMARY KEY (cust_id),
-- Μόνο ένας λογαριασμός για κάθε σπίτι.
CONSTRAINT phone_unique UNIQUE (phone_number)
)
/

DROP SEQUENCE cust_id_seq
/

CREATE SEQUENCE cust_id_seq MINVALUE 10000 MAXVALUE 99999 INCREMENT BY 1 START WITH 10000 NOCACHE  NOCYCLE ORDER

/

Yiannis P.

I think you should change CREATE SEQUENCE cust_id_seq ... INCREMENT BY1 to ... INCREMENT BY50. Here is why.

The declared sequence increment should match the @SequenceGenerator's allocationSize parameter. allocationSize is not specified and therefore defaults to 50. But the true INCREMENT BY is only 1, not 50. So there is a mismatch between allocationSize and the real INCREMENT BY.

Here's how this mismatch could cause the problem. TopLink Essentials (TLE) calls NEXTVAL() on cust_id_seq. The sequence returns 10000 + 1. TLE assumes the sequence just incremented by 50 (default JPA allocationSize) instead of 1 (actual amount). TLE subtracts allocationSize = 50 from 10001 to get 9951.

By the way, if you have the freedom to do so, consider upgrading to TLE's successor, EclipseLink (and to Glassfish v3).

In the customer entity in the annotation @SequenceGenerator I added the atribute allocationSize with value 1, now the primary keys start from 10000.

@Entity
@Table(name = "customer")
@SequenceGenerator(name="seq", sequenceName="cust_id_seq", allocationSize=1)
public class Customer implements Serializable
{

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="seq")
    @Column(name="CUST_ID")
    private int id;  
...
}