Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Very Confused - MySQL Connections not closing

843859Nov 7 2008 — edited Nov 20 2014
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 5 2008
Added on Nov 7 2008
4 comments
508 views