I recently added a new field in an application I'm working on. In MySQL, the column type is binary(64) and in Java, it's a byte[]. When I tried to map the new field in Hibernate, I discovered that Hibernate doesn't seem to have a type that corresponds to MySQL's binary type.

When I try a mapping like this...
...HBM2DDL's validation complains Adding a length attribute to the <property> changed what type of blob Hibernate expected in the database, but it was always some type of blob, not MySQL's actual binary type. I was able to work around this by nesting a <column> with a sql-type attribute, but I'm still wondering...

Since Hibernate has both a "blob" type and a "binary" type, why do both of those map to MySQL blobs, and no Hibernate type seems to map to a MySQL binary? It almost seems like a bug, or at the very least, a very large oversight. Thoughts?

- Brandon

In my database I have a column:

It's mapped to:

As far as I can tell, I took the default mapping that the Hibernate reverse-engineering tools generated and it works very well. Actually, the only part that's a pain is when you query the table through the MySQL client, you have to specify hex(sha512Digest) or it will print gobbledty-gook. Conversely, when updating through the MySQL client, use unHex('A01F3B2C').

One note: if you ever switch between byte[] and char[] or String types in Java, make sure you've thought about character encoding issues! java.security.MessageDigest returns an array of bytes, so I have no issues.

How about the Hibernate mapping? What type is it using?

And either way, is there no Hibernate type that corresponds to MySQL's plain old "binary" type? Everything seems to map to some flavor of blob.

Why do you want to use binary instead of varbinary or blob? It seems to me that varbinary does all the good things that binary does without changing (padding) the data value that you store in it. At the end of http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html they say, "If the value retrieved must be the same as the value specified for storage with no padding, it might be preferable to use VARBINARY or one of the BLOB data types instead." To me that sounds like a polite way of saying, "don't use the binary data type in MySQL."

In my mind, the MySQL binary datatype is one of those things like mediumint - a 3-byte integer. I'm not sure why MySQL supports it, but if you are using Java, there's no reason to use it.

Using a fixed-length binary column just seemed like the obvious choice for representing a field that should always be a fixed length and MySQL's encryption documentation recommends binary for "efficient storage and comparisons" in their hashing examples.
http://dev.mysql.com/doc/refman/5.5/en/ ... tions.html
Mentioning "efficient comparisons" suggests to me that a fixed-length binary is optimized for querying (ie: select * from widget where binary_data=...) and maybe the blob types are intended more for data that comes back as part of query based on other fields (ie: select png_image_data from picture where camera_manufacturer='Nikon').

Although I will say that I didn't entirely realize that MySQL pads short values. You bring up a good point about MySQL essentially changing your data when it pads. So maybe binary isn't the best choice in that respect but even with varbinary, I would still have my original problem - "binary" and "blob" in Hibernate are synonymous and translate to various MySQL blob types. Meanwhile, nothing in Hibernate correspond to (var)binary in MySQL. That's really want I wanted to ask about and bring attention to. MySQL's 3-byte mediumint might be an oddity, but binary?! It seems to be the recommended type for things like cryptography, which isn't an unusual thing for an application to deal with.

I might end up changing the column's type after all but I do have to say that I'm opposed to tweaking database schemas for the sake of appeasing a particular tool. Sure, I'm using Java with Hibernate today, but what if I switch persistence libraries next week? Or what if I decide that I want to write a "mobile" version of my site in Rails, and ActiveRecord doesn't like my Hibernate-specific tweaks? Or the tweaks I made don't enforce the same amount of data integrity?