Lob returns null using MySQL and jpa

Question

Hi folks,

I'm trying to store a document as a BLOB in a java domain object that I have.

I've seen several posts saying it can be done using @Lob for example

http://www.java2s.com/Tutorial/Java/0355__JPA/MarkByteArrayFieldAsLob.htm

So, the problem I have is, when I store the data all seems fine, I can see it in my mysql database, however when I try to get it out within the domain object, it's always null.

Any ideas?

Note, the relevant getter and setter I'm using within my entity is as follows:

@Lob
public byte[] getData() {
    return data;
}

public void setData(byte[] data) {
    this.data = data;
}

Update as per the comment of Pascal Thivent, I've added some code. I've also simplified the test. If I have one entity object, with two fields, an id and the data, eg

@Entity
@Table(name = "picture")
public class Picture extends BaseObject{

    Long id;
    byte[] data;    

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)  
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }


    @Lob
    public byte[] getData() {
        return data;
    }

    public void setData(byte[] data) {
        this.data = data;
    }
}

Now, I have no problem creating and saving the data. However, when I try to retrieve the data just by calling a standard

 this.entityManager.find(clazz, id);

Populated with the Picture.class and the relevant id, I get the following exception.

Note one very interesting line in the exception, which i think is key is:

CausedBy: mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column "[email protected]"

Note that "[email protected]" is the value of the byte array that I put into the data filed, so it's as if the sql is looking for a column name which is actually the name contents of the byte array. Weird!

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not load an entity: [com.model.Picture#2]
    at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:630)
    at org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:195)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:198)
    at $Proxy28.find(Unknown Source)
    at com.service.impl.BaseManagerImpl.get(BaseManagerImpl.java:55)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at $Proxy32.get(Unknown Source)
    at com.service.UserManagerTest.testSaveOrUpdatePicture(UserManagerTest.java:33)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at junit.framework.TestCase.runTest(TestCase.java:154)
    at junit.framework.TestCase.runBare(TestCase.java:127)
    at junit.framework.TestResult$1.protect(TestResult.java:106)
    at junit.framework.TestResult.runProtected(TestResult.java:124)
    at junit.framework.TestResult.run(TestResult.java:109)
    at junit.framework.TestCase.run(TestCase.java:118)
    at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: org.hibernate.exception.SQLGrammarException: could not load an entity: [com.model.Picture#2]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:1874)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:48)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:42)
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3044)
    at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:395)
    at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:375)
    at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:139)
    at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:195)
    at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:103)
    at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:878)
    at org.hibernate.impl.SessionImpl.get(SessionImpl.java:815)
    at org.hibernate.impl.SessionImpl.get(SessionImpl.java:808)
    at org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:175)
    ... 35 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '[email protected]' in 'field list'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1888)
    at com.mysql.jdbc.BlobFromLocator.length(BlobFromLocator.java:334)
    at org.hibernate.type.ByteArrayBlobType.get(ByteArrayBlobType.java:89)
    at org.hibernate.type.AbstractLobType.nullSafeGet(AbstractLobType.java:46)
    at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2096)
    at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380)
    at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308)
    at org.hibernate.loader.Loader.getRow(Loader.java:1206)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580)
    at org.hibernate.loader.Loader.doQuery(Loader.java:701)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:1860)
    ... 47 more

Generated SQL as per Pascal's request is:

Hibernate: select picture0_.id as id32_0_, picture0_.data as data32_0_ from picture picture0_ where picture0_.id=?

Answer 1

Try setting the fetch type for the blob. The JPA standard says it should default to eager loading, but Hibernate used to have some minor differences with the spec, so you might try the following

@Lob
@Basic(fetch = FetchType.EAGER)
public byte[] getData() {
        return data;
}

Answer 2

A little late for you maybe, but others may benefit.

I had a similar problem and solved it by removing the property '?emulateLocators=true' I had on my jdbc-URL (I needed it earlier for some other error).

There might exist a property on jpa/hibernate that allows it to work with emulated locators, but I did not look into it.