RE: [Springframework-developer] Petclinic Hibernate implementation

[<jue...@we...>]

Thomas, Trevor,

> Does Hibernate use a different ID strategy for different databases?  I =
think
what is implemented in the Petclinic is a strategy that would work the =
same
across different databses as long as there is a MaxValueIncrementer =
implementation.

I intend to configure Hibernate to use "identity" for all databases. =
Setting the correct Hibernate dialect will lead to "auto_increment" on =
MySQL and "identity" on HSQL then. In terms of the data model, this =
simply means dropping the sequence tables and defining the id columns as =
"auto_increment" respectively "identity" in the DDL scripts.

Sure, MaxValueIncrementer adopts a very generic approach that will work =
on any database, even if it doesn't support such a thing like identity =
columns. The disadvantage is that the mechanism is normally not native =
to the database and thus somewhat tied to the application.

> If we change the Petclinic JDBC implementation to use identity =
columns, then we
need to figure out how to run the insert and subsequent query to =
retrieve the id
using the same connection whether we are in a transaction or not.

This is very similar with MySQL and HSQL: You simply run the insert =
without specifying a value for the id field, and query "select =
last_insert_id()" respectively "call identity()" afterwards (I've looked =
up the latter in Hibernate's MySQLDialect and HSQLDialect =
implementations). Petclinic already uses MySQLJdbcClinic and =
HSQLJdbcClinic subclasses; it should be easy to encapsulate the =
id-fetching query there.

Of course, the range of possible Petclinic JDBC implementations will =
then be bound to identity-supporting databases. According to the =
Hibernate docs, those are "DB2, MySQL, MS SQL Server, Sybase and =
HypersonicSQL". Oracle is notably absent; I think we can live with =
Petclinic not running on Oracle, if we can allow for a dynamic switch =
between JDBC and Hibernate on both MySQL and HSQL that way!

>>auto-increment just allows to read in the actual id afterwards
>This is not reliable for concurrent inserts (which is why I generally =
use sequences.  For the sample this should be sufficient though.

According to what I found out about MySQL, "last_insert_id" returns the =
last used id on the same connection and is thus transactionally safe. =
Don't know about HSQL, but I frankly don't mind for sample purposes.

Juergen