Autoincrement with Grails/Hibernate for different DBs

2
votes

it's not a realy problem, but it surprises me:

when I use Grails with diffrent DBs, I get different counter increments...:

  • with the ootb hsqldb, every table gets its own counter which is always increased by 1
  • with an oracle db, it seems that all tables use the same global counter
  • now I am using javadb/derby and the generated id are huge!

where can I find some more information about this behaviour and which one is the best?

  • hsql seems to keep the counters small
  • with oracle, I get a global unique id - also a nice feature
  • but what about the derby behaviour?
1
databasehibernategrails

1 Answers

3
votes

It really depends on the default id generation strategy in the specific dialect. Grails allows you to customize the generation strategy with mapping closure.

The most 'safe' (i.e. being supported by every RDBMS) generation strategy is TABLE, and this is preferred choice of many JPA implementations. This is probably what you get in HSQLDB. However, Oracle support sequences and these objects are generally better optimized for handling key generation -- hence, the dialect for Oracle seems to use one global sequence. I'm not familiar with Derby, but probably there is identity column support there and what you get is some sort of UUID.