MySQL timing out? Can't tell what's going on, Java

i'm running this method to update an SQL using DBCP connection pooling:

After exactly 8 times, the method setValue stops doing so, and no data is sent.

    public static void setValue(String user, Integer id){
    Connection connection = null;
    try {
        try {
            connection = DataSource.getInstance().getConnection();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        ttl++;
        PreparedStatement ps = connection.prepareStatement("REPLACE INTO " + "accounts"+ " (user,id) VALUES(?,?)");
        ps.setString(1, user);
        ps.setInt(2, id);
        ps.executeUpdate();
                    ps.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return;
}

I am not familiar with MySQL or connection pooling, i do not know what is going wrong here. Please help me troubleshoot this or provide any suggestions? Thank you so much!

Answers


Yes, you should be closing your connection, which will simply return it to the pool. Basically you should add this in a finally {} block in your method. Hope this helps.

public static void setValue(String user, Integer id){
    Connection connection = null;
    try {
        try {
            connection = DataSource.getInstance().getConnection();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        ttl++;
        PreparedStatement ps = connection.prepareStatement("REPLACE INTO " + "accounts"+ " (user,id) VALUES(?,?)");
        ps.setString(1, user);
        ps.setInt(2, id);
        ps.executeUpdate();
                    ps.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    } finally {
        if (connection != null) connection.close();
    }    
    return;
}

My guess is that you are just using all the connections in your pool

try putting in a finally block

finally {
connection.close();
}

Even though you are using a connection pool you still have to close the connection - it's just that the connection pool doesn't actually close it, it just returns it to the pool so someone else can use it.


Need Your Help

setting the default value for insert parameters in sql datasource asp.net

asp.net sqldatasource

i have a datasource, which has an insertparameters, one of which whose type is set to Boolean, but while adding it from code behind insertparameters.defaultvalue always return string.

blackberry trying to create a count down timer and exception gets thrown when text is updated

blackberry timer

I’m trying to create a count down timer. I user a Timer object that is given a timertask. I have a label field called mTimerDisplay that is a static member of a class I call cGlobols.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.