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 !!!