JDBC SQLError: No operations allowed after connection closed
Hello, developer, welcome back to another brand-new tutorial. In this blog post, I will discuss a common JDBC SQL Error which is, “no operation allowed after connection close”. This error shows on bad connection design and when don’t close the MySQL connection after doing a database operation. It would be CRUD (Create, Read, Update and Delete) operation.
What will happen if we do not close the connection object in JDBC?
Before learning how to close a database connection after a SQL operation, it’s essential to know why we should close the database connection after SQL operation. If you don’t close the JDBC or database connection after an SQL operation the consequence is high and it’s dependent on the application. And you will notice “no operation allowed after connection close”. Below I am pointing few major issues that need to be considered.
- It leads the connection memory leakage.
- If the application server/web server is shut down, the connection will remain activated even though the user logs out.
- Suppose the database server has 50 connections available and 50 end users are requesting the connection. If the database server grants all of them, and after their usage, they are not closed, the database server would not be able to provide a connection for another request.
- The database may time out the connection, but the client socket will still be used so you may run out of file handles or the count of database connections may be exceeded.
- When open connections in database servers are increasing it will lead to slow performance and eventually, the database server will crash.
So it’s recommended to close the database connection after doing a SQL operation. But not sure, most of the time the database closes the connection after a certain time you can set in the database config.
People also Reading and Reading from this blog
- Login and Registration using JSP, Servlet, and MySQL.
- Watch the Complete JSP Tutorial on YouTube.
- File upload in JSP and Servlet.
Best way to close JDBC Connection Object
Our target is to overcome “no operations allowed after connection closed”. To avoid this error we should close the JDBC connection object at the end of the application or method. Using the “finally” block we should close the JDBC object. Meaning that we have to write the close statement inside the “finally” block. Below is a code snippet to close the JDBC connection object.
And please note that closing the connection object in reverse order is the best practice.
//first close the resultset object as rs
rs.close()
//second close the preparedstatement as pst
pst.close();
//and finally close the database connection object as con
con.close();
JDBC Connection class design
Designing a class or method is a complete developer’s choice. This post will share two approaches to the writing connection method. The first one is class-level instantiation and the second one is method-level instantiation. Get a details explanation on a video on YouTube about why we should not use class-level instantiation. Below is the example code snippet.
That’s all for this post. I hope you enjoyed this post and feel free to share it with your friends. If you feel that any information is incorrect in this post please feel free to comment to reach out to me on Instagram.
