how shall I define sequences in my project (which

Question

We have a java project that uses Hibernate as the OR Mapper. We need the project to work with both Oracle and Mysql. because some customers don't want to install Oracle. we have added @sequenceGenerator annotation on entities to have sequence. but the project can not connect to Mysql DBMS with these annotations. Is there any way that we can have sequences in both mysql and Oracle mode, without any changes?

Answer 1

in accordance to my knowledge it is not possible because

MySQL support AUTO_INCREMENT and Oracle support SEQUENCE

and there are many other factors to consider like data types and indexes

As Oracle is an ORDBMS where as mysql is DBMS it is lack of many features present in MySQL

SOLUTION

i think we can do this with separate databases for each of them

[ As we have many free tools to convert the data from Oracle to MySQL we can use them. Even some of them are not free but we can covert the structure usnig them link1 link2]

database migration needs a full understanding of the data types,indexes,column and table options,etc of both source and destination databases

or

you can use PostgreSQl which also supports SEQUENCES and it is also a GPL

Answer 2

Most portable way is to use TableGenerator. It is portable because no database provider specific constructs are used. Last generated value is stored to the database table, and value is retrieved and incremented via SQL queries.

Usage is also covered in Hibernate documentation.