HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

"Building Multimedia Repositories"
Vol 5 Issue 6, p.76

	

Listing 1: Inserting Word document in BLOB column using PL/SQL 

 The following code (Steps 2­5) inserts MyResume.doc in the résumé column of sam_emp table. 

Step 1: Create a directory object in Oracle. 
 Here¹s how to create a directory object called MY_FILES that represents C:\MY_DATA
  directory. 
 You must have the create directory privilege in Oracle. 

 create or replace directory 
  MY_FILES as 'C:\MY_DATA'; 

Step 2: Insert a row with an empty BLOB in your table and return the   locater. 
Step 3: Point to the Word file to be loaded from the directory created   in Step 1 using bfile data type. 
Step 4: Open the file and use the locater from Step 2 to insert the file. 
Step 5: Close the file and commit the transaction. 

declare 
    f_lob   bfile; 
    b_lob   blob; 

begin 

    insert into sam_emp(empno,ename,resume) 
 values ( 9001, 'Samir',empty_blob() ) 
 return résumé into b_lob; 

    f_lob := bfilename( 'MY_FILES', 'MyResume.doc' ); 
    dbms_lob.fileopen(f_lob, dbms_lob.file_readonly); 
    dbms_lob.loadfromfile 
 ( b_lob, f_lob, dbms_lob.getlength(f_lob) ); 
    dbms_lob.fileclose(f_lob); 

    commit; 

end; 
/ 

Listing 2: Building index to search documents 

   The steps listed below index all the Microsoft Word-formatted résumés stored in the
    résumé column to the sam_emp table. The résumés can then be searched using SQL. 
Step 1: Add a primary key to your table if it does not exist. To make empno primary key of
 the sam_emp table execute the following command. 

 alter table sam_emp add constraint 
 pk_sam_emp primary key(empno); 

Step 2: Get the privileges (ctxapp role) to create text indexes from   administrators. 
Step 3: Create the index with the appropriate filter object. Filters 
 determine how to extract text for document indexing from the 
 word processor, formatted documents as well as plain text. 
 See Oracle8i intermedia Text for complete list of filters. 

 create index ctx_doc_idx on sam_emp(résumé) 
 indextype is ctxsys.context parameters 
 ('filter CTXSYS.INSO_FILTER'); 

Listing 3: Searching documents using SQL and JDBC 

package package1; 
import javax.servlet.*; 
import javax.servlet.http.*; 
import java.io.*; 
import java.sql.*; 

/** 
 This servlet searches documents stored in an Oracle8i database repository using SQL and
  JDBC. The hit list is displayed in an HTML table with hyperlinks. JDK 1.1.7 and an Oracle
   Thin JDBC 1.22 compliant driver used. 

* 
* @author Samir Shah 
* @version 1.0 
**/ 
public class MyServletCtx extends HttpServlet{ 
   Connection cn; 

   public void init(ServletConfig parm1) 
  throws ServletException { 
    super.init( parm1); 
    try{ 
    DriverManager.registerDriver( 
 (new oracle.jdbc.driver.OracleDriver())); 
    cn =DriverManager.getConnection 
 ("jdbc:oracle:thin:@sshah:1521:o8i", 
          "scott", "tiger"); 
    } 
    catch (SQLException se){se.printStackTrace();} 
  } 

  public void doGet(HttpServletRequest req, 
     HttpServletResponse res) throws IOException{ 

      doPost(req,res); 
  } 

  public void doPost(HttpServletRequest req, 
    HttpServletResponse res) throws IOException{ 

    PrintWriter out = res.getWriter(); 
    res.setContentType("text/html"); 

    //The term to search in resume column 
    String term = req.getParameter("term"); 
    if (term == null) 
       term="security"; 

    out.print("<html>"); 
    out.print("<body>"); 
    out.print("<H1>Search Result</H1>"); 
    out.print("<table border=1 bgcolor=lightblue>"); 
    out.print("<tr><th>ID#</th><th>Name</th></tr>"); 
    out.print("<tr>"); 
    try{ 
     Statement st = cn.createStatement(); 

     //search the term in resume column using SQL 
     String query = 
         "Select empno,ename from sam_emp" + 
         " where contains(resume,'"+term+"')>0"; 

     ResultSet rs = st.executeQuery(query); 

      while (rs.next()){ 
        out.print("<td>"+ rs.getInt(1)+"</td>"); 
        out.print("<td>" + 
          "<A HREF=http://sshah:8080/" + 
          "servlet/MyServlet?term=" + 
          rs.getString(1) + 
          " target=Document>" + 
          rs.getString(2) + 
          "</A></td>"); 
        out.print("</tr>"); 
      } 

      out.print("</table>"); 
      out.print("</body>"); 
      out.print("</html>"); 
     }//try 
     catch (SQLException se){se.printStackTrace();} 

  } 
} 

Listing 4 

package package1; 

import javax.servlet.*; 
import javax.servlet.http.*; 
import java.sql.*; 
import java.io.*; 
import oracle.jdbc.driver.*; 
import oracle.sql.*;  //for oracle.sql.BLOB 

/** 

 This class reads the entire document from the résumé LOB column. It takes one parameter,
  term, to search a specific employee from the sam_emp table and returns the document
   stored in that row. 

* JDK 1.1.7, Oracle Thin JDBC 1.22 compliant driver 
* with Oracle type extension classes (oracle.sql) 
* 
* @author Samir Shah 
* @version 1.0 
**/ 
public class MyServlet extends HttpServlet{ 
  Connection cn; 

  public void doGet(HttpServletRequest req, 
  HttpServletResponse res) 
  { 
   try{ 
    doPost(req,res); 
   }catch (IOException ie){ie.printStackTrace();} 
  } 

  public void init(ServletConfig parm1) 
 throws ServletException 
  { 

    super.init( parm1); 
    try{ 
    DriverManager.registerDriver( 
 (new oracle.jdbc.driver.OracleDriver())); 
    cn =DriverManager.getConnection( 
 "jdbc:oracle:thin:@sshah:1521:o8i", 
        "scott", "tiger"); 
    } 
    catch (SQLException se){se.printStackTrace();} 
  } 

  public void doPost(HttpServletRequest req, 
    HttpServletResponse res) throws IOException 
  { 
     InputStream is=null; 
     oracle.sql.BLOB blob=null; 

     res.setContentType("application/msword"); 
     OutputStream os = res.getOutputStream(); 
     String term = req.getParameter("term"); 

     if (term==null) 
        term="9001"; 

     try{ 
     Statement st = cn.createStatement(); 
     ResultSet rs = st.executeQuery 
          ("Select resume from sam_emp"+ 
           " where empno="+term); 
  
      while (rs.next()){ 
          blob=((OracleResultSet)rs).getBLOB(1); 
          is=blob.getBinaryStream(); 
      } 

      int pos=0; 
      int length=0; 
      byte[] b = new byte[blob.getChunkSize()]; 

      while((length=is.read(b))!= -1){ 
       pos+=length; 
       os.write(b); 
      } 
     }//try 
     catch (SQLException se) 
 { 
   se.printStackTrace(); 
        } 
     finally { 
                is.close(); 
     } 

  } 

} 




 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.