Unable to set attribute of ResultSet in Servlet

Question

In my project,I am getting the query result from the database into my servlet and passing the result to JSP after setting the attribute for the ResultSet and then accessing it in JSP.But somehow i am able to set and access my all other attributes in JSP and servlets except the ResultSet.

So here is my database access function getcart()

public static ResultSet getCart(int item,int cust){
ResultSet rs=null,res=null;
String i_name;
int price;
try{
    rs=stmnt.executeQuery("SELECT i_name,price FROM items WHERE i_id="+item);
    rs.next();
    i_name=rs.getString(1);
    price=rs.getInt(2);
    stmnt.executeUpdate("INSERT INTO cart(prod,price,c_id,i_id) VALUES('"+i_name+"',"+price+","+cust+","+item+")");
    res=stmnt.executeQuery("SELECT prod,price,count(*) FROM cart WHERE c_id="+cust+" GROUP BY i_id"); 
    res.next();
}catch(SQLException e){}
return res;

}

Here is the JSP

<%ResultSet r=(ResultSet)request.getAttribute("cart");%>
<div id="pageContent">
        <div style="margin:24px; text-align:left;"><br />
        <table width="100%" border="2" cellspacing="0" id ="table1">
            <tr>
            <td width="15%" bgcolor="#000000" align="center"><strong>Product</strong></td>
            <td width="10%" bgcolor="#000000" align="center"><strong>Price</strong></td>
            <td width="12%" bgcolor="#000000" align="center"><strong>Quantity</strong></td>
            <!--<td width="9%" bgcolor="#000000" align="center"><strong>Total</strong></td>-->
            </tr>
            <%while(r.next()){%>
            <tr class="spaceUnder">
                <td width="15%" bgcolor="#FFFFFF" align="center"><font color="#000"> <%=re.getString(1)%></font></td>
                    <td width="10%" bgcolor="#FFFFFF" align="center"><font color="#000"> <%=re.getInt(2)%></font></td>
            <td width="12%" bgcolor="#FFFFFF" align="center"><font color="#000"> <%=re.getInt(3)%> </font></td>
            </tr>
            <%}%>
        </table> 
            <div class="container">
        <left><h3 style="color:#FFFFF;padding-top:30px;"><font color="#000">Total: <%=total%> </font></h3></left>
            </div>
        </div>
</div>

And here is the servlets snippet:

                res=accessDB.getCart(it,ci);
                request.setAttribute("cart",res);
                view=request.getRequestDispatcher("cart.jsp");
                view.forward(request, response);

I dont know WHAT is happening?? I have now spent a lot of time on this.And my database is not empty.So res.next() is valid.

One thing is working.When i break down my ResultSet in my servlets code using getInt and getString,and then send these to JSP individually instead of sending the whole ResultSet ,then getAttribute in JSP works and i can print my result.But don't know why the whole ResultSet is not going.I am using Netbeans 8.0.1.

Please Help.

You could store it in the session `session.setAttribute("cart", res);` (You can get HttpSession by doing `request.getSession().setAttribute("cart");` Then retrieve it in your jsp like `session.getAttribute("cart");` then remove it from the session in your jsp after retrieval like `session.removeAttribute("cart");` — brso05, Nov 11 '14 at 21:59
Are you trying to find a way to trick the JVM into leaking memory? Because you have succeeded. — Boris the Spider, Nov 11 '14 at 22:01
@Boris What do you mean?I remember got one memory error from netbeans. — awhitesong, Nov 11 '14 at 22:04
@RC. nah, someone sent `SELECT RING_BELL...WHERE ID=1; DROP TABLE BELLS --`. — Boris the Spider, Nov 11 '14 at 22:04
You don't close the result set. You don't close the statement. You don't close the connection. You are leaking resources like a sieve that was left outside to rust for several decades. — Boris the Spider, Nov 11 '14 at 22:08
One more thing.I am having one more page with the same stuff.But in that page and its servlet,everything is working fine.What's going on with this one? — awhitesong, Nov 11 '14 at 22:09
Put the data from the resultset in an array or map or list or something. Then close the resultset and return that. — developerwjk, Nov 11 '14 at 22:13
See [Efficient way to Handle ResultSet in Java](http://stackoverflow.com/questions/7507121/efficient-way-to-handle-resultset-in-java) — developerwjk, Nov 11 '14 at 22:25
Its not working.Carefully closed all the statements,ResultSets and connections.Reduced the memory but still everything works except this page's resultSet.What is happening? — awhitesong, Nov 12 '14 at 00:34
I have a java class having about five to six static functions.Does that effect? — awhitesong, Nov 12 '14 at 00:41
@Hannes Thanks. ResultSet was a problem that was causing memory leaks.Got to learn something.Now done. — awhitesong, Nov 12 '14 at 22:15

Anurag Anand · Answer 1 · 2014-11-13T09:57:39.647

0

From my observation, I think your Query SELECT prod,price,count(*) FROM cart WHERE c_id="+cust+" GROUP BY i_id returns only one row.

First of all you should not use res.next(); just before returning RESULTSET object. This is a wrong way of designing a function. What getCart(int item,int cust) should do is just return the RESULTSET.

Problem that I assume , that seems to be here is that, your query just returns one row. As you call res.next(); just before returning the RESULTSET object. The resultset cursor already points to the first row of the result.

When you call <%while(r.next()){%> , this time it gets NULL , because next row in resultset is non-existent. That`s why result cannot be printed.

What I have observed now that you have made a typo in you jsp code. <%=re.getString(1)%> what it should be is <%=r.getString(1)%> beacause you are creating <%ResultSet r=(ResultSet)request.getAttribute("cart");%> Resultset variable named r but you are using re . But re does not exist in the file. I think JSP should not even compile, because re variable is never defined.

Thats the reason its not working.

edited Nov 13 '14 at 09:57

answered Nov 12 '14 at 08:18

Anurag Anand

500
1
7
13

There are enough rows to give the answer even after group by.And how can i print my exception in servlets?I know i have put two next() for the first go but considering i had more than 2 rows and it should work,i tested it and it didn't work. – awhitesong Nov 12 '14 at 20:28
[Question 1](http://stackoverflow.com/questions/6182771/how-to-properly-handle-exceptions-in-a-jsp-servlet-app) [Quetion 2](http://stackoverflow.com/questions/8135980/how-can-i-print-error-stack-trace-in-jsp-page) You can read here for printing exception in jsp. – Anurag Anand Nov 13 '14 at 09:33
I have edited my answer , there is a typo in your code. It should work now. – Anurag Anand Nov 13 '14 at 09:58
@awhitesong did you try my answer – Anurag Anand Nov 14 '14 at 07:45
I corrected the typo "r" before running my program otherwise it wouldn't have compiled.Secondly now that its running,i have more than one row in my table and already had an idea about these two ResultSet next() functions.So i corrected all these but it still didn't run. I think the best thing is not to pass ResultSet as an attribute because it cannot be serialized or closed at that time.So it causes a lot of memory leak.So the best idea is to either use odt or use JSTL tags. – awhitesong Nov 15 '14 at 01:27
@awhitesong there are always workarounds.. You can put Resultset values in a collection and pass it. Using odt and JSTL is good idea too. – Anurag Anand Nov 15 '14 at 10:44

Unable to set attribute of ResultSet in Servlet

1 Answers1