• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Search and Result Page

 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a search page that is dynamically populated from the database. It has 4 pull down menus:
Artist:
Genre:
Parental Advisory:
Location:

I need to construct a SQL statement from the selections and display the results on a results.jsp page. Do you know of any good resources etc. on how to do this?

Thanks
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm assuming that you are having no problems getting the values from the selections in your servlet, and that your question regards how to build up the appropriate SQL select statement from these values. (Correct me if wrong).

As such, I'm going to move this to the JDBC forum since the code to do so is pretty much JSP and Servlet independent.
[ June 03, 2004: Message edited by: Bear Bibeault ]
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Re-opened with new info:

Actually I have the SQL built already. I need some help in constructing the results look and feel. I am not sure how to display the results in the html tables and do the paging.

Thank for your help

 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Search this and the Servlets forum, as well as JDBC and perhaps Tomcat for info on this issue. It's been discussed many a time.

One of the easiest routes is to let the database do the work for you if it supports "limit". I know MySQL and PostgreSQL make it easy to slice and dice a result set into "pages".
 
Matt Hoffman
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok.. Here is what I am trying to do.. I am very new to OOP and Java, so please if there is something that I can do different, let me know.

1. Here is the search form that will post(or whatever) the criteria selected to a results.jsp page.

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>

<%@ page import ="artist.*" %>
<%@ page import ="genre.*" %>
<%@ page import ="location.*" %>
<%@ page import ="parental.*" %>
<%@ page import ="album.*" %>
<%@ page import ="cd.*" %>
<%@ page import ="java.util.*" %>
<%-- beans used in this JSP --%>
<jsp:useBean id = "artist" scope = "page"
class = "artist.ArtistBean" />
<jsp:useBean id = "artistData" scope = "request"
class = "artist.ArtistDataBean" />
<jsp:useBean id = "album" scope = "page"
class = "album.AlbumBean" />
<jsp:useBean id = "location" scope = "page"
class = "location.LocationBean" />
<jsp:useBean id = "locationData" scope = "request"
class = "location.LocationDataBean" />
<jsp:useBean id = "parental" scope = "page"
class = "parental.ParentalBean" />
<jsp:useBean id = "parentalData" scope = "request"
class = "parental.ParentalDataBean" />
<jsp:useBean id = "genre" scope = "page"
class = "genre.GenreBean" />
<jsp:useBean id = "genreData" scope = "request"
class = "genre.GenreDataBean" />
<jsp:useBean id = "cd" scope = "request"
class = "cd.SearchCDBean" />

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<jsp:setProperty name = "artist" property = "*" />
<jsp:setProperty name = "genre" property = "*" />
<jsp:setProperty name = "parental" property = "*" />
<jsp:setProperty name = "album" property = "*" />
<jsp:setProperty name = "location" property = "*" />

<form name="form1" method="post" action="resultsCD.jsp">
<p>Artist:
<select name="artist">
<option value="All" selected>All Artists</option>
<%
List artistList = artistData.getArtistList();
Iterator artistListIterator = artistList.iterator();
while ( artistListIterator.hasNext() ) {
artist = ( ArtistBean ) artistListIterator.next();
out.println("<OPTION value=\"" + artist.getArtistID() + "\">" + artist.getArtistName());
out.println("</OPTION>");
}
%>
</select>
</p>
<p>Genre:
<select name="genre">
<option value="All" selected>All Genres</option>
<%
List genreList = genreData.getGenreList();
Iterator genreListIterator = genreList.iterator();
while ( genreListIterator.hasNext() ) {
genre = ( GenreBean ) genreListIterator.next();
out.println("<OPTION value=\"" + genre.getGenreID() + "\">" + genre.getGenreName());
out.println("</OPTION>");
}
%>
</select>
</p>
<p>Parental Advisory:
<select name="advisory">
<option value="All" selected>All Advisorys</option>
<%
List parentalList = parentalData.getParentalList();
Iterator parentalListIterator = parentalList.iterator();
while ( parentalListIterator.hasNext() ) {
parental = ( ParentalBean ) parentalListIterator.next();
out.println("<OPTION value=\"" + parental.getParentalID() + "\">" + parental.getParentalName());
out.println("</OPTION>");
}
%>
</select>
</p>
<p>Location:
<select name="location">
<option value="All" selected>All Locations</option>
<%
List locationList = locationData.getLocationList();
Iterator locationListIterator = locationList.iterator();
while ( locationListIterator.hasNext() ) {
location = ( LocationBean ) locationListIterator.next();
out.println("<OPTION value=\"" + location.getLocationID() + "\">" + location.getLocationName());
out.println("</OPTION>");
}
%>
</select>
<br>
<br>
<input type="submit" name="Submit" value="Submit">
</p>
</form>
<p> </p>
<p> </p>
</body>
</html>

Here is the Results page that I really haven't done anything with..

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>
<%@ page import ="artist.*" %>
<%@ page import ="genre.*" %>
<%@ page import ="location.*" %>
<%@ page import ="parental.*" %>
<%@ page import ="album.*" %>
<%@ page import ="cd.*" %>
<%@ page import ="java.util.*" %>
<%-- beans used in this JSP --%>

<jsp:useBean id = "artistData" scope = "session"
class = "artist.ArtistDataBean" />
<jsp:useBean id = "album" scope = "page"
class = "album.AlbumBean" />
<jsp:useBean id = "location" scope = "page"
class = "location.LocationBean" />
<jsp:useBean id = "locationData" scope = "session"
class = "location.LocationDataBean" />
<jsp:useBean id = "parental" scope = "page"
class = "parental.ParentalBean" />
<jsp:useBean id = "parentalData" scope = "session"
class = "parental.ParentalDataBean" />
<jsp:useBean id = "genre" scope = "page"
class = "genre.GenreBean" />
<jsp:useBean id = "genreData" scope = "session"
class = "genre.GenreDataBean" />
<jsp:useBean id = "cd" scope = "session"
class = "cd.SearchCDBean" />



<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="75%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="19%">Artist</td>
<td width="21%">Album</td>
<td width="20%">Genre</td>
<td width="20%">Parental Advisory</td>
<td width="20%">Location</td>
</tr>
<tr>
<td height="22"></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>
</body>
</html>

Any suggestions or a good starting point for me?

Thanks
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic