Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) 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: 17309
11
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: 17309
11
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: 17309
11
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: 17309
11
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.
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!