JPA entity compatibility for both SQL Server and Oracle (auto-increment column issues)

Question

I'm developing a Java EE JBoss service which will be deployed in two different environments: one using SQL Server as the database, and a different one which uses Oracle 10g.

My database schemas are very similar, except that the primary key columns are identity-auto-increment in SQL Server and generated using sequences in Oracle.

With JPA, is it possible to use a single set of entities with both DB's?

Thanks!

Answer 1

Of course you can, but just specify the generated-value definition for the PK field in XML metadata rather than annotations. Use 2 different "orm.xml" files, so, for example, use "persistenceUnitOracle" for Oracle that references orm-oracle.xml, and "persistenceUnitSqlServer" for SQLServer that references orm-sqlserver.xml