Monday, 8 October 2007

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();
}


No comments: