This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes JPA impl. w/Hibernate & Oracle PK Sequences/Triggers Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA impl. w/Hibernate & Oracle PK Sequences/Triggers" Watch "JPA impl. w/Hibernate & Oracle PK Sequences/Triggers" New topic
Author

JPA impl. w/Hibernate & Oracle PK Sequences/Triggers

Ted Smyth
Ranch Hand

Joined: May 28, 2008
Posts: 73
I would like to call upon the Java community for a rather specific problem I've been unable to resolve for a while now using the following setup:
- Weblogic 10
- JRE 1.6.05 (well actually JRockit)
- JEE 5.0
- Struts 1.2.x
- JPA with Hibernate 3.2.5.ga as provider
- Oracle 10g

My problem is persisting a new entity (ie. no @Id set when the object is persisted). I was using Weblogic's default implementation of JPA (OpenJPA/Kodo) and persisting new objects works just fine as per:

Entity snippet:


DAO snippet:


Yeah the summaryId is a String (in reality a numeric value). Anyway, with the OpenJPA provider, this works wonderfully for inserting new entities to the database. However, when I switched to Hibernate, immediately my inserts started failing with exceptions complaining that I cannot insert this entity without first setting the Primary Key.

Oracle has the following trigger which selects the next value from the sequence for the Summaries table before an insert, and stuffs it into the insert:



I want to avoid using any @GeneratedValue, @SequenceGenerator, @TableGenerator, etc, etc annotations, since our DBAs will be less than pleased if I tell them I want my application to manage primary keys & their sequences itself. How can I force Hibernate (or JPA, really) to perform the insert without trying to manage the PK field? OpenJPA seemed to have no problems inserting an entity and then retrieving it's PK... Arg.

Any ideas? I've been researching this issue for a while now, with no success (I could use some sequence generator annotations, but again, my DBAs want the triggers to handle the PK generation. Furthermore, the sequence is not even visible to the username our application uses to access the DB, so this approach will require more changes to the DB).

Cheers & Regards,
Edward


Edward Smith
Tomasz Szymanski
Greenhorn

Joined: Jan 11, 2010
Posts: 23
So your SUMMARIES table behaves just like for example a table with identity column in MSSQL: when doing an INSERT, you leave the primary key unspecified and it's generated by a database mechanism.
I may be wrong but I think @GeneratedValue with IDENTITY strategy should work well. I'd expect it to issue an INSERT without the PK value and then retrieve it immediately. Have you tried it?
Ted Smyth
Ranch Hand

Joined: May 28, 2008
Posts: 73
Tomasz Szymanski wrote:So your SUMMARIES table behaves just like for example a table with identity column in MSSQL: when doing an INSERT, you leave the primary key unspecified and it's generated by a database mechanism.
I may be wrong but I think @GeneratedValue with IDENTITY strategy should work well. I'd expect it to issue an INSERT without the PK value and then retrieve it immediately. Have you tried it?


Trying this now Tomasz, I'll respond with my results! Thanks.
Ted Smyth
Ranch Hand

Joined: May 28, 2008
Posts: 73


Oracle's dialect doesn't seem to support @GeneratedValue(strategy = GenerationType.IDENTITY)
Ted Smyth
Ranch Hand

Joined: May 28, 2008
Posts: 73
TTT!

I ended up having the DBA delete the trigger, which resolved my issue when using Hibernate (I had to add the appropriate @SequenceGenerator and @GeneratedValue annotations, however). But I encountered ANOTHER issue with a very simple query that Hibernate just refused to execute due to invalid column names. I tried Toplink and Eclipselink, and ran into similar issues...

Soooo back to OpenJPA it is! So much for JPA compliance across persistence providers...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JPA impl. w/Hibernate & Oracle PK Sequences/Triggers
 
Similar Threads
Toplink memory problem
why my oracle sequence id increases by 2 when I use hibernate to insert
not able to insert data in db using jpa(hibernate as provider) and jboss AS
Composite Key Mapping with OneToMany does not work
detached entity passed to persist