Web App cannot see Mysql data

[Ken Rubin]

Hello,

I am creating my first Web application and am trying to access data from a
MySql database. When I do my query, I can see the data on the console, but it does not get to the JSP. Any assistance would be greatly appreciated!

The localhost log shows:

SEVERE: Servlet.service() for servlet jsp threw exception
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:666)
at com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)
at org.apache.jsp.Result_jsp._jspService(Result_jsp.java:54)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:691)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:469)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:403)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:301)
at com.example.web.CoinSelect.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Unknown Source)
Aug 12, 2008 7:26:12 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet Ch3 Beer threw exception
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:666)
at com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)
at org.apache.jsp.Result_jsp._jspService(Result_jsp.java:54)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:691)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:469)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:403)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:301)
at com.example.web.CoinSelect.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Unknown Source)

My code for accessing MySql is C:\web_components\coins\src\com\example\model\Testnew.java and is below:
package com.example.model;
import java.sql.* ;

public class Testnew
{
static ResultSet rs;

public static void main( String[] args )
{
Testnew query = new Testnew();
query.getResult();

}
public ResultSet getResult()
{
rs=null;
try
{
Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
try
{
//Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/soup", "root", null );
Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/coinsystem", "root", "mallory1" );
try
{
Statement statement = con.createStatement();

rs = statement.executeQuery("SELECT POP.ISSUEDATE,PRICE.ISSUEDATE,PRICE.VERYFINE,POP.VERYFINE FROM SEATED_DIME_PRICES PRICE, SEATED_DIME_POPULATION POP WHERE POP.ISSUEDATE = PRICE.ISSUEDATE");

while ( rs.next() )
{
System.out.println( rs.getString( 1 ) + " " + rs.getFloat(3));
}
//rs.close();
//statement.close();
}
catch ( SQLException e )
{
System.out.println( "JDBC error: " + e );
}
finally
{
//con.close();
return rs;
}
}
catch( SQLException e )
{
System.out.println( "could not get JDBC connection: " + e );
}
}
catch( Exception e )
{
System.out.println( "could not load JDBC driver: " + e );
}
return rs;
}
}

My JSP at C:\Program Files\Apache Software Foundation\Tomcat 5.5\webapps\coins\Result.jsp is:
<%@ pageimport="java.util.*"%>
<%@ page import= "java.sql.*"%>

<html>
<body>
<hi align="center">Coin Data JSP</h1>
<p>

<%
ResultSet rs = (ResultSet)request.getAttribute("Coins");
while ( rs.next() )
{
out.print( "<br>Coins: " + rs.getString( 1 ) + " " + rs.getFloat(3));
}
%>
</body>
</html>

My Servlet at C:\web_components\coins\src\com\example\web\CoinSelect.java
is:
package com.example.web;

import com.example.model.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.sql.* ;


public class CoinSelect extends HttpServlet
{
public void doPost(HttpServletRequest request,
HttpServletResponse response)
throws IOException,ServletException
{
ResultSet result = null;

String Series = request.getParameter("Series");
String Grade = request.getParameter("Grade");
String PriceRange = request.getParameter("PriceRange");

Testnew query = new Testnew();
result = query.getResult();

request.setAttribute("Coins",result);

RequestDispatcher view = request.getRequestDispatcher("Result.jsp");

view.forward(request,response);
}
}


The Browser showed the following:

org.apache.jasper.JasperException: Operation not allowed after ResultSet closed
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:460)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:355)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.example.web.CoinSelect.doPost(Unknown Source)
javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)


root cause

javax.servlet.ServletException: Operation not allowed after ResultSet closed
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:841)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:774)
org.apache.jsp.Result_jsp._jspService(Result_jsp.java:67)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.example.web.CoinSelect.doPost(Unknown Source)
javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)


root cause

java.sql.SQLException: Operation not allowed after ResultSet closed
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:666)
com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)
org.apache.jsp.Result_jsp._jspService(Result_jsp.java:54)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.example.web.CoinSelect.doPost(Unknown Source)
javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

Again any help is greatly appreciated!!

Thank you,

Ken

[David O'Meara]

Yep, we don't do that.

Closing the Connection or the Statement also closes the ResultSet. If you want to retain the data, you either need to:
1) read it from the rs first, convert to some type of class representation and then pass the result object (or list) to the JSP
2) look into a RowSet, one implementation of which allows support for disconnected ResultSets. NOT recommended, just possible.

Passing Database resources around your application is a bad idea and leads to resource leakage.

[Ken Rubin]

Dave, ( or who may see this)

Thank you again for the response! I changed the code, but I still get a class cast exception, but it doesn't make sense to me that it is happening. Here is the message and changed code:

org.apache.jasper.JasperException: com.mysql.jdbc.ResultSet cannot be cast to java.util.Vector
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:460)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:373)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.example.web.CoinSelect.doPost(Unknown Source)
javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)


root cause

java.lang.ClassCastException: com.mysql.jdbc.ResultSet cannot be cast to java.util.Vector
org.apache.jsp.Result_jsp._jspService(Result_jsp.java:53)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.example.web.CoinSelect.doPost(Unknown Source)
javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

New Servlet Code:

package com.example.web;

import com.example.model.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.sql.* ;


public class CoinSelect extends HttpServlet
{
public void doPost(HttpServletRequest request,
HttpServletResponse response)
throws IOException,ServletException
{
Vector result = null;

String Series = request.getParameter("Series");
String Grade = request.getParameter("Grade");
String PriceRange = request.getParameter("PriceRange");

Testnew query = new Testnew();
result = query.getResult();

request.setAttribute("Coins",result);

RequestDispatcher view = request.getRequestDispatcher("Result.jsp");

view.forward(request,response);

}
}


Library code for Database:

package com.example.model;
import java.sql.* ;
import java.util.*;

public class Testnew
{
Vector return_value;

public static void main( String[] args )
{
Testnew query = new Testnew();
query.getResult();

}
public Vector getResult()
{
ResultSet rs;

rs=null;
try
{
Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
try
{
//Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/soup", "root", null );
Connection con = DriverManager.getConnection( "jdbc:mysql://localhost/coinsystem", "root", "mallory1" );
try
{
Statement statement = con.createStatement();

rs = statement.executeQuery("SELECT POP.ISSUEDATE,PRICE.ISSUEDATE,PRICE.VERYFINE,POP.VERYFINE FROM SEATED_DIME_PRICES PRICE, SEATED_DIME_POPULATION POP WHERE POP.ISSUEDATE = PRICE.ISSUEDATE");

while ( rs.next() )
{
System.out.println( rs.getString( 1 ) + " " + rs.getFloat(3));
return_value.add(rs.getString( 1 ) + " " + rs.getFloat(3));
System.out.println(return_value.get(0));

}
rs.close();
statement.close();
}
catch ( SQLException e )
{
System.out.println( "JDBC error: " + e );

}
finally
{
con.close();
return return_value;
}
}
catch( SQLException e )
{
System.out.println( "could not get JDBC connection: " + e );
}
}
catch( Exception e )
{
System.out.println( "could not load JDBC driver: " + e );
}
return return_value;

}
}
new JSP code:
<%@ pageimport="java.util.*"%>
<%@ page import= "java.sql.*"%>

<html>
<body>
<hi align="center">Coin Data JSP</h1>
<p>

<%
Vector rs = (Vector)request.getAttribute("Coins");

out.print( "<br>Coins: " + rs.get(0));

%>
</body>
</html>


Again any insights are greatly appreciated! I made sure to recompile everything and place it in deployment directories.

Best Wishes and Thanks,,

Ken

[Ulf Dittmer]

Where are you setting the request attribute? It sounds as if the attribute is a ResultSet, not a Vector.

[Ken Rubin]

Thanks for the response.

It took me some time to realize that I had a redundant class directory hierarchy where I was putting my changes, which wasn't
under WEB-INF, so any changes I made did not take. It's ok now.

Thanks again,

Ken