Different representation of UUID in Java Hibernate and SQL Server

Question

I am trying to map a UUID column in POJO to SQL Server table column using Hibernate.

The annotations are applied as follows:

@Id
@GeneratedValue
@Column(name = "Id", columnDefinition = "uniqueidentifier") 
public UUID getId(){ ... }

However, it seems that there is some endianness problem between the Java Hibernate mapping and SQL server.

For example, in my Java app, I have ids represented as:

4375CF8E-DEF5-43F6-92F3-074D34A4CE35
ADE3DAF8-A62B-4CE2-9D8C-B4E4A54E3DA1

whereas in SQL Server, these are represented as:

8ECF7543-F5DE-F643-92F3-074D34A4CE35
F8DAE3AD-2BA6-E24C-9D8C-B4E4A54E3DA1

Is there any way to have same representation at both sides?

Please note that uniqueidentifier is used only to have a uniqueidentifier typed id in SQL server instead of type binary; the same problem exists when uniqueidentifier is removed from annotation (the problem can be observed by converting binary is to uniqueidentifier).

is related to http://stackoverflow.com/questions/5745512/how-to-read-a-net-guid-into-a-java-uuid — Łukasz Rzeszotarski, Feb 27 '17 at 13:27
is related to http://stackoverflow.com/questions/22402929/uuid-madness-with-mssql — Łukasz Rzeszotarski, Feb 27 '17 at 13:31
How to convert from one pf above format to another using sql script? — Vikash, Oct 07 '21 at 06:21

Markus Ratzer · Answer 1 · 2020-11-25T13:02:23.910

40

You need to specify the @Type(type = "uuid-char") in addition to @Column(name="...", columnDefinition = "uniqueidentifier"), see also Problems mapping UUID in JPA/hibernate .

Alternatively you can use a String field for the id in Java and still keep uniqueidentifier in SQL Server.

edited Nov 25 '20 at 13:02

answered Feb 27 '17 at 12:15

Markus Ratzer

1,292
3
19
29

Originally I thought this wasn't correct, but have confirmed it works, if you use @Column(columnDefinition = "uniqueidentifier") @Type(type = "uuid-char") It ensures that the column type is created as "uniqueidentifier", but when reading/writing to the DB the UUID order is as expected (I.e. when you view the field in SSMS, it looks the same as when you use .toString() on the UUID). – Dylan Nicholson Oct 23 '18 at 01:21

Nico Van Belle · Answer 2 · 2017-02-27T13:43:58.447

6

Microsoft databases use GUIDs. It is Microsoft's implementation of the UUID standard.

This being said, you should use the guid generator.

@Id
@GenericGenerator(name = "generator", strategy = "guid", parameters = {})
@GeneratedValue(generator = "generator")
public String getId() {
    return id;
}

guid uses a database-generated GUID string on MS SQL Server and MySQL.

Also, have you set SQLServer2012Dialect? This also might solve some future issues.

edited Feb 27 '17 at 13:43

answered Feb 27 '17 at 13:33

Nico Van Belle

4,911
4
32
49

1

I tried the solution but it didn't work, My database has One GUID but when I fetch the data from the table, it generates different UUID. Any sort of help is appreciated. – agaonsindhe Jun 11 '20 at 12:55

Łukasz Rzeszotarski · Answer 3 · 2017-02-27T13:42:03.490

3

With SQL Server you should use guid strategy for your generator:

@GeneratedValue(generator = "my-uid")
@GenericGenerator(name = "my-uid", strategy = "guid")
@Id
private UUID uuid;

https://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/mapping.html

Java is using UUID generator in version 4 how can you see here:

4375CF8E-DEF5-43F6-92F3-074D34A4CE35

ADE3DAF8-A62B-4CE2-9D8C-B4E4A54E3DA1

edited Feb 27 '17 at 13:42

answered Feb 27 '17 at 11:16

Łukasz Rzeszotarski

5,791
6
37
68

Different representation of UUID in Java Hibernate and SQL Server

3 Answers3

Linked