JPA and compatibility with persistance providers and databases vendors

Question

JPA promises to be vendor neutral for persistence and database. But I already know than some persistence frameworks like hibernate are not perfect (character encoding, null comparison) and you need to adapt your schema for each database. Because there is two layers (the persistence framework and database), I would imagine they're some work to use some JPA codes...

Does anyone has some experiences with multiple support and if yes, what are the tricks and recommendations to avoid such incompatibilities ?

Answer 1

On the JPA level, the only thing you can do is use the JPA API of the currently used persistence library (i.e. when using hibernate, don't use Hibernate.initialize()).

On the DB level, your best bet is to keep things simple as chances of differences in behaviour grow as you move away from the most frequent use cases. Specifically, this might mean not using composite primary keys, not storing binary data, not using SQL execution at all...I'm sure others will have more good examples of practices which will allow you to move from one database to another easily.

The above made it possible for me to switch one application between PostgreSQL and Oracle and another between PostgreSQL and a few "dialects" (in hibernate lingo) of MySQL.