CODEWITHSUNDEEP
hibernatejpaeclipselinkspring-data-jpa
Gregor
Gregor

Reputation: 3027

Old Sequence Value for JPA Generated ID using Eclipselink

The generated id for a JPA entity generates an "old", used number, when Eclipselink is used as ORM, but with Hibernate the id is the correct next value of the sequence. In the entity class I use these annotations with the id field

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "s_customer")
@SequenceGenerator(name = "s_customer", sequenceName = "S_CUSTOMER")
private Long id;

I use flyway to setup and populate the db:

public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    jdbcTemplate.execute("create sequence S_CUSTOMER");
    jdbcTemplate.execute("create table CUSTOMER (" +
            "ID BIGINT PRIMARY KEY," +
            "DISPLAY_NAME VARCHAR(3000)," +
            "VERSION BIGINT" +
            ")");
}


public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    for (int i = 0; i < 10000; i++) {
        jdbcTemplate.update(
                "insert into CUSTOMER (ID, DISPLAY_NAME, VERSION) " + "select nextval('S_CUSTOMER'), ?, ? ",
                "SAP-SE", 0);
    }
}

As database I use PostgreSQL.

When my application is going to persist a new entity, using eclipselink, I get an error because the entity got an "old" id:

Call: INSERT INTO CUSTOMER (ID, DISPLAY_NAME, VERSION) VALUES (?, ?, ?)
bind => [3 parameters bound]
Query: InsertObjectQuery(Customer(id=9959, displayName=Bäckerei Brötchen, version=1)); nested exception is javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.3.v20160428-59c81c5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "customer_pkey"
Detail: Key (id)=(9959) already exists.

But when I manually select the current value of the sequence I get the value "10004". So why does eclipselink return a sequence value already used? The same thing with hibernate works just fine.

Upvotes: 2

Views: 1868

Answers (2)

NoisyBoy
NoisyBoy

Reputation: 364

I'd already same allocationSize as defined in DB sequence, but still sporadically was getting old sequence value.

Upvotes: 0

Chris Sekas
Chris Sekas

Reputation: 159

Although this is an old question, recently I had the same problem.

The thing is that the allocationSize (by default 50) should be the same with the clause INCREMENT (by default 1) when you created the sequence in the DB. So if you want to have a sequence that it will be incremented let's say by 100 you should write something like that:

CREATE SEQUENCE my_seq_id
  INCREMENT 100
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1;

And the JPA entity should be like that:

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "my_seq_id")
@SequenceGenerator(name = "my_seq_id", sequenceName = "my_seq_id", allocationSize = 100)
private Long id;

Upvotes: -1

Related Questions