• 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

Schema generated via Hibernate, sequence issue?

 
clojure forum advocate
Posts: 3479
Mac Objective C Clojure
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi.
I got my database schema generated for PostgreSQL database via Hibernate annotations.
The schema generated tables definitions and one sequence definition called: hibernate_sequence
I wrote a couple of SQL text files to populate the database, I run those files via Apache Ant.

I start to smell some kind of disaster...
I shouldn't hard code IDs by hand as I did, I have to use hibernate_sequence to populate all IDs in all tables, right?
If it is so, any ideas how to do it with my SQL files and Ant?
Thanks. (I hope this is the right forum to ask)
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well lets say it was Oracle, just since I know Oracle's sequence.

You would just use hibernate_sequence.next_val in your insert.

hibernate_sequence is just like any other sequence object in the database. You don't have to use Hibernate to use that sequence.

Mark
 
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yeah so far I haven't been able to get Hibernate to play nicely with PostGreSQL when using hbm2ddl. It defaults to using sequences, but not in the way that PostGreSQL tends to like (ie one sequence per table, hooked up via a serial data type for smart column defaults). So far I can either
a) setup extra annotations to configure the sequence name, and hand-code the DDL to use SERIAL
or
b) struggle through the same problem you're seeing

I've heard that you might be able to configure the Generator to use type IDENTITY instead of SEQUENCE and it might actually use the serial instead. but I haven't played enough yet to confirm
 
Hussein Baghdadi
clojure forum advocate
Posts: 3479
Mac Objective C Clojure
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please correct me if I'm wrong,
Is hibernate_sequence shared with all tables? is this safe in clustered deployment?
And to use the sequence in my SQL files, I have to write:

?
Thank you.
 
Eric Nielsen
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes the Hibernate default for PostGreSQL is very odd, at least from my perspective as a long time PostGreSQL user.. Not sure if it makes sense to Oracle-types, but it seems like that is who they were targetting with how they made Hibernate deal with sequences..

Yes its a global sequence for all tables (almost an Object Identifier).

I've been using the following annotations to configure my tables... but this seems to break hbm2ddl so you need to handle schema changes manually....



When using this system, the PK of the table could have been declared SERIAL and thus the default of grabbing the next identifier from the per-table sequence works ( so you don't need to explicitly call next_val, etc)

I really wish there was a way to use a NamingStrategy to provide the sequence name for an entity....

However I would suggest giving the following a try (I haven't tested this yet)

Oracle-types, but it seems like that is who they were targetting with how they made Hibernate deal with sequences..

Yes its a global sequence for all tables (almost an Object Identifier).

I've been using the following annotations to configure my tables... but this seems to break hbm2ddl so you need to handle schema changes manually....



Since I've been told that will create a type of BIGSERIAL in the database and should play properly with hbm2ddl.
[ February 11, 2008: Message edited by: Eric Nielsen ]
 
Hussein Baghdadi
clojure forum advocate
Posts: 3479
Mac Objective C Clojure
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I've been using the following annotations to configure my tables... but this seems to break hbm2ddl so you need to handle schema changes manually....


Would you please telling me in more details what are changes I have to do manually?
 
Mark Spritzler
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you tried



For yourself to see what it does generate in Postgres? I think what he was saying is the field type of the PK isn't the best type for Postgres. However, I would think that if you have this in your annotations and it generates the tables, that you don't have to manually call nextval yourself, that Hibernate will include what it needs in the insert statement itself. But this is a guess on my part, as I haven't done this specific situation myself.

Mark
 
Eric Nielsen
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There's two approaches:
1) is Mark Spritzler said, based on my first example. I seem to recall it didn't play well with some part of either hbm2ddl or dbUnit failing to respect dropping tables in reverse order (hence FK violations). I'm hoping to re-examine the possibly solutions this weekend.

2) The untested example shown above with the
@GeneratedValue(strategy=GenerationType.IDENTITY) instead of
the default @GeneratedValue or the approach one version using explciit sequence. I've heard of others using this approach with success and am looking forward to trying it out myself....


My gut instinct is that approach 1 is probably better if you want to hand-craft your DDL and plan to migrate your database externally from the application. Approach 2 is probably better if you're going to use hbm2ddl.
 
reply
    Bookmark Topic Watch Topic
  • New Topic