如何查询以二进制形式存储在数据库中的UUID(JPA/Hibernate/MySQL) [英] How do I query UUIDs stored as binary in a database (JPA/Hibernate/MySQL)

问题描述

我有一个基于Java/JPA/Hibernate/MySQL的应用程序.我想使用UUID进行对象标识，但是我想确保数据库性能不会受到影响.

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.

我发现了这个很棒的博客，发布了 JPA和UUID主键，这让我了解了一些方法.请注意，如何通过以二进制形式存储UUID(而不是字符串表示形式)来优化UUID的存储.

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.

但是，现在我想使用EntityManager.createQuery从数据库中查询时遇到问题.是否可以/希望查询二进制数据?还是应该在二进制版本的旁边存储字符串UUID，以方便查询?

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?

推荐答案

在Hibernate 4.1.2和MySQL-Connector-J 5.1.18中进行了测试，您可以定义UUID字段:

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;
}

...并使用UUID实例进行查询:

...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();

这篇关于如何查询以二进制形式存储在数据库中的UUID(JPA/Hibernate/MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！