Hi all,

I have a spring roo webapp which uses Hibernate (3.6.2) as JPA. The entities primary keys are annotated with @Id and @GeneratedValue(strategy = GenerationType.AUTO). The webapp is running fine and I may insert/update/delete records in any table at will. I have a data interface outside the webapp implemented in Pentaho Kettle 4.2.1 aka PDI which periodically inserts and updates records in the database and of course increases the primary keys (strategy there is max(id)+1).

When does hibernate get a new primary key if the strategy is set to AUTO? If have tried the GenerationType.TABLE with no luck. I may do not use GenerationType.SEQUENCE.

May I refresh the internal hibernate PK counters? Do I have to restart the persistanceUnit in some sort? If so how would i do that?

Cheers
A Noob

Hi,

According documentation GenerationType.AUTO always does a fallback to either
Identity-, Sequence- or Hilo-generator
depending on the capabilities of the underlying database.

Following your comments I guess in your case it falls back to GenerationType.TABLE.

If true then on your database there must exist a table named hibernate_sequences with
following 2 columns:
sequence_name (varchar](255) NULL) and
sequence_next_hi_value (int NULL)

This table holds a hilo value for each table.

Hibernate gets the new primary key when you persist a new entity object.



As I know this is not possible, unless you implement and use a own custom generator.




Yes. If in meantime you have altered the content of table hibernate_sequences outside hibernate
(for example within PDI) then you must close and restart the entitymanager factory
in order that the changed values in table hibernate_sequences are considered by hibernate




An alternative approach would consist in using identity generator type which is supported by databases like DB2,MySQL,MS-SQL , Sybase ,HSQLDB etc. making the DB is responsible to assign the primary keys for new inserted objects.

Advantages of this approach:
-no matter about duplicate assigns or wrong set sequence because the db takes the full responsibility
-insertions of new records also feasible also outside hibernate application.

Disadvantages:
-only applicable with table per class-hierarchy mapping (aka flat-mapping).
-no deferring (batching) of jdbc statement possible on insertions because hibernate needs to know the assigned id immediately
(= extra jdbc round trip on makePersistent)
-You need to bootstrap your database again when switching between table and identity generator, because the schema changes.

I will first play with the hibernate_sequence table and restarting the EMF and if that fails revert to the SEQUENCE strategy.

You are great!

Thanks

Hi,

an alternative to restart the emf would be following:
in some way you tell the hibernate-application how many primary keys where assigned outside hibernate within PDI.
Then in hibernate you blow away exaclty the same number of primary keys without ever propagating them to the database.
In this way you gain again up to the true next free number.