hibernate and oracle sequence GenericGenerator creates gap

Question

I've mapped my class as follow (omitted other fields as only ID matters):

@Entity
@Table(name = "MODEL_GROUP")
@Cache(usage = CacheConcurrencyStrategy.TRANSACTIONAL)
public class SettlementModelGroup implements Serializable
{


@Id
@GeneratedValue(generator = "MODEL_GROUP_SEQ", strategy = GenerationType.SEQUENCE)
@GenericGenerator(name = "MODEL_GROUP_SEQ",
        strategy = "sequence",
        parameters = @Parameter(name = "sequence", value = "SEQ_MODEL_GROUP_MODEL_GROUP_ID"))
@Column(name = "MODEL_GROUP_ID", nullable = false)
private Integer modelId;
}

when I'm saving new object:

Integer modelGroupId = sessionFactory.getCurrentSession().save( modelGroup );
System.out.println( modelGroupId );

ID is set as for example 23, but when I look at the database it is actually 24. This is leading to many problems, as I'm using this ID later on. Any idea why it is making this gap?

SQL logs show that everything is fine (I thinks so):

Hibernate: 
 select
     SEQ_MODEL_GROUP_MODEL_GROUP_ID.nextval 
 from
     dual
Hibernate: 
 insert 
 into
     MODEL_GROUP
     (DOMAIN_ID, DESCRIPTION, NAME, PERIOD_TYPE_ID, MODEL_GROUP_TYPE_ID, STATUS_ID, OWNER_ID, MODEL_GROUP_ID) 
 values
     (?, ?, ?, ?, ?, ?, ?, ?)

Trigger and Sequence:

CREATE SEQUENCE "SEQ_MODEL_GROUP_MODEL_GROUP_ID" 
  INCREMENT BY 1 
  START WITH 1 
  NOMAXVALUE 
  MINVALUE 1 
 NOCYCLE 
 NOCACHE 
 NOORDER
;

CREATE OR REPLACE TRIGGER "TRG_MODEL_GROUP_MODEL_GROUP_ID" 
BEFORE INSERT 
ON "MODEL_GROUP" 
FOR EACH ROW 
WHEN (NEW."MODEL_GROUP_ID" is NULL)
BEGIN 
    SELECT "SEQ_MODEL_GROUP_MODEL_GROUP_ID".NEXTVAL 
    INTO :NEW."MODEL_GROUP_ID" 
    FROM DUAL; 
END;

I know nothing of hibernate, but, if you google for "how does hibernate handle oracle sequences", I think you'll find the answers to your questions. — Mark J. Bobak, Jul 27 '12 at 18:55

score 1 · Accepted Answer · edited May 23 '17 at 10:30

Apparently, when Hibernate ask your database for nextValue of ID, it fires also Trigger. So when I ask for ID, I've got number 23 but when actually saving to database by commiting transaction, it is increased again so I've got 24. Solution is described here:

HIbernate issue with Oracle Trigger for generating id from a sequence

To make it work correctly, I changed Trigger:

CREATE OR REPLACE TRIGGER "TRG_MODEL_GROUP_MODEL_GROUP_ID" 
BEFORE INSERT 
ON "MODEL_GROUP" 
FOR EACH ROW 
WHEN (NEW."MODEL_GROUP_ID" is NULL)
BEGIN 
    SELECT "SEQ_MODEL_GROUP_MODEL_GROUP_ID".NEXTVAL 
    INTO :NEW."MODEL_GROUP_ID" 
    FROM DUAL; 
END;

hibernate and oracle sequence GenericGenerator creates gap

1 Answers1