Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Pros & Cons on J2EE Table GenerationTypes

 
Jack Bush
Ranch Hand
Posts: 235
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Eric Nielsen
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 235
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic