Sam
Sam

Reputation: 8693

auto incremented number generation for a non primary key column

I have used the following id generation strategy for primary keys.

    @Id
        @GeneratedValue(strategy = IDENTITY)
        @Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false)
        private Integer id;

I would like to do the same for a non primary key column. [a] What would be the semantics of defining an automatic generation scheme for such a key [b] Is there a guarantee that there will not be a gap in numbers for the generated number.

Upvotes: 12

Views: 19529

Answers (4)

Akash Kathait
Akash Kathait

Reputation: 1

Looks like there is no direct way to generated unique id that too in incremental order. however we can use synchronized method to first get latest value from DB and then increment that value but it will degrade api performance as only one request will be able to access synchronized block.

private synchronized Object helper()  {
  Integer i = repo.findMax() ;
     try {
          Thread.sleep(4000);
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }
        if (i== null) i = 0 ;
        DemoEntity entity = new DemoEntity();
      entity.setSeq(++i);
    return repo.save(entity) ;
}

Upvotes: 0

Vasudev
Vasudev

Reputation: 1007

I solved it using columnDefinition property in @Column annotation.

@Column(name = "non_pk_column", columnDefinition = "BIGINT(20) NOT NULL UNIQUE KEY auto_increment")
Long nonPkColumn;

Upvotes: 3

James McMahon
James McMahon

Reputation: 49609

If you remove the @Id annotation, and keep the rest (changing the field name of course), that should work.

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "columnName", unique = true, nullable = false, insertable = false, updatable = false)
    private Integer columnName;

By allowing the database to generate the column values you will ensure that there are no gaps, except in the case of deletes and rollbacks.

For instance, if you delete rows in the middle of your table, then that will create a gap that will not be filled.

Upvotes: 5

ewernli
ewernli

Reputation: 38605

Normally, auto incremented value are ensured to be always increasing, but there can be gaps.

Gaps can happen if two inserts happen concurrently, and one transaction is rolled back, for instance (If the database were to ensure there is no gap, all transactions would need to be serialized.)

EDIT

Example for oracle taken from this page:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.

The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.

Upvotes: 2

Related Questions