CODEWITHSUNDEEP
javamysqljdbcconnection
Martin Preusse
Martin Preusse

Reputation: 9369

MySQL with Java: Open connection only if possible

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

Upvotes: 0

Views: 1598

Answers (3)

BalusC
BalusC

Reputation: 1108712

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.

Upvotes: 1

Jigar Joshi
Jigar Joshi

Reputation: 240890

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

Also See :

Upvotes: 3

Mihai Toader
Mihai Toader

Reputation: 12243

Something like should work better.

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

Upvotes: 0

Related Questions