DaniWeb Forum Index > Web Development > JSP

data retrieval trouble

Thread Solved

•

Join Date: Apr 2008

Posts: 11

Reputation: timon_zed is an unknown quantity at this point

Solved Threads: 0

timon_zed

Offline

Newbie Poster

data retrieval trouble

Sep 13th, 2009

Hi there,
I have a servlet which is meant to retrieve data from the database by compairing the number entered via a web form. The trouble is that, it only gives me the last record in the database and not any other record. I have 5 records in the database table and i want to be able to retrieve any one of them using the servlet. The code for the jsp and the servlet is below. Can anyone please point out where my mistake is and how I can resolve it.
Thanks in advance.

*the JSP Code*

 Help with Code Tags 
 JSP Syntax (Toggle Plain Text) 
<html>
<head>
<title>Check Your data</title>
<body>
<form name="check" method="post" action="checkAvailable">
        <table>
	  <tr>
		 <td><input name="command" type="hidden" value=""></td>
	  </tr>
	  <tr>
		<td>Product No: </td>
		<td><input name="prodNum" id="prodNum" type="text"></td>
	  </tr>
	<tr>
		<td><input name="check" type="submit" id="search" value="Check"></td>
		<td>&nbsp;</td>
	</tr>
	</table>
</form>  
</body>
</head>
</html>
<html>
<head>
<title>Check Your data</title>
<body>
<form name="check" method="post" action="checkAvailable">
        <table>
	  <tr>
		 <td><input name="command" type="hidden" value=""></td>
	  </tr>
	  <tr>
		<td>Product No: </td>
		<td><input name="prodNum" id="prodNum" type="text"></td>
	  </tr>
	<tr>
		<td><input name="check" type="submit" id="search" value="Check"></td>
		<td>&nbsp;</td>
	</tr>
	</table>
</form>  
</body>
</head>
</html>

the servlet

 Help with Code Tags 
 JSP Syntax (Toggle Plain Text) 
public class checkAvailable extends HttpServlet
{    
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException 
     {
 
	response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
	String ProdID = new String("");
        String ProdName = new String("");
        String PAmt = new String("");
 
        try 
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//the driver manager string
            Connection con = DriverManager.getConnection("jdbc:odbc:Args","admin","");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery ("SELECT ProductID,PName,Stock FROM Product");
 
            while(rs.next())
            {
                ProdID = rs.getString("ProductID");
                ProdName = rs.getString("PName");
                PAmt = rs.getString("Stock");
            }
            con.close();
            rs.close ();
            st.close ();
        }
        catch(Exception e)
        {
            System.out.print("Unable to find DB");
        }
        if(ProdID.equals(request.getParameter("prodNum")))
        {
            out.print("Prodcut ID : " +ProdID+ " Product Name : " +ProdName+ " Quantity"+PAmt);
        }
        else
        {
 
            out.print("Please Enter Correct Product Number");
 
        }
	out.close();
    }
}
public class checkAvailable extends HttpServlet
{    
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException 
     {
        
	response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
	String ProdID = new String("");
        String ProdName = new String("");
        String PAmt = new String("");
       
        try 
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//the driver manager string
            Connection con = DriverManager.getConnection("jdbc:odbc:Args","admin","");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery ("SELECT ProductID,PName,Stock FROM Product");
            
            while(rs.next())
            {
                ProdID = rs.getString("ProductID");
                ProdName = rs.getString("PName");
                PAmt = rs.getString("Stock");
            }
            con.close();
            rs.close ();
            st.close ();
        }
        catch(Exception e)
        {
            System.out.print("Unable to find DB");
        }
        if(ProdID.equals(request.getParameter("prodNum")))
        {
            out.print("Prodcut ID : " +ProdID+ " Product Name : " +ProdName+ " Quantity"+PAmt);
        }
        else
        {
            
            out.print("Please Enter Correct Product Number");
            
        }
	out.close();
    }
}

•

Join Date: Jun 2006

Posts: 7,761

Reputation: ~s.o.s~ has much to be proud of

Solved Threads: 491

~s.o.s~

Offline

Failure as a human

Re: data retrieval trouble

Sep 13th, 2009

You essentially throw away almost all the records read by reading the database entries in the ResultSet loop by not doing anything. If you want to compare the user entered value with the ones present in the database, do the comparison in the ResultSet loop. Set a flag and break out of the loop if the comparison succeeds.

Also a few points worth mentioning:

- Class names in Java should begin with an uppercase character; the same applies for Servlets.

- Since the JVM maintains a string pool, try not to use new String() *ever* in your code. Just use the string literals and let the JVM do the heavy lifting of deciding whether to use the existing pool instance or create a new one i.e.

 Help with Code Tags 
 JSP Syntax (Toggle Plain Text) 
// Instead of doing
String name = new String("");
 
// Do
String name = "";
 
// Or better yet; though depends on the particular use case
String name = null;
// Instead of doing
String name = new String("");

// Do
String name = "";

// Or better yet; though depends on the particular use case
String name = null;

- For production/real use, don't prefer a JDBC-ODBC bridge driver but a pure Java Type 4 driver. Even if you are trying out things, doing things the right way would be much preferred. Use a database like MySQL or Derby along with the type 4 drivers which come along with it.

- You can simplify things a *lot* by modifying the query instead of jumping through all those hoops yourself. Just add an additional WHERE clause in your query and check if the returned ResultSet has at least one record; if yes, then the product was found, if no, it wasn't.

- Use PreparedStatement for efficiency since databases and drivers are known to perform certain optimizations related to them. Something like:

 Help with Code Tags 
 JSP Syntax (Toggle Plain Text) 
String userProdId = request.getParameter("prodNum");
PreparedStatement pstmt =
  connection.prepareStatement("SELECT ProductID,PName,Stock FROM Product WHERE ProductID = ?");
pstmt.setString(1, userProdId);
ResultSet rs = pstmt.executeQuery();
if(rs.next()) {
  // a record was found; read the other data
} else {
  // no such product id exists
}
String userProdId = request.getParameter("prodNum");
PreparedStatement pstmt =
  connection.prepareStatement("SELECT ProductID,PName,Stock FROM Product WHERE ProductID = ?");
pstmt.setString(1, userProdId);
ResultSet rs = pstmt.executeQuery();
if(rs.next()) {
  // a record was found; read the other data
} else {
  // no such product id exists
}