How do I query UUIDs stored as binary in a database (JPA/Hibernate/MySQL)

Question

I have a Java/JPA/Hibernate/MySQL based app. I want to use UUIDs for object identity, however I want to ensure database performance does not suffer.

I found this great blog posting JPA and UUID Primary Keys which gets me some of the way there. Notice how the storage of the UUID is optimized by storing it in binary form (versus the string representation.

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

However, now I have an issue when I want to query from the database using EntityManager.createQuery. Is it possible/desirable to query against binary data? or, should I store the String UUID along-side the binary version to facilitate querying?

Answer 1

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

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

...and query with a UUID instance:

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