Close connection in DAO
My DAO Object
package com.myselect;
import java.sql.*;
import java.sql.DriverManager;
import java.util.logging.Level;
import java.util.logging.Logger;
public class DataAccess {
Connection conn;
PreparedStatement pst;
ResultSet rs;
public DataAccess() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/db", "root", "root");
} catch (ClassNotFoundException ex) {
Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
}
}
public String getAge(String name) {
String userage = null;
try {
pst = conn.prepareStatement("select age from mydb where name= ?");
pst.setString(1, name);
rs = pst.executeQuery();
while (rs.next()) {
userage = rs.getString("age");
System.out.println(userage);
}
} catch (Exception ex) {
Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
}
return userage;
}
public int insertRecord(String name, int addage) {
int b = 0;
try {
pst = conn.prepareStatement("insert into mydb values(?,?)");
pst.setString(1, name);
pst.setInt(2, addage);
b = pst.executeUpdate();
} catch (Exception ex) {
Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
}
return b;
}
}
I want to close the connection. I have several servlets that call methods insertRecord
and getAge
. What's the best way to close my connection? Create another method and call the method insertRecord
and getAge
or in the constructor?
source to share
Before considering connection, you should always close objects ResultSet
and Statement
every time you use them. Don't store them as state in an object DataAccess
. Have them as local variables and close them after using them:
public String getAge(String name) {
PreparedStatement pst = null;
ResultSet rs = null;
String userage = null;
try {
pst = conn.prepareStatement("select age from mydb where name= ?");
pst.setString(1, name);
rs = pst.executeQuery();
while (rs.next()) {
userage = rs.getString("age");
System.out.println(userage);
}
} catch (Exception ex) {
Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
} finally {
// closing JDBC resources
if(rs != null) {
rs.close();
}
if(pst != null) {
pst.close();
}
return userage;
}
The same for the connection: ideally each method should create an object Connection
and close it after using it. Depending on how often you open and close a connection in a method, you can use a connection pool to share open connections for more efficiency.
To close connections and its resources, you can create a way to do this:
public void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
... // close resources here
}
source to share
Open and close connections as necessary. In your case, you open a connection at some point and then using it on another. This means that you are opening a connection and potentially not using it for some time.
What you would need to do would be to make the methods self-contained, which means the methods have to open the connection, and when done, close it. The closing part must be done in a block finally
, this will ensure that no matter what happens, the collection will always be closed.
Something like that:
public String getAge(String name) {
String userage = null;
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/db", "root", "root");
PreparedStatement pst = conn.prepareStatement("select age from mydb where name= ?");
pst.setString(1, name);
rs = pst.executeQuery();
while (rs.next()) {
userage = rs.getString("age");
System.out.println(userage);
}
} catch (Exception ex) {
Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
}
finally {
if(conn != null) {
conn.close();
}
if(pst != null) {
pst.close();
}
if(rs != null) {
rs.close();
}
}
return userage;
}
EDIT: as suggested by @virtualpathum, you can also take a look at third party frameworks that provide you with the means to manage the connection pool, usually through an implementation of the Singleton programming pattern.
source to share