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”.
