Understanding JPA sequence generator

I’m using spring data JPA’s sequence generator to assign primary keys to entities.

Model contains:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_post")
@SequenceGenerator(name = "seq_post", allocationSize = 5)
private Long id;

The corresponding sequence definition(for SQL Server DB):

CREATE SEQUENCE dbo.seq_post START WITH 1 INCREMENT BY 5;

Since I wanted to start the ids from 100 instead of 1, so I updated the sql script to

CREATE SEQUENCE dbo.seq_post START WITH 100 INCREMENT BY 5;

And then I encountered the problem as mentioned here. I fixed it by the solution mentioned there.

This made me wonder, when I want the DB sequence to start from 1 then why does this issue does not happen? Based on the answer mentioned here I would expect the ids to not start from 1, but that does not happen. Why is that the case?

Answer

Well first check if you set the property hibernate.id.new_generator_mappings to true as recomended

Than you are right in adjusting the allocationSize with the sequence INCREMENT BY.

If you want to start the ID with a specific value it seems to obey the following rules:

• to start with one set the sequence to START WITH 1 (this seems to be an exception)

• to start with X > 1 set the sequence START WITH X + 50 (accually the same is true for X < 1)

e.g. to start with 5000 with the default allocationSize of 50 define the sequence as follows

create sequence sub_seq
       START WITH 5050
       INCREMENT BY 50
       NOCACHE;

Note that I’m using the NOCACHE option, because I assume Hibernate is the only user of this sequence, so caching is not realy meningfull (and actually replaced with the allocation size.

You also loose between the sessions approx. 1/2 of the allocationSizeof the IDs and you do not want to increase it with additional loss of the cached IDs.