display SQL rows in table

Question

How can I display all rows from SQL database in JSP table?

In order to one row represent one 'td' tag in table. This code below displays only last row from my database.

CartController.java:

@RequestMapping(value="/cart.html", method = RequestMethod.GET)
public ModelAndView cartPage(Model model) throws SQLException, ClassNotFoundException {

    PreparedStatement pst;
    ResultSet rs;
    Connection con;

    con = db.getConnect(); 

    pst = con.prepareStatement("SELECT * FROM onlineshop.cart");
    rs = pst.executeQuery();

    while (rs.next()) {
        model.addAttribute("ID", rs.getString(1));
        model.addAttribute("picture", rs.getString(2));
        model.addAttribute("name", rs.getString(3));
        model.addAttribute("company", rs.getString(4));
        model.addAttribute("type", rs.getString(5));
        model.addAttribute("price", rs.getString(6));
    }

    ModelAndView cart = new ModelAndView("Cart");

    return cart; 

}

and here is fragment of Cart.jsp:

<div style="padding-right: 40px">
    <table border="1">
        <tr>
            <td>ID</td>
            <td>Product</td>
            <td>Name</td>
            <td>Company</td>
            <td>Type</td>
            <td>Price</td>
            <td>Action</td>
        </tr>
        <tr>
        <td>${ID}</td>
        <td><img src="${picture}"/></td>
        <td>${name}</td>
        <td>${company}</td>
        <td>${type}</td>
        <td>${price}</td>
        <td></td>
        </tr>

    </table>
</div>

Error:

type Exception report

message An exception occurred processing JSP page /WEB-INF/Cart.jsp at   line 74

description The server encountered an internal error that prevented it   from fulfilling this request.

exception
org.apache.jasper.JasperException: An exception occurred processing JSP   page /WEB-INF/Cart.jsp at line 74

71: </tr>
72: <c:forEach var="cart" items="carts">
73: <tr>
74:         <td>${cart.getID()}</td>
75:         <td><img src="${cart.getPicture()}"/></td>
76:         <td>${cart.getName()}</td>
77:         <td>${cart.getCompany()}</td>


Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:574)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:476)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:396)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:168)
org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:303)
org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1243)
  org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:1027)
 org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:971)
  org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)
 org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:871)
javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
 org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)


root cause
javax.el.MethodNotFoundException: Method not found: class   java.lang.String.getID()
javax.el.Util.findWrapper(Util.java:351)
javax.el.Util.findMethod(Util.java:213)
javax.el.BeanELResolver.invoke(BeanELResolver.java:156)
org.apache.jasper.el.JasperELResolver.invoke(JasperELResolver.java:147)
org.apache.el.parser.AstValue.getValue(AstValue.java:159)
org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:184)
org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageContextImpl.java:943)
org.apache.jsp.WEB_002dINF.Cart_jsp._jspx_meth_c_005fforEach_005f0(Cart_jsp.java:237)
org.apache.jsp.WEB_002dINF.Cart_jsp._jspService(Cart_jsp.java:190)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:438)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:396)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:168)
org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:303)
org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1243)
org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:1027)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:971)
 org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:871)
javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
  org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)


 note The full stack trace of the root cause is available in the Apache    Tomcat/8.0.29 logs.

Ali Dehghani · Accepted Answer · 2016-01-04T17:09:53.143

4

Because you're overriding cart information and obviously the last row wins the bet. You should maintain a List of Carts in your controller and push them to your view. Then in the view, iterate through them and put them in each row.

Suppose you have a Cart class like this:

public class Cart {
    private String ID;
    private String picture;
    private String name;
    private String company;
    private String type;
    private String price;

    // getters and setters
}

Change your controller to create a List of Carts and add each Cart to the list:

...
List<Cart> carts = new ArrayList<>();
while (rs.next()) {
            Cart cart = new Cart();
            cart.setID(rs.getString(1));
            cart.setPicture(rs.getString(2));
            cart.setName(rs.getString(3));
            cart.setCompany(rs.getString(4));
            cart.setType(rs.getString(5));
            cart.setPrice(rs.getString(6));

            carts.add(cart);
        }
model.addAttribute("carts", carts);
...

And in the view:

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
...
<c:forEach var="cart" items="carts">
<tr>
        <td>${cart.getID()}</td>
        <td><img src="${cart.getPicture()}"/></td>
        <td>${cart.getName()}</td>
        <td>${cart.getCompany()}</td>
        <td>${cart.getType()}</td>
        <td>${cart.getPrice()}</td>
        <td></td>
</tr>
</c:forEach>

edited Jan 04 '16 at 17:09

answered Jan 04 '16 at 16:58

Ali Dehghani

46,221
15
164
151

1

At this point, it's probably worth using `JdbcTemplate` as well. – chrylis -cautiouslyoptimistic- Jan 04 '16 at 17:56
or even `JPA Repository` – Ali Dehghani Jan 04 '16 at 17:58
@AliDehghani It looks good and I want to implement this solution, but... I got this: "Unknown tag (c:forEach)" - I imported 4 JARs (taglibs jstlel, spec, impl and compat) to my 'lib' folder and it still doesn't work... – dante Jan 04 '16 at 18:44
http://stackoverflow.com/questions/4928271/how-to-install-jstl-the-absolute-uri-http-java-sun-com-jstl-core-cannot-be-r – Ali Dehghani Jan 04 '16 at 18:56
Use maven or gralde for dependency management. Adding jar files to lib folder is so messy – Ali Dehghani Jan 04 '16 at 18:57
@AliDehghani I managed with this JARs already, but here is another error.. check my post – dante Jan 04 '16 at 20:04
Add full stack trace there – Ali Dehghani Jan 04 '16 at 20:09
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/99728/discussion-between-ali-dehghani-and-dante). – Ali Dehghani Jan 04 '16 at 20:18

display SQL rows in table

1 Answers1