Java UUID persisting in H2 and Sql Server with Spring JPA

Question

Say I need persist some data in the database and one column is request_id which is a random uuid created by my application.

Firstly, What should be the type I use in my DDL create statements for H2 and SQL Server database? I am using H2 for local and sql server for production.

CREATE TABLE REQUESTS_DETAILS
(
    REQUEST_ID --what should be the type for H2 and SQLServer? 
    NAME NVARCHAR(20)   NOT NULL,
);

Secondly, What would be the corresponding java type when using Spring JPA Entity?

With H2 (and Postgres), it's `UUID`. With SQL Server, I think it's `uniqueidentifier`. — chrylis -cautiouslyoptimistic-, Jul 06 '20 at 17:46
@chrylis-cautiouslyoptimistic- would this work with JPA? What would be the type on JPA Entity to work with the differences between the dbs — M06H, Jul 06 '20 at 17:53
Just use `java.util.UUID`. Hibernate, at least, maps it to native database types with no problem. — chrylis -cautiouslyoptimistic-, Jul 06 '20 at 19:20
@chrylis-cautiouslyoptimistic- Actually it doesn't. Hibernate seems to inconsistently map java.util.UUID on H2 to UUID when it is a direct entity attribute, but BINARY when it is an embedded attribute. — Dave, Nov 09 '21 at 01:57

score 1 · Answer 1 · answered Jul 06 '20 at 21:48

I have experienced going through all this for days recently. Take a look here and here.

Is this uuid going to be your Primary Key?

For the data type, it depends what is the business need for the data type of uuid. I would recommend you read this at least once.

To summarize and as per what I understood so far, use data type as String for your uuid and go for uuid4. If you are planning for Spring JPA/Hibernate, go for Long type for Primary/Foreign keys. That way you can easily maintain your keys and can index them easily rather than messing around indexing String type uuids.

And if you are looking for a unique identifier (as I was in my case) like uuid, go for a separate field in your entity of String type and generate uuid4 data type and treat it as a String. String is suggested as you can always port them from one DB to another without any hassle. Always consider the fact of migrating your data between different DBs (MSSQL/Oracle/DB2/Postgres etc).

Now, coming back to using UUID, you can always do:

@NotEmpty
@Column(name = "useruuid")
@GenericGenerator(name = "uuid", strategy = "uuid4")
private String useruuid;

and to generate UUIDs you can simply use :

UUID.randomUUID()

or

UUID.randomUUID().toString();

Take a look at here and here for sample project which I recently posted.

Java UUID persisting in H2 and Sql Server with Spring JPA

1 Answers1