What orm.xml features should be avoided to stay database agnostic?

Question

I'm embarking on an adventure in JPA and want to, inasmuch as possible, stay database agnostic. What orm.xml features should I avoid to stay database agnostic?

For example, if I use strategy="AUTO" in orm.xml as follows:

<id name="id">
    <generated-value strategy="AUTO" />
</id>

...then MySQL shows it as an AUTO_INCREMENT column which may (I'm not yet sure) cause problems if I needed to deploy to Oracle.

Answer 1

JPA features

• You can use all JPA features. At worse you will need to change the annotations or orm.xml (e.g. if you want to use a special sequence in oracle), but all features are supported one way or another without impacting the code. That's what's nice with an ORM -- you have an extra-layer of abstraction.

Reserved keywords

• Don't use reserved word to name your tables and columns

Keep close to SQL-92 standard

The way the queries are translated (especially the native ones) is loose. This is great in some case, but can lead to some problems sometimes:

• Don't use AS in native queries
• Never use SELECT * in native queries
• User = for equality and not ==
• Use only the SQL-92 standard functions

Answer 2

In general MySQL "AUTO_INCREMENT" should be used when selecting value generation of "IDENTITY", and on Sybase SERIAL, and on DB2 ... etc. Some RDBMS don't have something equivalent.

Value generation of "AUTO" is for the implementation to choose what is best for that datastore. Yes, on MySQL they may choose AUTO_INCREMENT, and on Sybase SERIAL, and on Oracle SEQUENCE, etc etc, but from the user code point of view that one will (should) work on any spec-compliant implementation. Obviously you cannot then switch JPA implementations and expect it to use the exact same mechanism, since JPA impl #1 may choose AUTO_INCREMENT on MySQL, and JPA impl #2 may choose some internal mechanism etc etc.

Answer 3

I am not familiar with JPA, but in general a reasonable ORM should be database agnostic (for the the major databases) for all of its mappings.

Especially an "AUTO" Increment strategy should work out of the box..

When switching the database, you have to deal with migration issues for the existing data.