0

I am working on an application that currently supports MSSQL and MYSQL and now we need to make it compatible with Oracle and Postgresql too. We have a strong dependency on the Id generated for the records as we use the same Id to generate some logical identifiers for the records.Therefore we want each of the tables to have it's own sequence such that each new record has a new logical identifier which is in the sequence n,n+1,n+2 and the logical identifiers will therefore be XYn,XY(n+1),XY(n+2).

Using the generation strategy AUTO for the Id worked with both MSSQL and MYSQL but doesn't work with Oracle or Postgresql as the generation strategy AUTO ultimately maps to SEQUENCE for these databases. I need to maintain a common code for all these databases and cannot afford to make any database changes. Please suggest some ways to get this done.

Improve this question
5
  • As I recall it is possible to create sequences in POSTGRE and use it for ID generation in a way suits you. Did you check documentation for @Generated ?? Commented Apr 1, 2015 at 7:43
  • 1
    What's wrong with SEQUENCES? Commented Apr 1, 2015 at 8:16
  • A sequence, from what i know is shared by all the tables in the database. The logical identifiers i am talking about have to maintained for multiple tables, therefore each table needs a sequence of it's own. Commented Apr 1, 2015 at 9:03
  • sequence is independent "object" in Oracle. You can use one SEQUENCE for all tables a or you can create one SEQUENCE for each table. Also each sequence can have different initial value a also different next/step value. You can also use for example only one SEQUENCE for all tables having next = 100 and then simply add entity id to the last two digits of the generated id. Commented Apr 1, 2015 at 9:32
  • Thanx for the Information! It was helpful. But i wonder if using a Generation strategy AUTO with the annotation @SequenceGenerator will work with MYSQL and MSSQL. If it does....problem solved! Commented Apr 1, 2015 at 10:06

2 Answers 2

Reset to default
1

If you want to be portable across every RDBMS, GenerationStrategy=TABLE is a solution. Check for some information in this article

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Upvote because there is no mention of performance limitations and this basically is the only way to make hibernate do all the work for you.
Exactly what i am worried about, keeping a common code is the requirement but the performance is even more important. The performance is the USP of the application, i cannot let the performance suffer just to accommodate another database
Unless you will develop own solution as part of JPA, you will have to deploy appliaction version for each RDBMS, or at least dedicated DAO module that suit your needs.
0

Why don't you use UUID instead of sequence numbers? That is more portable.

In Java there is already an implementation of UUID.

As far as I remember there is no built-in sequence mechanism in Oracle, so you can't use the ID generator from Hibernate.

Improve this answer

1 Comment

The reason we cannot use a UUID for IDs is the dependency that i have mentioned in my question itself. The logical identifiers that are used for each record are generated using the ID's and UUID does not provide an ID that a user can relate to i.e. the ids generated are random.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.