Issue
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
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.
Answered By - user2957378