How to store images in Oracle Using Stored Procedures

 

Address: http://database.51cto.com/art/201010/231598.htm

 

Oracle image storage is a feature that we often need to implement. The following describes how to use the stored procedure to store images in Oracle. If you have encountered any problems in Oracle image storage, take a look.

To store images in Oracle with the Blob type, first create the following in the database:

-- Connect to the Administrator

 
 

  
  
conn sys/tbsoft as sysdba; 
 
 

-- Authorize Scott user

 
 

  
  
grant create any directory to scott; 
 
 

-- Return to Scott user

 
 

  
  
conn scott/tiger; 
 
 

-- Create a table for storing images

 
 

  
  
CREATE TABLE IMAGE_LOB (T_ID VARCHAR2 (5) NOT NULL,T_IMAGE BLOB NOT NULL); 
 
 

-- Create a directory for storing images

 
 

  
  
CREATE OR REPLACE DIRECTORY IMAGES AS 'C:\picture'; 
 
 

-- Create a folder named picture in C :.

 
 

  
  
CREATE OR REPLACE PROCEDURE IMG_INSERT (TID VARCHAR2,FILENAME VARCHAR2) AS 
 
 

F_lob bfile; -- file type

B _lob blob;

 
 

  
  
BEGIN     
  
  
iNSERT INTO IMAGE_LOB (T_ID, T_IMAGE)     
  
  
VALUES (TID,EMPTY_BLOB ()) RETURN T_IMAGE INTO B_LOB    
 
 

-- Insert an empty blob.

 
 

  
  
F_LOB:= BFILENAME ('IMAGES', FILENAME); 
 
 

-- Get files in the specified directory

 
 

  
  
DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONLY); 
 
 

-- Open a file in read-only mode

 
 

  
  
DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,DBMS_LOB.GETLENGTH (F_LOB)); 
 
 

-- Transfer object

 
 

  
  
DBMS_LOB.FILECLOSE (F_LOB); 
 
 

-- Close the original file

 
 

  
  
COMMIT;  
  
  
END;  
  
  
/  
 
 

-- Put an image 1.gif under c: \ picture.

-- Save the image to a table

 
 

  
  
call IMG_INSERT('1','1.gif'); 
 
 

Create a web project to connect to the database and create a blobdao class to retrieve blob images in the table.

 
 

  
  
Public class blobdao {
  
  
Private Static final blobdao instance = new blobdao ();
  
  
Private connection conn = NULL;
  
  
Private blobdao (){
  
  
}
  
  
Public static blobdao getinstance (){
  
  
Return instance;
  
  
}
  
  
Private void initconn (){
  
  
Conn = dbaccess. getinstance (). getconn ();
  
  
}
  
  
Public byte [] getimage (string imgname ){
  
  
Bufferedinputstream INS; // gets the Blob Io stream
  
  
Byte [] bt = NULL;
  
  
Initconn ();
  
  
Blob BO = NULL;
  
  
Preparedstatement PS = NULL;
  
  
Resultset rs = NULL;
  
  
String SQL = "select t_image from image_lob where t_id =? ";
  
  
Try {
  
  
PS = conn. preparestatement (SQL );
  
  
PS. setstring (1, imgname );
  
  
Rs = ps.exe cutequery ();
  
  
If (Rs. Next ()){
  
  
BO = Rs. getblob ("t_image ");
  
  
Try {
  
  
INS = new bufferedinputstream (BO. getbinarystream ());
  
  
Int buffersize = (INT) BO. Length (); // obtain the Blob length.
  
  
Bt = new byte [buffersize];
  
  
Try {
  
  
INS. Read (BT, 0, buffersize );
  
  
} Catch (ioexception e ){
  
  
// Todo auto-generated Catch Block
  
  
E. printstacktrace ();
  
  
}
  
  
// Create a byte Cache
  
  
} Catch (sqlexception e ){
  
  
// Todo auto-generated Catch Block
  
  
E. printstacktrace ();
  
  
}
  
  
}
  
  
} Catch (sqlexception e ){
  
  
// Todo auto-generated Catch Block
  
  
E. printstacktrace ();
  
  
} Finally {
  
  
Try {
  
  
Rs. Close ();
  
  
PS. Close ();
  
  
Conn. Close ();
  
  
} Catch (sqlexception e ){
  
  
// Todo auto-generated Catch Block
  
  
E. printstacktrace ();
  
  
}
  
  
}
  
  
Return Bt;
  
  
}
  
  
}
  
  
 
 
 

Call the getimage () method in the action and display the image on the page.

 
 

  
  
public ActionForward execute(ActionMapping mapping, ActionForm form,  
  
  
              HttpServletRequest request, HttpServletResponse response) {  
  
  
        // TODO Auto-generated method stub  
  
  
             BlobDAO blobDAO = BlobDAO.getInstance();  
  
  
        byte[] bs = blobDAO.getImage("1");  
  
  
 
  
  
        try {  
  
  
 
  
  
              response.getOutputStream().write(bs);  
  
  
        } catch (IOException e) {  
  
  
              // TODO Auto-generated catch block  
  
  
              e.printStackTrace();  
  
  
        }  
  
  
        return null;  
  
  
    }  
 
 

Add images to the database

Put the image in drive c: \ 4.gif

 
 

  
  
Public void savaimg (string imgid ){
  
  
// Upload the ID of the image stored in the database
  
  
Initconn ();
  
  
Statement ST = NULL;
  
  
Blob blob = NULL; // image type
  
  
Outputstream = NULL; // output stream
  
  
File file = NULL; // File
  
  
Inputstream = NULL; // input stream
  
  
Resultset rs = NULL;
  
  
Try {
  
  
Conn. setautocommit (false); // The transaction is operated by the programmer.
  
  
St = conn. createstatement ();
  
  
St.exe cutequery ("insert into image_lob values ('" + imgid + "', empty_blob ())");
  
  
Rs = st.exe cutequery ("select t_image from image_lob where t_id = '" + imgid + "' for update ");
  
  
If (Rs. Next ()){
  
  
Blob = (BLOB) Rs. getblob (1 );
  
  
Outputstream = blob. getbinaryoutputstream ();
  
  
File = new file ("C: \ 4.gif ");
  
  
Inputstream = new fileinputstream (File );
  
  
Byte [] B = new byte [blob. getbuffersize ()];
  
  
Int Len = 0;
  
  
While (LEN = inputstream. Read (B ))! =-1 ){
  
  
System. Out. println (LEN );
  
  
Outputstream. Write (B, 0, Len );
  
  
}
  
  
}
  
  
} Catch (sqlexception e ){
  
  
// Todo auto-generated Catch Block
  
  
E. printstacktrace ();
  
  
} Catch (filenotfoundexception e ){
  
  
// Todo auto-generated Catch Block
  
  
E. printstacktrace ();
  
  
} Catch (ioexception e ){
  
  
// Todo auto-generated Catch Block
  
  
E. printstacktrace ();
  
  
} Finally {
  
  
Try {
  
  
Inputstream. Close ();
  
  
Outputstream. Flush ();
  
  
Outputstream. Close ();
  
  
Rs. Close ();
  
  
St. Close ();
  
  
Conn. Commit ();
  
  
Conn. Close ();
  
  
} Catch (ioexception e ){
  
  
// Todo auto-generated Catch Block
  
  
E. printstacktrace ();
  
  
} Catch (sqlexception e ){
  
  
// Todo auto-generated Catch Block
  
  
E. printstacktrace ();
  
  
}
  
  
}
  
  
}