Java JDBC connection status

Question

I am (successfully) connecting to a database using the following:

java.sql.Connection connect = DriverManager.getConnection(
  "jdbc:mysql://localhost/some_database?user=some_user&password=some_password");

What should I be checking to see if the connection is still open and up after some time?
I was hoping for something like connect.isConnected(); available for me to use.

score 59 · Answer 1 · edited Mar 07 '23 at 21:20

UPDATE

Oh, I just saw there is a new method available since 1.6:

java.sql.Connection.isValid(int timeoutSeconds):

Returns true if the connection has not been closed and is still valid. The driver shall submit a query on the connection or use some other mechanism that positively verifies the connection is still valid when this method is called. The query submitted by the driver to validate the connection shall be executed in the context of the current transaction.

ORIGINAL ANSWER

Your best chance is to just perform a simple query against one table, e.g.:

select 1 from SOME_TABLE;

postgresql jdbc executes "select 1", for the record. – rogerdpack Jun 16 '15 at 20:25 — rogerdpack, Jun 16 '15 at 20:25

score 34 · Accepted Answer · edited Nov 15 '16 at 14:50

34

Nothing. Just execute your query. If the connection has died, either your JDBC driver will reconnect (if it supports it, and you enabled it in your connection string--most don't support it) or else you'll get an exception.

If you check the connection is up, it might fall over before you actually execute your query, so you gain absolutely nothing by checking.

That said, a lot of connection pools validate a connection by doing something like SELECT 1 before handing connections out. But this is nothing more than just executing a query, so you might just as well execute your business query.

edited Nov 15 '16 at 14:50

Ahmed Ashour

5,179
10
35
56

answered Oct 14 '11 at 08:00

dty

18,795
6
56
82

2

There's some definite gain in testing the connection before using it (especially if it's a long lived connection and the underlying network might have used it). But this is also accurate in that you still need to handle errors in your own code since it might go down anytime. – rogerdpack Jun 30 '15 at 17:21

score 17 · Answer 3 · edited Jun 05 '18 at 10:53

17

Use Connection.isClosed() function.

The JavaDoc states:

Retrieves whether this Connection object has been closed. A connection is closed if the method close has been called on it or if certain fatal errors have occurred. This method is guaranteed to return true only when it is called after the method Connection.close has been called.

edited Jun 05 '18 at 10:53

Nathan Hughes

94,330
19
181
276

answered Oct 14 '11 at 07:59

Buhake Sindi

87,898
29
167
228

5

As the javadoc says this **will not** give a valid answer in this case. It will only tell whether `close()` has been called or not. – Adam Arold Apr 09 '14 at 15:49
4

OK after some experimentation with the Postgres JDBC driver (at least), basically, if the underlying connection "is lost," `isClosed()` will still return true. However, when you *use* the connection, and it raises any exception, after that point, `isClosed()` will now return true. So basically you can "get away" with using `isClosed` (and avoid the latency of an extra call to `isValid()`) if you can somehow afford an an interrupted query here or there. Other than that `isValid()` is a good way to test, but requires an extra round trip time. – rogerdpack Jun 16 '15 at 20:24
2

Not sure why this has upvotes, this part of the Javadoc was omitted from the answer: `This method generally cannot be called to determine whether a connection to a database is valid or invalid. A typical client can determine that a connection is invalid by catching any exceptions that might be thrown when an operation is attempted.` – heez Jan 07 '19 at 20:58

score 12 · Answer 4 · edited Jan 27 '21 at 10:05

12

You also can use

public boolean isDbConnected(Connection con) {
    try {
        return con != null && !con.isClosed();
    } catch (SQLException ignored) {}

    return false;
}

edited Jan 27 '21 at 10:05

Edson Passos

117
10

answered Oct 03 '16 at 04:35

Lasitha Senanayake

137
1
4

8

Should be, if(con!=null || !con.isClosed()) so that your check is on the object first, if it's null it will not continue with the validation of the IF statement. Your's will throw an exceptions because con might be NULL. Then you don't need the whole try catch at all. – Chizl May 24 '17 at 12:45
1

Or you can just do `return con != null && !con.isClosed()`. – user3932000 Jun 19 '19 at 13:53

score 4 · Answer 5 · answered Jun 17 '15 at 09:52

4

If you are using MySQL

public static boolean isDbConnected() {
    final String CHECK_SQL_QUERY = "SELECT 1";
    boolean isConnected = false;
    try {
        final PreparedStatement statement = db.prepareStatement(CHECK_SQL_QUERY);
        isConnected = true;
    } catch (SQLException | NullPointerException e) {
        // handle SQL error here!
    }
    return isConnected;
}

I have not tested with other databases. Hope this is helpful.

answered Jun 17 '15 at 09:52

Madan Sapkota

25,047
11
113
117

Where does `db` come from? – MonoThreaded Jun 17 '15 at 12:05
See in the question `connect` variable it is `db` whatever you name it. – Madan Sapkota Jun 17 '15 at 14:29
Then you may want to close your PreparedStatement to prevent memory leaks – MonoThreaded Jun 17 '15 at 15:26
@MonoThreaded, do whatever you like, it is just example. I am not writing real app here! – Madan Sapkota Jun 17 '15 at 16:02
1

If you call the `isValid(int timeoutSeconds)` method instead, you basically get this behavior, but with a timeout to boot :) – rogerdpack Jun 30 '15 at 17:19

access_granted · Answer 6 · 2019-12-10T23:10:44.323

1

The low-cost method, regardless of the vendor implementation, would be to select something from the process memory or the server memory, like the DB version or the name of the current database. IsClosed is very poorly implemented.

Example:

java.sql.Connection conn = <connect procedure>;
conn.close();
try {
  conn.getMetaData();
} catch (Exception e) {
  System.out.println("Connection is closed");
}

edited Dec 10 '19 at 23:10

answered Aug 14 '19 at 16:17

access_granted

1,807
20
25

score 0 · Answer 7 · answered Jan 14 '22 at 17:32

Here is a simple solution if you are using JDBC to get the default connection

 private Connection getDefaultConnection() throws SQLException, ApiException {
    Connection connection = null;
    try {
         connection = dataSource.getConnection ();
    }catch (SQLServerException sqlException) {
        // DB_UNAVAILABLE EXCEPTION
    }
    return connection;
}

Java JDBC connection status

7 Answers7

UPDATE

ORIGINAL ANSWER

Linked