1

I noticed a strange problem happening with the application I am writing.

I use Hibernate, Spring and Oracle database. There is a Orders table in the database, which has its PK generated by a sequence. I defined this in the application as follows:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator="SEQ_ORDER_ID")
@SequenceGenerator(name="SEQ_ORDER_ID", sequenceName="SEQ_ORDER_ID", allocationSize=1)
@Column(name = "ID_ORDER", length = 10, nullable = false)

For saving the Order into database, I create a new Order Order order = new Order();, set its values and use session.persist(order).

Now, the Order object has the correct primary key generated, let's say 120, while previous one was 119, but when I look into the database, the newly created order row has primary key value 121. I used debug and went through the process of creating the Order, that is how I got these values.

If I try creating a new order directly in sqldeveloper, the sequence works correctly and values increment by one.

This happens with every insert, not just Order. The primary key value written into database is consistently +2 instead of +1.

All of the Oracle sequences have the INCREMENT_BY value 1.

I tried adding sessionFactory.getCurrentSession().flush(); after the persist, but nothing changes.

Has anyone ever experienced this? Do You have any idea what I might be doing wrong here that causes this behavior?

EyfI
  • 975
  • 2
  • 17
  • 24
  • Are you asking why Oracle does not generates sequence increment_by 1? if so, please check http://stackoverflow.com/questions/17165954/oracle-sequence-not-generating-the-continuous-number. – exiter2000 May 01 '17 at 19:17
  • I am trying to figure out what happens after I save the value with `persist`. I can see that the `Order` object gets the correct primary key value, but to the database is saved incorrect value (the correct value +1). – EyfI May 01 '17 at 19:19
  • Have you enabled sql logging to make sure that the application is not generating extra sequence values? Sequences shouldn't magically skip values – Shiraaz.M May 01 '17 at 20:20
  • I don't think I have, how would I go about doing that? – EyfI May 01 '17 at 20:42
  • 1
    how do you use sqldeveloper? I doubt you are using triggers to always assign a new sequence value. in this case Spring will generate one number and the trigger will take the next. – lsalamon May 02 '17 at 00:02
  • I am using triggers to assign the next sequence value on new row creation. Is that wrong? – EyfI May 02 '17 at 03:47
  • It is not wrong, but this is the reason for your question as Isalamon wrote. – Christian13467 May 02 '17 at 04:18
  • I may have found the answer in another question here, I will look into it when I can. Thank you all so much for pointing me in the right direction. – EyfI May 02 '17 at 04:25

1 Answers1

1

Thanks to everyone for pointing me in the right direction!

The issue was in the triggers in my database generating new ID even though one was sent from the application. I had to add WHEN (NEW.ID_ORDER is NULL) before the trigger's BEGIN to fix this issue.

As per this answer: HIbernate issue with Oracle Trigger for generating id from a sequence

Community
  • 1
  • 1
EyfI
  • 975
  • 2
  • 17
  • 24