aspose file tools*
The moose likes Object Relational Mapping and the fly likes Pros & Cons on J2EE Table GenerationTypes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Pros & Cons on J2EE Table GenerationTypes" Watch "Pros & Cons on J2EE Table GenerationTypes" New topic
Author

Pros & Cons on J2EE Table GenerationTypes

Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Hi All,

I am particularly interested in using Glassfish with MySQL and would like to find out which of these options best suit my requirement while not being locked (trapped) into a vendor specific environment going forward.

Below is table generation strategies available in J2EE:


What is the pros and crons between them?

This question has been posted on http://forums.sun.com/thread.jspa?threadID=5320315 without any response for a while.

Your suggestion would be very much appreciated.

Thanks,

Jack
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

Auto, it just uses the underlying database's default. So for Oracle that would be a sequence table, for MySQL and SQLServer that would be the auto-increment field. By using Auto is the only one that will work for all database vendors.

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
However, AUTO, IMO breaks under PostGreSQL. (Or at least selects the wrong "default" generator.)

It selects SEQUENCE, but uses a very non-PostGreSQL-ism of a single sequence for the entire database. Rather than the more normal/native sequence per key (as would be created by the SERIAL type in PostGreSQL). If you override the choice to use IDENTITY rather than auto it tends to behave better. I don't know if the single sequence column for all databases is an Oracle-ism, but I haven't seen it in my exposure there. So perhaps its more an issue that SEQUENCE hasn't really been implemented intelligently anywhere....

I had extreme difficulty getting AUTO working with PostGreSQL with either hand coded DDL or gving hibernate control of the scheme with hbm2ddl. But have had relatively few problems when I use IDENTITY.

This default choice seems to be persistent across JPA implementations so it doesn't seem to be a vendor problem, but I can't tell if its a problem in the spec to begin with....
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

Originally posted by Eric Nielsen:
However, AUTO, IMO breaks under PostGreSQL. (Or at least selects the wrong "default" generator.)

It selects SEQUENCE, but uses a very non-PostGreSQL-ism of a single sequence for the entire database. Rather than the more normal/native sequence per key (as would be created by the SERIAL type in PostGreSQL). If you override the choice to use IDENTITY rather than auto it tends to behave better. I don't know if the single sequence column for all databases is an Oracle-ism, but I haven't seen it in my exposure there. So perhaps its more an issue that SEQUENCE hasn't really been implemented intelligently anywhere....

I had extreme difficulty getting AUTO working with PostGreSQL with either hand coded DDL or gving hibernate control of the scheme with hbm2ddl. But have had relatively few problems when I use IDENTITY.

This default choice seems to be persistent across JPA implementations so it doesn't seem to be a vendor problem, but I can't tell if its a problem in the spec to begin with....


Interesting. I have used Postgres a few times and never had this problem with Auto. What version of Postgres and what Dialect class are you using?

Thanks

Mark
Eric Nielsen
Ranch Hand

Joined: Dec 14, 2004
Posts: 194
The problem occurs under PostGreSQL 8.2.x and 8.3.x. Dialect is "POSTGRESQL".

To get it to work with a "legacy" database that used all hibernate's and postgresql's defaults naming conventions required adding about 2 annotations to every entity -- one to create the named sequence and one to link that sequence to the id column. Since then I've changed to using identity. I would much prefer auto, but it just doesn't seem to work.

And I'm not comfortable with Hibernate's default of a single sequence column for all tables in the database. (when doing green field development and hbm2ddl)
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

Originally posted by Eric Nielsen:
The problem occurs under PostGreSQL 8.2.x and 8.3.x. Dialect is "POSTGRESQL".

To get it to work with a "legacy" database that used all hibernate's and postgresql's defaults naming conventions required adding about 2 annotations to every entity -- one to create the named sequence and one to link that sequence to the id column. Since then I've changed to using identity. I would much prefer auto, but it just doesn't seem to work.

And I'm not comfortable with Hibernate's default of a single sequence column for all tables in the database. (when doing green field development and hbm2ddl)


Ok so older versions

"I'm not comfortable with Hibernate's default of a single sequence column for all tables in the database."

I completely agree, which is why I always create a sequence table for each table, and specify the sequence name in my Annotations.

Mark
Jack Bush
Ranch Hand

Joined: Oct 20, 2006
Posts: 235
Gentlemen,

Thanks for the brief yet thorough response on your experience.

I will stay with AUTO option when using MySQL then.

Jack
Eric Nielsen
Ranch Hand

Joined: Dec 14, 2004
Posts: 194
Originally posted by Mark Spritzler:


Ok so older versions

"I'm not comfortable with Hibernate's default of a single sequence column for all tables in the database."

I completely agree, which is why I always create a sequence table for each table, and specify the sequence name in my Annotations.

Mark


Umm... not older version. 8.3.x is the current release...

Setting a sequence name doesn't seem to be respected when using AUTO... unless you also override the GeneratorType....
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

Sorry Eric, when I read this line

To get it to work with a "legacy" database that used all hibernate's and postgresql's defaults naming conventions required adding about 2 annotations to every entity


And saw Legacy, I assumed that meant you had to do that with an older version of Postgres.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Pros & Cons on J2EE Table GenerationTypes