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?

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.