Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Searching with jsp  RSS feed

 
shuzo monsoon
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, is there anyone who can help me with my problem?! At present I am creating an online CD shopping website. I have uaed Access for the database and created some simple pages using JSP (as i'm quite new to the language). What I'm trying to do at the moment is allow users to enter the title of a CD into an input text box and then search through the database to find records that match this input. I hav tried a few ways but nothing is working. The user types the inout into a form which gets passed to a 'search' page. ANY help will be appreciated. Thanks!
 
William Brogden
Author and all-around good cowpoke
Rancher
Posts: 13078
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't try to do everything at once. By involving a form, JSP and a database you have multiple things that can go wrong. Get the parts working separately - for example get your database access class working with search terms from a command line interface. Get your JSP working with fake database access stubs. When the parts all work, put them together.
Bill
[ February 11, 2004: Message edited by: William Brogden ]
 
shuzo monsoon
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well that's good advice and thanks but the connection to the database through the webpages works fine, I've managed to print out contents of the tables in my database on a webpage and can add products to a table and have even got code for the searching to 'compile' but it doesn't run correctly.
I'm unsure about assigning the input text from a user to an SQL query in order to match the records from the table.
Here's an example of the code I have written: If you notice anything particularly wrong, please let me know.
<%
String dsn = "mcaiysm3";
String dbURL = "jdbc dbc:" + dsn;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(dbURL,"","");

Statement stmt = con.createStatement();
String query = "select title from CD";
ResultSet rs = stmt.executeQuery(query);
String [] cdTitle = {"rs"};
String inputTitle = request.getParameter("searchTitle");
boolean found = false;
rs.close();
int i=0;
while(!found && i<cdTitle.length) {
if(cdTitle[i] == inputTitle) {
ResultSet res = stmt.executeQuery( "SELECT Title, Artist, Price FROM CD WHERE title='"+request.getParameter("searchTitle")+"'");
found = true;
i++;
while(res.next())
{
out.print("<tr><td>");

out.print(res.getString("Title"));
out.print("</td><td>");
out.print(res.getString("Artist"));
out.print("</td><td>");
out.print("�" + res.getFloat("Price"));
out.print("</td></tr>");

}
res.close();
}
}
}
catch(Exception ee)
{
ee.printStackTrace();
}
%>
 
Daniil Sosonkin
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe use a query to search using wild card LIKE:
String reqTitle = request.getParamert("searchTitle");
if (reqTitle == null || reqTitle.trim().equals(""))
throw new JspException("Empty search request.");
ResultSet rs = stmt.executeQuery("SELECT Title, Artist, Price FROM CD WHERE title LIKE '%" + reqTitle + "%'");
and then print those that were found:
while (rs.next())
{....}
NOTE: in MS Access wild card I think is done w/ *s instead of %s
 
danny liu
Ranch Hand
Posts: 185
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
that is a good try for you, you better wrapper the database part into a class, then you can use it over and over.
two things I wanna point out here:
a.
res.close();

you should release the connection too,that is an important resource.
b.
ee.printStackTrace();

this output the error message to the standard error stream, here it is
a system log. if you wanna show the message to the client, you should use
out.print(ee.getStackTrace());
 
shuzo monsoon
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I understand about putting all the functionality into a class but firstly I simply wanted to attempt searching and browsing the database separately. I have made a java class for holding the connections to the database but have not successfully implemented it yet. I'll get to that (I hope!) but this search thing is difficult as I've tried many different ways and none seem to work.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!