MySQL No operations allowed after statement closed

Question

I'm currently doing a Struts2 Web Application with MySQL for DB connection, and having issue with a section of my code to actually work. Constant error of:

com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after statement closed.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
at com.mysql.jdbc.Statement.checkClosed(Statement.java:385)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1107)
at com.jpalenci.action.RequestAction.getUserData(RequestAction.java:75)
at com.jpalenci.action.RequestAction.execute(RequestAction.java:50)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at ognl.OgnlRuntime.invokeMethod(OgnlRuntime.java:871)
at ognl.OgnlRuntime.callAppropriateMethod(OgnlRuntime.java:1294)
at ognl.ObjectMethodAccessor.callMethod(ObjectMethodAccessor.java:68)
at com.opensymphony.xwork2.ognl.accessor.XWorkMethodAccessor.callMethodWithDebugInfo(XWorkMethodAccessor.java:117)
at com.opensymphony.xwork2.ognl.accessor.XWorkMethodAccessor.callMethod(XWorkMethodAccessor.java:108)
at ognl.OgnlRuntime.callMethod(OgnlRuntime.java:1370)
at ognl.ASTMethod.getValueBody(ASTMethod.java:90)
at ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
at ognl.SimpleNode.getValue(SimpleNode.java:258)
at ognl.Ognl.getValue(Ognl.java:494)
at ognl.Ognl.getValue(Ognl.java:458)
at com.opensymphony.xwork2.ognl.OgnlUtil$2.execute(OgnlUtil.java:315)
at com.opensymphony.xwork2.ognl.OgnlUtil.compileAndExecute(OgnlUtil.java:346)
at com.opensymphony.xwork2.ognl.OgnlUtil.getValue(OgnlUtil.java:313)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:430)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:290)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:251)
at org.apache.struts2.interceptor.DeprecationInterceptor.intercept(DeprecationInterceptor.java:41)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:256)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:168)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:265)
at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:76)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:138)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:229)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:229)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:191)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:73)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at org.apache.struts2.interceptor.DateTextFieldInterceptor.intercept(DateTextFieldInterceptor.java:125)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:91)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:253)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:145)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:171)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:140)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:193)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:189)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:245)
at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54)
at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:567)
at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:81)
at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:99)
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:217)
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.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

The RequestAction Class that I currently have is basically a complete copy of one of my working projects, But for some reason I can't get it to work for this Application.

RequestAction.Java:

public String execute() throws SQLException{
    String ret ="";

    try{
        Class.forName(myDriver);

        try {
            getDBConn();
            callInsertUserData();
            getUserData();
        } catch (Exception e) {
            e.printStackTrace();
        }
        ret = SUCCESS;  
    } catch(Exception e){
        e.printStackTrace();
        ret = ERROR;
    } finally {
        if(dbConn != null){
            dbConn.close();
        }
    }

    return ret;
}

public static void getUserData() throws SQLException{

    getDBConn();

    try{        
        String selectQuery = "SELECT FullName, PhoneNumber, EmailAddress, CheckOutDate, DeviceUsage, DeviceSelection " 
        + "FROM devices_requested";

        rs = callStmt.executeQuery(selectQuery);

        rs.last();

        out_FullName = rs.getString("FullName");
        out_PhoneNumber = rs.getString("PhoneNumber");
        out_EmailAddress = rs.getString("EmailAddress");
        out_CheckOutDate = rs.getString("CheckOutDate");
        out_DeviceUseage = rs.getString("DeviceUsage");
        out_DeviceSelection = rs.getString("DeviceSelection");

        callStmt.executeUpdate();

        rs.close();

    } catch(SQLException e){
        e.printStackTrace();
    } finally {
        if(callStmt != null){
            callStmt.close();
        }
    }
}

/*
 * Method Which Calls To MySQL Stored Procedure That Inserts Information
 * That Comes From The Register Form,Which Then Is Updated Into The
 * DataBase.
 */
public static void callInsertUserData() throws SQLException{
    String insertUserData = "{CALL insertUserData(?,?,?,?,?,?)}";

    try{
        dbConn = getDBConn();
        callStmt = dbConn.prepareCall(insertUserData);

        callStmt.setString(1, in_FullName);
        callStmt.setString(2, in_PhoneNumber);
        callStmt.setString(3, in_EmailAddress);
        callStmt.setString(4, in_CheckOutDate);
        callStmt.setString(5, in_DeviceUsage);
        callStmt.setString(6, in_DeviceSelection);

        callStmt.executeUpdate();

    } catch (SQLException e) {
        System.out.println(e.getMessage());
        e.printStackTrace();
    } finally {
        if (callStmt != null) {
            callStmt.close();
        }
    }
}

/*
 * MySQL DataBase Connection, Attempts To Connect To Specified DB URL With
 * UserName & Password.
 */
private static Connection getDBConn() {
    Connection dbConn = null;

    try {
        Class.forName(myDriver);
    } catch (ClassNotFoundException e) {
        System.out.println(e.getMessage());
    }
    try {
        dbConn = DriverManager.getConnection(myURL, dbUser, dbPass);
        return dbConn;
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return dbConn;
}

Have no clue why the Connection isnt working, I've tried to mess around and look from my other project to see what differences there are that makes the connection and can't find it unless the callStmt.executeUpdate(); is giving the problem.

Edit: Updated callInUserData Method:

public static void callInUserData() throws SQLException {
    // CallableStatement callStmt = null;
    Connection dbConn = getDBConn();

    String inUserData = "{CALL inUserData(?,?,?,?,?,?)}";
    CallableStatement callStmt = dbConn.prepareCall(inUserData);

    try {
        // System.out.println("I am here" + FullName.toString());
        // System.exit(1);
        callStmt.setString("in_FullName", FullName);
        callStmt.setString("in_PhoneNumber", PhoneNumber);
        callStmt.setString("in_EmailAddress", EmailAddress);
        callStmt.setString("in_CheckOutDate", CheckOutDate);
        callStmt.setString("in_DeviceUsage", DeviceUsage);
        callStmt.setString("in_DeviceSelection", DeviceSelection);

        callStmt.execute();
    } catch (SQLException e) {
        System.out.println(e.getMessage());
        e.printStackTrace();
    } finally {
        if (dbConn != null) {
            dbConn.close();
        }
    }
}

Updated my insert method with what was recommended for reading by @Roman C, Other parts of my code was updated as well to fit the actual purpose of closing and opening proper connections for MySQL.

Answer 1

You have used the same Statement object in the code below

rs = callStmt.executeQuery(selectQuery);

This statement is already closed by the previous method. You need to create a new statement object to use with the query.

Statement st = dbConn.createStatement();
rs = st.executeQuery(selectQuery);

A few notes about JDBC usage in actions:

Struts2 is used generally on the presentation layer of the web application, while JDBC is used on the persistence layer. So messing JDBC code inside the actions is discouraged. From the view port of the Struts2 action it has nothing to know how and where you store your objects, and to make code more maintainable and testable recommended to separate the action code from database manipulation code. Read more about DAO/DTO pattern used by Java EE to architect your web application.

References:

• Processing SQL Statements with JDBC
• Core J2EE Patterns - Data Access Object