Java -AutoRetry connection by JDBC Driver for SQLServer No53

How to implement auto retry with some internal by Java is shown in this blog. the connection string using Microsoft JDBC Driver for SQL Server “Connection String” has a feature of auto retry with some internal.

▼1. Auto retry with some interval feature of Connection String of Microsoft JDBC Driver for SQL Server (Version 9.4 以上)

As the mandatory setting for a cloud service, the following retry with some interval in a connection string can be used after “Microsoft JDBC Driver 9.4 for SQL Server”.

  • connectRetryCount (Default 1, unit time) : the number of retry between 0 and 255
  • connectRetryInterval (Default 10、unit second) : wait time until retry

▼2. Writing Java code to implement auto-retry when failing to connect to SQL Server

2.1. Creating a Java code for auto-retry connection

This code uses the database “TestDB” that was created in advance. since SQL authentication is used , “sa” user and the password of “sa” are added in the connection string. this code will implement 3 retries with 15 seconds interval.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

public class ConSQLRetry2 {
    public static void main(String[] args) throws InterruptedException{
        Connection con = null;

        System.out.println("START: " + new Timestamp(new Date().getTime()));
        try {
            String connectionUrl = "jdbc:sqlserver://localhost:1433;" + "databaseName=TestDB;user=sa;password=Password;loginTimeout=30;socketTimeout=60000;queryTimeout=60;cancelQueryTimeout=60;connectRetryCount=3;connectRetryInterval=15;;encrypt=true;trustServerCertificate=true;";
            System.out.println("Connecting to SQL Server...: " + new Timestamp(new Date().getTime()));
            con = DriverManager.getConnection(connectionUrl);
        } catch (SQLException se) {
            System.out.println("Connection failed after several retries.");
            se.printStackTrace();
        }
        System.out.println("This connection ended - Current Timestamps 1: " + new Timestamp(new Date().getTime()));
    }
} 

(**note) An environment is Ubuntu 20.04.4 LTS, Microsoft SQL Server 2022 (CTP2.0) -16.0.600.9 x64

2.2. Showing logs when the connection succeeded.

START: 2022-06-24 10:23:00.906
Connecting to SQL Server...: 2022-06-24 10:23:00.913
This connection ended - Current Timestamps 1: 2022-06-24 10:23:01.488

2.3. Showing logs when the connection failed

The incorrect port 1433 is set in this case instead of the correct listening port 1433 of SQL Server.

START: 2022-06-24 10:24:07.665
Connecting to SQL Server...: 2022-06-24 10:24:07.692
Connection failed after several retries.
This connection ended - Current Timestamps 1: 2022-06-24 10:24:37.687
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1443 has failed. Error: "Connection refused (Connection refused). Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:237)
	at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:288)
	at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2720)
	at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:761)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3180)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2833)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2671)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1640)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:936)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
at ConSQLRetry2.main(ConSQLRetry2.java:15)

▼3. Reference

1 Setting the connection properties
2 Building the Connection
3 Ubuntu: Install SQL Server on Linux – SQL Server | Microsoft Docs
4 Java – Connect to SQL Server on Ubuntu No.5
5 Java – Error handling & Retry in SQL Server on Ubuntu No.6
6 Java – Connection String of Microsoft JDBC Driver for SQL Server No.7

That’s all. Have a nice day ahead !!!

Leave a Reply

Your email address will not be published. Required fields are marked *