How to create Id on hibernate entity that is a byte[] that maps to a varbinary value in mysql

Question

I am trying to create an Entity that has a byte[12] id in hibernate. It seems like it does not like to have a byte[] as a primary key and as another column it sets it up as a tinyblob in the backing mysql db. I have tried to create a String, but the problem is that the string in java is 2 bytes per char, while it is one byte per char in mysql. I'm looking for something like this

@Entity
public class TestClass {
    @Id
    @Column(length=12)
    private byte[] id;
...

to map to a mysql table like this

Table 'testclass'
   Column id - varbinary     length 12     primary key
    ...

I have tried a number of different ways (primarily trying to fiddle with Strings) to do this but it does not seem to work right. Has anyone been able to do this already? Thanks.

Answer 1

As you experienced, this is not supported by standard JPA. From the specification:

2.1.4 Primary Keys and Entity Identity

...

The primary key (or field or property of a composite primary key) should be one of the following types: any Java primitive type; any primitive wrapper type; java.lang.String; java.util.Date; java.sql.Date. In general, however, approximate numeric types (e.g., floating point types) should never be used in primary keys. Entities whose primary keys use types other than these will not be portable. If generated primary keys are used, only integral types will be portable. If java.util.Date is used as a primary key field or property, the temporal type should be specified as DATE.

That being said, it appears that you could use a wrapper type around the byte[] and implement a custom user type. The UserType for a byte[] identifier property wiki page provides an implementation.

Refer to the section 2.4.3.2. Type of the Hibernate Annotations documentation to declare and use this custom type with annotations.

References

• JPA 1.0 Specification
  • Section 2.1.4 "Primary Keys and Entity Identity"
• Hibernate Annotations Reference Guide
  • 2.4.3.2. Type

Resource

• JBoss Community Wiki
  • UserType for a byte[] identifier property

Answer 2

byte[] is not supported as primary key for a reason - using a BLOB field for a primary key isn't a good idea (mysql will have to index blobs, I'm not sure this will have good performance). I'm not even sure mysql allows blob keys. There is an error that says BLOB/TEXT can't be used in key specifications.

What you can use is:

• UUID - it is 128 bit unique identifier
• Base64 - (using commons-codec) encode your byte array to base64 and use a string primary key.