Problem obtaining entities with uniqueidentifier primary key

Question

In order to investigate whether Hibernate's behavior is different than NHibernate's for a particular usage scenario, I began writing a Java SE & Hibernate port of the relevant parts of my NHibernate-based application, but encountered a problem with obtaining an entity where the corresponding SQL Server 2008 R2 Express table uses a uniqueidentifier for the primary key.

Here's the mapping file:

<!-- User.hbm.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <class name="hibernatetest.entity.User" table="aspnet_Users">
    <id name="Id">
      <column name="UserId" sql-type="uniqueidentifier" not-null="true" />
    </id>
    <!-- ... -->

And corresponding POJO definition:

// hibernatetest/entity/User.java
package hibernatetest.entity;

import java.util.UUID;

public class User {
    private UUID id;

    public UUID getId() {
        return id;
    }

    public void setId(UUID id) {
        this.id = id;
    }
    //...

And main code:

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
Session session = sessionFactory.openSession();
User currentUser = (User) session.get(User.class, UUID.fromString("473D248D-8D91-41C9-BD73-8ACA45539D79"));

The problem is that the call to Session#get returns null, as it fails to find the user entity for primary key '473D248D-8D91-41C9-BD73-8ACA45539D79'.

When I enable full logging, I see:

DEBUG [main] (AbstractBatcher.java:401) - select user0_.UserId as UserId0_0_ from aspnet_Users user0_ where user0_.UserId=?
Hibernate: select user0_.UserId as UserId0_0_ from aspnet_Users user0_ where user0_.UserId=?
TRACE [main] (NullableType.java:133) - binding '2c6d8085f3f2808eeae1f6e1aef5f4e9ecaed5d5c9c43c19837718ed05af828082ca808cece5e1f3f4d3e9e7c2e9f4f3ca808bedeff3f4d3e9e7c2e9f4f3f8f03df30a4ac5d31df9c7bda40d0d11c149' to parameter: 1

I don't know how Hibernate derived the value '2c6d8085f..., but this is not the value binding that I expected, and it prevents the user entity from being located.

If I change the type of the id field of hibernatetest.entity.User from java.util.UUID to String, then this line succeeds in finding the entity:

User currentUser = (User) session.get(User.class, "473D248D-8D91-41C9-BD73-8ACA45539D79");

And the log output becomes:

DEBUG [main] (AbstractBatcher.java:401) - select user0_.UserId as UserId0_0_ from aspnet_Users user0_ where user0_.UserId=?
Hibernate: select user0_.UserId as UserId0_0_ from aspnet_Users user0_ where user0_.UserId=?
TRACE [main] (NullableType.java:133) - binding '473D248D-8D91-41C9-BD73-8ACA45539D79' to parameter: 1

How did Hibernate calculate the value '2c6d8085f... to bind for the UUID '473D248D-8D91-41C9-BD73-8ACA45539D79'? How can I make it bind the actual UUID value instead?

Pace · Accepted Answer · 2011-01-24T23:33:40.990

The Java UUID (java.util.UUID) and the SQL Server uniqueidentifier are two different things. You are telling Hibernate to map between the two. I'm surprised Hibernate is able to do anything at all, and not surprised that what it does generate is very strange.

As best I know (don't do much work with SQL Server) Hibernate is going to treat the uniqueidentifier as a plain old String column. As such, your best bet would probably be to map it a String. You could still have a getUUID() and setUUID() as...

public UUID getUUID() {
    return UUID.fromString(id);
}

public void setUUID(UUID val) {
    id = val.toString();
}

Just make sure if you're doing property-based mapping that Hibernate knows the UUID field is a transient field and doesn't try to store both the ID and the UUID.

It's possible that SQL server has some kind of special UUID class you could use in Java that can convert to/from the Java UUID. It's also possible that some new version of Hibernate has come out with support for mapping java.util.UUID to SQL Server's UUID but I don't think either of these possibilities are true.

Edit: After looking into the problem further I can see some of your confusion. It appears that NHibernate DOES in fact convert from System.GUID to SQL Server's uniqueidentifier (makes sense since both are Microsoft-releated). However, I don't believe there is a Hibernate equivalent.

score 2 · Answer 2 · answered Aug 15 '14 at 07:02

I had the same problem and I solved it by adding uuid-char type to column.

<!-- User.hbm.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http //www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="hibernatetest.entity.User" table="aspnet_Users">
        <id name="Id" type="uuid-char">
            <column name="UserId" sql-type="uniqueidentifier" not-null="true" />
        </id>
<!-- ... -->

For those of us using annotations `@Type(type = "uuid-char")` — sMaN, Jun 01 '17 at 05:34

Problem obtaining entities with uniqueidentifier primary key

2 Answers2

Linked