MySQL with Java: Open connection only if possible

Question

I'm running a database-heavy Java application on a cluster, using Connector/J 5.1.14. Therefore, I have up to 150 concurrent tasks accessing the same MySQL database. I get the following error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections

This happens because the server can't handle so many connections. I can't change anything on the database server. So my question is: Can I check if a connection is possible BEFORE I actually connect to the database?

Something like this (pseudo code):

check database for open connection slots
if (slot is free) {
    Connection cn = DriverManager.getConnection(url, username, password);
}
else {
    wait ...
}

Cheers

Jigar Joshi · Accepted Answer · 2011-01-11 17:49:31Z

3

try{
    //try to connect
}catch(com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException ex) {
    //what ever you want to do
}

Also See :

Connection Pooling

answered Jan 11, 2011 at 17:49

Jigar Joshi

241k42 gold badges409 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Preusse Over a year ago

Exactly what I was looking for, I embedded it in a while loop. Thank you.

BalusC · Accepted Answer · 2011-01-11 17:51:54Z

1

Use a connection pool like as for example C3P0. A bit decent connection pool can wait a certain timeout and retry a certain amount of times until a connection is available and then return it. Also ensure that you call Connection#close() in finally block of every try where you acquire the connection. Otherwise the connection is never released to pool (or to the database when you don't use a pool) and you will run out of them anyway.

answered Jan 11, 2011 at 17:51

BalusC

1.1m377 gold badges3.7k silver badges3.6k bronze badges

1 Comment

Martin Preusse Over a year ago

Thanks for the suggestion, I didn't know C3P0. All my connections are closed, it's a cluster thing.

Mihai Toader · Accepted Answer · 2011-01-11 17:49:16Z

0

Something like should work better.

try 
{
    Connection cn = DriverManager.getConnection(url, username, password);
} 
catch ( com.mysql.jdbc.exceptions.MySQLNonTransientConnection e ) 
{
    // wait ...
}

answered Jan 11, 2011 at 17:49

Mihai Toader

12.3k1 gold badge31 silver badges33 bronze badges

Collectives™ on Stack Overflow

MySQL with Java: Open connection only if possible

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related