javaprogrammer javaprogrammer - 5 months ago 5
SQL Question

How to print all values from sql table instead of just first row

SQL Table payroll_items

Appearance on http

I have a sql table called "payroll_items" with three rows of data, as you can see. You can also see my code below, which is a java servlet which retrieved the data from database and displays it in a table format. However, as you can see in the table format, it's only showing the first row of the sql table (SA, Superannuation, etc...), but not the other two rows (ST and WA). How can I display all the values in a table format? It's only showing first row.

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/payroll_system", "root", "");
PreparedStatement ps = con.prepareStatement("select ItemCode, ItemName, ItemDescription, Rate from payroll_system.payroll_items");
ResultSet rs = ps.executeQuery();
st = rs.next();
if(st) {

out.println("<tr>");
out.println("<td><h3>Item Code</h3></td>");
out.println("<td><h3>Item Name</td></h3>");
out.println("<td><h3>Item Description</h3></td>");
out.println("<td><h3>Rate</h3></td>");
out.println("</tr>");

out.println("<tr>");
out.println("<td>"+ rs.getString("ItemCode")+"</td>");
out.println("<td>"+ rs.getString("ItemName")+"</td>");
out.println("<td>"+ rs.getString("ItemDescription")+"</td>");
out.println("<td>"+ rs.getString("Rate")+"</td>");
out.println("</tr>");

Answer

iterate ResultSet. You forgot to add query in ResultSet

ResultSet rs = ps.executeQuery("select ItemCode, ItemName, ItemDescription, Rate from payroll_system.payroll_items"); 
while (rs.next()) {
                 out.println("<tr>");
                 out.println("<td>"+ rs.getString("ItemCode")+"</td>");
                 out.println("<td>"+ rs.getString("ItemName")+"</td>");
                 out.println("<td>"+ rs.getString("ItemDescription")+"</td>");
                 out.println("<td>"+ rs.getString("Rate")+"</td>");
                 out.println("</tr>");
}