Mapping Java [] Byte to MySQL Binary (64) in Hibernate

Question

I was having trouble mapping a byte array to a MySQL database in Hibernate, and I was wondering if I was missing something obvious. My class looks something like this:

public class Foo { private byte[] bar; // Getter and setter for 'bar' } 

A table is defined in MySQL 5.5 as follows:

 CREATE TABLE foo ( bar BINARY(64) NOT NULL) 

And the Hibernate 3.6.2 mapping looks something like this:

 <hibernate-mapping> <class name="example.Foo" table="foo"> <property name="bar" column="bar" type="binary" /> </class> </hibernate-mapping> 

I use hbm2ddl only for verification, and it gives me this error when deploying the application:

 Wrong column type in foo for column bar. Found: binary, expected: tinyblob 

If using type = "binary" in the mapping does not cause Hibernate to expect the column type to be binary (instead of tinyblob), I don't know what it would do. I spent some time at Google, but could not find the exact error. Solutions for such errors were ...

• Specify "length" in the <property>. This changes the type of the expected Hibernate type, but it always has several varieties of blob instead of the binary type found.
• Instead of declaring a “type” in the property element, insert a column element and assign it an attribute of type sql. This work, but it will also make a MySQL-specific binding, so I would like to avoid it if possible.

Is there anything other than this setting that might cause this mismatch? If I specify type = "binary" instead of "blob", why does Hibernate expect blob instead of binary?

Answer 1

I believe the problem is type="binary" .

This type is a sleeping, general type. It is not directly tied to specific types of DB-engine. They translate to different types of SQL based on the driver you are using. Apparently, the MySQL driver maps the hibernation type to binary with tinyblob.

A complete list of hibernation types is available here .

You have 2 options. You can modify the CREATE TABLE script to save this column with the tinyblob data type. Then checking your hibernation will not fail, and your application will work. This will be the proposed solution.

The second option should be used only if you want to use the BINARY data type in the database. What you can do is specify the sql type in the hibernate mapping so that you use hibernate mode to use the type you want. The display will look like this:

 <property name="bar"> <column name="bar" sql-type="binary" /> </property> 

The main downside of this is that you lose independence from DB rendering, so most people use hibernation first. This code will only work with database engines that are of the BINARY data type.

Answer 2

What we decided to do to solve a similar problem was to write our own custom type.

UserTypes are relatively easy to implement. Just create a class that implements org.hibernate.usertype.UserType and implements @override methods.

in your sleep definitions, using a user type is pretty simple:

 <property name="data" type="com.yourpackage.hibernate.CustomBinaryStreamUserType" column="binary_data" /> 

Simply put, what this will do is execute this class to read and write data from the database. In particular, the nullSafeGet and nullSafeSet methods are used.

In our case, we used this to compress gzip binary data before writing it to the database, and unpack it as a read. This hides the fact that data is compressed from the application using that data.

Answer 3

I think there is a simple solution for displaying binary columns in sleep mode.

The "BINARY" columns can be easily mapped to the "java.util.UUID" in hibernation entity classes.

For example, a column definition would look like

 `tokenValue` BINARY(16) NOT NULL 

Hibernate Entitiy will have the code below to support the BINARY column

 private UUID tokenValue; @Column(columnDefinition = "BINARY(16)", length = 16) public UUID getTokenValue() { return this.tokenValue; } public void setTokenValue(UUID sessionTokenValue) { this.tokenValue = tokenValue; }