Not able to retrieve data with PreparedStatement

Question

I have written some code which will retrieve data from database . If I have giving hard coded value then it is working fine but I want to do dynamic then it not fetching data from db.

Coding

UserLogin.java

    PrintWriter out=response.getWriter();
    String firstName = request.getParameter("firstName"); 
    String password = request.getParameter("password"); 
    Connection con= DBConnection.getConnection();   
    String sql = "select count(*) from hmssignupdetails where firstname=?  and password=?";
    PreparedStatement ps;
    try {
        ps = con.prepareStatement(sql);
         ps.setString(1, firstName);
         ps.setString(2, password);
         ResultSet rs=ps.executeQuery();
         if(rs.next()){
            int i= rs.getInt(1);
             if(i==1){
                 request.setAttribute("err",firstName );
                 System.out.println("Details");
                 /*System.out.println(rs.getInt(1));
                 System.out.println(rs.getString(2));*/

                 response.sendRedirect("CustomerDetail.jsp");
             }
             else{
                 System.out.println("Sorry UserName or Password Error!");  
                 RequestDispatcher rd=request.getRequestDispatcher("UserLogin.jsp");  
                 request.setAttribute("err", "Invalid login");
                 rd.include(request, response);  
             }

         }

how to set the value of username and password and I need to get the value in below jsp page . Please suggest ?

CustomerDetail.jsp

    <%
    String firstName = request.getParameter("firstName");
    String password = request.getParameter("password");
    Connection con = DBConnection.getConnection();
    //String sql = "select * from HMSSIGNUPDETAILS";
    /* Statement stmt = con.createStatement();
      ResultSet rset = stmt
    .executeQuery("select * from HMSSIGNUPDETAILS where firstname='Malay' and password='123'");  */
     System.out.println("fetching data");
      String sql = "select * from HMSSIGNUPDETAILS where firstname=? and password=?";
    PreparedStatement ps;
    ps = con.prepareStatement(sql);
    ps.setString(1, firstName);
    ps.setString(2, password);
    ResultSet rset = ps.executeQuery(); 

%>

Above comment line code is working fine but if dynamic want to do with the help of Prepared statement then it is not able to retrieve data from db ? what may be reason ?

To dsiplay data

  <center>
        <table border="1" bordercolor="red" style="width: 100%;">
        <tr style="font-size: 16px; color: red;">       
            <td>USER ID</td>
            <td>FIRSTNAME</td>
            <td>LASTNAME</td>
            <td>EMAIL</td>
            <td>PASSWORD</td>
            <td>REPASSWORD</td>
            <td>IDENTIFICATION</td>
            <td>PHONE NO</td>
            <td>ADDRESS</td>
        </tr>
        </table>
    </center>
  <%
     if(rset.next()) {
%>
<center>
    <table border="1" bordercolor="red" style="width: 100%">
        <tr style="font-size: 20px; color: white;">
            <td><%=rset.getString("userid")%></td>
            <td><%=rset.getString("firstname")%></td>
            <td><%=rset.getString("lastname")%></td>
            <td><%=rset.getString("email")%></td>
            <td><%=rset.getString("password")%></td>
            <td><%=rset.getString("repassword")%></td>
            <td><%=rset.getString("identification")%></td>
            <td><%=rset.getString("phoneno")%></td>
            <td><%=rset.getString("address")%></td>
        </tr>
    </table>
</center>
<%
    }
%>

Are the firstName and password variables being populated as you expect? If you hardcode the String variables to firstName = 'Malay' and password = '123' (instead of getting them from request object) does the prepared statement code work? — Monica Granbois, May 09 '16 at 03:02
Yes one row is retrieving if hard coded value I am giving @Monica. — Binay Kumar, May 09 '16 at 03:06
ps.setString(1, firstName); ps.setString(2, password); like this i have done . — Binay Kumar, May 09 '16 at 03:15
Sorry, I meant the request.getParameter("firstName"). Where is that "firstName" coming from? From your comment above, it sounds like the prepared statement code is working if you hard code the values (I.e. ps.setString(1, 'Malay')). This leads me to believe the request.getParameter("firstName") (and password too) isn't returning what you want. — Monica Granbois, May 09 '16 at 03:30
mostly coding error , print and check value has set properly in firstName & password , and data is in same case ? (upper / lower / any space in data ) this will be problem. — Ramki, May 09 '16 at 05:19
I'm not sure and i can't check this. But i think the problem is columns names. Oracle always returns column names with capital letter. Also you can print your column names from result sets. `ResultSetMetaData rsmd = rset.getMetaData();` `String name = rsmd.getColumnName(1);` — Arkadiusz Łukasiewicz, May 09 '16 at 06:36
What do you see on server console if you execute `System.out.println("Firstname: " + firstName)`? And the same for the `password`? — ujulu, May 09 '16 at 08:22
You are getting null because the request.getParameter("firstName") is not finding the firstName parameter. How is the firstName parameter value entered? Is there a form where a user enters firstName? Can you provide the code from there? — Monica Granbois, May 09 '16 at 14:51
@Binay - in the UserLogin.java code you need to pass the firstName and password parameters to your CustomerDetail.jsp See: http://stackoverflow.com/questions/3608891/pass-variables-from-servlet-to-jsp So, in UserLogin.java you need to something like: request.setAttribute("firstName", firstName); request.getRequestDispatcher("CustomerDetail.jsp").forward(request, response); — Monica Granbois, May 10 '16 at 03:24
yes monica in the morning only I got the ans this is what exactly i have done . @MonicaGranbois thank u so much — Binay Kumar, May 10 '16 at 03:29

Not able to retrieve data with PreparedStatement

0 Answers0