aspose file tools
The moose likes JDBC and Relational Databases and the fly likes Web App cannot see Mysql data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Storm Applied this week in the Other Open Source APIs forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Web App cannot see Mysql data" Watch "Web App cannot see Mysql data" New topic
Author

Web App cannot see Mysql data

Ken Rubin
Ranch Hand

Joined: Mar 08, 2004
Posts: 66
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
Rancher

Joined: Mar 06, 2001
Posts: 13459

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
Ranch Hand

Joined: Mar 08, 2004
Posts: 66
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
Rancher

Joined: Mar 22, 2005
Posts: 42955
    
  73
Where are you setting the request attribute? It sounds as if the attribute is a ResultSet, not a Vector.
Ken Rubin
Ranch Hand

Joined: Mar 08, 2004
Posts: 66
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Web App cannot see Mysql data