Sequence in Oracle

Create Sequence

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

In case that we already have data in DB, use this snippet:

declare
    new_id number;
begin
   select NVL(max(id),0) + 1
   into   new_id
   from   my_table;

    execute immediate 'Create sequence my_seq
                       start with ' || new_id ||
                       ' increment by 1';
end;
/

Use Sequence

In Entity object:

    /** The auto id. */
    private Long autoId;

    /**
     * Gets the auto id.
     *
     * @return the auto id
     */
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sq")
    @SequenceGenerator(name = "sq", sequenceName = "my_seq", allocationSize=1)
    @Column(name = "AUTOID", unique = true, nullable = false, precision = 30, scale = 0)
    public Long getAutoId() {
        return this.autoId;
    }

    /**
     * Sets the auto id.
     *
     * @param autoId the new auto id
     */
    public void setAutoId(Long autoId) {
        this.autoId = autoId;
    }


GenerationType.SEQUENCE is specific for Oracle. Because Oracle uses Sequence strategy for create AutoId.

In @SequenceGenerator, the “allocationSize” is by default set to 50
And, by default, the “increment by” is set to 1.

Note:

It’s important to note that, the sequence generator work correctly, only when the “allocationSize” and the “increment by” are same, otherwise there is a possibility of getting the “unique constraint violation” exception. therefore, it is better to explicitly define “allocationSize” and “increment by”.