Sample Code: BLOB insertion in Oracle10g

Responding to a customer question about Blob insertion in Oracle 10g DB using the JDBC 3.0 driver. Very Simple application. Download Java Source See insertBlob method.

package demo;

import java.sql.*;
import java.io.*;
import java.sql.PreparedStatement;
import java.util.*;

import oracle.jdbc.driver.*;
import oracle.sql.BLOB;



/**
 * Insert record in the MEDIA table 
 *   MEDIA (file_name varchar2(256), file_content BLOB);
 */
public class BlobOracle 
{
  private final static String hostname = "localhost";
  private final static String port = "1521";
  private final static String sid = "ORCL";
  private final static String username = "scott";
  private final static String password = "tiger";
  private static String fileLocation;
  private static Connection connection;

  public BlobOracle()
  {
  }

  /**
   * 
   * @param args
   */
  public static void main(String[] args)
  {
    try 
    {
      if (args.length == 0)
      {
       System.out.println("\n\n  Usage demo.BlobOracle ");
       System.exit(0);
      }
  
      fileLocation = args[0];
  
      setConnection();
      insertBLOB();
  
    } catch (Exception ex) 
    {
      ex.printStackTrace();
    } finally 
    {
    }
  }


  private static void setConnection() throws SQLException
  {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      connection = DriverManager.getConnection("jdbc:oracle:thin:@"+hostname+ ":"+ port +":"+ sid , username , password);
      connection.setAutoCommit(false); // we must control the commit
  }

  private static void insertBLOB() throws SQLException, Exception
 {
    BLOB blob;
    File file ;
    FileInputStream is;
    OutputStream os;

      long ts1 = System.currentTimeMillis();


      //Create a statement.
      PreparedStatement pstmt = connection.prepareStatement("insert into media (file_name, file_content) values (?,empty_blob())");
      file = new File(fileLocation);
      String fileName = file.getName();
      //Set the file name and execute the query
      pstmt.setString(1, fileName);
      pstmt.execute();

      //Take back the record for update (we will insert the blob)
      //supposely the file name is the PK
      pstmt = connection.prepareStatement("select file_content from media where file_name = ? for update");
      pstmt.setString(1, fileName);
  
      //Execute the query, and we must have one record so take it
      ResultSet rset = pstmt.executeQuery();
      rset.next();

      //Use the OracleDriver resultset, we take the blob locator
     blob = ((OracleResultSet)rset).getBLOB("file_content");

     is = new FileInputStream(file); //Create a stream from the file
     // JDBC 2.0
     //os = blob.getBinaryOutputStream(); //get the output stream from the Blob to insert it
     // JDBC 3.0
     os = blob.setBinaryStream(0); //get the output stream from the Blob to insert it
 
      //Read the file by chuncks and insert them in the Blob. The chunk size come from the blob
      byte[] chunk = new byte[blob.getChunkSize()];
      int i=-1;
      System.out.println("Inserting the Blob");
      while((i = is.read(chunk))!=-1)
      {
        os.write(chunk,0,i); //Write the chunk
        System.out.print('.'); // print progression
      }

      // When done close the streams
      is.close();
      os.close();

      //Close the statement and commit
      pstmt.close();
      long ts2 = System.currentTimeMillis();

      connection.commit();
      connection.close();
  
      System.out.println("\n"+ (ts2 - ts1) +" ms" );      
  

  }


}