Fetch Multiple Records from Mysql Database Using Where Clause

Question

Im using a web application ,which runs on Apache Tomcat and has Mysql Database as backend

I want to retrieve multiple rows for a particular column usin the Where clause

For Example If I give select * from xyz where type=abc

I should get all the rows having type abc.

Problem:

I use the JDBC connection to achieve this, however Only the 1st row matching the where condition is returned rather than all the rows (even though multiple rows match the criteria in database)

Kindly help me resolve this

Code:

<%@ page import="java.sql.*" %>
<html>
<head><link href="style.css" rel ="stylesheet" type="text/css"></head>

<body bgcolor="white" >

<div id="container">
<div id="header">
    <img src="logo.jpg">

  <div class ="horiztext"><p> Order Tracker</p></div>
  </div>

</div>
  <br>

    <img src="banner.jpg" width="1500 " height="5"><br>
    <% if(session.getAttribute("username") !=null)
{
 %>
     <div id="navbar">
    <ul> 
        <li><a href="newoder.jsp">New Order</a></li> 
        <li><a href="updateorder.jsp">Update Order</a></li> 
        <li><a href="trackorder.jsp">Track Order</a></li> 
        <li><a href="trackdelay.jsp">Track Delay</a></li> 
        <li><a href="vieworder.jsp">View Database</a></li> 
        <li><a href="delete.jsp">Delete Order</a></li> 
        <li><a href="logout.jsp">Logout</a></li>
  </ul> 
  </div>   
  <br>
  <br>

    <form>
    <TABLE cellpadding="15" border="1" style="background-color: #ffffcc;">
    <%
    String ProductNamez=request.getParameter("ProductName");

    Class.forName("com.mysql.jdbc.Driver").newInstance();
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3307
 /test","root", "root");
    Statement st=conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM inventory WHERE ProductName = '"+
  ProductNamez +"' ");
    if(rs.next()){ 
        %>

<tr>
<tr><th>Serial No</th>
<th>Product Name</th>
<th>Product Type</th>
<th>Model</th>
<th>Make</th>
<th>License / Voucher</th>
<th>Location</th>



</tr> 
<tr>
<td><%=rs.getString(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
<td><%=rs.getString(5)%></td>
<td><%=rs.getString(6)%></td>
<td><%=rs.getString(7)%></td>


</tr>
          <%
    }
    %>
    </table>
    </form>
    <%
} 
else { %>
you are not logged in click here to <a href="eric.jsp"><b>login</b></a>

<%
} %> 
</body>
</html>

O. Jones · Accepted Answer · 2013-05-10 10:53:56Z

3

You'll be wanting

    while (rs.next()){

instead of

    if (rs.next()){

answered May 10, 2013 at 10:53

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheEwook · Accepted Answer · 2013-05-10 10:54:35Z

0

It's because you just do a if(rs.next()){ so only the first row is shown.

You should do this instead

while (rs.next()) {

        [...]

    }

answered May 10, 2013 at 10:54

TheEwook

11.2k6 gold badges39 silver badges55 bronze badges

Collectives™ on Stack Overflow

Fetch Multiple Records from Mysql Database Using Where Clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related