Using sequence on ID column in Oracle with Hibernate creates strange (negative) values

Question

I am trying to learn the use of Hibernate and JPA with an Oracle database.

This is, what I have so far.

Table and sequence declaration

CREATE TABLE MESSAGE( ID    INTEGER NOT NULL PRIMARY KEY,
                      Text  VARCHAR2( 255 ) NOT NULL );

CREATE SEQUENCE Message_Seq START WITH 1 INCREMENT BY 1;

hibernate.cfg.xml

<hibernate-configuration>
<session-factory>

    <!-- Database connection settings -->
    <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
    <property name="hibernate.connection.password">tbi</property>
    <property name="hibernate.connection.url">jdbc:oracle:thin:@LHT:3100/LHT0</property>
    <property name="hibernate.connection.username">tbi</property>
    <property name="hibernate.default_schema">tbi</property>

    <!-- SQL dialect -->
    <property name="hibernate.dialect">org.hibernate.dialect.Oracle8iDialect</property>

    <!-- Echo all executed SQL to stdout -->
    <property name="show_sql">true</property>

    <!-- Mapping class -->
    <mapping class="entity.Message"/>
</session-factory>

Entity Class

package entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Table(name = "message")
public class Message {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_Sequence")
    @SequenceGenerator(name = "id_Sequence", sequenceName ="Message_Seq")
    @Column(name = "ID")
    private Long id;

    @Column(name = "TEXT")
    private String text;

    public Message() {
    }

    public Message(String text) {
        this.text = text;
    }

    @Override
    public String toString() {
        return "Message [id=" + id + ", text=" + text + "]";
    }

}

Client class

package client;

import org.hibernate.Session;
import util.HibernateUtil;
import entity.Message;

public class HelloWorldClient {

    public static void main(String[] args) {

        Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();

        Message message = new Message("Hello, World!");

        session.save(message);

        session.getTransaction().commit();
        session.close();

}

}

If I execute this, the first time I see this as SQL output in the console:

Hibernate: select tbi.Message_Seq.nextval from dual
Hibernate: select tbi.Message_Seq.nextval from dual
Hibernate: insert into tbi.message (TEXT, ID) values (?, ?)

Note how the sequence call is done twice.

The table looks like this:

SELECT *
FROM   MESSAGE;

ID  Text
--  -------------
1   Hello, World!

SELECT Message_Seq.NEXTVAL
FROM   DUAL;

NEXTVAL
-------
3

If I execute my example the second time, I see this console output:

Hibernate: select tbi.Message_Seq.nextval from dual
Hibernate: insert into tbi.message (TEXT, ID) values (?, ?)

Note how the sequence call is done once.

SELECT *
FROM   MESSAGE;

ID  Text
--- -------------
1   Hello, World!
-45 Hello, World!

SELECT Message_Seq.NEXTVAL
FROM   DUAL;

NEXTVAL
-------
5

nextval counts correctly, but where does the -45 come as an ID?

This goes on and on from there:

ID  Text
--- -------------
1   Hello, World!
-43 Hello, World!
-44 Hello, World!
-45 Hello, World!

To be honest, I am totally confused at the moment.

No you are not using the JPA API. That is the Hibernate API! — Neil Stockton, Jan 18 '17 at 16:45
Please refer to the following. http://stackoverflow.com/questions/9861416/hibernate-generates-negative-id-values-when-using-a-sequence — TechEnthusiast, Jan 19 '17 at 04:41

score 1 · Accepted Answer · answered Jan 19 '17 at 08:12

The hint of TechEnthusiast in the comment did the trick.

You have to set the

<property name="hibernate.id.new_generator_mappings">false</property>

in your Hibernate configuration and also tell hibernate to increment the sequence like you configured your sequence on the Oracle database, e. g. if you set

INCREMENT BY 1

in your database ddl, you need the the

allocationSize=1

correspondingly in your @SequenceGenerator annotation of your entity bean.

Using sequence on ID column in Oracle with Hibernate creates strange (negative) values

1 Answers1