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?
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.