Retry JDBC connection in Java

0
votes

I am trying to have a retry logic for getting JDBC connection in case I get SQL Exception with something like :

    int counter = 0;
Connection conn = null;
    while (null == conn) {
      try {
        conn = GetConnectionObject;

      } catch (SQLException e) {
        if (++counter > MAX_RETRY) {
          //Log ERROR
          break;
        }
      } finally {
        if (null != conn) {
          try {
            DbUtils.close(conn);
          } catch (SQLException e) {
            logger.error("Exception while closing the connection object");
          }
        }
      }
    }

I cannot test this currently hence need some help.

This will work fine if I get exception and then I can log after retrying. But if we DO NOT get exception, it will come to the finally block and close the connection. Try-Catch-Finally are inside while loop.

So If I close my connection, flow if reach

while( null== conn)

Will my connection object become null after closing ?

Or If there is some other way around to implement retry part ?

2
javajdbc
Only explicitly setting conn to null will make it null. Closing a connection doesn't make the variable null, it just closes the connection. – Mark Rotteveel
Note we prefer a technical style of writing here. We gently discourage greetings, hope-you-can-helps, thanks, advance thanks, notes of appreciation, regards, kind regards, signatures, please-can-you-helps, chatty material and abbreviated txtspk, pleading, how long you've been stuck, voting advice, meta commentary, etc. Just explain your problem, and show what you've tried, what you expected, and what actually happened. – halfer

2 Answers

0
votes

No, it won't become null after closing. Use Connection.isClosed() instead of while( null== conn). Also, you should get //Do some task. out of this code since it's goal is to get a JDBC connection.

0
votes

Here is the tested method for your problem. This method tries 3 times for the connection and when it will get the DB connection it will Print Success message and will run the query and display the result otherwise it will print the error message. Also if the connection is successful then it will close the connection after executing query in finally block.

public void retryDBConnection(){
int counter = 0;
        Connection con = null;
        while(counter < 3 && con == null){
        try{
            String str = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            Class.forName(str).newInstance();
            con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;Database=TestDB;", "sa", "sqldb");
            System.out.println("DB Connection Successful!");
            PreparedStatement prep = con.prepareStatement("select ID, User_Name from tblUser where ID = 9");
            ResultSet rs = prep.executeQuery();
            if(rs.next()){
                System.out.println("User ID = " + rs.getString(1));
                //name = rs.getString(2);
            }
        }
        catch(SQLException e){
//          System.out.println(e.getSQLState());
            if(e.getErrorCode() == 0 || e.getErrorCode() == 4060)
            counter++;
            System.out.println("Attempt: " + counter +", Could not establish DB Connection!");
            System.out.println("Error Code: " + e.getErrorCode());

        }
    catch(Exception e){
        e.printStackTrace();
    }finally{
        if(con != null){
            try {
                con.close();
                System.out.println("Connection closed...");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    }

}

Here is the out put of the method.

Attempt: 1, Could not establish DB Connection! Error Code: 0 Attempt: 2, Could not establish DB Connection! Error Code: 4060 DB Connection Successful! User ID = 9 Connection closed...