How can I request UUIDs stored as binary in the database (JPA / Hibernate / MySQL)

Question

I have a Java / JPA / Hibernate / MySQL application. I want to use the UUID to identify the object, however I want the database performance not to suffer.

I found this wonderful JPA blog post and the primary UUID keys that bothered me. Notice how the UUID memory is optimized by storing it in binary form (compared to the string representation.

It solves part of the problem, because now we can effectively insert objects into the database.

However, now I have a problem when I want to query the database using EntityManager.createQuery. Is it possible / desirable to request binary data? or, should the StUU UUID be kept next to the binary version to facilitate requests?

Answer 1

Tested with Hibernate 4.1.2 and MySQL-Connector-J 5.1.18, you can define the UUID field:

@Entity class EntityType { @Column( columnDefinition = "BINARY(16)", length = 16 ) private UUID id; } 

... and the request with the UUID instance:

 UUID id = ....; EntityType result = em.createQuery( "SELECT x FROM EntityType x WHERE x.id = ?1″, EntityType.class ) .setParameter( 1, id ).getSingleResult(); 

Answer 2

As long as you already have the ID in binary format, the request is simple:

 byte[] id = ....; em.createQuery("SELECT x FROM TableName x WHERE x.id = ?1″, TableName.class).setParameter(1, id).getSingleResult(); 

In fact, if you are just looking at the primary key, you can use

 em.find(TableName.class, id); 

Getting the identifier in binary format can be a little painful, especially if you need to pass it to URLs, etc. I recommend encoding / decoding Base64; Apache Commons Codec has helper methods for moving from bytes [] to a string protected by a URL and then back to bytes []

Answer 3

The load of 16 bytes per 1 billion records is approximately 15 GB. If you have so much of the data, you will have more serious scalability issues, and those 15Gb at 10 cents / GB or less won't really be a big problem. Many of the many relationships can grow to this size faster, but still have nothing to worry about.

To summarize, just go to the string representation. This will save you a lot of effort when working with the database at a fairly low cost.

PS My personal preference is to use numeric identifiers, but this is a separate discussion.