Hibernate can't insert UUID to VARCHAR column

Question

I am working on setting up a MySQL database and using Hibernate and Play Framework for the backend. I am having problems with the IDs of entries. I defined my id column as VARCHAR(36):

CREATE TABLE `logaritmical`.`users` (
   `id` VARCHAR(36) NOT NULL,
   `username` TEXT NOT NULL,
    `email` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `id_UNIQUE` (`id` ASC)
);

Now, the @Entity class is like this:

@Entity
@Table(name = "users")
public class UserDO {

   @Id
   @GeneratedValue(generator = "uuid2")
   @GenericGenerator(name = "uuid2", strategy = "uuid2")
   @Column(name = "id", updatable = false, nullable = false, columnDefinition = "VARCHAR(36)")
   private UUID id;
    
   @Column
   private String username;
    
   @Column
   private String email;
}

When doing the insert, I get the following error: Incorrect string value: '\xEF\xA5!\x89\xF3K...' for column 'id' at row 1

If I change the column type and columnDefinition to BINARY(16) the insert works, but it has the disadvantage that the ID is not human-readable when doing selects.

Additional info: persistence.xml looks like this:

<persistence-unit name="defaultPersistenceUnit" transaction-type="RESOURCE_LOCAL">
   <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
   <non-jta-data-source>DefaultDS</non-jta-data-source>
   <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL8Dialect"/>
   </properties>
</persistence-unit>

DB Configuration and versions of libraries looks like this: jpa.default=defaultPersistenceUnit "org.hibernate" % "hibernate-entitymanager" % "5.4.24.Final", "mysql" % "mysql-connector-java" % "8.0.22",

What can be done to have the UUID working with VARCHAR? Is there something I am missing?

Answer 1

Try to use the following definition:

import org.hibernate.annotations.Type;

@Entity
@Table(name = "users")
public class UserDO {
    @Id
    @GeneratedValue(generator = "uuid2")
    @GenericGenerator(name = "uuid2", strategy = "uuid2")
    @Column(name = "id", updatable = false, nullable = false, columnDefinition = "VARCHAR(36)")
    @Type(type = "uuid-char")
    private UUID id;

    // ...
}

It looks like that for your dialect UUID by default mapped to the uuid-binary basic type.

P.S. Please note that saving UUID PK as a string can lead to the performance issue, as it explained in this article:

Aside from the 9x cost in size (36 vs. 4 bytes for an int), strings don’t sort as fast as numbers because they rely on collation rules.