Too Many Client Task Exception in Java JDBC

Question

I am creating a JDBC ODBC connection through JAVA programs. I have to make with this connection so many times . After some times the program throws the Too Many Client Task Exception . How can is resolve this problem . I am pasting a sample example of my requirement

class connectiondemo
{

public void connect()
{

 try {

   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection con=DriverManager.getConnection("Jdbc:Odbc:dsn");
   Statement st= con.createStatement();

   }

  catch(Exception ex)
  {
   ex.printStackTrace();
  }
 }
}


calling programs

 class 
 {
  public static void main(String args[])

   {
    //supose i have to call connect methods thousands of times then what is the solution

    connectdemo demo= new connectdemo();
   dem0.connect();

   }
  }

score 0 · Answer 1 · answered Jul 25 '13 at 11:21

0

It seems you are opening too many db connections and not closing them. You need to make sure that you close the connections once you are done while executing the jdbc statements on that connection. However, if you forget, Java's garbage collector will close the connection when it cleans up stale objects.

Relying on garbage collection, especially in database programming, is very poor programming practice. You should make a habit of always closing the connection with the close() method associated with connection object.

Maybe adding a finally block should help you, something like this :

Connection con = null;
 try {

   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   con=DriverManager.getConnection("Jdbc:Odbc:dsn:);
   Statement st= con.createStatement();

   }

  catch(Exception ex)
  {
   ex.printStackTrace();
  } finally {
      try{
          con.close();
      } catch (Exception e) {
      }
  }
 }

answered Jul 25 '13 at 11:21

Juned Ahsan

67,789
12
98
136

how in this example its very tough in the real programming – adesh singh Jul 25 '13 at 11:23
but in finally block if my connections operations are not completed and its being disconnected then it will throw the exception again – adesh singh Jul 25 '13 at 11:26
finally is executed after the try and catch statements. So execution of jdbc statements should be done as part of the try block. – Juned Ahsan Jul 25 '13 at 11:27
juned in try block the connection has been created and then in finally block the connection has been disconnected so how can i use the connection in calling program – adesh singh Jul 25 '13 at 11:34

StuPointerException · Answer 2 · 2013-07-25T11:29:43.123

0

How many connections do you actually require? The options are:

A single connection that is stored globally and used multiple times (connects only once).
A connection pool containing a number of connections that are connected when the pool starts up. This allows multiple threads to access the database (almost) concurrently.

I suspect the first option is probably the right one for your application. In which case, connect once and share the connection, otherwise your DBA might have something to say:

public static Connection getConnection() {
    if(connection == null) {
        //make and store connection globally
    }
    return connection;
}

public static void closeConnection() {
     if(connection != null) {
          //close connection
     }
}

public void clientMethod() {
    <SomeClass>.getConnection().prepareStatement("whatever");
}

Making a database connection is a costly exercise and will have a noticeable impact on the performance of your application, do it as few times as you can.

edited Jul 25 '13 at 11:29

answered Jul 25 '13 at 11:23

StuPointerException

7,117
5
29
54

Or, better yet, learn how to use a database connection pool and amortize the cost of creating connections over many calls. – duffymo Jul 25 '13 at 12:19
I agree; if there is more than one thread requiring a database connection at a given time. Otherwise you have a fancy connection pool with only one connection 'checked out' at a time, which is obviously pointless. – StuPointerException Jul 25 '13 at 12:24
No mention about use case details. And I'd still recommend a pool in the single thread case for this user, because I don't think the knowledge is there to get this class right. – duffymo Jul 25 '13 at 12:28

score 0 · Accepted Answer · answered Jul 25 '13 at 12:22

The key is saving connections.Why not use a static method to call connections as in:

public class Connector {

private static final String URL = "jdbc:mysql://localhost/";
private static final String LOGIN = "root";
private static final String PASSWORD = "azerty";
private static final String DBNAME = "videotheque";
private static Connector connector;
private static Connection connection;

private Connector() {
}

public synchronized static Connector getInstance() {
    if (connector == null) {
        connector = new Connector();
    }
    return connector;
}

public static Connection getConnection() {
    if (connection == null) {
        Connection c = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            c = DriverManager.getConnection(URL + DBNAME, LOGIN, PASSWORD);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return c;
    }
    return connection;
}

For connecting you can use it as:

Connector.getInstance().getConnection()

score 0 · Answer 4 · answered Jul 25 '13 at 12:22

0

This is poor looking code for many reasons. I'd recommend that you not try to write this yourself, given your level of programming sophistication. Better to acquire and use a connection pool written by someone else, like the Apache DBCP library.

answered Jul 25 '13 at 12:22

duffymo

305,152
44
369
561

Too Many Client Task Exception in Java JDBC

4 Answers4