Image insertion into database through JPA

[Gvn Karthik]

In the code below I'm reading an image file. The function readImageOldWay(file) reads the file into a byte array as shown by the class ImageService below the class ImageMain. I'm using BLOB datatype in the database to store the image but I'm getting an exception for images larger than 5kB approximately. Check the following code and the exception.

public class ImageMain   
{     
    public static void main(String[] args) throws Exception  
    {  
        ImageExample example=new ImageExample();  
        File file=new File("D:/vv.jpg");  
        EntityManager em=null;  
          
        EntityManagerFactory emf=Persistence.createEntityManagerFactory("Enigma");  
        em=emf.createEntityManager();  
          
        try  
        {  
            example.setImageFile(new ImageService().readImageOldWay(file));  
            example.setId(987);  
            em.getTransaction().begin();  
            em.persist(example);  
            em.getTransaction().commit();  
            System.out.println("Persisted");  
        }  
          
        finally  
        {  
            if(em!=null)  
            {  
                em.close();  
            }             
        }  
  
    }  
  
}  
  
  
public class ImageService   
{  
    public byte[] readImageOldWay(File file) throws IOException  
    {  
      InputStream is = new FileInputStream(file);  
      long length = file.length();  
      if (length > Integer.MAX_VALUE)  
      {  
        System.out.println("File too large");  
      }  
        
      byte[] bytes = new byte[(int) length];  
      int offset = 0;  
      int numRead = 0;  
      while (offset < bytes.length && (numRead = is.read(bytes, offset, bytes.length - offset)) >= 0)  
      {  
        offset += numRead;  
      }  
        
      if (offset < bytes.length)  
      {  
        throw new IOException("Could not completely read file " + file.getName());  
      }  
        
      is.close();  
      for(int i=0;i<bytes.length;i++)  
      {  
          System.out.println(bytes[i]);  
      }  
      return bytes;  
    }  
      
}

When I insert a bigger image say of size above 150kB, I get the following exception. What should I be doing to get rid of this exception? How can I insert bigger images?

Exception in thread "main" javax.persistence.RollbackException: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build b04-fcs (04/11/2008))): oracle.toplink.essentials.exceptions.DatabaseException  
Internal Exception: java.sql.SQLException: Data size bigger than max size for this type: 180140  
Error Code: 17070  
Call: INSERT INTO ImageExample (ID, IMAGEFILE) VALUES (?, ?)  
    bind => [987, [B@49d67c]  
Query: InsertObjectQuery(ImageExample@b25b9d)  
    at oracle.toplink.essentials.internal.ejb.cmp3.transaction.base.EntityTransactionImpl.commit(EntityTransactionImpl.java:120)  
    at oracle.toplink.essentials.internal.ejb.cmp3.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:60)  
    at ImageMain.main(ImageMain.java:25)  
Caused by: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build b04-fcs (04/11/2008))): oracle.toplink.essentials.exceptions.DatabaseException  
Internal Exception: java.sql.SQLException: Data size bigger than max size for this type: 180140  
Error Code: 17070  
Call: INSERT INTO ImageExample (ID, IMAGEFILE) VALUES (?, ?)  
    bind => [987, [B@49d67c]  
Query: InsertObjectQuery(ImageExample@b25b9d)  
    at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)  
    at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:566)  
    at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:452)  
    at oracle.toplink.essentials.internal.sessions.AbstractSession.executeCall(AbstractSession.java:690)  
    at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)  
    at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:214)  
    at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:346)  
    at oracle.toplink.essentials.internal.queryframework.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:191)  
    at oracle.toplink.essentials.internal.queryframework.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:205)  
    at oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:564)  
    at oracle.toplink.essentials.queryframework.InsertObjectQuery.executeCommit(InsertObjectQuery.java:89)  
    at oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.performUserDefinedWrite(DatabaseQueryMechanism.java:750)  
    at oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.performUserDefinedInsert(DatabaseQueryMechanism.java:714)  
    at oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.insertObjectForWriteWithChangeSet(DatabaseQueryMechanism.java:602)  
    at oracle.toplink.essentials.queryframework.WriteObjectQuery.executeCommitWithChangeSet(WriteObjectQuery.java:162)  
    at oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:390)  
    at oracle.toplink.essentials.queryframework.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:109)  
    at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:628)  
    at oracle.toplink.essentials.queryframework.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:555)  
    at oracle.toplink.essentials.queryframework.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:138)  
    at oracle.toplink.essentials.queryframework.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:110)  
    at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2233)  
    at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:952)  
    at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:909)  
    at oracle.toplink.essentials.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:269)  
    at oracle.toplink.essentials.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:190)  
    at oracle.toplink.essentials.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:2657)  
    at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1044)  
    at oracle.toplink.essentials.internal.ejb.cmp3.base.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:403)  
    at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1126)  
    at oracle.toplink.essentials.internal.ejb.cmp3.base.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:107)  
    at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:856)  
    at oracle.toplink.essentials.internal.ejb.cmp3.transaction.base.EntityTransactionImpl.commit(EntityTransactionImpl.java:102)  
    ... 2 more  
Caused by: java.sql.SQLException: Data size bigger than max size for this type: 180140  
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)  
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)  
    at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:95)  
    at oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.java:2414)  
    at oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStatement.java:1134)  
    at oracle.jdbc.driver.OraclePreparedStatement.setBytes(OraclePreparedStatement.java:2170)  
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2823)  
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3015)  
    at oracle.toplink.essentials.platform.database.oracle.OraclePlatform.setPrimitiveParameterValue(OraclePlatform.java:593)  
    at oracle.toplink.essentials.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:1422)  
    at oracle.toplink.essentials.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:1483)  
    at oracle.toplink.essentials.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:1471)  
    at oracle.toplink.essentials.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:623)  
    at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:485)  
    ... 33 more  

[Paul Sturrock]

Oracle's BLOB data type supports up to 4 GB so should work OK with files of the size you say they are. Are you sure of the size and data type?

What version of the driver are you using? And what version of Oracle?

[James Sutherland]

What version of Oracle are you using? Oracle thin JDBC driver had a 4k limitation for blob binding, I believe they have fixed this in their latest version. I think OCI never had this limitation.

TopLink supported using a locator to write blob > 4k if you used the Oracle9Platform and define the mapping type as blob.
Unfortunately TopLink Essentials did not include this support, but EclipseLink does.

[Gvn Karthik]

I'm using Oracle 10g at present but with the above code I'm not able to write images of higher sizes to database. Is there another way of doing this? Another way which I was thinking of was importing java.sql.Blob and defining a Blob attribute in my entity class. But how do I read it? If I read it as a byte stream using FileStream and InputStream classes and store it in a byte array, how do I set it in the Blob attribute?

[Gvn Karthik]

Any suggestions regarding the exception? How can I do image insertion into database using JPA?

[Gvn Karthik]

I have Oracle 10g Express Edition installed. When I checked the size of the image column which is of BLOB type, the size turned out to be 4000. That explains why I was not able to insert an image of higher size.

How do I increase the size of the BLOB field so that I can store bigger images?

[Paul Sturrock]

When I checked the size of the image column which is of BLOB type, the size turned out to be 4000.

That statement makes no sense without a unit of measure. 4000 of what?

Reading the Oracle Express documentation, it appears LOB fields support:

A LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured.

[Gvn Karthik]

That size refers to 4000 bytes. Thats why I was able to insert only images of size approximately 4kB. When I created the table in the database, I used the following query:

create table images(id number(5),photo BLOB);

How should I change query so that I can accomodate bigger size images?

[Paul Sturrock]

Looking at the Oracle Express documentation again, you don't need to change your DDL because the BLOB data type's limits are far higher than 4KB (as my quote from the docs states).

Oracle's LOB data types use semantics to decide how to store the data after the first 4kb, but they store more than 4KB.

[Gvn Karthik]

So, you mean to say that I can use the datatype BLOB in the back end and I should use the datatype Lob(@Lob) in my Entity class???

Then how do I read the image? As a stream of bytes?

[Paul Sturrock]

No - LOB is short hand for CLOB and BLOB.

[Gvn Karthik]

So how can I solve my issue of inserting bigger images? I mean, more than 4kB?