CODEWITHSUNDEEP
javamysqldatabasetomcatjdbc
Ananya Krishnaswamy
Ananya Krishnaswamy

Reputation: 68

How to specify millisecond timeouts in JDBC calls to MySQL database?

In developing a fast MySQL Java application, how can I specify timeouts in milliseconds when performing the following operations:

  1. getting a connection from the connection pool
  2. executing a query

I am unable to find the answer to (1) in the MySQL documentation on Connection Pooling with Connector/J.

I did find the maxWait connection pool attribute in Tomcat's connection pool that allows for specifying a timeout in milliseconds as a pool attribute. If the getConnection operation takes more than maxWait time then a timeout exception is raised.

Is the use of Tomcat connection pool the only option for my first requirement?

Regarding the second requirement of specifying a query timeout, the JDBC document has the method setQueryTimeout on the Statement object. But this takes the timeout value in seconds (an integer value). How can my requirement of specifying timeout in milliseconds be met?

Upvotes: 1

Views: 1617

Answers (1)

Erwin Bolwidt
Erwin Bolwidt

Reputation: 31269

If your library doesn't have builtin support for timeouts, you can always use an ExecutorService and a Future to emulate a timeout:

private ExecutorService executor = Executors.newCachedThreadPool();
private DataSource dataSource;

public void doSomething() throws InterruptedException, ExecutionException, TimeoutException, SQLException {
    Connection connection =
            executor.submit(() -> dataSource.getConnection()).get(400, TimeUnit.MILLISECONDS);
    try (Statement stmt = connection.createStatement()) {
        try (ResultSet resultSet = executor.submit(() -> stmt.executeQuery("SELECT * FROM blah"))
                .get(800, TimeUnit.MILLISECONDS)) {
            // Do something with result set
        }
    }
}

This will leave threads in the threadpool running (trying to complete the getConnection or the query) but you can respond within a set time to your user / caller.

Upvotes: 1

Related Questions