CODEWITHSUNDEEP
javamysqljspjdbc
dpk12
dpk12

Reputation: 85

error: No operations allowed after connection closed

I know that it is a bad idea to put SQL code or any database related code in JSP. But I want to try this way please help. In the below code in retrieve.jsp I have to enter empid and datefrom and dateto then it will show the result on dispaly.jsp page as table with all the details and total working hours and overtime of the employee.

retrieve.jsp

%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="com.eis.bean.Provider"%>
<%@page import="com.eis.bean.ConnectionProvider"%>
<%@page import="java.sql.*" %>

<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>JSP Page</title>
</head>
<body>
    <h4>Enter Employee ID and the dates</h4>
    <form name="retrieve form" action="display1.jsp" method="POST">
        <table border="0">

            <tbody>
                <tr>
                    <td>Employee ID</td>
                    <td><input type="text" name="Emp_id" required="required"/></td>
                </tr>
                <tr>
                    <td>From Date:</td>
                    <td><input type="date" name="From" value="yyyy/MM/dd" required="required"/></td>
                </tr>
                <tr>
                    <td>To Date:</td>
                    <td><input type="date" name="To" value="yyyy/MM/dd" required="required"/></td>
                </tr>
            </tbody>
        </table>
        <input type="reset" value="Clear" name="clear" />
        <input type="submit" value="Submit" name="submit" />
    </form>
</body>
</html>

display.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="com.eis.bean.Provider"%>
<%@page import="com.eis.bean.ConnectionProvider"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>

<%
String empid = request.getParameter("Emp_id");
String from = request.getParameter("From");
String to = request.getParameter("To");
Connection conn= null;
PreparedStatement ps = null; 
ResultSet rs = null; 

conn = ConnectionProvider.getConn(); 
try {

    ps = conn.prepareStatement("SELECT * FROM timsheetdb.logintable WHERE Emp_id=? and LoginDate BETWEEN ? AND ?; ");
    ps.setString(1, empid); 
    ps.setString(2, from);
    ps.setString(3, to);

    out.print("<table border=1>");  
    out.print("<caption>TimeSheet </caption>");  
    rs = ps.executeQuery();  
    ResultSetMetaData rsmd = rs.getMetaData(); 
    int total=rsmd.getColumnCount();  
     if (rs.next()) {
 %><jsp:include page="retrieve.jsp"/>
<%

    out.print("<center><h1>Timsheet</h1></center>");
    out.print("<b>Employee ID</b> " + empid + "");
    out.print("<br>" + "<b>From Date:</b> " + from);
    out.print("<br>" + "<b>To Date:</b> " + to);
    out.print("<tr>");  
        for(int i=1;i<=total;i++)  
        {  
        out.print("<th>"+rsmd.getColumnName(i)+"</th>");  
        }  
        out.print("</tr>");
 while(rs.next())  
        {  
        out.print("<tr><td>"+rs.getString(1)+"</td><td>"+rs.getString(2)+"</td><td>"+rs.getString(3)+"</td><td>"+rs.getString(4)+"</td><td>"+rs.getString(5)+"</td><td>"+rs.getString(6)+"</td><td>"+rs.getString(7)+"</td><td>"+rs.getString(8)+"</td><td>"+rs.getString(9)+"</td><td>"+rs.getString(10)+"</td></tr>");  

        }  
        out.print("</table>");  
} 
else
     { 
out.println("Please check the Employee ID and Dates "); 
%>
<jsp:include page="retrieve.jsp"/>
<%}
} catch (Exception ex) {
    System.out.println(ex);
}   
finally {  
         if (conn != null) {  
            try {  
                conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        } 

        if (ps != null) {  
            try {  
                ps.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
        if (rs != null) {  
            try {  
                rs.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  

    }  
%>

ConnectionProvider.class

import java.sql.*; 

public class ConnectionProvider {  
  private static Connection conn=null;  
  static{  
    try{  
      Class.forName("com.mysql.jdbc.Driver");
      conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/timsheetdb","root","lion");  
    }catch(Exception e){}  
  }  

  public static Connection getConn(){  
    return conn;  
  }  

}

Below is the error:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:958)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1236)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1231)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4140)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4109)
at org.apache.jsp.display_jsp._jspService(display_jsp.java:113)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:438)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:396)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2503)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2492)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)

Upvotes: 0

Views: 3589

Answers (1)

f1sh
f1sh

Reputation: 11934

Your connection is obtained using ConnectionProvider.getConn();

That function has to make sure the returned connection is still open or return a new connection otherwise.

I guess at the moment it always returns the same instance of a connection that is created only once.

EDIT: change your ConnectionProvider to this:

public class ConnectionProvider {  
  public static Connection getConn(){  
    try {  
      Class.forName("com.mysql.jdbc.Driver");
      return DriverManager.getConnection("jdbc:mysql://localhost:3306/timsheetdb","root","lion");  
    } catch(Exception e){}  

    return null;  
  }  
}

This is not good code and I personally wouldn't use it like that, but it should solve your problem. Now a new connection to the database is created each time.

Upvotes: 2

Related Questions