12

My database is Oracle, and my id column value is an Oracle sequence, this sequence is executed by a trigger, so, before each row is inserted this trigger use this sequence to get the id value. So I am confused in which id strategy generation should I define in my entity class.

@GenericGenerator(name = "generator", strategy = "increment")
@Id
@GeneratedValue(generator = "generator")

or 

@Id
@GeneratedValue(strategy = GenerationType.AUTO, generator = "idGenerator")
@SequenceGenerator(name="idGenerator", sequenceName="ID_SEQ")

or 

@Id
@GeneratedValue(strategy = GenerationType.AUTO)

Really confused, Could someone shed some light on this subject? Please explain clearly..

Kirk Woll
  • 76,112
  • 22
  • 180
  • 195
kneethan
  • 423
  • 1
  • 8
  • 22
  • Is that trigger a requirement? – Heiko Rupp Mar 16 '11 at 20:09
  • the db team operates independently and they had this trigger for this id. My confusion was what is really going on with these annotations to persist the entity with or without the id? if you provide id manually to the insert_into then this trigger will be discarded? – kneethan Mar 16 '11 at 21:08
  • It depends on how the trigger has been written - if Hibernate supplies the ID, does the trigger overwrite it from the sequence, or does the trigger only set the ID if it is NULL? – Jeffrey Kemp Mar 17 '11 at 08:30

1 Answers1

8

I had also a projet where an Oracle DB that provides the data to my @Entity classes. As you said, a sequence generates the id for the PK of the table via a trigger. This was the annotations that I used in one of these classes:

@Id
@GeneratedValue(strategy = GenerationType.AUTO, generator = "G1")
@SequenceGenerator(name = "G1", sequenceName = "LOG_SEQ")
@Column(name = "ID", unique = true, nullable = false, precision = 22, scale = 0)
public int getId() {
    return this.id;
}

This is the second syntax that you have showed in your post. There's no call to the trigger in the Java code because the trigger is managed by the DB. I remember that I had to have the sequence and the trigger at the same time in the DB if I didn't wanted to have problems. The trigger asked if the id of the row to insert is null or = 0. In this case the sequence LOG_SEQ is called.

So if you provide a value to the @Id of your entity it could be inserted in the DB (if that Id doesn't exist) and the sequence would not be called. Try to see the code of the trigger to see exactly what it happens.

Darrell Teague
  • 4,132
  • 1
  • 26
  • 38
jomaora
  • 1,656
  • 3
  • 17
  • 26
  • 1
    Thanks for your answer, but I'm going to use the first approach for not relying on trigger. so in entire application I can use the first approach and not worry about trigger or not trigger or sequence or not sequence. any thoughts? so My approach will be : ` @GenericGenerator(name = "generator", strategy = "increment") @Id @GeneratedValue(generator = "generator") – kneethan Mar 16 '11 at 21:53
  • @kneethan Yes you can try the first approach. Let me know what kind of answer you have cause I have the feeling that Oracle bothers about the way you use theses annotations... I remember that I have no problem with MsSqlServer but with Oracle. – jomaora Mar 16 '11 at 22:04
  • Please have a look at this example: http://developer-should-know.com/post/82479486933/how-to-use-oracle-before-insert-trigger-for-id It shows how to implement a custome hibernate generator that retrieves the id post insert. – Paul A. Trzyna May 22 '17 at 15:41