Working with Lobs using Java

Prepare Database
=============
conn system/oracle
create directory IMG as 'C:\IMG';

-- I already add the oracle.gif and oraclelogo.gif files inside a c:\IMG

CREATE TABLE lob_table (filename VARCHAR2(128),blobdata BLOB);

CREATE OR REPLACE PROCEDURE LoadFILEIntoBLOB (myfilename IN VARCHAR2) IS

out_blob BLOB;
in_file BFILE := BFILENAME('IMG', myfilename);
blob_length INTEGER;

BEGIN

-- Obtain the size of the blob file
DBMS_LOB.FILEOPEN(in_file, DBMS_LOB.FILE_READONLY);
blob_length:=DBMS_LOB.GETLENGTH(in_file);
DBMS_LOB.FILECLOSE(in_file);

-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to out_blob.
INSERT INTO lob_table VALUES (myfilename, EMPTY_BLOB())
RETURNING blobdata INTO out_blob;

-- Load the image into the database as a BLOB
DBMS_LOB.OPEN(in_file, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(out_blob, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(out_blob, in_file, blob_length);

-- Close handles to blob and file
DBMS_LOB.CLOSE(out_blob);
DBMS_LOB.CLOSE(in_file);

COMMIT;
END;
/

exec LoadFileIntoBlob('oracle.gif');

--Ensure that picture in Loaded

SELECT filename,DBMS_LOB.GETLENGTH(blobdata)
lob_table;

FILENAME DBMS_LOB.GETLENGTH(BLOBDATA)
------------- ---------------------------------------------
oracle.gif 7868


Get the photo from JSP


<%
ServletOutputStream out1 = response.getOutputStream();
JDBConnection con = new JDBConnection();
Blob photo = null;
String query = " Select blobdata from lob_table ";
String sysdate= "";
try
{
PreparedStatement pst = con.conn.prepareStatement(query);
ResultSet rst = pst.executeQuery();
if (rst.next())
{
photo = rst.getBlob(1);
}
response.setContentType("image/gif");
InputStream in = photo.getBinaryStream();
int length = (int) photo.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1)
{

out1.write(buffer, 0, length);
}
in.close();
out1.flush();
con.conn.close();
}
catch(SQLException e)
{
throw new Exception(""+e.getMessage());
}
catch(Exception e)
{
throw new Exception(""+e.getMessage());
}
%>

Get the photo from Servlet

package project1;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import oracle.jdbc.driver.OracleResultSet;


public class DisplayPhoto extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=windows-1252";

public void init(ServletConfig config) throws ServletException {
super.init(config);
}

public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType(CONTENT_TYPE);
// PrintWriter out = response.getWriter();
ServletOutputStream out = response.getOutputStream();
//out.println("");
//out.println("");
//out.println("");


JDBConnection con = new JDBConnection();
Blob photo = null;
String query = " Select blobdata from lob_table ";
String sysdate= "";
try
{
PreparedStatement pst = con.conn.prepareStatement(query);
ResultSet rst = pst.executeQuery();
if (rst.next())
{
photo = rst.getBlob(1);
}
response.setContentType("image/gif");
InputStream in = photo.getBinaryStream();
int length = (int) photo.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1)
{
//System.out.println("writing " + length + " bytes");
out.write(buffer, 0, length);
}
in.close();
out.flush();
con.conn.close();
}
catch(SQLException e)
{
throw new ServletException(""+e.getMessage());
}
catch(Exception e)
{
throw new ServletException(""+e.getMessage());
}
//out.println("

Date: "+sysdate+"

");
//out.println("");
//out.close();
}
}

package project1;
import java.awt.*;
import java.sql.*;
import java.awt.event.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.lang.*;

public class JDBConnection
{
public Connection conn=null;
public Statement stm=null;
public JDBConnection()
{
try
{
registerDB();
}
catch(Exception e)
{
System.out.println("Error...");
}
}

public void registerDB()throws Exception
{
try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn=DriverManager.getConnection
("jdbc:oracle:thin:@michaelgeorgiou:1521:orcl","system","oracle");

stm = conn.createStatement();
}
catch (SQLException e)
{
throw new Exception("Sql error: "+e.getMessage());
}
}


}