Lesson Learned #260: Java vs Connection is closed error message.
Published Dec 28 2022 03:28 AM 1,183 Views

Today, I worked on a service request that our customer got the following error message using Java code: Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:1202)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement.java:1063)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:445)
at testconnectionms.SQLTest.main(SQLTest.java:64). I would like to share with you what was my lessons learned here.

 

The first thing is to isolate the problem running the suggested Java code to see the results:

 

package testconnectionms;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.lang.model.util.ElementScanner6;
import javax.sql.DataSource;

/**
 * Simple connection test.
 *
 */
public class SQLTest
{
	static {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (Exception ex) {
			System.err.println("Unable to load JDBC driver");
			ex.printStackTrace();
			System.exit(1);
		}
	}

	public static void main(String[] args)
		throws SQLException
	{
		String username   = "username";
		String password   = "Password";
		String envname    = "env";
		String seconds   = "10";
		        
		System.out.println("Arguements are: username="+username+",password="+password+",envname="+envname+",seconds="+seconds);
		
		String url = String.format("jdbc:sqlserver://servername.database.windows.net:1433;database=dbname;sslProtocol=TLSv1.2", envname,envname);

		System.out.println("-- Connecting to " + url);
		
		long start = System.currentTimeMillis();
		Connection connection = DriverManager.getConnection(url, username, password);

		if (false) {
			
		}
		
		final long time1 = System.currentTimeMillis();
		long time2 = time1;
		long cnt = 0;
		Integer secInt = Integer.valueOf(seconds);

        PreparedStatement st = connection.prepareStatement("SELECT * from demo");
		st.setQueryTimeout(20);
		while (time1 + (secInt.intValue() * 1000) >= time2)
		{
			cnt++;
			
			
			ResultSet rs = st.executeQuery();
			rs.next();
			rs.close();
			
			
			time2 = System.currentTimeMillis();
		}
		st.close();
		
		Long statementsCount = Long.valueOf(cnt);
		Long statementsPerSecond = Long.valueOf(cnt / ((time2 - time1) / 1000));
		Double timePerStatement = Double.valueOf(1000.0 / (cnt / ((time2 - time1) / 1000.0)));
		
		System.out.println("statementsCount="+statementsCount);
		System.out.println("statementsPerSecond="+statementsPerSecond);
		System.out.println("timePerStatement="+timePerStatement);
	}
	
	private static void usage(String msg)
	{
        System.err.println("Usage: SQLTest -username username -password password -envname envname -seconds seconds");
        System.err.println(msg);
	}
}

 

This Java script worked well, also, using TCPView  we could see that even using redirect the connection is working fine. 

 

Jose_Manuel_Jurado_0-1672226464340.png

 

But, what is happening if we have any transient issue in the connectivity?, in this situation, we are going to have a similar error message The connection is closed that we shared before and in this situation, the suggestion is always to use a connection and execution retry-logic.

 

Example for connection retry-logic class.

 

package testconnectionms;

import java.sql.*;


public class ClsRetryLogic 
{
    private Connection oConnection;
    private boolean bClose = true;
    private long lConnectiontime=0;
    public boolean HazUnaConexionConReintentos(String sConnection)
    {
     int retryIntervalSeconds = 10;
     double calculation;
     boolean returnBool=false;
    
     for (int tries = 1; tries <= 5; tries++)
     {
       try
         { 
          if(tries>1) 
          {
            System.out.println("Waiting time: " + retryIntervalSeconds);
            Thread.sleep(1000 * retryIntervalSeconds);
            calculation = retryIntervalSeconds * 1.5;
            retryIntervalSeconds = (int) calculation;            
          }
         System.out.println("Connecting to Database");
         final long time1 = System.currentTimeMillis();
         oConnection = DriverManager.getConnection(sConnection);
         System.out.println("Connected to Database");         

         final long time2 = System.currentTimeMillis();
         this.setConnectiontime((time2 - time1));
  

         returnBool = true;
         break;
         }
       catch(Exception e)
       {
        System.out.println("Error connecting to .. " + e.getMessage());
       }  
    }  
    return returnBool;
}

  public Connection getConn() {
    return oConnection;
  }

  public void setCloseConnection(boolean bCloseIndicator)
  {
      this.bClose=bCloseIndicator;
  }

  public boolean getCloseConnection()
  {
      return this.bClose;
  }

  public void setConnectiontime(long lTime)
  {
      this.lConnectiontime=lTime;
  }

  public long getConnectiontime()
  {
      return this.lConnectiontime;
  }


}

 

Example for Execution retry-logic class.

 

package testconnectionms;
import java.sql.*;

public class ErrorClient {
    private boolean bClose = true;
    private boolean bReadingSQLData = true;
    private int iTotalIteractions = 1;
    private int iDelay=0;
    private String sSQLConnection = "";
    private String sSQLToExecuteRead = "";
    private int iTimeSQLCommandTimeout=30;

    public void LoadData() throws SQLException 
    {
    ClsRetryLogic[] oRetryLogic = new ClsRetryLogic[this.getTotalIteractions()];
    for(int i=0;i<this.getTotalIteractions();i++)
    {
      oRetryLogic[i]=new ClsRetryLogic();  
      oRetryLogic[i].setCloseConnection(bClose);
      System.out.println("Interaction # " + i);
      if( oRetryLogic[i].HazUnaConexionConReintentos(this.getSQLConnection() ));
      {
        if(this.getReadingSQLData())
        {
         readData(oRetryLogic[i].getConn());
        }  
        if(oRetryLogic[i].getCloseConnection())
        {
            oRetryLogic[i].getConn().close();
        }
      }
    }
}
    private void readData(Connection connection) {
    for (int tries = 1; tries <= 5; tries++)
     {        
      try
       {
        System.out.println("Reading Data! " + this.getSQLReadToExecute());
        PreparedStatement readStatement = connection.prepareStatement(this.getSQLReadToExecute());
		final long time1 = System.currentTimeMillis();
        readStatement.setQueryTimeout(this.getSQLCommandTimeout());
        ResultSet resultSet = readStatement.executeQuery();
        while(resultSet.next())
        {
          System.out.println(resultSet.getLong("id"));
        }  
        final long time2 = System.currentTimeMillis();
		Long statementsPerSecond = Long.valueOf((time2 - time1) / 1000);
		System.out.println("Time="+statementsPerSecond);
        break;
      }   
      catch(Exception e)
      {
       System.out.println("Reading Data " + tries + " - Error  .. " + e.getMessage());
      }  
     }
    }

    public void setCloseConnection(boolean bCloseIndicator)
    {
        this.bClose=bCloseIndicator;
    }
  
    public boolean getCloseConnection()
    {
        return this.bClose;
    }

    public void setReadingSQLData(boolean bReadingSQLData)
    {
        this.bReadingSQLData=bReadingSQLData;
    }
  
    public boolean getReadingSQLData()
    {
        return this.bReadingSQLData;
    }

    public void setTotalIteractions(int iTotalIteractions)
    {
        this.iTotalIteractions=iTotalIteractions;
    }
  
    public int getTotalIteractions()
    {
        return this.iTotalIteractions;
    }

    public void setDelay(int iDelay)
    {
        this.iDelay=iDelay;
    }
  
    public int getDelay()
    {
        return this.iDelay;
    }

    public void setSQLConnection(String sSQL)
    {
        this.sSQLConnection=sSQL;
    }
  
    public String getSQLConnection()
    {
        return this.sSQLConnection;
    }

    public void setSQLReadToExecute(String sSQL)
    {
        this.sSQLToExecuteRead=sSQL;
    }
  
    public String getSQLReadToExecute()
    {
        return this.sSQLToExecuteRead;
    }
    public void setSQLCommandTimeout(int i)
    {
        this.iTimeSQLCommandTimeout=i;
    }
  
    public int getSQLCommandTimeout()
    {
        return this.iTimeSQLCommandTimeout;
    }
 

}

 

Enjoy!

Version history
Last update:
‎Dec 28 2022 03:28 AM
Updated by: