How to save a UUID as binary(16) in java

Question

I have a table TestTable with columns ID as binary(16) and name as varchar(50)

I've been trying to store an ordered UUID as PK like in this article Store UUID in an optimized way

I see the UUID is saved in database as HEX (blob)

So I want to save this ID from java but I am getting this error

Data truncation: Data too long for column 'ID' at row 1

I am currently using the library sql2o to interact with mysql

So basically this is my code

String suuid = UUID.randomUUID().toString();
String partial_id = suuid.substring(14,18) + suuid.substring(9, 13) + suuid.substring(0, 8) + suuid.substring(19, 23) + suuid.substring(24)
String final_id = String.format("%040x", new BigInteger(1, partial_id.getBytes()));
con.createQuery("INSERT INTO TestTable(ID, Name) VALUES(:id, :name)")
        .addParameter("id", final_id)
        .addParameter("name", "test1").executeUpdate();

The partial id should be something like this 11d8eebc58e0a7d796690800200c9a66

I tried this statement in mysql without issue

insert into testtable(id, name) values(UNHEX(CONCAT(SUBSTR(uuid(), 15, 4),SUBSTR(uuid(), 10, 4),SUBSTR(uuid(), 1, 8),SUBSTR(uuid(), 20, 4),SUBSTR(uuid(), 25))), 'Test2');

But I got the same error when I remove the unhex function. So how can I send the correct ID from Java to mysql?

UPDATE

I solved my problem inspired on the answer of David Ehrmann. But in my case I used the HexUtils from tomcat to transform my sorted UUID string into bytes[]:

byte[] final_id = HexUtils.fromHexString(partial_id);

The immediate cause of the error is that `final_id` is 64 characters wide, but the column only holds 16 bits. — Tim Biegeleisen, Oct 28 '16 at 03:36
**NOTE**: The approach mentioned here **cannot be used with Java** as the link is about MySQL-UUID (UUID version 1) which isn't the same as Java `UUID.randomUUID()` (produces UUID version 4). Version 1 is timestamp based which is the reason for reordering (and possibly truncating the MAC address). It needs a quite precise lookup of the time (nanoseconds). Version 4 is a completely random number, where **reordering has no use at all** and you should not snip parts, see https://en.wikipedia.org/wiki/Universally_unique_identifier. Storing type 4 as `byte[16]` stays a reasonable approach. — Simon Sobisch, Sep 21 '17 at 11:48

David Ehrmann · Accepted Answer · 2016-10-28T04:53:03.247

13

Try storing it as bytes:

UUID uuid = UUID.randomUUID();
byte[] uuidBytes = new byte[16];
ByteBuffer.wrap(uuidBytes)
        .order(ByteOrder.BIG_ENDIAN)
        .putLong(uuid.getMostSignificantBits())
        .putLong(uuid.getLeastSignificantBits());

con.createQuery("INSERT INTO TestTable(ID, Name) VALUES(:id, :name)")
    .addParameter("id", uuidBytes)
    .addParameter("name", "test1").executeUpdate();

A bit of an explanation: your table is using BINARY(16), so serializing UUID as its raw bytes is a really straightforward approach. UUIDs are essentially 128-bit ints with a few reserved bits, so this code writes it out as a big-endian 128-bit int. The ByteBuffer is just an easy way to turn two longs into a byte array.

Now in practice, all the conversion effort and headaches won't be worth the 20 bytes you save per row.

edited Oct 28 '16 at 04:53

answered Oct 28 '16 at 03:35

David Ehrmann

7,366
2
31
40

Thanks for your answer. Very appreciated. Can you explain a bit your code? The code of the article suggests to rearrange the UUID removing the hyphens and sorting the segments as let's say ID = 1d8eebc58e0a7d7, the ByteBuffer should have the same idea? – Maximus Decimus Oct 28 '16 at 03:53
I successfully saved the record but I did this query mysql: select hex(id), length(hex(id)), name from testtable; and I got this as ID "626E316000774FE1A3E14BBD99E74EB0" So that's not the format that I was expecting as in the question. It should be something like this 1d8eebc58e0a7d7 – Maximus Decimus Oct 28 '16 at 04:02
@MaximusDecimus 16 bytes is 32 hex digits, so why would you expect `1d8eebc58e0a7d7`, which is only 15 digits? – Andreas Oct 28 '16 at 04:14
Sorry I copied the value wrongly. 11d8eebc58e0a7d796690800200c9a66 – Maximus Decimus Oct 28 '16 at 04:18
1

And the other way around: `ByteBuffer buf = ByteBuffer.wrap(uuidBytes); UUID uuid = new UUID(buf.getLong(), buf.getLong())`. – Ruslan Stelmachenko Aug 20 '20 at 17:18

How to save a UUID as binary(16) in java

1 Answers1

Linked