Inserting/Reading BLOB's with 11g / JDK 1.6

Here is some code I used to insert/read BLOB images in an 11g database using 11g JDBC driver with JDK 1.6. In these tests I uploaded 30K images which was lighting fast as you would expect.

Table:

drop sequence blob_seq;
drop table blob_fun;

create sequence blob_seq
start with 1
increment by 1
/

create table blob_fun (id number, image_col blob, image_size number)
/

Insert Code:

  public void run() throws SQLException, IOException
 {
   Connection conn = getConnection();
   conn.setAutoCommit(false);
   PreparedStatement pstmt = null;
   String sql = "insert into blob_fun values (blob_seq.nextval, ?, ?)";
 
   byte [] b = new byte[1024];
    ByteArrayOutputStream b1 = new ByteArrayOutputStream();

   FileInputStream f1 = new FileInputStream("D:\\temp\\blob\\ip-settings.jpg");
 
   int i;
   do
   {
     i = f1.read(b);
     b1.write(b);
   } while( i!= -1);

   ByteArrayInputStream bais = new ByteArrayInputStream(b1.toByteArray());
 
   try
   {
     pstmt = conn.prepareStatement(sql);
     pstmt.setBlob(1, bais);
     pstmt.setInt(2, bais.available());
     pstmt.execute();
   
     System.out.println("Successfully uploaded BLOB");
     conn.commit();
     bais.close();
     f1.close();
   }
   catch (Exception e)
   {
     e.printStackTrace();
   }
   finally
   {
     if (pstmt != null)
     {
       pstmt.close();
     }
   
     if (conn != null)
     {
       conn.close();
     }
   }

   conn.close();
 }

Read Code:

public void run() throws SQLException, IOException
{
  Connection conn = getConnection();
  conn.setAutoCommit(false);
  PreparedStatement pstmt = null;
  BLOB b = null;
  ResultSet rset = null;
  String sql = "select image_col from blob_fun where id = 1";

  try
  {
    pstmt = conn.prepareStatement(sql);
    rset = pstmt.executeQuery();
    rset.next();
    b = (BLOB) rset.getBlob(1);
    int chunk = b.getChunkSize();
    byte[] buffer = new byte[chunk];
    int length;
        
    FileOutputStream outFile = null;
    outFile = new FileOutputStream
      ("D:\\temp\\blob\\readfromdbfile.jpg");
    InputStream instream = b.getBinaryStream();
    
    // Fetch data  
    while ((length = instream.read(buffer)) != -1)
    {
      outFile.write(buffer, 0, length);
    }

    // Close input and output streams
    instream.close();
    outFile.close();  
  
    System.out.println("Successfully read/saved BLOB to file system from DB");
    conn.commit();
  }
  catch (Exception e)
  {
     e.printStackTrace();
  }
  finally
  {
    if (rset != null)
    {
      rset.close();
    }
  
    if (pstmt != null)
    {
      pstmt.close();
    }

    if (conn != null)
    {
      conn.close();
    }
  }

  conn.close();
}

UpdateableResultSet with BLOB column

When trying to use an UpdateableResultSet with a BLOB column the following error occurs:

Exception in thread "main" java.sql.SQLException: Invalid operation for read only resultset: updateBlob
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)

The code being used looks something like this:

   String querySql2 = "SELECT id, col2 FROM blob_test";
 
   stmt = null;
   stmt = conn.createStatement
     (ResultSet.TYPE_SCROLL_INSENSITIVE,
      ResultSet.CONCUR_UPDATABLE);
   ResultSet rs = null;
   rs = stmt.executeQuery(querySql2);
   rs.next();
   // Fails at this line with exception
   rs.updateBlob(2, inputStreamUpdate); 

This occurs because updateBlob(int, java.io.InputStream) realizes that the user is binding a stream with unknown length and throws an exception. To get around this you need to use the following two methods which work fine.

• updateBlob(int, Blob)
• updateBlob(int,java.io.InputStream, long)

See the following documentation for more information on ResultSet's:

http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/resltset.htm#i1022319