As the title says, I've been messing around with Java and MySQL the short story is after a while I get connection errors such as the following:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.
I've read up on it as much as I can and basically its caused by the client keeping too many sockets open. Here in lies the confusion. The app itself is a very simplified MSN replacement with a database backend for logging call details (small business type scenario with 4 operators). From the chat side there is only ever 2 open sockets.
At various times people view a list of records from the database which in theory opens the DB connection, grabs all the stuff and closes the DB connection.
However checking netstat (windows XP clients) I get an enormous amount of local ports in a TIME_WAIT state set to the DB server on site.
I have no idea why this happens but if I wait for 5 or 10minutes the connections disappear and everything is back to normal (I can close the application down and still have the same problem in netstat).
The basic flow of any connection I make to the database looks like this:
protected Vector<Vector<String>> getRequests(int numberToRetrieve){
Vector<Vector<String>> records = new Vector<Vector<String>>();
String selectStatement = "SELECT * FROM requests2 ORDER BY request_date DESC LIMIT " + numberToRetrieve;
Connection conn = null;
Statement query = null;
ResultSet results = null;
try{
Class.forName ("com.mysql.jdbc.Driver").newInstance ();
try{
conn = DriverManager.getConnection (dbPath, dbUser, dbPass);
query = conn.createStatement();
query.executeQuery(selectStatement);
results = query.getResultSet();
while(results.next()){
//DO SOME STUFF
}
}catch(SQLException e){
e.printStackTrace();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(results != null){
try{
system.out.println("Closing ResultSet");
results.close();
results = null;
}catch(Exception e){System.out.println("ERROR");}
}
if(query != null){
try{
system.out.println("Closing Query");
query.close();
query = null;
}catch(Exception e){System.out.println("ERROR");}
}
if (conn != null){
try{
system.out.println("Closing Connection");
conn.close ();
conn = null;
}catch (Exception e){System.out.println("ERROR");}
}
}
return records;
}
I always see the Closing messages and never any error messages. Could someone please put me out of my misery and let me know what stupid mistake I've been making?