Hibernate and Sql Sequence

Did you ever try to use Hibernate sequence? what if you want it to be used in SQL and in Hibernate at the same time?

First lets get to have sql sequence:

create the sequence:

create sequence yourSeqName
minvalue 1 
maxvalue 999999999999999 
increment by 1 
start with 1 
cache 1000 
order  nocycle;

for getting the value of the sequence in the a script:

Oracle - yourSeqName.nextval

SQL- NEXT VALUE FOR yourSeqName

The sequences will be saved in:

Oracle - SYS.ALL_SEQUENCES

SQL - SYS.SEQUENCES

Sequence in hibernate: the annotation will be on the id column

@Id
@Column(name = "ID", nullable = false)
protected Long id;

with the annotation:

@Id
@Column(name = "ID", nullable = false)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "yourEntitySeqGen")
@SequenceGenerator(name = "yourEntitySeqGen", sequenceName = "yourSeqName")
protected Long id;

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "yourEntitySeqGen") could be in the SuperClass, and then each SubClass could have different: @SequenceGenerator(name = "yourEntitySeqGen", sequenceName = "yourSeqName").

If the column id is not annotated with @GeneratedValue it will be a problem.

NOTE: for this there is no need to create a database sequence. Hibernate uses a generic sequence. But if you need a sequence that will be used in a script of SQL and also in Hibernate create the sequence as we said.

there is another annotation GenericGenerator: (sequence_name -should be like this!)

@GenericGenerator(name = "yourEntitySeqGen", strategy = "sequence", parameters = {
        @org.hibernate.annotations.Parameter(name = "sequence_name", value = "yourSeqName"),
})

You should use one of the following @SequenceGenerator OR @GenericGenerator.

Debugging (if any problems):

for getting the sequence name debug (on the start server):

org.hibernate.id.enhanced.SequenceStyleGenerator#configure

for getting the script of the sequence debug (on the start server):

org.hibernate.tuple.entity.EntityMetamodel#EntityMetamodel

Getting this error on SQL database?

Invalid object name <sequence>.

So - Hibernate generates the wrong SQL query:

    select
        next_val as id_val 
    from
        yourSeqName with (updlock,rowlock)

instead of:

SELECT NEXT VALUE FOR dbo.yourSeqName;

You need to change the Hibernate properties in your "application.properties" file to specify the correct dialect. Ex: instead of:

#Hibernate properties
hibernate.dialect =  org.hibernate.dialect.SQLServerDialect

use:

#Hibernate properties
hibernate.dialect = org.hibernate.dialect.SQLServer2012Dialect

Good Luck!

#Hibernate #Sequence #Sql