• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 73
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 73
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 73
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


Oracle's dialect doesn't seem to support @GeneratedValue(strategy = GenerationType.IDENTITY)
 
Ted Smyth
Ranch Hand
Posts: 73
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
So you made a portal in time and started grabbing people. This tiny ad thinks that's rude:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic