Hibernate 5 Sequence Generate Issue

Question

I am migrating to hibernate 5 from 3. I am seeing the sequence generator not working properly in Hibernate 5. I have sequence defined with minimum value 1000 and increment by 1. But when I am trying to create a new entity record, I am seeing a record inserted with id 951. It seems like the id was minuses 50 from actual sequence next value. In my case the ID should be 1000.

Please let me know any help.

Here is my entity and sequence:

Entity:

@Entity
@Table(name = "SOME TABLE")
public class Group {

  @Id
  @Column(name = "id")
  @SequenceGenerator(name = "name",  sequenceName ="SEQ_name" )
  @GeneratedValue(strategy = GenerationType.AUTO, generator="name")
  private Long id;

  @Pattern(regexp = "^[^\\*]*$", message = "{3011}")
  @Size(message = "{3014}")
  @NotBlank(message = "{3000}")
  @Column(name = NAME, unique = true, nullable = false)
  private String name;

Sequence:

CREATE SEQUENCE  SEQ_name MINVALUE 1000 NOMAXVALUE INCREMENT BY 1 CACHE 20 NOORDER NOCYCLE;

Answer 1

Hibernate calls SEQ_name.nextval but if the allocationSize is greater than 1, it decrements it by the allocationSize and increments it by 1.

The next (allocationSize-1) key generation are done without communication with the database simple by increasing by 1.

As a result, if you use the default allocationSize which is 50, there are two consequences:

The sequence must have the INCREMENT BY set to the same value as allocationSize

To align the sequence with existing key in the database, set the START WITH to

max(ID) + allocationSize

Here a small example

-- START WITH = max(ID) + allocation size 
-- INCREMENT BY = allocation size
-- e.g. if 100 is the last key,
-- to start with a key 101 - set START WITH to 150
CREATE SEQUENCE hib_seq START WITH 150 INCREMENT BY 50;



select hib_seq.nextval - 50 + 1 from dual;
101
-- 49 times Hibernate performs increase by 1 - keys 102 to 150
-- new sequence generation
select hib_seq.nextval - 50 + 1 from dual;
151

Note this behaviour requires that property hibernate.id.new_generator_mappings to true as recomended