将图片从Oracle数据库中读出显示在JSP页面

One of my recent assignments was to build a web page that needed to show images that are stored in a database.

 

 

CREATE TABLE PHOTOS (   IMAGEID   NUMBER(10),   IMAGE     BLOB ) /

CREATE OR REPLACE PROCEDURE Display_Image(p_id NUMBER) IS     Photo BLOB     v_amt NUMBER DEFAULT 4096;     v_off NUMBER DEFAULT 1;     v_raw RAW(4096);   BEGIN     -- Get the blob image     SELECT image     INTO   Photo     FROM   PHOTOS     WHERE  IMAGEID = p_id;     owa_util.mime_header('images/gif');      BEGIN       LOOP         -- Read the BLOB         dbms_lob.READ(Photo, v_amt, v_off, v_raw);         -- Display image         htp.prn(utl_raw.cast_to_varchar2(v_raw));         v_off := v_off + v_amt;         v_amt := 4096;       END LOOP;       dbms_lob.CLOSE(Photo);     EXCEPTION       WHEN NO_DATA_FOUND THEN         NULL;     END;   END; /

The web page could be called with the following URL and did what I was asked to do

img src="http://machine:port/pls/myapp/display_image?p_id=12" width="115" border="0"

 

This works like a charm but has a caveat that I discovered when presenting it to the network department. The network department didn’t like the idea of exposing the database server to the Internet, which indeed is considerably unsafe.

Back to the whiteboard, I thought of using Web Service. This approach just didn’t feel right and appeared to be too complex for this little solution to build. Eventually I decided to write a JavaServer Page to do the job.

 The Java class to stream the image from the database column

package image; import java.sql.*; import java.io.*; import java.util.*; import oracle.jdbc.*; import oracle.sql.*; public class images {   /*-------------------------    *   Get the Blob image    *------------------------*/   public static byte[] getPhoto (OracleConnection conn, int iNumPhoto)        throws Exception, SQLException   {     String req = "" ;     Blob img ;     byte[] imgData = null ;     Statement stmt = conn.createStatement ();         // Query     req = "Select image From IMAGES Where ImageID = " + iNumPhoto ;         ResultSet rset  = stmt.executeQuery ( req );         while (rset.next ())     {          img = rset.getBlob(1);       imgData = img.getBytes(1,(int)img.length());     }            rset.close();     stmt.close();         return imgData ;   }   }

 The JavaServer Page includes the bean so its methods can be accessed in the JSP page using scriplets and “photo” as a named bean reference

 

<%@ page import = "image.*" %> <%@ page import = "java.io.*" %> <%@ page import = "oracle.jdbc.OracleConnection" %> <jsp:useBean id="photo" class="image.images" scope="session" /> <%     int iNumPhoto ;   oracle.jdbc.driver.OracleConnection conn = null;     if ( request.getParameter("imgID") != null )   {        iNumPhoto = Integer.parseInt(request.getParameter("imgID")) ;         try     {         conn = …………;        conn.setAutoCommit (false);           // get the image from the database        byte[] imgData = photo.getPhoto( conn, iNumPhoto  ) ;          // display the image        response.setContentType("image/gif");        OutputStream o = response.getOutputStream();        o.write(imgData);        o.flush();        o.close();     }     catch (Exception e)     {       e.printStackTrace();       throw e;     }     finally     {       … Close the connexion … ;     }    } %>

To display the image on the web, I now use the following image URL

img src="image.jsp?imgID=12" width="115" border="0"