Connection timeout for DriverManager getConnection

Question

I am trying to connect to DB using the standard JDBC way

connection = DriverManager.getConnection(url, username, password);

Is there a maximum value of timeout on the connection, how long does a connection live, can I increase the value. I want in cases the connection to be open forever , is it a good idea.

Answer 1

You can set the Timeout on the DriverManager like this:

 DriverManager.setLoginTimeout(10);
 Connection c = DriverManager.getConnection(url, username, password);

Which would imply that if the connection cannot open within the given time that it times out.

In terms of keeping a connection open forever, it is possible if you do not close the connection but it may not be a good idea. Connections should be closed as soon as you are finished with them.

If you want to optimise the opening and closing of connections then you can use a connection pool.

Answer 2

The value is usually DB-controlled. You have no control over it using code. It depends on the DB server used. It is usually around 30 minutes up to one hour.

On the other hand, keeping a Connection open forever is a very bad idea. Best practice is to acquire and close Connection, Statement and ResultSet in the shortest possible scope to avoid resource leaks and potential application crashes caused by the leaks and timeouts.

True, connecting the DB is an expensive task. If your application is supposed to run a relatively long time and to connect the DB fairly often, then consider using a connection pool to improve connecting performance. If your application is a webapplication, then take a look in the appserver's documentation, it usually provides a connection pooling facility in flavor of a DataSource. If it is a client application, then look for 3rd party connection pooling libraries which have proven their robustness with years, such as Apache Commons DBCP (commonly used, used in lot appservers), C3P0 (known from Hibernate) and Proxool (if you want XA connections).

Keep in mind, when using a connection pool, you still have to write proper JDBC code, i.o.w. acquire and close all the resources in the shortest possible scope. The connection pool will on its turn worry about actually closing the connection or just releasing it back to pool for further reuse.

You may get some more insights out of this article how to do the JDBC basics the proper way.

Hope this helps and happy coding.

Answer 3

Here is how to do it with Connector/J MYSQL driver:

String qqq = "jdbc:mysql://localhost/Test?connectTimeout=TIME_IN_MILLIS";
conn = DriverManager.getConnection(qqq, db_user, db_pass);

It worked for me after setLoginTimeout() did nothing.

Answer 4

Just reposting a more complete repost of a comment from user flamming_python as answer because it worked for me:

dbConnectionString = "jdbc:mysql://"+dbHost+":"+dbPort+"/"+dbTable+"?user="+dbUser+"&password="+dbPassword;
Properties properties = new Properties();
properties.put("connectTimeout", "2000");
dbConnect = DriverManager.getConnection(dbConnectionString, properties);

Original comment:
"LoL @ your threads - you can do it quite simply by creating a Properties object prop, then prop.put("connectTimeout", "2000") (where the "2000" is the timeout in ms), and then pass your prop object to the DriverManager.getConnection() method along with your url"

Answer 5

As bestro suggested, it's possible to use a future with an associated timeout. For example:

ExecutorService executor = Executors.newSingleThreadExecutor();
Future<Connection> future = executor.submit(new Callable<Connection>() {

    @Override
    public Connection call() throws Exception {
        Connection con = DriverManager.getConnection(url, username, password);
        return con;
    }
});

Connection con = null;
try {
    con = future.get(5, TimeUnit.SECONDS);
} catch (InterruptedException | ExecutionException | TimeoutException ex) {
    Logger.getLogger(Scratch.class.getName()).log(Level.SEVERE, null, ex);
}
executor.shutdownNow();

if (con == null) {
    System.out.println("Could not establish connection");
} else {
    System.out.println("Connection established!");
}

Answer 6

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);