Web Info X-change

I wanted to save and load large binary objects (BLOB) from Oralce database and get into array of bytes in Java.

The access to database cound’t be done directly to the table, it uses a stored procedure that insert’s the record and returns then primary key of the table.

After long research, I’m giving back to the community…

I use an object UtilsDB.getDataSource().getConnection(); to get a connection from JNDI … bue the important is you have some way to get the database connection.

Atention: This doesn’t work if you’re using ojdbc14-9.0.2.0.0.jar. I had to change this jar to ojdbc14-10.2.0.3.0.jar, then it worked ok.

So, to save a chunck of bytes (a PDF file, for example) into a Dabatase

public BigDecimal saveBlobIntoDatabase (byte [] ficheiro,
String nomFicheiro,
String nomDirectoria)
throws SQLException, IOException {

Connection oracleConnection = UtilsDB.getDataSource().getConnection();

CallableStatement stmt = oracleConnection.prepareCall(DBResources.F_GRAVA_BLOB_BD);

BLOB blob = BLOB.createTemporary(oracleConnection, false, BLOB.DURATION_SESSION);

OutputStream outputStream = blob.setBinaryStream(0L);
InputStream inputStream = new ByteArrayInputStream(ficheiro);
byte[] buffer = new byte[blob.getBufferSize()];
int byteread = 0;
while ((byteread = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, byteread);
}
outputStream.close();
inputStream.close();

BigDecimal numFicheiro = new BigDecimal(0);
stmt.registerOutParameter(1, OracleTypes.NUMBER);
stmt.setBlob(2, blob);
stmt.setString(3, nomFicheiro);
stmt.setString(4, nomDirectoria);
stmt.execute();
numFicheiro = stmt.getBigDecimal(1);
stmt.close();

return numFicheiro;

}

To read (do the reverse):

public byte[] getBlobFromDatabase(BigDecimal numFicheiro) throws BusinessException {

Connection conn = null;
try {

conn = (Connection) UtilsDB.getDataSource().getConnection();

CallableStatement ocs = null;
byte[] content = null;

try {

ocs = conn.prepareCall(DBResources.F_OBTEM_BLOB_BD);

ocs.registerOutParameter(1, OracleTypes.BLOB);
ocs.setBigDecimal(2, numFicheiro );

ocs.executeQuery();

BLOB blob = null;
if (ocs.getObject(1) instanceof BLOB) {
blob = (BLOB) ocs.getObject(1);
}

content = new byte[ (int) blob.length()];
InputStream blobstream = blob.getBinaryStream();
try {
blobstream.read(content , 0 , (int) blob.length());
} catch (IOException ioex) {
ioex.printStackTrace();
} finally {
try {
blobstream.close();
} catch (Exception ignored) {}
}
return content;

} catch (SQLException sqlex) {
sqlex.printStackTrace();
throw sqlex;
} finally {
UtilsDB.close(ocs);
}
} catch (Exception daoe) {
throw new BusinessException(daoe.getMessage() , daoe);
} finally {
UtilsDB.close(conn);
}

}

Also read from:

http://stackoverflow.com/questions/862355/overcomplicated-oracle-jdbc-blob-handling

http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/oralob.htm#1043220