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
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).
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;
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
uuid() we can generate UUIDs.
SELECT uuid(); /*dbe07414-49d1-11e6-b7a7-0242ac140002*/
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" );
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
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", '-', ''));
On GitHub I created a simple Spring Boot + Hibernate/JPA project to demonstrate UUIDs in action.