Auto increment IDs are not working well when it comes to distributed databases. Instead, we should use UUIDs. Let’s consider the pros and cons of UUIDs and how we can use them with Hibernate and MySQL.

Introduction to UUIDs

Pros

Universally Unique IDentifiers (UUIDs) are unique across every database – globally!

This leads to the following advantages:

• Easy merging of entries from different databases. No conflicting primary keys anymore.
• Easy replication and synchronization of distributed databases.
• We can generate UUIDs everywhere. So there is no round trip to the database necessary, because we can generate UUIDs in the application layer. This also simplifies tests and allows easy batch inserts of entities referencing each other.
• Auto incremental IDs are guessable, which can lead to security issues (e.g. playing around with the ID parameter in the URL and scrape all content).

Cons

However, there are also some disadvantages:

• UUIDs increase the required size for the value and the index. A single UUID needs 16 bytes. Contrarily, a normal int key only needs 4 bytes.
• They make ad-hoc queries more clumsy (see below).
• UUIDs in REST resources increase the payload size.

Create a Table

It’s not a good idea to use VARCHAR(36) as a column type for UUIDs. Instead, we should use a BINARY(16) column. This minimizes the required value size (less bytes, no dashes) and index size. However, this makes queries a little bit more complicated.

CREATE TABLE product (
   `id` BINARY(16) NOT NULL primary key
   ,`name` varchar(64)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Hibernate Integration

Hibernate supports java.util.UUID and UUID generation. Consequently, Hibernate itself generates the UUID on the client-side. Just add the following annotations to your entity class:

@Id
@GeneratedValue(generator = "uuid2")
@GenericGenerator(name = "uuid2", strategy = "uuid2")
@Column(columnDefinition = "BINARY(16)")
private UUID id;

Useful SQL Snippets

With uuid() we can generate UUIDs.

SELECT uuid(); /*dbe07414-49d1-11e6-b7a7-0242ac140002*/

Inserting

Please mind, that uuid() returns a UUID with dashes. We have to remove them with replace() in order to insert an entry. Moreover, we have to use unhex() which converts a hexadecimal (human-readable) UUID to its binary representation. Now it fits into a BINARY(16) column.

INSERT INTO product VALUES(
   unhex(replace(uuid(), '-', ''))
   , "car"
);

Reading

Use hex() to convert the UUID bytes back to their hexadecimal representation.

SELECT id, name FROM product;
/* BLOB, 'car' */
SELECT hex(id), name FROM product;
/* 'BFF641BA9F3A4584A1BA53824E7AB3B9', 'car' */

Let’s query for a certain UUID using unhex().

SELECT hex(id), name FROM product
   WHERE id = unhex('BFF641BA9F3A4584A1BA53824E7AB3B9');

/* or if you have a UUID with dashes: */
SELECT hex(id), name FROM product
   WHERE id = unhex(replace("2b08e375-275d-473e-910d-32700e34b61a", '-', ''));

Demo

On GitHub I created a simple Spring Boot + Hibernate/JPA project to demonstrate UUIDs in action.