?
Solved

Search by group category description

Posted on 2011-04-25
14
Medium Priority
?
292 Views
Last Modified: 2012-05-11
I would like to write a query to allow the user of my library website written using JSP to search for a book in a different of ways....I have finished to write code to allow to user to search the book by title or author
<sql:query var="booksList" scope="request"  dataSource = "${dB}">
	SELECT * FROM Books
     WHERE    BookTitle LIKE ?
       AND    Author  LIKE ?
       AND    Edition  LIKE ?
	   AND    Year LIKE ?
       AND    categoryID LIKE ?
	<sql:param value="%${param.BookTitle}%" />
	<sql:param value="%${param.Author}%" />
	<sql:param value="%${param.Edition}%" />
	<sql:param value="%${param.Year}%" />
	<sql:param value="%${param.categoryID}%" />
</sql:query>

Open in new window

now the problem is to write code to search by bookcategory( basically i have to query two tables linked by categoryID and should display books titles and authors...

The two tables look llike this ( in access)...

table1 Books
===========

BookTitle      Author      Edition     categoryID

xxxxxx        xxxxx        xxx           001


Table2 Categories

CategoryID      CategoryDescription

001                  Health

The user will be asked to enter categoryDescription of books they would like to search for...The output will be like

BookTitle   Author
xxxxx       xxxxx
0
Comment
Question by:ozzyfanta
  • 7
  • 7
14 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 35460696

It doesn't seem that for this kind of result ou even ned to link the tables,
as you can just use query:

<sql:query var="booksList" scope="request"  dataSource = "${dB}">
SELECT BookTitle, Author  FROM Books
     WHERE      categoryID LIKE  ?
<sql:param value="%${param.categoryID}%" />

Do you really like to match just substring with "like"
or direct equality  with

   ...categoryID = ?
  and
<sql:param value="${param.categoryID}" />

Perhaps equality should be more appropriate for categoryID query





0
 
LVL 47

Expert Comment

by:for_yan
ID: 35460729
If you want to join tables this is not difficult either, for example:

<sql:query var="booksList" scope="request"  dataSource = "${dB}">
SELECT Books.BookTitle, Categories.CategoryDescription  FROM Books, Categories
     WHERE    BookTitle LIKE ?
and Book.CategoryID = Categories.CategoriID
<sql:param value="%${param.BookTitle}%" />
<sql:param value="${param.categoryID}" />

This is necessary when you need infgormation from
dufferent tables - e.g.,  BookTitle from one table and
CategoryDescription from another


     
0
 

Author Comment

by:ozzyfanta
ID: 35460820
it is giving me the following error:

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException:

SELECT Books.BookTitle, Categories.CategoryDescription  FROM Books, Categories
     WHERE    BookTitle LIKE ?
AND Books.CategoryID = Categories.CategoriID

: Unexpected exception : 1
      org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:536)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:410)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 47

Expert Comment

by:for_yan
ID: 35460860
Perhpas you should look a little bit below in your exceptioon stack trace
where it refers to the  code written by you - not library classes - this stack trace does not say where in
your code the problem happend

Did you assign parameters caorrectly - you don't show it in this snippet?

Do you have access to SQL propmt directly to yoiur database - first
try to run the query you want to execute form your JSP directly
from SQL prompt command line

0
 

Author Comment

by:ozzyfanta
ID: 35460871
This is the form i have created to get the input from the user ( the input should be a categoryDescription like health,technolgy,etc...)
<html>
<head>
<title>Search by group category</title>
</head>
<body>
Please enter information
<form action="findCategory.jsp" method="get">
<table>
<tr>
<td>Book Description Category:</td>
<td><input type="text" name="CategoryDescription"></td>
</tr>
<tr>
<tr>
<td><input type="submit"  name="submitButton" /></td>
<td><input type="reset"  name="resetButton" /></td>     
</tr>
</table>
</body>
</html>

Open in new window


and the page where i am struggling is called findCategory.jsp..(it will query the databse and forward the result to listCategory.jsp)

the result of the query will be forwarded to this page called listCategory.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<html>
<head>
<title>Search Result</title>
</head>
<body>
<c:choose>
<c:when test="${CatList.rowCount == 0}">
Sorry, no book were found.
</c:when>
<c:otherwise>
The following books were found:
<p>
<table border="1">
<th>BookTitle</th>
<th>CategoryID</th>
<c:forEach items="${CatList.rows}" var="rows">
<tr>
<td><c:out value="${rows.BookTitle}" /></td>
<td><c:out value="${rows.CategoryID}" /></td>
</tr>
</c:forEach>
</table>
<td><a href="category.html">Search by book category</a></td>
</c:otherwise>
</c:choose>
</body>
</html>

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
ID: 35460953
You say that your original query worked for you ?

1) So show the corresponding snippet which you created for this new query.

2) Then look at the excpetion stack trace below of waht you posted - do you see the line referrring to your code,
not standard library code

3) Then try to execute the query as you want it from SQL command prompt

Post the results of 1),2) ,3)
0
 

Author Comment

by:ozzyfanta
ID: 35461033
in the code you posted for me, i dont think you are taking into accounts that the user input only the CategoryDescription...
You mean i should post the result of my first query? when the user input the BookTitle or Author?

This is the code:
search.html
<html>
<head>
<title>Search in user Database</title>
</head>
<body>
Please enter information
<form action="find.jsp" method="get">
<table>
<tr>
<td>Book Title:</td>
<td><input type="text" name="BookTitle"></td>
</tr>
<tr>
<td>Author:</td>
<td><input type="text" name="Author"></td>
</tr>
<tr>
<td><input type="submit"  name="submitButton" /></td>     
<td><input type="reset"  name="resetButton" /></td>     
</tr>
</table>
</body>
</html>

Open in new window


The code to query the database called find.jsp
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>

<sql:setDataSource
      var          = "dB"
      scope        = "session" 
      driver       = "sun.jdbc.odbc.JdbcOdbcDriver" 
      url          = "jdbc:odbc:dB"
 /> 

<sql:query var="booksList" scope="request"  dataSource = "${dB}">
	SELECT * FROM Books
     WHERE    BookTitle LIKE ?
       AND    Author  LIKE ?
       AND    Edition  LIKE ?
	   AND    Year LIKE ?
       AND    categoryID LIKE ?
	<sql:param value="%${param.BookTitle}%" />
	<sql:param value="%${param.Author}%" />
	<sql:param value="%${param.Edition}%" />
	<sql:param value="%${param.Year}%" />
	<sql:param value="%${param.categoryID}%" />
</sql:query>
<jsp:forward page="list.jsp" />

Open in new window


and the last one, the display the result of the query, called list.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<html>
<head>
<title>Search Result</title>
</head>
<body>
<c:choose>
<c:when test="${booksList.rowCount == 0}">
Sorry, no book were found.
</c:when>
<c:otherwise>
The following books were found:
<p>
<table border="1">
<th>BookTitle</th>
<th>Author</th>
<c:forEach items="${booksList.rows}" var="rows">
<tr>
<td><c:out value="${rows.BookTitle}" /></td>
<td><c:out value="${rows.Author}" /></td>
</tr>
</c:forEach>
</table>
<td><a href="category.html">Search by book category</a></td>
</c:otherwise>
</c:choose>
</body>
</html>

Open in new window


Now the probleme lie when i want to search using the categoryDescription in Categories table and display only the Booktitle,Author of the Books table..
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35461372
Well, we don't seem to understand each other.

Look at your original question.
You stated that you have one query working
You asked how to create another query.

Your question was a little bit strange ffrom the beginning because
you stated that you want to search in two tables by category,
but you wanted back only BookTitle and Awthor - both fields
are coming from one of the tables, so - no reason to
search in two tables and to join them to have such output

Nevertheless, I suggested the query how to select
by category from this one table and
also in my second posting  showed how to join tables, assuming
that you also wanted to select the
categori description which is indeed in another table

Now I'd think you'd tell me how these new queries work.
Instead you are posting lots of codes which do not seem to have
connection to your original question, at least that connection is not obvious.
 Please, read attentively
the trail from the very beginning and try to formulate
what it is that you want.
Then I can try to think how to help you.

0
 

Author Comment

by:ozzyfanta
ID: 35461373
In the first code you posted, we could clearly see that you did not take account of the input...the user will input the CategoryDescription( like Health) and the output will go first to the second take to get the corresponding CategoryID and display the BookTitle and Author that correspond to that CategoryID in the first table(table 1 books)...I will post shortly the error i am getting from the second code you posted...Thanks
0
 

Author Comment

by:ozzyfanta
ID: 35461393
I think there is no need to do that cos i presume that you misunderstood my question...thanks
0
 

Author Comment

by:ozzyfanta
ID: 35461528
I meant in table Categories and the row is called CategoryDescription ...
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35461719

Indeed, sorry I misread one word in your question.
idn't understand that they provide Category Description

Then I think you should do it like that, with user providing substring of
Category Descriptin

<sql:query var="booksList" scope="request"  dataSource = "${dB}">
SELECT Books.BookTitle, Books.Awthor  FROM Books, Categories
     WHERE    
and Books.CategoryID = Categories.CategoriID
and Category.CategoryDescription like ?
<sql:param value="%${param.CategoryDescription}%" />


0
 
LVL 47

Accepted Solution

by:
for_yan earned 2000 total points
ID: 35461727

Misspelled Author in the post above.
This is corrected:

<sql:query var="booksList" scope="request"  dataSource = "${dB}">
SELECT Books.BookTitle, Books.Author  FROM Books, Categories
     WHERE    
and Books.CategoryID = Categories.CategoriID
and Category.CategoryDescription like ?
<sql:param value="%${param.CategoryDescription}%" />

0
 

Author Closing Comment

by:ozzyfanta
ID: 35463288
Thanks again ...The solution was excellent
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question