How to use String UUID in Hibernate with MySQL

When creating the database structure it is important to make sure that each row in a table has a unique ID so that it can be easily indexed, retrieved, and manipulated when needed. The most common methods are to use an auto-incremented column or a generated UUID.

I won’t be covering the auto-incremented method since it poses no real problems and can be mapped to an Integer in the Java domain class. When using an UUID, this can pose some tricks, depending on the configuration and MySQL version. The good news is that there is an easy way of mapping the Java UUID column to MySQL using Hibernate and no additional libraries are needed.

First, let’s look at the sample table. I will be using a simplified version of a USERS table that stores login information for each registered user to a site. We will need the following information:

• ID
• Email
• Name
• Password Hash

This is a simplified structure and only has a minimum number of columns. Storing an unsalted password hash is not recommended. Look up best practices of storing passwords in the database when creating the final structure.

We want the ID to be unique so we will be using the Java UUID in our domain object. Because we want all the information to be human readable as well, the ID should be stored as a String. Since we know the format of the UUID when represented as a String, we know that it has a length of 36 characters, so we can define the column as VARCHAR(36).

Now, we create our domain class using Hibernate annotations to map it to our existing MySQL table.

@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;

    @Column(name = "password_hash")
    private String passwordHash;
}

We expect that everything works as intended and that Hibernate knows how to map the UUID to the VARCHAR(36) column, but this is only partially true. When executing the code and trying to do an insert, an java.sql.SQLException will be thrown:

java.sql.SQLException: Incorrect string value: '\xE3\xAF\xF7d\x0CG…' for column 'id' at row 1
         at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
         at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
         at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
         at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
         at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
         at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
         at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
         at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
         at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
         at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
         at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)

This is because Hibernate tries to insert it as Binary data instead of String. There are two options at the moment. First, we can change the column of the ID in the database to be of type BINARY. This will solve the problem and inserting a new user will be successful. However, in doing so, the ID is no longer human-readable and it can be difficult in the future to debug, analyze logs, or manually manipulate the entries.

What we want is to have a String UUID column using Hibernate without the need to do any further manipulation in the code. This is where a new annotation comes into play: @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;

    @Column
    private String username;

    @Column
    private String email;

    @Column(name = "password_hash")
    private String passwordHash;
}

This annotation defines the Hibernate type mapping. Using “uuid-char” instructs Hibernate to store the UUID as a String, instead of the binary value. This way, it can be written to and read from a VARCHAR(36) column without the need for any pre-processing on our side.

Article originally posted on my personal website at (https://petrepopescu.tech/2021/01/how-to-use-string-uuid-in-hibernate-with-mysql/)