This appendix describes how to build a simple Web-search application using the CATSEARCH
index type, whether by writing your own code or using the Oracle Text Wizard. The following topics are covered:
The CTXCAT
index type 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).
The application can be produced by means of a catalog query application wizard, which produces the necessary code automatically. You can view and download the JSP application code, as well as the catalog query application wizard, at the Oracle Technology Network Web site:
http://www.oracle.com/technology/products/text
This Web site also has complete instructions on how to use the catalog query wizard.
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 Oracle Database server.
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 Web site 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.
This section lists the code used to build the example Web application. It includes the following files:
LOAD DATA INFILE 'loader.dat' INTO TABLE book_catalog REPLACE FIELDS TERMINATED BY ';' (id, title, publisher, price)
1; A History of Goats; SPINDRIFT BOOKS;50 2; Robust Recipes Inspired by Eating Too Much;SPINDRIFT BOOKS;28 3; Atlas of Greenland History; SPINDRIFT BOOKS; 35 4; Bed and Breakfast Guide to Greenland; SPINDRIFT BOOKS; 37 5; Quitting Your Job and Running Away; SPINDRIFT BOOKS;25 6; Best Noodle Shops of Omaha ;SPINDRIFT BOOKS; 28 7; Complete Book of Toes; SPINDRIFT BOOKS;16 8; Complete Idiot's Guide to Nuclear Technology;SPINDRIFT BOOKS; 28 9; Java Programming for Woodland Animals; LOW LIFE BOOK CO; 10 10; Emergency Surgery Tips and Tricks;SPOT-ON PUBLISHING;10 11; Programming with Your Eyes Shut; KLONDIKE BOOKS; 10 12; Forest Fires of North America, 1858-1882; CALAMITY BOOKS; 11 13; Spanish in Twelve Minutes; WRENCH BOOKS 11 14; Better Sex and Romance Through C++; CALAMITY BOOKS; 12 15; Oracle Internet Application Server Enterprise Edition; KANT BOOKS; 12 16; Oracle Internet Developer Suite; SPAMMUS BOOK CO;13 17; Telling the Truth to Your Pets; IBEX BOOKS INC; 13 18; Go Ask Alice's Restaurant;HUMMING BOOKS; 13 19; Life Begins at 93; CALAMITY BOOKS; 17 20; Dating While Drunk; BALLAST BOOKS; 14 21; The Second-to-Last Mohican; KLONDIKE BOOKS; 14 22; Eye of Horus; An Oracle of Ancient Egypt; BIG LITTLE BOOKS; 15 23; Introduction to Sitting Down; IBEX BOOKS INC; 15
<%@ 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="HUMMING BOOKS">HUMMING BOOKS <option value="WRENCH BOOKS">WRENCH BOOKS <option value="SPOT-ON PUBLISHING">SPOT-ON PUBLISHING <option value="SPINDRIFT BOOKS">SPINDRIFT BOOKS <option value="LOW LIFE BOOK CO">LOW LIFE BOOK CO <option value="KLONDIKE BOOKS">KLONDIKE BOOKS <option value="CALAMITY BOOKS">CALAMITY BOOKS <option value="IBEX BOOKS INC">IBEX BOOKS INC <option value="BIG LITTLE BOOKS">BIG LITTLE BOOKS </select> and price is <select name="v_op"> <option value="=">= <option value="<">< <option value=">">> </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="HUMMING BOOKS">HUMMING BOOKS <option value="WRENCH BOOKS">WRENCH BOOKS <option value="SPOT-ON PUBLISHING">SPOT-ON PUBLISHING <option value="SPINDRIFT BOOKS">SPINDRIFT BOOKS <option value="LOW LIFE BOOK CO">LOW LIFE BOOK CO <option value="KLONDIKE BOOKS">KLONDIKE BOOKS <option value="CALAMITY BOOKS">CALAMITY BOOKS <option value="IBEX BOOKS INC">IBEX BOOKS INC <option value="BIG LITTLE BOOKS">BIG LITTLE BOOKS </select> and price is <select name="v_op"> <option value="=">= <option value="<">< <option value=">">> </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 = ''CALAMITY BOOKS'' 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> <% } %>