[FIXED] Error: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed

Issue

Whenever I restart the Tomcat server, my webservice code is running fine at first time and produces the result however its throwing below error from second time onwards

com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

This error occurs only after I add try catch finally statement and closing the connections in finally block. Below is my code. I dont know where the issue is. Appreciate if you can help me on this.

package com.CIBIL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.CIBIL.dao.InitialiseCIBILDatabaseConnection;
import com.CIBIL.xmldom.AddressType;
import com.CIBIL.xmldom.ApplicantInfo;
import com.CIBIL.xmldom.BanktypeType;
import com.CIBIL.xmldom.CredithistoryType;
import com.CIBIL.xmldom.IdentityType;
import com.CIBIL.xmldom.PersonalType;

public class RetrieveData {
	
	String sqlquery = null;
	Connection repositoryDatabaseConnection = null; 
	ResultSet queryResult = null;
	PreparedStatement sqlToFetch = null;
	
	public ApplicantInfo retrievalApplicantData(String AadhaarNumber) throws Exception{
			
		repositoryDatabaseConnection = InitialiseCIBILDatabaseConnection.getFrameworkRepositoryDatabaseConnection();
		
		try {
			
			sqlquery = "Select a.[aadharid], p1.[pan], p2.[firstname], p2.[lastname], p2.[addressline1], p2.[addressline2], p2.[addressline3], " +
										"p2.[addressline4], p2.[addressline5], p2.[addressline6], p2.[city], p2.[state], p2.[pincode], p2.[country], p2.[mobile], p2.[DOB], " +
										"b.[bank_name] ,b.[bank_account] ,b.[ifsc_code],chs.[ccj] , chs.[defaults], chs.[bankrupt], chs.[cheque_fail], chs.[credit_score] " +
										"from [CIBIL].[dbo].[cibil_customer_aadharid] a " +
										"join [CIBIL].[dbo].[cibil_customer_pan] p1 on a.aadharid = p1.aadharid " +
										"join [CIBIL].[dbo].[cibil_customer_personal] p2 on p1.pan = p2.pan " +
										"join [CIBIL].[dbo].[cibil_customer_bank] b on p1.pan = b.pan " +
										"join [CIBIL].[dbo].[cibil_credit_hostory_and_score] chs on p1.pan = chs.pan " +
										"Where p1.aadharid = '"+ AadhaarNumber + "';";
			
			sqlToFetch = repositoryDatabaseConnection.prepareStatement(sqlquery);
			ResultSet queryResult = sqlToFetch.executeQuery();

			ApplicantInfo ApplicantObject = new ApplicantInfo();
			IdentityType IdentityObject = new IdentityType();
			PersonalType PersonalObject = new PersonalType();
			AddressType AddressObject = new AddressType();
			BanktypeType BankObject = new BanktypeType();
			CredithistoryType CreditObject = new CredithistoryType();
			
			while(queryResult.next())
			{
					
				IdentityObject.setAadhaarNumber(queryResult.getString("aadharid"));
				IdentityObject.setPanNumber(queryResult.getString("pan"));
				PersonalObject.setFirstName(queryResult.getString("firstname"));
				PersonalObject.setLastName(queryResult.getString("lastname"));
				AddressObject.setAddressline1(queryResult.getString("addressline1"));
				AddressObject.setAddressline2(queryResult.getString("addressline2"));
				AddressObject.setAddressline3(queryResult.getString("addressline3"));
				AddressObject.setAddressline4(queryResult.getString("addressline4"));
				AddressObject.setAddressline5(queryResult.getString("addressline5"));
				AddressObject.setAddressline6(queryResult.getString("addressline6"));
				AddressObject.setCity(queryResult.getString("city"));
				AddressObject.setState(queryResult.getString("state"));
				AddressObject.setPincode(queryResult.getString("pincode"));
				AddressObject.setCountry(queryResult.getString("country"));
				PersonalObject.setMobile(queryResult.getString("mobile"));
				PersonalObject.setDob(queryResult.getString("DOB"));
				BankObject.setBankName(queryResult.getString("bank_name"));
				BankObject.setBankAccountNumber(queryResult.getString("bank_account"));
				BankObject.setBankIFSCCode(queryResult.getString("ifsc_code"));
				CreditObject.setCcj(queryResult.getString("ccj"));
				CreditObject.setBankdefault(queryResult.getString("defaults"));
				CreditObject.setBankruptcy(queryResult.getString("bankrupt"));
				CreditObject.setChequeBounce(queryResult.getString("cheque_fail"));
				CreditObject.setFinalCreditScore(queryResult.getString("credit_score"));
				
				ApplicantObject.setApplicantIdentityInfo(IdentityObject);
				ApplicantObject.setApplicantPersonalInfo(PersonalObject);
				ApplicantObject.setApplicantAddressInfo(AddressObject);
				ApplicantObject.setApplicantBankInfo(BankObject);
				ApplicantObject.setApplicantCreditHistoryInfo(CreditObject);
			}
			
			return ApplicantObject;

		} catch (Exception e) {
			e.printStackTrace();
		}	
		finally{
			if (queryResult != null) {
		        try {
		        	queryResult.close();
		        } catch (SQLException e) { /* ignored */}
		    }
		    if (sqlToFetch != null) {
		        try {
		        	sqlToFetch.close();
		        } catch (SQLException e) { /* ignored */}
		    }
		    if (repositoryDatabaseConnection != null) {
		        try {
		        	repositoryDatabaseConnection.close();
		        } catch (SQLException e) { /* ignored */}
		    }
		}
		return null;
		
	}
}

Solution

You should be using PreparedStatement and binding variables.

Your best bet for learning how to use JNDI data sources is the Tomcat documentation.

Answered By - duffymo