In most cases it works fine but for 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 possibility of occurring such error.
Where does the 'con' variable live?
Is it ever closed?
Can I suggest adding a catch block in that try/catch to see whether the finally is occurring due to a prior exception, so you can log the exception, or wrap the close call (probably preferable) in a try/catch?
posted 5 months ago
Where does the 'con' variable live? <--- It is static class variable initialize with the method getConnection() .
Is it ever closed? <-- Managed by connection pooling so no need to close manually.
Can I suggest adding a catch block in that try/catch to see whether the finally is occurring due to a prior exception, so you can log the exception, or wrap the close call (probably preferable) in a try/catch? <- It handles from this method called.
Everything works fine in the most case. But in rare cases, it gives an error java.sql.SQLException: Invalid state, the Statement object is closed
Sounds like a threading race condition to me. Especially as the problem occurs rarely for no apparent reason.
Wild guess: Thread 1 gets a connection, executes a query... then Thread 2 gets the same connection but before it can finish executing the query, Thread 1 closes the connection, which automatically closes Thread 2's query.
Or something like that. You've said that the connection is stored in a class-level variable so it's quite possible that (despite your use of a connection pool) both threads use the same connection.
posted 5 months ago
You didn't say what sort of application this sits in.
If (as Paul suggests) it's a multi-threaded environment then a static connection is a recipe for problems.
Since you have a connection pool anyway there is no need for a single static instance. Just get a connection from the pool for a transaction, then close when finished.
However, as for the SQLException try/catch blocks, since you have no catch block in there then any prior exception (which I think is being thrown) is going to vanish if the close() method throws an exception.
That's why you should wrap the close() in a try/catch so that you do not lose the original exception.
Using the above in your finally ensures that it is the original exception that is passed up, and also prevents a failure to close from causing an error.