Displaying results from the servlet to jsp web page

[rushhour]

Hi all,

I am having a little problem with displaying results from a search html form which forwards onto a servlet for processing, then the results are displayed in a jsp web page. The html form contains a

<select>

for a drop down list to select what term to search for e.g. a user enters a name into the text field, and selects "Username" from the drop down selection box.

The problem is the results are turning back null on the results page.

Here is my code:

// Servlet

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

public class QuickSearchServlet extends HttpServlet
{
	Connection conn; // variable to connect to the database
    Statement stmt;  // variable to execute SQL statements
	
	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 on the server called w1172769_0 with mysql username and password
			conn = DriverManager.getConnection( "jdbc:mysql://localhost/" + "memberdb?user=root&password=hotfuzzbun") ;
                                            
			stmt = conn.createStatement( );
		}
		catch (SQLException ex) 
		{ 
			System.out.println("SQLException: " + ex.getMessage());
		}
		
		// extract the field libraryResourceInputField from the results.jsp webpage
		String strLookupMember = request.getParameter( "lookupMemberField" );
		String strKeywordSelection = request.getParameter( "keywordSelection" ) ;
		String strDatabase = request.getParameter( "Database" ) ;
		
		// create new Searchbean
		LookupBean lookup = new LookupBean();
		
		// 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 libraryResourceInputField has been filled in from the quich search form
		if ( strLookupMember.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 ( strKeywordSelection == "All Keywords" )
				{
					try
					{
						int count = 0;
						String strQuery = "SELECT * from member_list WHERE fullname = '" + strLookupMember + "'" ;
						ResultSet rs = stmt.executeQuery( strQuery ) ;
						
						while ( rs.next( ) )
						{
							count++ ;
							lookup.setUsername( rs.getString( "username" ) ) ;
							lookup.setFullName( rs.getString( "fullname" ) ) ;
							lookup.setJobTitle( rs.getString( "jobtitle" ) ) ;
						}
						
						if ( count != 1 )
						{
							bValid = false ;
							strError = "Nothing matches your Quick Search criteria" ;
						}
						
						if ( stmt != null )
						{
							stmt.close( ) ;
						}
						
						if ( conn != null )
						{
							conn.close( ) ;
						}
					}
					catch ( SQLException ex )
					{
						System.out.println( "SQLException: " + ex.getMessage( ) ) ;
					}
				}
				else if ( strKeywordSelection == "Username" )
				{
					try
					{
						int count = 0;
						String strQuery = "SELECT * from member_list WHERE username = '" + strLookupMember + "'" ;
						ResultSet rs = stmt.executeQuery( strQuery ) ;
						
						while ( rs.next( ) )
						{
							count++ ;
							lookup.setUsername( rs.getString( "username" ) ) ;
							lookup.setFullName( rs.getString( "fullname" ) ) ;
							lookup.setJobTitle( rs.getString( "jobtitle" ) ) ;
						}
						
						if ( count != 1 )
						{
							bValid = false ;
							strError = "Nothing matches your Quick Search criteria" ;
						}
						
						if ( stmt != null )
						{
							stmt.close( ) ;
						}
						
						if ( conn != null )
						{
							conn.close( ) ;
						}
					}
					catch ( SQLException ex )
					{
						System.out.println( "SQLException: " + ex.getMessage( ) ) ;
					}
				}
				else if ( strKeywordSelection == "FullName" )
				{
					try
					{
						int count = 0;
						String strQuery = "SELECT * from member_list WHERE fullname = '" + strLookupMember + "'" ;
						ResultSet rs = stmt.executeQuery( strQuery ) ;
						
						while ( rs.next( ) )
						{
							count++ ;
							lookup.setUsername( rs.getString( "username" ) ) ;
							lookup.setFullName( rs.getString( "fullname" ) ) ;
							lookup.setJobTitle( rs.getString( "jobTitle" ) ) ;
						}
						
						if ( count != 1 )
						{
							bValid = false ;
							strError = "Nothing matches 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( "lookup", lookup ) ;
			
			RequestDispatcher dispatcher = getServletContext( ).getRequestDispatcher( "/results.jsp" ) ;
			
			dispatcher.forward( request, response ) ;
		}
		else
		{
			session.setAttribute( "error", strError ) ;
			
			RequestDispatcher dispatcher = getServletContext( ).getRequestDispatcher( "/results.jsp" ) ;
			
			dispatcher.forward( request, response ) ;
		}
	}
	
	public void doGet( HttpServletRequest request, HttpServletResponse response ) throws IOException, ServletException
	{
		doPost( request, response ) ;
	}
}

// LookupBean

package beans;

public class LookupBean
{
	private String username ;
	private String fullname ;
	private String jobtitle ;
	
	public LookupBean()
	{
		
	}
	
	public void setUsername( String str )
	{
		username = str ;
	}
	
	public String getUsername()
	{
		return username ;
	}
	
	public void setFullName( String str )
	{
		fullname = str ;
	}
	
	public String getFullName()
	{
		return fullname ;
	}
	
	public void setJobTitle( String str )
	{
		jobtitle = str ;
	}
	
	public String getJobTitle()
	{
		return jobtitle ;
	}
}

<!-- results.jsp -->
<%@ page language="java"%>
<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>Username</b></td>
	  <td></td>
	  <td><b>FullName</b></td>
	  <td></td>
	  <td><b>Job Title</b></td>
   </tr>
   <tr>
      <jsp:useBean id="lookup" class="beans.LookupBean" scope="session" />
      <td><jsp:getProperty name="lookup" property="username" /></td>
	  <td><font color="white">--------</font></td>
	  <td><jsp:getProperty name="lookup" property="fullname" /></td>
	  <td><font color="white">--------</font></td>
	  <td><jsp:getProperty name="lookup" property="jobtitle" /></td>
   </tr>
</table>
</body>
</html>

In the results page in the table all the fields in the table show null. Any reason why it is doing this? Also can you help me to get it to display the correct result?

Thanks.

[Tolls]

First don't give your Servlet attributes.
So remove the conn and stmt declarations.

Next, don;t concatenate your queries together like that.
Use a PreparedStatement. It is quite likely your problem is coming from this concatenation.
It's OK to build the basic query, but the actual query parameters should be bound ('?').

Finally, you're storing stuff in the session that are really only for that particular request (the LookupBean and the error string). Stick them in the request.

ETA: Oh, and don't do this:

System.out.println( "SQLException: " + ex.getMessage( ) ) ;

in catches. You're losing loads of info. Do ex.printStackTrace() instead.