I would like to share the detail of connection string of JDBC Driver for SQL Server in this blog.
Contents
▼1. Connection String of Microsoft JDBC Driver for SQL Server
Connection String contains login timeout, socket timeout,query timeout, query timeout, authentication way and application name etc.
▼2. Example for the connection string of JDBC Driver for SQL Server
We can avoid intermittent connection issue by implementing timeout, retry and interval of retry etc.
- loginTimeout (Default 15 second) : The number of seconds the driver should wait before timing out a failed connection.
- socketTimeout (Default infinite, unit: millisecond) : The number of milliseconds to wait before a timeout is occurred on a socket read or accept.
- queryTimeout (Default -1 which means infinite, unit: second) : The number of seconds to wait before a timeout has occurred on a query.
- cancelQueryTimeout (Default infinite, unit: second) : This property can be used to cancel a queryTimeout set on the connection. Query execution hangs and doesn’t throw an exception if the TCP connection to the server is silently dropped. This property is only applicable if ‘queryTimeout’ is also set on the connection.The driver waits the total amount of cancelQueryTimeout + queryTimeout seconds, to drop the connection and close the channel.
This is an example for the connection string of Microsoft JDBC Driver for SQL Server.
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
public class ConnectionSQLSrvRetry2 {
private static final Logger log = LoggerFactory.getLogger(ConnectionSQLSrvRetry2.class);
public static void main(String[] args) throws InterruptedException {
Connection con = null;
boolean success = false;
int retryc = 0;
int max_retries = 3;
while (!success && retryc < max_retries) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:1433;" + "databaseName=TestDB;user=sa;password=password;loginTimeout=30;socketTimeout=60000;queryTimeout=60;cancelQueryTimeout=60;";
con = DriverManager.getConnection(connectionUrl);
System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
log.info("Connect to SQL Server successfully");
success = true;
} catch (ClassNotFoundException e) {
retryc++;
System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
log.error("Error Message: {}, retry count is {}", e, retryc);
// 30 seconds (30000 milliseconds) interval
Thread.sleep(30000);
} catch (SQLException se) {
retryc++;
System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
log.error("Error Messages: {}, retry count is {}", se, retryc);
// 30 seconds (30000 milliseconds) interval
Thread.sleep(30000);
}
}
try {
if (con != null) {
con.close();
System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
log.info("The connection is closed");
}else{
log.info("The connection was closed");
}
} catch (SQLException sec) {
System.out.println("Current Timestamps: " + new Timestamp(new Date().getTime()));
log.error("Error MEssages of fin ", sec);
}
}
}▼3. Reference
That’s all. Have a nice day ahead !!!