Quick Search Results page cant put multiple results into a table

[John Turner]

Hi all,

I have a little problem regarding my quick results search page created with jsp and jstl. What is wrong is that after I submit my quick search jsp form and the servlet processes the request, the results page is not appearing, only stating that no books match my search criteria. So it is not forwarding to the results page, even though I know there are multiple results i nthe database which should be output to a table. Here is my code:

// search servlet

import java.io.* ; 
import java.util.*;
import javax.servlet.* ;
import javax.servlet.http.* ;
import java.sql.*;
import beans.*;

public class SearchServlet extends HttpServlet
{
	Connection conn; // variable to connect to the database
    Statement stmt;  // variable to execute SQL statements
	private ArrayList<Bean> list;
	
	public void init( ) throws ServletException
	{
		try
		{
			Class.forName( "com.mysql.jdbc.Driver" ).newInstance(); // load the JDBC mysql driver
            
		} 
		catch (Exception ex) 
		{ 
			System.out.println( "Exception is: " + ex.toString( ));
		}
	}
	
	public void doPost( HttpServletRequest request, HttpServletResponse response ) throws IOException, ServletException
	{
		try 
		{
			// connect to the database 
			conn = DriverManager.getConnection( "jdbc:mysql://localhost/" + "dbtwo?user=root&password=pwd") ;
                                            
			stmt = conn.createStatement( );
		}
		catch (SQLException ex) 
		{ 
			System.out.println("SQLException: " + ex.getMessage());
		}
		
		// extract the field
		String strInputField = request.getParameter( "InputField" );
		String strKeyword = request.getParameter( "keyword" ) ;
		String strDatabase = request.getParameter( "Database" ) ;
		
		// create new bean
		Bean search = new Bean();
		
		// create a boolean for validation
		boolean bValid = true;
		
		// create a new http session
		HttpSession session = request.getSession( ); 
		
		// used to hold the error message displayed to a user
        String strError = (String) session.getAttribute( "error" );
		
		// test if InputField has been filled in from the quich search form
		if ( strInputField.length( ) == 0 )
		{
			strError = "Try again - Please Enter a Search Term"; // provide appropriate error message
            bValid = false;
		}
		
		// lookup the database for the search item
		if ( bValid )
		{		
				if (  strKeyword.equals( "All" ) )
				{
					try
					{
						int count = 0;
						String strQuery = "SELECT * from catalogue WHERE title LIKE '%" + strInputField + "%'" ;
						ResultSet rs = stmt.executeQuery( strQuery ) ;
						
						while ( rs.next( ) )
						{
							list = new ArrayList<Bean>();
							count++ ;
							search.setAuthor( rs.getString( "author" ) ) ;
							search.setTitle( rs.getString( "title" ) ) ;
							search.setDescription( rs.getString( "description" ) ) ;
							search.setEdition( rs.getString( "edition" ) ) ;
							search.setPublisher( rs.getString( "publisher" ) ) ;
							list.add(search);
						}
						
						if ( count != 1 )
						{
							bValid = false ;
							strError = "No Resources match your Quick Search criteria" ;
						}
						
						if ( stmt != null )
						{
							stmt.close( ) ;
						}
						
						if ( conn != null )
						{
							conn.close( ) ;
						}
					}
					catch ( SQLException ex )
					{
						System.out.println( "SQLException: " + ex.getMessage( ) ) ;
					}
				}
				
				}
				
				}
		
		if ( bValid )
		{
			
			session.setAttribute( "list", list ) ;
			
			RequestDispatcher dispatcher = getServletContext( ).getRequestDispatcher( "/results.jsp" ) ;
			
			dispatcher.forward( request, response ) ;
		}
		else
		{
			session.setAttribute( "error", strError ) ;
			
			RequestDispatcher dispatcher = getServletContext( ).getRequestDispatcher( "/search.jsp" ) ;
			
			dispatcher.forward( request, response ) ;
		}
	}
	
	public void doGet( HttpServletRequest request, HttpServletResponse response ) throws IOException, ServletException
	{
		doPost( request, response ) ;
	}
}

// Bean.java

package beans;

public class SearchBean
{
	private String author ;
	private String title ;
	private String description ;
	private String edition ;
	private String publisher ;
	private String isbn ;
	private String avaliability ;
	private String material_type ;
	private String url ;
	
	public SearchBean()
	{
		
	}
	
	public void setAuthor( String str )
	{
		author = str ;
	}
	
	public String getAuthor()
	{
		return author ;
	}
	
	public void setTitle( String str )
	{
		title = str ;
	}
	
	public String getTitle()
	{
		return title ;
	}
	
	public void setDescription( String str )
	{
		description = str ;
	}
	
	public String getDescription()
	{
		return description ;
	}
	
	public void setEdition( String str )
	{
		edition = str ;
	}
	
	public String getEdition()
	{
		return edition ;
	}
	
	public void setPublisher( String str )
	{
		publisher = str ;
	}
	
	public String getPublisher()
	{
		return publisher ;
	}
}

<%@ page language="java"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
<title>Results Page</title>
</head>
<body>
<h3>Results Page</h3>
<h3><font color="red">
         <% String e = (String) session.getAttribute( "error" );
				if ( e != null )
                { %>
				<font color="red">
				<% out.print( e );
				} %>
   </font></h3>
<table>
   <tr>
      <td><b>Author</b></td>
	  <td></td>
	  <td><b>Title</b></td>
	  <td></td>
	  <td><b>Edition</b></td>
	  <td></td>
	  <td><b>Publisher</b></td>
   </tr>
   <c:forEach var="list" items="${list}">
   <tr>
      <td>${list.author}</td>
	  <td></td>
	  <td>${list.title}</td>
	  <td></td>
	  <td>${list.edition}</td>
	  <td></td>
	  <td>${list.publisher}</td>
   </tr>
   </c:forEach>
</table>
</body>
</html>

Again to clarify, I know my database has more than one search term such as different java books, but when I select "All" from my search form, and submit this, instead of forwarding this to the results page, it gives me an error that no resources match my search criteria. However, when I search for a specific title, the book will be displayed in the results table. How can I get my code to actually display all the results that match the search term provided by me?

Thanks if you can help.

[Paul Clapham]

You're asking why your query returns zero records? That sure looks like a JDBC question to me. Moving...

[Jeanne Boyarsky]

I see two things that are odd.
1) It looks like you only run the query if "all" is selcted? This seems like it isn't what you want.
2) The actual query looks for a title. If you are looking for "All", you'd need to omit the where clause.

String strQuery = "SELECT * from catalogue WHERE title LIKE '%" + strInputField + "%'" ;