How we can open/close JDBC connections?
In the previous blog, we have seen JDBC and how to connect to MySQL from Java. In this blog, we will see how we can open/close JDBC connections.
In the previous blog, we have already seen how to register a JDBC driver using DriverManager. We will also see how we get the connection only if a connection is closed or not done previously, else will return the old connection object.
DatabaseConnector class is as shown below ( you can copy the code from here…)
Before opening a new connection we will check if the connection object is null or the connection is already closed then only we will open a new connection else we will return the previous connection.
We will read from the students’ table which has data as follows.
As we can see in the above table we have the data in the students’ table for roll numbers 1, 2, 3, and 5. We have created one class JDBCConnect as shown below (copy code from here…)
As we can see from the above code, for each value of “i” from 1 to 5 we are getting a connection from the DatabaseConnector and reading the values if data is present.
Note that we are closing the connection when data for that particular value of “i” is not present.
- At first where the value of “i” is 1 since the connection is not created earlier code inside the if condition from the getConnection() method will be executed and the connection will be opened.
- For “i” = 2 and 3 since the previously opened connection is present, if the condition will not be executed and previous connection object will be returned.
- For i = 4 also, a previously opened connection is used, from the table we can see that the value for roll number 4 is not present, hence since the value is not present else condition will be executed where we are closing the connection.
- For i = 5, since we have already closed the connection in loop 4, con.closed() value will be true, and the code inside the if condition will be executed and a new connection will be opened.
The output of the above program will be as below:
Note that we should always close the connection explicitly once not required further to end the database session. Garbage Collector closes the unused connection only if the program ends or the service is stopped. Hence it’s always a good practice to close the connection if our program is running for a long time.
To ensure that a connection is closed, we can provide a ‘finally’ block in the code since a ‘finally’ block always executes, regardless of whether an exception occurs or not. The second option is to use try with resources and initialize the connection object inside the try as shown in the below example.
