Member Avatar for timon_zed

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*

<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

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();
    }
}
  • 2 Contributors
  • 2 Replies
  • 139 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by timon_zed
Member Avatar for ~s.o.s~

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.

// 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:

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
}
Edited by ~s.o.s~ because: n/a
Member Avatar for timon_zed

Alright thanx ama give it a whirl.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.