Hibernate auto increment sequence is lossing after

Question

I am using SpringMVC+Hibernate+Mysql combination for my application

I am stugling with one problem from start is -

"In my application some tables having auto-increment feature added using Hibernate.So from that tble when I am deleting any records and then when I am inserting again some more, I am not getting the proper sequence "

For eg. suppose my table ABC initially have 100 records (primary key for eg. abc_id having value in proper sequence from 1 to 100) .

Now when I deleted for eg. 50 records and again inserted 50 more records but this time sequence I am getting is starting from 101 even though I am having same 100 records. So I need record should start after last Id value that is from 51 to 100 again. So in such situations my tables are loosing sequence and this is very frequent case in my application.

Following is my POJO design

@Entity
@Table(name = "outlet_info", catalog = "secondary_sales")
public class OutletInfo implements java.io.Serializable {
    // Fields
    private static final long serialVersionUID = 7915773659216514833L;
    private Integer outletId;//autoIncrement fields
    private String outletTypeInfo;
    private String outletName;
    private String outletGrade;

    // Constructors

    /** default constructor */
    public OutletInfo() {
    }

    @Id
    @GenericGenerator(name="generator", strategy="increment")
    @GeneratedValue(generator="generator")
    @Column(name = "outlet_id", unique = true, nullable = false)
    public Integer getOutletId() {
        return this.outletId;
    }

    public void setOutletId(Integer outletId) {
        this.outletId = outletId;
    }

    //other getters and setters
}

So in my case, when i m deleting any Outlet(POJO) row either "through application using Hibernate session.delete() function" or direct "SQL delete query", I m loosing sequence in both case. Needed some suggestions what can be done here to avoid such case.

thanks

Answer 1

Auto increment does not guarantee sequential numbering. This is not its purpose. Its purpose is to provide uniqueness in the most load-efficient way, so it will always return the highest available number.
The ids you want to reclaim might have already been used by other rows and trying to provide some mechanism to cope with it is not that simple.
In fact you should consider your surrogate keys just what they meant to be: unique identifiers with no other semantics. If you want some sequential numbering you are free to write you custom logic and apply it to (preferably) some additional column.

See also this