<%@ 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>
<%
}
%>