Getting error java.sql.SQLException: Invalid state, the Statement object is closed

[salman-khandu]

I have following code.

public static int execute(String query) throws SQLException {
Statement stmt = null;
try {
    stmt = con.createStatement();
    int recordcount = stmt.executeUpdate(query);
    return recordcount;
} finally {
    stmt.close(); //ERROR getting exception here 
}

public static Connection getConnection() throws SQLException,ClassNotFoundException {
    con = DbConnectionPool.getDbConnectionPool().getConnection();
    return con;
}

con is static class variable initialize with the method getConnection() . Exception throws from execute method handle by caller method.

In most cases, it works fine but in some cases, it will give error "java.sql.SQLException: Invalid state, the Statement object is closed" when I call stmt.close() in finally block. What is the possibility of occurring such error?

Thank you Salman

[vladmihalcea]

1. You should never assign a Connection to a static variable. Think of concurrency issues that might occur.
2. Use a connection pool that recycles connections and set a reasonable TTL as well. That might fix your issue which could be caused by the fact that you are holding onto that connection for way too long.

Just read my book High-Performance Java Persistence for more details.

[salman-khandu]

You should never assign a Connection to a static variable. Think of concurrency issues that might occur. <-- I have just run sample test with the same method by 1000 threads where I didn't get any error.

Why this error java.sql.SQLException: Invalid state, the Statement object is closed occurred? is statement object close automatically in exception case?

Thank you

[vladmihalcea]

Why this error java.sql.SQLException: Invalid state, the Statement object is closed occurred? is statement object close automatically in exception case?

If you only get it from time to time, only logging will help you determine what triggered it.

[salman-khandu]

Error occurred only in some cases. How can I configure logs to determine it?

Thank you

[vladmihalcea]

Just read my book and you'll find the answer to all these questions.

[salman-khandu]

Thank you vlad

When I check oracle doc https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html then close statment by if (stmt != null) { stmt.close(); }