Search code examples
databaseormjpadatabase-agnostic

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

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.

Solution

  • 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