JDBC Too Many Connections Error

Question

I know this probably is a similar question from the rest, (well originally, before I tried something new, it was a bit unique but it never solved the main problem), but I probably need to discuss this with someone who can help because I could never get what's causing this despite already reading various posts from this site. Bottom line is I need to keep on making plenty of sequential queries but I ended up making too many connections.

What my program does is that it displays data about each member and that it's sort of a tree or network where, in order to get the data you need for each member, you have to scout through every other member that points to that current member (or child's data) , and the data of the member that points to the member that points to the current member (or grandchild's data) and so on. Hence, why I need to keep making queries cause I need to get the data off of each child. Each node has I think a minimum children of 5 and on my 34th member, it gave off that "Too Many Connections" error.

I have read how to open and close the Connections and all but am I still doing it incorrectly? I've tried changing the max connections but that's not really a long term solution for me. Here's how I do it:

public class SQLConnect {

private Connection con;
private Statement st;
private ResultSet rs;

public SQLConnect() {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname?zeroDateTimeBehavior=convertToNull", "root", "");
        st = con.createStatement();

    } catch (ClassNotFoundException | SQLException ex) {
        System.out.println("Error in constructor: " + ex);
    }
}

//this method gets called before I make another query
public void reconnect() {
    try {
        st.close();
        con.close();
        if (con.isClosed()) {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname", "root", "");
            st = con.createStatement();
        }
    } catch (SQLException ex) {
        Logger.getLogger(SQLConnect.class.getName()).log(Level.SEVERE, null, ex);
    }
}

//sample method on how I do queries
public ResultSet getMemberViaMemberId(String mID) {
    try {
        String query = "CALL getMemberViaMemberId(" + mID + ");"; //procedure call
        rs = st.executeQuery(query);

    } catch (Exception ex) {
        System.out.println("Error: " + ex);
    }

    return rs;
}

}//end of class

The way I call it in my JForm is this..

SQLConnect connect;

public Class(){
   connect = new SQLConnect();
}

public void methodThatGetsCalledALot(String current_id){
    connect.reconnect(); //refer to SQLConnectClass displayed above
    ResultSet member = connect.getMemberViaMemberId(current_id);
    try{
        if (member.next()) {
            lastName = member.getString("last_name");
            firstName = member.getString("first_name");
        }

        //display data...
    } catch (SQLException ex){
    }
}

The code:

connect.reconnect();
ResultSet rs = connect.callSQLMethod();

is the most essential bit and is called by every class, and by every method that needs to fetch data. I have to acknowledge that I never bother closing ResultSet because often times it's inside a loop and gets replaced with new data anyway.

Again, my problem is: I cant continue fetching data anymore because of too many connections. Am I really closing things properly or am I missing something? Any suggestions on how to fix this? If my question is too confusing, I'd add more details if required. Thank you. If anyone's to keen on freely helping me out, I'd go for some emailing. Thank you! And Happy New Year btw.

Answer 1

You seem to be creating a lot of connections and recursing with the ResultSet open. Don't create new connections all the time, all you need is one connection and don't reconnect all the time. You actually don't need the reconnect method at all (unless you connection closes automatically, in which case you can check if it is closed before executing query). And you need to close the ResultSet once you are done retrieving values.

All you need is the data and not the resultset. So take the data and release the resource ie ResultSet. So do this -

In your getMemberViaMemberId don't return ResultSet, in that method itself, iterate through the resultset and create the object for the row and store it into a collection and return that collection after closing the ResultSet. And dont call reconnect method at all.

Close the single connection that you have when exiting the program.