H2 Database generation strategy is leaving gaps between id values

Question

I'm working on a REST API using Spring. I have this class, which id's is being generated automatically:

@Entity
public class Seller implements Serializable{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    private double tasa;

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public double getTasa() {
        return tasa;
    }
    public void setTasa(double tasa) {
        this.tasa = tasa;
    }

}

I added some endpoints to create, delete and get a seller from the DB. So my problem arises when I delete one seller from the DB. When I try to create a new one, I was expecting to get the lower available value for the id but what is actually doing is using some kind of counter/sequence. Let me show you:

So in my second post instruction I was expecting a json with id = 1, instead I received a 2. I tried using TABLE and IDENTITY strategies but the unwanted behavior continued. So my question is: how can I achieve the behavior I desire? I don´t want gaps between my seller's ids.

score 1 · Answer 1 · answered May 01 '20 at 02:04

In general the database are designed to be incremental. When the ID is generated, it is not generated based on the content of the tables. instead of it, the ID is generated using a sequence. In your example you have some records, but imagine a database with a lot of records. The database generates the IDs based on a Sequence (or similar), to avoid read the data, an expensive process.

If the ID is not relevant to the business, then this behavior doesn't affect your process. (Like the message's id in a chat).

If the ID is important, I recommend to redefine the delete process. you probably need to preserve all the ids, like a customer id.

If you want to preserve the sequence and allow delete records, the recommendation is to generate the id by yourself, but you need to lead with problems like concurrence

score 1 · Answer 2 · answered May 01 '20 at 02:36

I tried using TABLE and IDENTITY strategies but the unwanted behavior continued.

This is not unwanted behaviour. Check How primary keys are generated.

So my question is: how can I achieve the behavior I desire? I don´t want gaps between my seller's ids

One way to achieve this is to not use @GeneratedValue(strategy = GenerationType.AUTO) and set id manually from program and in there you can put any logic you want.

It's not recommended to set primary key manually. If you want you can use any other field like seller_code for this behaviour. Another question here which is similar to this.

H2 Database generation strategy is leaving gaps between id values

2 Answers2