Store byte array in MySQL with Hibernate

Question

I'm trying to save an entity with a byte array field. I'm using Hibernate and JPA on top of a MySQL database. This is the field definiton, which worked fine for an embedded H2 database:

@Entity(name = "blob")
public class Blob {
    ...
    @Lob
    @Basic(fetch = FetchType.LAZY)
    @Column(name = "blobImg", nullable = false)
    private byte[] blobImg;
}

Now, with MySQL database, an exception is thrown everytime I execute blobRepository.save(). Actually, is may be thrown when Hibernate tries to autocreate the table of Blob entity. The exception is the following:

o.h.engine.jdbc.spi.SqlExceptionHelper : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'blob (blobCols, blobImg, blobRows, channel, idBlobPersistence) values (50, _bina' at line 1

I've tried to change the field definition with several approaches I've found on the web:

Approach 1:

@Column(name = "blobImg", nullable = false, columnDefinition = "BINARY(256)", length = 256)
private byte[] blobImg;

Approach 2:

@Lob
@Column(name="blobImg", columnDefinition="bytea")
private byte[] blobImg;

Approach 3: Defining an hibernate mapping on blob.hbm.xml file and refering it from entityManagerFactory bean:

<?xml version='1.0' encoding='UTF-8'?>
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="mappingResources">  
        <list>  
            <value>blob.hbm.xml</value>  
        </list>  
    </property>
</bean>

blob.hbm.xml mapping:

<hibernate-mapping>
    <class name="guiatv.persistence.domain.Blob" table="blob">
        <property name="blobImgProperty">
            <column name="blobImg" sql-type="binary"></column>
        </property>
    </class>
</hibernate-mapping>

Approach 4: changing blob.hbm.xml mapping to the following:

<?xml version='1.0' encoding='UTF-8'?>
<hibernate-mapping>
    <class name="guiatv.persistence.domain.Blob" table="blob">
        <property name="blobImg" type="binary">
            <column name="blobImg" />
        </property>
    </class>
</hibernate-mapping>

All of them are throwing the same exception.

How can I solve it? Thank you!

Solution 1

Well,

I have tried your suggestions @Ernusc. The first one:

@Lob(type = LobType.BLOB)
private byte[] blobImg;

it is no compiling for me. It may be my Hibernate version is different as yours. About the second option:

@Type(type = "org.hibernate.type.BlobType")
@Lob
private byte[] blobImg;

It does compile, but it fails at runtime. It was showing the following error:

2015-09-14 23:21:01.324  WARN 7436 --- [bTaskExecutor-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2015-09-14 23:21:01.324 ERROR 7436 --- [bTaskExecutor-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'blob blob0_ where blob0_.idBlobPersistence=326' at line 1
2015-09-14 23:21:01.337  INFO 7436 --- [bTaskExecutor-3] o.h.e.internal.DefaultLoadEventListener  : HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: could not extract ResultSet   

Also, when I tried to follow the trace, I found it was actually throwing the following exception:

java.lang.ClassCastException: [B cannot be cast to java.sql.Blob

Then I realized the first of the error messages was actually caused by my stupid idea of naming my entity as "blob", which is probably a reserved keyword in Hibernate. So I changed the entity declaration from:

@Entity(name = "blob")
public class Blob {
   ...
}

to:

@Entity(name = "blobframe")
public class Blob {
   ...
}

Then, your second suggestion was only throwing that ClassCastException exception. However, some of the approaches I tried before do work now. For instance:

@Column(name = "blobImg", nullable = false, columnDefinition = "BINARY(256)", length = 256)
private byte[] blobImg;

UPDATE: This approach is NOT working for me. It fails on blobframe table creation with the following field definition (don't know the reason, though):

@Lob
@Column(name="blobImg", columnDefinition="bytea")
private byte[] blobImg;

Thank you Ernusc for your quick response.

Solution 2

If you want to convert your byte[] to sql type Blob, you can do the following:

@Lob(type = LobType.BLOB)

or

@Type(type = "org.hibernate.type.BlobType")
@Lob

Hope it helps

Comments

[OndroMih]

You do not need to specify the type of LOB, it is inferred from the type of the field. For byte array, JPA automatically assumes it is BLOB. For String, it assumes CLOB.

[OndroMih]

@Emusc it is written directly in Javadoc for @Lob: docs.oracle.com/javaee/7/api/javax/persistence/Lob.html - "The Lob type is inferred from the type of the persistent field or property, and except for string and character-based types defaults to Blob. "

[Alexey Usharovski]

How about portability of this approach? You can run it on Postgres but on on MySQL.