Using UUID as ID in MySQL
Thanks MySQL!
The MySQL Blog Archive and the MySQL documentation give us the way to use UUIDs as the primary key or id with relative ease.
The blog shows in full detail why use UUIDs, so here we are focusing on how.
The how
We are going to use plain SQL and then Hibernate / JPA on this post.
SQL
First, we need to create the table with the UUID column. Notice how the UUID is used as a default value.
CREATE TABLE uuid_table (
uuid_as_id binary(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID())),
some_value varchar(36) DEFAULT NULL,
PRIMARY KEY (uuid_as_id),
UNIQUE KEY uuid_as_id_UNIQUE (uuid_as_id)
) ENGINE=InnoDB;
Now when we insert a new record and then a select, assuming we are using MySQL Workbench, we can see a BLOB label in the uuid_as_id column, and a value like 0x550E8400E29B41D4A716446655440000 if using the MySQL client command line.
INSERT INTO uuid_table (some_value) VALUES ('Some Value');
SELECT * FROM uuid_table;
So, to get the 550e8400-e29b-41d4-a716-446655440000-like value we have to use BIN_TO_UUID:
SELECT BIN_TO_UUID(uuid_as_id) FROM uuid_table;
But what if we already have a UUID? We can't directly insert it like a string/varchar, but we have UUID_TO_BIN. Notice that is the same as the default value on the CREATE TABLE command.
INSERT INTO uuid_table (uuid_as_id, some_value)
VALUES (UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000'), '550e8400-e29b-41d4-a716-446655440000');
Now we want to select a record by its id, we are using UUID_TO_BIN again.
SELECT BIN_TO_UUID(uuid_as_id), some_value FROM
uuid_table WHERE uuid_as_id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');
/*
* OR
*/
SELECT BIN_TO_UUID(uuid_as_id), some_value FROM
uuid_table WHERE uuid_as_id = 0x550E8400E29B41D4A716446655440000;
And last but not least, we can see the UUID length in bytes:
SELECT LENGTH('550e8400-e29b-41d4-a716-446655440000'),
LENGTH(UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000'));
Java
Now, for Java we can use plain old JDBC and native SQL so there is not going to be that much difference with what we have done earlier. Having said that, there is JPA and Hibernate.
Maven
Dependencies and targeting Java 11.
<!-- ... -->
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>11</maven.compiler.source>
<maven.compiler.target>11</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.6.5.Final</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
<scope>provided</scope>
</dependency>
</dependencies>
<!-- ... -->
persistence.xml
Location of persistence.xml file: src/main/resources/META-INF/persistence.xml
<persistence version="2.2"
xmlns="http://xmlns.jcp.org/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="uuid-sql-jpa-pun">
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL8Dialect" />
<property name="javax.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver" />
<property name="javax.persistence.jdbc.user" value="root" />
<property name="javax.persistence.jdbc.password" value="root_password" />
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/uuid_sql_jpa" />
<property name="hibernate.hbm2ddl.auto" value="none" />
<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="true"/>
</properties>
</persistence-unit>
</persistence>
The Entity
Using @Id @GeneratedValue (strategy = GenerationType.AUTO) to let JPA / Hibernate know that the id will come from MySQL.
package dev.ralphdeving;
import java.util.UUID;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table (name = "uuid_table")
public class UUIDEntity {
@Id
@GeneratedValue (strategy = GenerationType.AUTO)
@Column (name = "uuid_as_id")
private UUID uuidAsId;
@Column (name = "some_value")
private String someValue;
}
The Main
Notice when we use entityManager.persist and entityManager.merge.
package dev.ralphdeving;
import java.util.List;
import java.util.UUID;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
public class App {
public static void main (String[] args) {
EntityManagerFactory entityManagerFactory = null;
EntityManager entityManager = null;
EntityTransaction entityTransaction = null;
try {
entityManagerFactory = Persistence.createEntityManagerFactory("uuid-sql-jpa-pun");
entityManager = entityManagerFactory.createEntityManager();
entityTransaction = entityManager.getTransaction();
entityTransaction.begin();
// Letting MySQL handle the UUID
UUIDEntity uuidEntityAuto = new UUIDEntity();
uuidEntityAuto.setSomeValue("Auto");
entityManager.persist(uuidEntityAuto);
System.out.println("UUID From MySQL: " + uuidEntityAuto.getUuidAsId().toString());
// Inserting UUID manually
UUIDEntity uuidEntityManual = new UUIDEntity();
uuidEntityManual.setUuidAsId(UUID.randomUUID());
uuidEntityManual.setSomeValue("Manual");
entityManager.merge(uuidEntityManual);
entityTransaction.commit();
TypedQuery<UUIDEntity> query = entityManager
.createQuery("SELECT uu FROM UUIDEntity uu",
UUIDEntity.class);
List<UUIDEntity> queryResultList = query.getResultList();
for (UUIDEntity uu : queryResultList) {
System.out.println(uu);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (entityManager != null) {
entityManager.close();
}
if (entityManagerFactory != null) {
entityManagerFactory.close();
}
}
}
}