Servlets: issue with storing/printing specific attributes using HttpSession, array

Question

I am still new to servlets and JDBC stuff and would like some help on the following code:

try{
        String selectSQL = "select * from product_list where category = '"+category+"'";
        Statement stmt = conn.createStatement();
        ResultSet rs1 = stmt.executeQuery(selectSQL);

        ArrayList<Float> idList = new ArrayList<Float>();

out.println("<table border=\"1\"><tr><th>Item_ID</th><th>Item_name</th><th>Title</th><th>Category</th><th>Image_name</th><th>Price</th><th>Stock_Count</th></tr>");

while(rs1.next()){

    out.println("<tr><td>"+ rs1.getFloat("item_id") + "</td>");
    out.println("<td>" + rs1.getString("item_name") + "</td>"); 
    out.println("<td>"+"<a href =\"ItemDetail\">" + rs1.getString("title")+"</a>" + "</td>");
    out.println("<td>" + rs1.getString("category") + "</td>");
    out.println("<td>" + rs1.getString("image_name") + "</td>");
    out.println("<td> " + rs1.getFloat("price") + "</td>");
    out.println("<td> " + rs1.getFloat("stock_count") + "</td>");
    out.println("</tr>");
         HttpSession session = request.getSession(true);
        idList.add(rs1.getFloat("recording_id"));
        session.setAttribute("id", idList);
}
out.println("</table>");

        conn.close();
    } catch(SQLException se) {
        System.err.println(se);
    }

What I want to do is that it will store every single item_id in the session but only display the details of the one where its title's link is clicked by the user(every title has the same hyperlink) in another servlet, I have attempted to store all the ids in a array list but nothing is showing on the other servlet that is meant to have received the array list, is there something I done wrong there, any help would be appreciated.

Here is the code used in a different servlet to receive the attribute from the above table

HttpSession session = request.getSession(true);
    ArrayList<Float> id = (ArrayList<Float>) session.getAttribute("id");

Thats some really nasty code but try putting the HttpSession before the table you print. Create a session before you print the output — Zuko, Nov 12 '15 at 17:00
I know -_- the code is bad , and putting the HttpSession before I print the table didn't make a different, still nothing is showing in the other servlet, I think something is wrong in the array part of the code. — Jeff Halo, Nov 12 '15 at 18:03

Zuko · Accepted Answer · 2015-11-13T14:53:29.323

Here is a working sample code. Modify accordingly

 //My first servlet using Java-8
 try (Connection con = DriverManager.getConnection("xxxx")) {
        String category = "fish";
        try (PrintWriter out = response.getWriter()) {
            try (ResultSet rs1 = con.createStatement()
                    .executeQuery("select * from product_list where category = '" + category + "'")) {
                HttpSession session = request.getSession();
                ArrayList<Float> list = new ArrayList<>();
                out.print(
                        "<table border=\"1\"><tr><th>Item_ID</th><th>Item_name</th><th>Title</th><th>Category</th><th>Image_name</th><th>Price</th><th>Stock_Count</th></tr>");
                while (rs1.next()) {
                    list.add(rs1.getFloat("item_id"));
                    out.println("<tr><td>" + rs1.getFloat("item_id") + "</td>");
                    out.println("<td>" + rs1.getString("item_name") + "</td>");
                    out.println("<td>" + "<a href ='" + request.getContextPath() + "/Second?id="+rs1.getFloat("item_id")+"'>"
                            + rs1.getString("title") + "</a>" + "</td>");
                    out.println("<td>" + rs1.getString("category") + "</td>");
                    out.println("<td>" + rs1.getString("image_name") + "</td>");
                    out.println("<td> " + rs1.getFloat("price") + "</td>");
                    out.println("<td> " + rs1.getFloat("stock_count") + "</td>");
                    out.println("</tr>");
                }
                session.setAttribute("list", list);
                out.println("</table>");
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

and my second servlet is here

 protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    // then just use that to get it through a GET Http Method
    String id = request.getParameter("id");
    HttpSession session = request.getSession();
    ArrayList<Float> list = (ArrayList<Float>) session.getAttribute("list");
    response.getWriter().append("item:  ").append(String.valueOf(list.get(0)));
}

prints on screen the output item: 45.0

Hope it helps.

Thanks, the above code is working but how do I apply that to my own code? Sorry I am still a noob at coding. — Jeff Halo, Nov 13 '15 at 13:53
See Edit. but modify according as i have not ammended the database connection string — Zuko, Nov 13 '15 at 14:09
Recommend you use a datasource pool and lucky for you tomcat provides one — Zuko, Nov 13 '15 at 14:11
The code is working but unfortunately it isn't doing what I wanted it to do, currently it is printing out the item_id in the second servlet starting from (0) but what I wanted it to do is to only get the item_id from the one the user click its title link on the same row so for example item_id '1' has a title 'Apple' and the user click on the 'Apple' link then it will show in depth details of the item according to Apple's item_id. It's messy I know...... — Jeff Halo, Nov 13 '15 at 14:48
it is still only showing the item_id at (0) which wasn't the one I clicked the title on — Jeff Halo, Nov 13 '15 at 15:38
Oh great, it is working now! Thanks a lot for the help, I have been stuck on this problem for 3 days now lol, thanks again. — Jeff Halo, Nov 13 '15 at 15:54
I am new around here. Close as in delete the question or accept an answer? — Jeff Halo, Nov 13 '15 at 16:00
Its accept if a solution has worked for you.. but its ok. I've seen the update — Zuko, Nov 13 '15 at 16:01

score 0 · Answer 2 · answered Nov 13 '15 at 07:10

Jeff, Jeff, Jeff....

The answer to your question I think is pretty simple, but I'd like to help you clean it up a bit because there's a lot of things that could be done better here. This code has resource leaks and can teach you some bad habits.

The reason that the ID's aren't showing up correctly is probably this line:

    HttpSession session = request.getSession(true);
    idList.add(rs1.getFloat("recording_id"));
    session.setAttribute("id", idList);
} //End while

You're resetting the idList attribute every single time you iterate through the ResultSet. You need to get the session outside of the loop, modify the ArrayList, and then set the attribute once.

HttpSession session = request.getSession(true); //Get the session only once
ArrayList<Float> idList = new ArrayList<Float>();   //Declare output variables outside of the try block.

//Now declare your JDBC resources; always declare them as null outside of the try block.
//This allows you to close them properly later.

PreparedStatement stmt = null;
ResultSet rs1 = null;

try{
    String selectSQL = "select * from product_list where category = ?";

    stmt = conn.prepareStatement(selectSQL);

    stmt.setString(1, category);

    rs1 = stmt.executeQuery(selectSQL);

    out.println("<table border=\"1\"><tr><th>Item_ID</th><th>Item_name</th><th>Title</th><th>Category</th><th>Image_name</th><th>Price</th><th>Stock_Count</th></tr>");

    while(rs1.next()){

         out.println("<tr><td>"+ rs1.getFloat("item_id") + "</td>");
         out.println("<td>" + rs1.getString("item_name") + "</td>"); 
         out.println("<td>"+"<a href =\"ItemDetail\">" + rs1.getString("title")+"</a>" + "</td>");
         out.println("<td>" + rs1.getString("category") + "</td>");
         out.println("<td>" + rs1.getString("image_name") + "</td>");
         out.println("<td> " + rs1.getFloat("price") + "</td>");
         out.println("<td> " + rs1.getFloat("stock_count") + "</td>");
         out.println("</tr>");
         idList.add(rs1.getFloat("recording_id")); //Add to the list in the loop.

       }

    out.println("</table>");

    } catch(SQLException se) {
        System.err.println(se); //This is OK, but consider 'throw new RuntimeException(se)' here instead so that the exception propagates.
    } finally {
   //Close the resources backwards from the way that they were opened.
   //Check them all for null first in case there's an error in the middle somewhere, 
   //otherwise you'll get a NullPointerException from the finally block and it will "swallow"
   //the original error.
   if(rs1 != null) {
      try {
        rs1.close();
      } catch(SQLException x) {
        System.err.println(x); //Don't throw here; keep trying to close resources
      }
   }

   if(stmt != null) {
      try {
        stmt.close();
      } catch(SQLException x) {
        System.err.println(x); 
      }
   }

   if(conn != null) {
      try {
        stmt.close();
      } catch(SQLException x) {
        System.err.println(x);
      }
   }
}

session.setAttribute("id", idList); //Set the list once and only once.

Thank you for the reply, I tried running the above code but got a error 'java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).' displayed on the console, what does it mean? — Jeff Halo, Nov 13 '15 at 13:37
The code above uses a PreparedStatement, which binds a variable to the value of "category." This prevents SQL injection attacks, since the parameter value is not part of the statement. If you were to use a Statement instead, someone could put in `; DROP TABLE PRODUCT_LIST` as the category and make your table disappear. As to the error... you are sure that the selectSQL contains a `?`, correct? That's key. — Brad, Nov 13 '15 at 21:03
My current selectSQL is like this String selectSQL = "select * from product_list where category = '"+category+"'"; do I need to put ? somewhere in there or something @Brad — Jeff Halo, Nov 13 '15 at 22:19
Instead of `select * from product_list where category = '"+category+"'` use `select * from product_list where category = ?` The question mark replaces concatenating "category". — Brad, Nov 14 '15 at 23:34

Servlets: issue with storing/printing specific attributes using HttpSession, array

2 Answers2