错误:com.microsoft.sqlserver.jdbc.SQLServerException:连接已关闭

Question

每当我重新启动Tomcat服务器时，我的Web服务代码第一次运行良好并产生结果，但是从第二次开始抛出以下错误

com.microsoft.sqlserver.jdbc.SQLServerException：连接已关闭。

只有在我添加 try catch finally 语句并在 finally 块中关闭连接后，才会发生此错误。下面是我的代码。我不知道问题出在哪里。如果您能在这方面帮助我，请不胜感激。

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;
		
	}
}

Answer 1

你应该使用 PreparedStatement 和绑定变量。

学习如何使用 JNDI 数据源的最佳选择是 Tomcat 文档。