B
CATSEARCH Query Application

This appendix describes how to build a simple web-search application using the CATSEARCH index type. The following topic is covered:

CATSEARCH Web Query Application Overview

The CTXCAT indextype is well suited for merchandise catalogs that have short descriptive text fragments and associated structured data. This appendix describes how to build a browser based bookstore catalog that users can search to find titles and prices.

This application is written in Java Server Pages (JSP).

You can view and download application code at the Oracle Technology Network web site:

http://otn.oracle.com/products/text

The JSP Web Application

This application is based on Java Server pages and has the following requirements:

Your Oracle database (version 8.1.7 or higher) is up and running.
You have a web server such as Apache up and running and correctly configured to send requests to the Oracle9i server.

Building the JSP Web Application

This application models an online bookstore where you can look up book titles and prices.

Step 1 Create Your Table

You must create the table to store book information such as title, publisher, and price. From SQL*Plus:

sqlplus>create table book_catalog (
          id        numeric,
          title     varchar2(80),
          publisher varchar2(25),
          price     numeric )

Step 2 Load data using SQL*Loader

You load the book data from the operating system command line with SQL*Loader:

sqlldr userid=ctxdemo/ctxdemo control=loader.ctl

Step 3 Create index set

You can create the index set from SQL*Plus:

sqlplus>begin
          ctx_ddl.create_index_set('bookset');
          ctx_ddl.add_index('bookset','price');
          ctx_ddl.add_index('bookset','publisher');
        end;

Step 4 Index creation

You can create the ctxcat index from SQL*Plus as follows:

sqlplus>create index book_idx on book_catalog (title) 
        indextype is ctxsys.ctxcat
        parameters('index set bookset');

Step 5 Try a simple search using catsearch

You can test the newly created index in SQL*Plus as follows:

sqlplus>select id, title from book_catalog 
        where catsearch(title,'Java','price > 10 order by price') > 0

Step 6 Copy the catalogSearch.jsp file to your website jsp directory.

When you do so, you can access the application from a browser. The URL should be http://localhost:port/path/catalogSearch.jsp

The application displays a query entry box in your browser and returns the query results as a list of HTML links. See Figure B-1, "Screen shot of Web Query Application".

Figure B-1 Screen shot of Web Query Application

Text description of catalogj.gif follows.

Text description of the illustration catalogj.gif

JSP Sample Code

This section lists the code used to build the example web application. It includes the following files:

loader.ctl
loader.dat
catalogSearch.jsp

See Also:
http://otn.oracle.com/products/text/

loader.ctl

      INFILE 'loader.dat'
        INTO TABLE book_catalog 
        REPLACE 
        FIELDS TERMINATED BY ';'
        (id, title, publisher, price)

loader.dat

1,A History of the Sciences, MACMILLAN REFERENCE,50
2,Robust Recipes Inspired by the Rustic Foods of France, Italy, and 
America,MACMILLAN REFERENCE,28
3, Atlas of Irish History, MACMILLAN REFERENCE, 35
4, Bed and Breakfast Guide: Arizona, New Mexico and Texas, MACMILLAN REFERENCE, 
37
5, Before You Say "I Quit"; A Guide to Making Successful Job Transitions, 
MACMILLAN REFERENCE,25
6,Born to Shop Hong Kong; The Ultimate Travel Guide for Discriminating 
Shoppers,MACMILLAN REFERENCE, 28
7,Complete Book of Sauces, MACMILLAN REFERENCE,16
8,Complete Idiot's Guide to American History,MACMILLAN REFERENCE, 28
9,Advanced Java Programming, with CD-ROM, MCGRAW HILL BOOK CO, 10
10, Java Master Reference With CDROM,IDG BOOKS WORLDWIDE,10
11, Oracle Performance Tuning Tips & Techniques, OSBORNE, 10
12, Core Java 1.1; Fundamentals, with CDROM, PRENTICE HALL, 11
13, Lady Oracle, DOUBLEDAY & CO 11
14, Core Java 1.1; Advanced Features, with CDROM, PRENTICE HALL, 12
15, Discover Java With Cd, IDG BOOKS WORLDWIDE, 12
16, CORBA & Java; Where Distributed Objects Meet the Web With CDROM, MCGRAW HILL 
BOOK CO,13
17, Java 1.1 Developer's Handbook; With CDROM With CDROM, SYBEX INC, 13
18, Java with Borland C++,AP PROFESSIONAL,    13
19, Just Java 1.1,  PRENTICE HALL,   17
20, Internet Programming; An Introduction to Object Oriented Programming with 
Java,  ADDISON WESLEY PUB CO INC,  14
21, Oracle Certified Professional DBA Certification Exam Guide With CDROM, 
OSBORNE,  14
22, Eye of Horus; An Oracle of Ancient Egypt, THOMAS DUNNE BOOKS,     15
23, Java 1.1 Certification Study Guide With CDROM, SYBEX INC,  15

catalogSearch.jsp

<%@ page import="java.sql.* , oracle.jsp.dbutil.*" %>
<jsp:useBean id="name" class="oracle.jsp.jml.JmlString" scope="request" >
<jsp:setProperty name="name" property="value" param="v_query" />
</jsp:useBean>

<%
  String connStr="jdbc:oracle:thin:@machine-domain-name:1521:betadev";

  java.util.Properties info = new java.util.Properties();

  Connection conn = null;
  ResultSet  rset = null;
  Statement  stmt = null;

  

       if (name.isEmpty() ) { 

%>
           <html>
             <title>Catalog Search</title>
             <body>
             <center>
               <form method=post>
               Search for book title:
               <input type=text name="v_query" size=10>
               where publisher is
               <select name="v_publisher">
                  <option value="ADDISON WESLEY">ADDISON WESLEY
                  <option value="AP PROFESSIONAL">AP PROFESSIONAL
                  <option value="DOUBLEDAY & CO">DOUBLEDAY & CO
                  <option value="IDG BOOKS WORLDWIDE">IDG BOOKS WORLDWIDE
                  <option value="MACMILLAN REFERENCE">MACMILLAN REFERENCE
                  <option value="MCGRAW HILL BOOK CO">MCGRAW HILL BOOK CO
                  <option value="OSBORNE">OSBORNE
                  <option value="PRENTICE HALL">PRENTICE HALL
                  <option value="SYBEX INC">SYBEX INC
                  <option value="THOMAS DUNNE BOOKS">THOMAS DUNNE BOOKS
               </select>
               and price is 
               <select name="v_op">
                 <option value="=">=
                 <option value="&lt;">&lt;
                 <option value="&gt;">&gt;
               </select>
               <input type=text name="v_price" size=2>
               <input type=submit value="Search">
               </form>
             </center>
             <hr>
             </body>
           </html>

<%
      }
      else {

         String v_query = request.getParameter("v_query");
    String v_publisher = request.getParameter("v_publisher");
         String v_price = request.getParameter("v_price");
         String v_op    = request.getParameter("v_op");
%>

         <html>
           <title>Catalog Search</title>
           <body>
           <center>
            <form method=post action="catalogSearch.jsp">
            Search for book title:
            <input type=text name="v_query" value= 
            <%= v_query %>
            size=10>
            where publisher is
            <select name="v_publisher">
                  <option value="ADDISON WESLEY">ADDISON WESLEY
                  <option value="AP PROFESSIONAL">AP PROFESSIONAL
                  <option value="DOUBLEDAY & CO">DOUBLEDAY & CO
                  <option value="IDG BOOKS WORLDWIDE">IDG BOOKS WORLDWIDE
                  <option value="MACMILLAN REFERENCE">MACMILLAN REFERENCE
                  <option value="MCGRAW HILL BOOK CO">MCGRAW HILL BOOK CO
                  <option value="OSBORNE">OSBORNE
                  <option value="PRENTICE HALL">PRENTICE HALL
                  <option value="SYBEX INC">SYBEX INC
                  <option value="THOMAS DUNNE BOOKS">THOMAS DUNNE BOOKS
            </select>
            and price is 
            <select name="v_op">
               <option value="=">=
               <option value="&lt;">&lt;
               <option value="&gt;">&gt;
            </select>
            <input type=text name="v_price" value=
            <%= v_price %> size=2>
            <input type=submit value="Search">
            </form>
            </center>
          
<%
     try {

       DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver() );
       info.put ("user", "ctxdemo");
       info.put ("password","ctxdemo");
       conn = DriverManager.getConnection(connStr,info);

         stmt = conn.createStatement();
         String theQuery = request.getParameter("v_query");
         String thePrice = request.getParameter("v_price");

 // select id,title 
 // from book_catalog 
 // where catsearch (title,'Java','price >10 order by price') > 0

 // select title 
 // from book_catalog 
 // where catsearch(title,'Java','publisher = ''PRENTICE HALL'' and price < 40 
order by price' )>0

         String myQuery = "select title, publisher, price from book_catalog 
where catsearch(title, '"+theQuery+"', 'publisher = ''"+v_publisher+"'' and 
price "+v_op+thePrice+" order by price' ) > 0";
         rset = stmt.executeQuery(myQuery);

         String color = "ffffff";

         String myTitle = null;
         String myPublisher = null;
         int myPrice = 0;
         int items = 0;

         while (rset.next()) {
            myTitle     = (String)rset.getString(1);
       myPublisher = (String)rset.getString(2);
            myPrice     = (int)rset.getInt(3);
            items++;

            if (items == 1) {
%>
               <center>
                  <table border="0">
                     <tr bgcolor="#6699CC">
                       <th>Title</th>
              <th>Publisher</th>
              <th>Price</th>
                     </tr>
<%
            }
%> 
            <tr bgcolor="#<%= color %>">
             <td> <%= myTitle %></td>
             <td> <%= myPublisher %></td>
        <td> $<%= myPrice %></td>
            </tr>
<%
            if (color.compareTo("ffffff") == 0)
               color = "eeeeee";
             else
               color = "ffffff";

      }
      
   } catch (SQLException e) {

%>

      <b>Error: </b> <%= e %><p>

<%

  } finally {
       if (conn != null) conn.close();
       if (stmt != null) stmt.close();
       if (rset != null) rset.close();
   }
   
%>
    </table>
    </center>
   </body>
   </html>
<%
 }
%>

B CATSEARCH Query Application