PK auto-gen'ed n Oracle with trigger, sequence not working

metazone · **Joined:** Thu Nov 30, 2006 10:48 am **Posts:** 59

I'm using JPA/Hibernate with Oracle. In Oracle, I'm using a single PK field for each table (surrogate primary key) and auto-generating it using a before-insert trigger that uses a sequence. When trying to save a new object, I'm getting an error when trying to do a save of a new object using JPA/Hibernate. Note that I thought maybe it was due to the trigger so ... I disabled trigger on both tables and still had the same problem/

SQL Trace: Hibernate: call next value for DIRECT_ROUTING_ID_SEQ

com.att.newton.exc.ObjectSaveException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at com.att.newton.dao.entity.jpahibernate.EntityDAOJPAHibernateBase.save(EntityDAOJPAHibernateBase.java:88)
at com.att.newton.dao.DirectRoutingDAOTest.save_Test(DirectRoutingDAOTest.java:71)

DAO method:

Code:

em.getTransaction().begin(); 
em.persist(object);
em.getTransaction().commit();

Model classes:

Code:

@Entity
@Table(name="DIRECT_ROUTING")
public class DirectRouting extends NScheduler
{
   // SequenceGenerator: name = name that is referenced by other annotations; sequenceName = actual name of the sequence in the db
   // GeneratedValue: generator = the name of the primary key generator to use as specified in the SequenceGenerator or TableGenerator annotation. 
   
   @Id
   @SequenceGenerator(name="DIRECT_ROUTING_ID_SEQ", sequenceName="DIRECT_ROUTING_ID_SEQ", allocationSize=1)
   @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="DIRECT_ROUTING_ID_SEQ")
   @Column(name="ID")
   private Integer ID;

   // DirectRouting 1:M bidirectional DirectRoutingNote
   @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="directRouting")
   private List<DirectRoutingNote> directRoutingNotes;

...

@Entity
@Table(name="DIRECT_ROUTING_NOTE")
public class DirectRoutingNote extends NScheduler
{
   @Id
   @SequenceGenerator(name="DIRECT_ROUTING_ID_SEQ", sequenceName="DIRECT_ROUTING_ID_SEQ", allocationSize=1)
   @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="DIRECT_ROUTING_ID_SEQ")
   @Column(name="ID")
   private Integer ID;
   
   @Column
   private int sequenceNum; // e.g. 1, 2, 3
   
   @Column
   private String note;
   
   // DirectRouting 1:M bidirectional DirectRoutingNote
   @ManyToOne(fetch=FetchType.EAGER)
   @JoinColumn(name="DIRECT_ROUTING_ID")
   private DirectRouting directRouting;
...

Db tables, triggers and sequences:

Code:

<< ============== >>
<< DIRECT_ROUTING >>
<< ============== >>

  CREATE TABLE "NTSCHED"."DIRECT_ROUTING" 
   (   "ID" NUMBER(11,0), 
   "CUSTOMERMCN" VARCHAR2(35 BYTE), 
   "CUSTOMERMCNGRC" VARCHAR2(100 CHAR), 
   "ENTEREDDATE" TIMESTAMP (6), 
   "LASTUPDATEDDATE" TIMESTAMP (6), 
   "UPDATEDBY" VARCHAR2(8 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
  TABLESPACE "MS" ;


  CREATE UNIQUE INDEX "NTSCHED"."DIRECT_ROUTING_PK" ON "NTSCHED"."DIRECT_ROUTING" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
  TABLESPACE "MS" ;


  ALTER TABLE "NTSCHED"."DIRECT_ROUTING" MODIFY ("ID" NOT NULL ENABLE);

  CREATE OR REPLACE TRIGGER "NTSCHED"."DIRECT_ROUTING_ID_TRIG" 
BEFORE INSERT ON DIRECT_ROUTING FOR EACH ROW
BEGIN
  IF :NEW.ID IS NULL THEN
     SELECT DIRECT_ROUTING_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
  END IF;
END;
/
ALTER TRIGGER "NTSCHED"."DIRECT_ROUTING_ID_TRIG" ENABLE;


<< =================== >>
<< DIRECT_ROUTING_NOTE >>
<< =================== >>

  CREATE TABLE "NTSCHED"."DIRECT_ROUTING_NOTE" 
   (   "ID" NUMBER(11,0), 
   "SEQUENCENUM" NUMBER(11,0), 
   "NOTE" VARCHAR2(100 CHAR), 
   "ENTEREDDATE" TIMESTAMP (6), 
   "LASTUPDATEDDATE" TIMESTAMP (6), 
   "DIRECT_ROUTING_ID" NUMBER(*,0), 
   "UPDATEDBY" VARCHAR2(8 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
  TABLESPACE "MS" ;

  CREATE UNIQUE INDEX "NTSCHED"."DIRECT_ROUTING_NOTES_PK" ON "NTSCHED"."DIRECT_ROUTING_NOTE" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
  TABLESPACE "MS" ;

  ALTER TABLE "NTSCHED"."DIRECT_ROUTING_NOTE" MODIFY ("DIRECT_ROUTING_ID" NOT NULL ENABLE);
  ALTER TABLE "NTSCHED"."DIRECT_ROUTING_NOTE" MODIFY ("ID" NOT NULL ENABLE);

  CREATE OR REPLACE TRIGGER "NTSCHED"."DIRECT_ROUTING_NOTE_ID_TRIG" 
BEFORE INSERT ON DIRECT_ROUTING_NOTE FOR EACH ROW
BEGIN
  IF :NEW.ID IS NULL THEN
     SELECT DIRECT_ROUTING_NOTE_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
  END IF;
END;
/
ALTER TRIGGER "NTSCHED"."DIRECT_ROUTING_NOTE_ID_TRIG" ENABLE;

metazone · **Joined:** Thu Nov 30, 2006 10:48 am **Posts:** 59

The problem has been resolved. This is what I did:
1. I no longer need the before-insert trigger (the trigger that accesses the sequence). I disabled it.

2. I kept the following the same in the model class:

Code:

@Id
@SequenceGenerator(name="DIRECT_ROUTING_ID_SEQ", sequenceName="DIRECT_ROUTING_ID_SEQ", allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="DIRECT_ROUTING_ID_SEQ")
@Column(name="ID")
private Integer ID;

3. But ... I then received the error, "ORA-06576: not a valid function or procedure name". The fix was to change the hibernate dialect in persistence.xml (I'm using Oracle 11g but didn't see a dialect specifically for 11g so ... using 10g):
<

Code:

properties>
   <!-- Commented out this one: <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/> -->
   <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>

4. IMPORTANT: I then realized I could just a single sequence in Oracle for all the tables, instead of a sequence for each individual table. I wanted to put this ID field in a base class (treated as a mapped superclass b/c I don't want to have this single root as part of a single-table implementation for the inheritance hierarchies below it) and, if I had to use a different sequence for each table, I'd have to put the ID in each Entity class (instead of a base class) b/c the sequence name is required. So ... I created a single sequence for our application and put the ID in the root class (the mapped superclass) as follows (MY_APP is replaced with the actual app name):

Code:

@Id
@SequenceGenerator(name="MY_APP_ID_SEQ", sequenceName="MY_APP_ID_SEQ", allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="MY_APP_ID_SEQ")
@Column(name="ID")
private Integer ID;

Notes:
1. I saw info from TopLink (JPA Provider) where we can let the provider provide a default sequence. I tried it with the following:
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE)
private long ID;

but I received an error:

Hibernate: select hibernate_sequence.nextval from dual

java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist