How to force timeout for DriverManager.getConnecti

问题:

I have an application which will establish DB connection with MySQL and execute queries. Sometimes the DriverManager.getConnection() method call takes 2 seconds and sometimes it takes 30 seconds. Is there any way to control this method to timeout after 2 seconds?

DriverManager.setLoginTimeout() doesn't seem to work.

Actually, am able to set timeout for statement.executeQuery() by sleeping the thread for my timeout value and closing the connection after wakeup. But its the connection establishment part where I couldn't really set the timeout.

Would appreciate any help.

回答1:

If there's no other options, you could always just execute the call in a separate thread, which you abort/ignore if it doesn't finish in 2 secs.

EDIT Here's an example of what I was thinking:

public class Dummy extends Thread {
private volatile Connection conn = null;
@Override
public void run() {
    try {
        this.conn = DriverManager.getConnection("foobar") ;
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
static public Connection getConnection() {
    Dummy d = new Dummy() ;
    d.start() ;
    try {
        Thread.sleep(2000) ;
    } catch (InterruptedException e) {}
    return d.conn ;
}
}

Then you can just call the static Dummy.getConnection() method other places in your code. One drawback is that this method will always take 2 secs, but changing it to return immediately when the thread is finished isn't too hard.

回答2:

You can use ExecutorService interface from Java. Below is a sample of what you need to do.

Future<Boolean> future = executor.submit(YOUR_METHOD);
future.get(TIMEOUT_YOU_NEED, TimeUnit.SECONDS);

回答3:

Try setting the socketTimeout (Time in milliseconds) on the connection URL or on the connection pool (if you are using pooling). Take care not to set this value too low or it will overwrite the statement timeout value.

try {
    this.conn = DriverManager.getConnection("url?socketTimeout=2000") ;
} catch (SQLException e) {
    e.printStackTrace();
}

or

<jdbc-connection-pool 
                     connection-validation-method="table"
                     fail-all-connections="true"
                     idle-timeout-in-seconds="300"
                     is-connection-validation-required="true"
                     statement-timeout-in-seconds="2"
                     validation-table-name="dual"
                     ..... >
   <property name="databaseName" value="...."/>
   <property name="serverName" value="....."/>
   <property name="User" value="....."/>
   <property name="Password" value="......."/>
   <property name="URL" value="jdbc:mysql://...../...."/>
   <property name="driverClass" value="...."/>
   <property name="socketTimeout" value="2000"/>
</jdbc-connection-pool>

Setting this fixed the timeout issue for me.

回答4:

Here is an example how to use:

http://www.javadocexamples.com/java/sql/DriverManager/setLoginTimeout%28int%20seconds%29.html

回答5:

Thank you to codebolt, I don't know if it's the best solution but this works for me. A 10 seconds time out.

public class Dummy extends Thread {
             private volatile java.sql.Connection conn = null;
             private boolean sleep = true;
            @Override
             public void run() {
                 try {

                     String driver = "net.sourceforge.jtds.jdbc.Driver";
                     Class.forName(driver).newInstance();                       
                     //timeout
                     DriverManager.setLoginTimeout(10);
                     this.conn = DriverManager.getConnection(url, user, pwd);
                     sleep = false;
                 } catch (Exception e) {}
             }
             public java.sql.Connection getConnection() {
                 Dummy d = new Dummy() ;
                 d.start() ;
                 try {
                     for(int i=1; i<=10; i++) {
                         //Wait 1 second
                         if (d.sleep){
                             Thread.sleep(1000);  
                         }
                     }  
                 } catch (InterruptedException e) {}
                 return d.conn ;
             }
             }

And the call:

Dummy a = new Dummy();
connection = a.getConnection();
if (connection != null) {....

回答6:

I expanded upon the answers by CodeBolt and anpadia to make the following complete class. It has only one static method that accepts all needed connection parameters, including the timeout duration. Additionally, the method wil also throw SQLException and ClassNotFoundException if they occur.

Usage:

String connectionUrl = "...";
String user = "...";
String password = "...";
String driver = "org.postgresql.Driver"; // for example

int timeoutInSeconds = 5;

Connection myConnection =
    ConnectWithTimeout.getConnection(connectionUrl, user, password, driver, timeoutInSeconds);

Below is the implementation:

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;


public class ConnectWithTimeout extends Thread {

    private static String _url;
    private static String _user;
    private static String _password;
    private static String _driver;

    private static volatile Connection _connection = null;
    private static volatile boolean _sleep = true;
    private static volatile SQLException _sqlException = null;
    private static volatile ClassNotFoundException _classNotFoundException = null;

    @Override
    public void run() {
        try {
            Class.forName(_driver);
            _connection = DriverManager.getConnection(_url, _user, _password);
        }
        catch (SQLException ex) {
            _sqlException = ex;
        }
        catch (ClassNotFoundException ex) {
            _classNotFoundException = ex;
        }
        _sleep = false;
    }

    public static Connection getConnection(String url, 
                                           String user, 
                                           String password, 
                                           String driver, 
                                           int timeoutInSeconds) 
        throws SQLException, ClassNotFoundException {

        checkStringOrThrow(url,      "url");
        checkStringOrThrow(user,     "user");
        checkStringOrThrow(password, "password");
        checkStringOrThrow(driver,   "driver");

        if (timeoutInSeconds < 1) {
            throw new IllegalArgumentException(
                "timeoutInSeconds must be positive");
        }

        _url = url;
        _user = user;
        _password = password;
        _driver = driver;

        ConnectWithTimeout conn = new ConnectWithTimeout();
        conn.start();

        try {
            for (int i = 0; i < timeoutInSeconds; i++) {
                if (_sleep) {
                    Thread.sleep(1000);
                }
            }
        }
        catch (InterruptedException ex) {
        }

        if (_sqlException != null) {
            throw _sqlException;
        }

        if (_classNotFoundException != null) {
            throw _classNotFoundException;
        }

        return _connection;
    }

    private static void checkStringOrThrow(String variable, String variableName) {
        if (variable == null || variable.length() == 0) {
            throw new IllegalArgumentException(
                "String is null or empty: " + variableName);
        }
    }
}