aspose file tools*
The moose likes Object Relational Mapping and the fly likes Schema generated via Hibernate, sequence issue? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Schema generated via Hibernate, sequence issue?" Watch "Schema generated via Hibernate, sequence issue?" New topic
Author

Schema generated via Hibernate, sequence issue?

Hussein Baghdadi
clojure forum advocate
Bartender

Joined: Nov 08, 2003
Posts: 3479

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)
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Eric Nielsen
Ranch Hand

Joined: Dec 14, 2004
Posts: 194
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
Bartender

Joined: Nov 08, 2003
Posts: 3479

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

Joined: Dec 14, 2004
Posts: 194
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
Bartender

Joined: Nov 08, 2003
Posts: 3479

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
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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

Joined: Dec 14, 2004
Posts: 194
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Schema generated via Hibernate, sequence issue?