Using a oracle sequence on non primary key

Hibernate ORM
1

Hi everyone,

For my project i need to use hibernate to insert some values into a table. To do this, i absolutely need to use two sequences from the oracle database. The first one is used to generate the primary key and it’s working great. But the second is used to increment a field that is not an id.

It seems that i can’t use the @GeneratedValue annotation on a field that is not an id but i wanted to know if it was still possible to use the oracle sequence ?

Thanks in advance.

2

As explained in this article, you need to use the Hibernate-specific @Generated annotation.

In your case, you can simply do this using a DEFAULT column value:

@Column(
    columnDefinition = "int8 DEFAULT nextval('sensor_seq')",
    insertable = false
)
private Long sequenceId;

Or, you can use a database-generated value using Hibernate but it requires more work than a DEFAULT column value.

3

int8 DEFAULT nextval('sensor_seq')in ORACLE ?
Try
NUMBER(19,0) DEFAULT ON NULL sensor_seq.nextval ...other constraints here...
(for 12c)

Before 12c, I think you need a db trigger or init the field manually with JDBC call to get next sequence value,
which means reporting the init operation of the @Entity to the repository layer instead of the domain one.

4

Thanks a lot for the help.

@p3consulting

I tried your suggestion but it seems the sequence is not called and hibernate try to insert the Default value instead. Maybe, i did something wrong, does this implementation seems correct to you ?

@Column(name="FIELD", columnDefinition = " NUMBER(19,0) DEFAULT ON NULL sensor_seq.nextval", insertable = false)
@Generated(GenerationTime.INSERT)
private long field;
5

I tested it on PostgreSQL and it works like a charm:

So, just adapt it to Oracle. The approach is working as demonstrated by the test above.

6

Java side is not enough, did you modify the table definition in ORACLE?

BTW, it should also be possible to have a ORACLE virtual column of which expression call a function doing the nextval (you can’t have a sequence.nextval directly in the defining expression of a virual column).
The only price to pay is that you could not partition the table on that column then.

Something like:
NUMBER(19,0) generated always as (mynextnsqfunc()) virtual

7

Yes but ORACLE supports DEFAULT with sequence only in 12c… so first megane has to confirm which version is used.

8

My sequence is defined in the database if that’s what you mean.

The version of Oracle should be 11g… so i guess i’m going to need to init the field manually.

9

You can use Hibernate-specific database-generated columns as explained in the User Guide.

10

What I meant was “did you run the ALTER TABLE... to modify the column definition” but since your DB is 11g the DEFAULT sensor_seq.nextval will not work then better use a DB trigger or if your Hibernate version supports it (>= 4.3) the database-generated columns referenced by Vlad.

For the second option, you will have a JDBC round trip hidden in the @ValueGenerationType because you can’t just return sensor_nsq.nextval from getDatabaseGeneratedReferencedColumnValue, the generated SQL will trigger a “sequence number not allowed here” SQL error.

Up to you to evaluate what will perform better…

11

Thanks a lot for your help on this matter. I implemented the database generated columns and it’s working great.

1 Like
12

@megane-vilain i’m also facing same issue…can you share the solution with oracle database.