I am using a Java application front end to connect and interact with a database on a MySQL 5.6 server. Using the JDBC connector from MySQL I am having issues with connections made to the server not closing when con.close() is called. The connections are all dropped when the application is closed though. The following is the dao class used to make queries.
package binaparts.dao;
import java.sql.*;
import org.json.JSONArray;
import org.json.JSONObject;
import binaparts.properties.*;
import binaparts.util.ToJSON;
public class DBConnect {
protected Statement st = null;
protected ResultSet rs = null;
protected Connection con = null;
protected PreparedStatement pst = null;
private String configFilePath = "config.properties";
public DBConnect(){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
}
private Connection getDBConnection() throws Exception{
ConfigurationManager configProps = new ConfigurationManager(configFilePath);
String host = configProps.getProperty("host");
String port = configProps.getProperty("port");
String database = configProps.getProperty("database");
String user = configProps.getProperty("user");
String password = configProps.getProperty("password");
try{
con = DriverManager.getConnection("jdbc:mysql" + "://" + host + ":" + port + "/" + database, user, password);
}catch(SQLException SQLex){
con = null;
}catch(Exception ex){
ex.printStackTrace();
con = null;
}finally{
try{rs.close();} catch(Exception ex) { /*ignore*/}
try{st.close();} catch(Exception ex) { /*ignore*/}
try{pst.close();} catch(Exception ex) { /*ignore*/}
}
return con;
}
The next bit of code is a method to verify the user of the application with the database. There are some System.out.println() statements to track the con variable through the process.
public boolean verifyUser() throws Exception {
ConfigurationManager config = new ConfigurationManager(configFilePath);
String username = config.getProperty("appUser");
String password = config.getProperty("appPassword");
boolean userCheck = false;
try{
if(con == null){
System.out.println("there is not a connection");
}
if(con != null){
System.out.println("there is a connection");
}
con = getDBConnection();
if(con == null){
System.out.println("get connection did not work");
}
if(con != null){
System.out.println("get connection did work");
}
JSONObject temp = queryReturnUser(username).getJSONObject(0);
con.close();
String un = null;
try{
un = temp.get("username").toString();
}catch(Exception ex){un = " ";}
String pw = null;
try{
pw = temp.get("password").toString();
}catch(Exception ex){pw = " ";}
if(username.equals(un)){
if(password.equals(pw)){
userCheck = true;
}
}
}catch(Exception ex){/*ignore*/}
finally{
try{
if(con == null){
System.out.println("connection was terminated before finally");
}
if(con != null){
System.out.println("connection was not terminated before finally");
System.out.println("trying again...");
con.close();
}
if(con != null){
System.out.println("there is a connection still");
}
}catch(Exception ex){ex.printStackTrace();}
}
return userCheck;
}
The output from running that method is:
there is not a connection
get connection did work
connection was not terminated before finally
trying again…
there is a connection still
If I change the con.close(); to con = null; then I get the output:
there is not a connection
get connection did work
connection was terminated before finally
So the connection is successfully terminating at that point, but I feel like this is not the proper way to do it.
Best Answer
I think you want
con.isClosed()
rather thancon==null
for your check. Just because you've closed aConnection
doesn't mean your reference to the object itself will benull
.