Can we generate primary keys automatically without JDBC queries or calls?

Question

I am trying to generate alpha-numberic (e.g. TESLA1001) primary keys automatically in Hibernate. I am currently using Oracle database, so I have a JDBC call to my_sequence.NEXTVAL (1002) to increment number and append to the prefix (TESLA).

We are considering MySQL as an option, but they do not support sequences. So I am forced to re-write the Custom ID generation technique using JDBC call to a stored procedure.

Is there any way I can have a generic implementation to generate custom primary keys without the use of JDBC and database dependent queries? So, in future, if I need to test my application with MSSQL, I need to change my hiberate configuration only and things work fine!

Answer 1

Because you need a way to coordinate the sequence number, you'll always have to use a centralized sequence generator. An alpha-numerical primary key will perform worse on indexing than a UUID generator.

If I were you, I'd switch to UUID identifers which are both unique and portable across all major RDBMS.