-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
tchize
 Post subject: generator with Oracle ROWID and triggers
PostPosted: Tue May 10, 2005 9:20 am 
Senior
Senior

Joined: Tue May 10, 2005 9:00 am
Posts: 125
Hibernate version:3.0.2
Name and version of the database you are using:Oracle 8

Hello.
considering a simple table USER_FUNCTION containing a few fields and a primary key of type number named FCT_ID.
Also, considering this table has the following trigger script to automatically set the FCT_ID:

Code:
CREATE OR REPLACE TRIGGER user_function_id_trig
BEFORE INSERT ON USER_FUNCTION
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
  -- local variables here
begin
  select user_function_seq.nextval into :new.FCT_ID from dual;
end user_function_id_trig;
/


in php, the sql used to insert data and fetch the id looks like this (may be a bit wrong as am writing this from memory but you get the idea):
Code:
"INSERT into USER_FUNCTION (FCT_NAME_FR,FCT_NAME_NL) value (:a,:b) returning FCT_ID into :fct_id";


How do i configure the generator part in the hibernate mapping so the primary key is fetched from database after the insert? I saw there is a select generator which does fetch id using a select but it requests that one of the other persistent object's properties is unique. (like the SocialSecurityNumber in docs).
There is also a sequence generator but it won't work because at insert time, the trigger will change the value set by hibernate for FCT_ID.

So what do i put there?:
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="be.rmi.intranet.db.users" default-lazy="true">
<class name="be.rmi.intranet.db.users.Function" table="USER_FUNCTION" lazy="true">
  <id name="id">
   <column name="fct_id" not-null="true" unique="true" sql-type="NUMBER"/>
   <generator class="***SO WHAT?**">

                  ??? ???????????????????? ????

   </generator>
  </id>
   <property name="nameFr" column="FCT_NAME_FR" type="string" not-null="false"/>
   <property name="nameNl" column="FCT_NAME_NL" type="string" not-null="false"/>
   <many-to-one name="manager" column="manager" not-null="false"/>
   <set name="backups" table="USER_FCT_BACKUP" sort="be.rmi.intranet.util.PrioritizableComparator">
       <key column="FCT_ID"/>
       <one-to-many class="be.rmi.intranet.db.users.FunctionBackup"/>
   </set>
</class>
</hibernate-mapping>


Thanks
Top
 Profile  
 
jesse_sweetland
 Post subject:
PostPosted: Tue May 10, 2005 10:08 am 
Regular
Regular

Joined: Mon Jul 26, 2004 2:28 pm
Posts: 86
Location: Pensacola, Florida
Is getting rid of the trigger an option? If so, you could alter your mapping slightly and let Hibernate handle the ID assignment:

Code:
<generator class="native">
   <property name="sequence">user_function_seq</property>
</generator>


Hibernate will peform a select, update the object, and then perform an insert. Alternately, you could implement the Lifecycle interface onSave( ) method to select and update the primary key value once the insert is performed, although I don't know that you will have access to the value returned from the insert statement, so you would have to select on a unique business key anyway...

If is is critical to keep the current behavior, then you may want to set insert="false" in the mapping and handle insertions manually through JDBC using the insert statement you provided.

- Jesse
Top
 Profile  
 
tchize
 Post subject:
PostPosted: Tue May 10, 2005 10:23 am 
Senior
Senior

Joined: Tue May 10, 2005 9:00 am
Posts: 125
jesse_sweetland wrote:
Is getting rid of the trigger an option?

No, not an option, we need existing applications relying on this trigger to still work.

jesse_sweetland wrote:
If is is critical to keep the current behavior, then you may want to set insert="false" in the mapping and handle insertions manually through JDBC using the insert statement you provided.

This is quite like losing all advantages of Hibernate :)
Not a solution too, considering we would have to implement this for about all our pojos. Still seeking for another solution.
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Tue May 10, 2005 10:46 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Use the "select" generator.
Top
 Profile  
 
jesse_sweetland
 Post subject:
PostPosted: Tue May 10, 2005 5:33 pm 
Regular
Regular

Joined: Mon Jul 26, 2004 2:28 pm
Posts: 86
Location: Pensacola, Florida
He mentioned being unable to use the select generator due to lack of a unique value to select on...

If you can't remove the trigger, can you modify it to only set :new.FCT_ID if :new.FCT_ID is null?

- Jesse
Top
 Profile  
 
tchize
 Post subject: yes
PostPosted: Thu May 12, 2005 3:20 am 
Senior
Senior

Joined: Tue May 10, 2005 9:00 am
Posts: 125
jesse_sweetland wrote:
If you can't remove the trigger, can you modify it to only set :new.FCT_ID if :new.FCT_ID is null?


Indeed that's the solution we choose in the end.
We use a sequence generator in hibernate and changes script to
Code:
PROMPT CREATE OR REPLACE TRIGGER user_function_id_trig

CREATE OR REPLACE TRIGGER user_function_id_trig
BEFORE INSERT ON USER_FUNCTION
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
  -- local variables here
begin
  select NVL(:new.FCT_ID,user_function_seq.nextval) into :new.FCT_ID from dual;
end user_function_id_trig;
/


Thanks for help
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.