Mapping Java byte[] to MySQL binary(64) in Hibernate

Question

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

public class Foo {
    private byte[] bar;

    // Getter and setter for 'bar'
}

The table is defined like this in MySQL 5.5:

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

And the Hibernate 3.6.2 mapping looks similar to this:

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

I am using hbm2ddl for validation only and it gives me this error when I deploy the application:

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

If using type="binary" in the mapping wouldn't cause Hibernate to expect the column's type to be binary (instead of tinyblob,) I don't know what would. I spent some time Googling this but couldn't find the exact error. The solutions for similar errors were to...

1. Specify "length" on the <property>. That changes what type Hibernate expects but it's always some variety of blob instead of the "binary" type it's finding.
2. Instead of declaring a "type" on the property element, nest a column element and give it a sql-type attribute. That work but that would also make the binding specific to MySQL so I would like to avoid it if possible.

Does anything stand out about this setup that would cause this mismatch? If I specify type="binary" instead of "blob", why is Hibernate expecting a blob instead of a binary?

Gasometer

Binary types have caused me no end of frustration in Java/Hibernate. Typically the way I work around the issue is to Base64 encode the binary data on the Java side, and then store it in the database as a TEXT field. Then the property in your entity can be of type String, and everything works much more smoothly. – Delacroix

Yes, it does seem that byte arrays are troublesome in Hibernate. Is it not a bug that that the mapping is explicitly telling Hibernate to expect binary as the type, yet it's looking for different types of blobs instead? Very frustrating indeed! – Gasometer

This is not explicitly telling Hibernate to use binary. The type attribute of the property element is not used for specifying SQL data types. I explain this in my answer below. I can't comment on how Hibernate behaves when set to use a BINARY data type (properly), I haven't tried it. – Ruder

Since MySQL's binary type seems to be "orphaned" by Hibnerate and workarounds seem to be the only way to map them, I posted this to the Hibernate forums. forum.hibernate.org/viewtopic.php?f=1&t=1011076 I'm kind of hoping this gets considered a bug and fixed in the future. – Gasometer

Answer 1

I believe the problem is type="binary".

That type is a hibernate, generic type. It does not directly map to DB-engine specific types. They are translated to different SQL types based on driver you are using. Apparently the MySQL driver maps the hibernate type "binary" to a tinyblob.

The full list of hibernate types is available here.

You have 2 options. You can change your CREATE TABLE script to store that column with a tinyblob data type. Then your hibernate validation would not fail and your application would work. This would be the suggested solution.

The second option should be used only if you HAVE to use BINARY data type in the DB. What you can do is specify a sql-type in the hibernate mapping so that you enforce hibernate to use the type you want. The mapping would look like this:

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

The main down side to this is you lose DB -engine independence which is why most people use hibernate in the first place. This code will only work on DB engines which have the BINARY data type.

Ruder

You are correct - type="binary" is definitely the problem. I was just (incorrectly) assuming that since there is both a blob Hibernate type and a binary Hibernate type, the "blob" Hibernate type would correspond to the various MySQL blob types and binary would correspond to binary. Unfortunately, there doesn't seem to be any Hibernate type that corresponds to a MySQL binary, which seems like a rather large oversight. – Gasometer

Answer 2

What we ended up doing to solve a problem similar to this is write our own custom UserType.

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

in your hibernate definitions, using a user type is pretty easy:

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

Simply put, What this will do is execute this class for reading and writing the data from the database. Specifically the methods nullSafeGet and nullSafeSet are used.

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

Bourgogne

A UserType is easy enough to write and would certainly work but it doesn't seem any more database-neutral than option 2 from the question - declaring the <property> with a nested <column> with a sql-type attribute. The difference is that using "option 2" is a one-line solution that doesn't introduce more code (opportunity for bugs) to the project. Although I will say that in your example of gzipping data, a UserType seems like the best place for it. I think that's pretty clever. – Gasometer

Answer 3

I think there is an easy solution for mapping Binary columns in hibernate.

"BINARY" columns can be easily mapped to "java.util.UUID" in hibernate entity classes.

For e.g. Column definition will look like

`tokenValue` BINARY(16) NOT NULL

Hibernate Entitiy will have below code to support 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;
}

Granddad

Specifying the column's SQL definition is an option that's already been discussed (including in the question.) – Gasometer