should close connection or close session?

[raj chopra]

Hii All,

my project uses jdbc and hibernate while executing queries. most of time connections,session,resultsets and prepared statements,callable statements were not closed.
This leads lo resource management issues and i tend to close the connections. so i added the try catch finally block and modified the code to close the connections

In this regard i have a dialama in closing the connections or the sessions? which would be better and safe practise?

sample code would be

public String getBypassClientIds() throws Exception {
		String bypassClienIDs = null;
		Session session =getSession();
		Connection con = null;
		ResultSet rs  = null;
		PreparedStatement stmt = null;
		try 
		{
				// modified the existing try cath and added finally block to close the connection 			
			con = session.connection();
			 stmt = con.prepareStatement(SBShortlistedBypass);
			
			rs = stmt.executeQuery();
			if(rs.next())
			{
				bypassClienIDs=rs.getString(1);
			
			}
			releaseSession(session);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("raj says that the finally loop was added here in getBypassClientIds() of sbwinningbiddaohibernate and exception was "+e);
			
		}
		finally
		{
			rs.close();
			stmt.close();
			con.close();  or session.close() ;
		}
		return bypassClienIDs;

	}

[K. Tsang]

Connection is the way to go.

Assuming it's web app, this Session (HttpSession) will get invalidated when the user logs out or when the app idles too long and timeout. Closing such session after each database transaction can have what effect on your app?

[Paul Clapham]

Seems to me what would be better (in the normal sort of application) would be to tell Hibernate where your connection pool is, and let Hibernate manage that. Then use Hibernate transactions to manage your database transactions, and use Hibernate sessions according to one of the preferred ways described in the Hibernate documentation.

Trying to manage your own database connections seems to me to be a recipe for confusion, and indeed that seems to be what is happening.

[raj chopra]

Thanks for your reply Tsang,

Yes my project is web application. i am not sure on the effets of closing session in my app, because the code is inconsistent . on few areas code involved hibernate sessions and few orther areas with simple jdbc connections.on trial and error basis i closed few connections last time and cross cheked in tomcat server cmd prompt. it was ok on few classes, and connection closed out exception was result on orther class files.

with your suggestions i prefer to close the connection, as my primary idea is to close the database connection once the query is performed.

hope i am going in right path.

[K. Tsang]

It is unusual to use both JDBC and Hibernate together in different classes. I suggest you use Hibernate or JDBC throughout. If using Hibernate, like Paul said let the Hibernate code handle the connection pool and stuff. If JDBC you should close the connection after each call.

[raj chopra]

yes the code is incosistant that's the problem i am dealing with.

could you suggest me how do i proceed in closing the connection in below file

package com.syntel.sb.arch.batch.dao.hibernate;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import org.hibernate.Session;

import com.syntel.sb.appl.bo.client.SBReqLineItem;
import com.syntel.sb.appl.vo.admin.MailerVO;
import com.syntel.sb.arch.batch.common.SBAutoGen;
import com.syntel.sb.arch.batch.common.SBBatEndLog;
import com.syntel.sb.arch.batch.common.SBBatStartLog;
import com.syntel.sb.arch.batch.common.SBBatchQueries;
import com.syntel.sb.arch.batch.common.SBErrorBat;
import com.syntel.sb.arch.batch.dao.SBAuctionClosingDAO;
import com.syntel.sb.arch.batch.vo.SBAuctionClosingVO;
import com.syntel.sb.arch.batch.vo.SBBatEndVO;
import com.syntel.sb.arch.batch.vo.SBBatStartVO;
import com.syntel.sb.arch.batch.vo.SBClientReqClosedOutConfirmationVO;
import com.syntel.sb.arch.batch.vo.SBErrorBatVO;
import com.syntel.sb.arch.dao.hibernate.SBBaseDAOHibernate;
import com.syntel.sb.arch.util.MailerComponent;
import com.syntel.sb.arch.util.SBDateUtil;
import com.syntel.sb.arch.util.SBLoadProps;

public class SBAuctionClosingDAOHibernate extends SBBaseDAOHibernate implements SBAuctionClosingDAO, SBBatchQueries {

	String toAddress=null;
	List ccAddress=null;

	static int count=0;

	public SBAuctionClosingVO closeAuction(SBAuctionClosingVO sbAuctionClosingVO) throws Exception {
		logger.debug("in SBAuctionClosingDAOHibernate----------------");
		SBBatStartLog batStartLog=new SBBatStartLog();
		SBBatStartVO sbBatStartVO =new SBBatStartVO();
		SBErrorBat sbErrorBat = new SBErrorBat();
		SBErrorBatVO sbErrorBatVO =new SBErrorBatVO();
		SBClientReqClosedOutConfirmationVO sbClientReqClosedOutConfirmationVO=new SBClientReqClosedOutConfirmationVO();
		//System.out.println("In DAO Hibernate");
		Session session = getSession();
		//System.out.println("session.................. "+session.connection());
		Connection con = session.connection();
		//System.out.println("In DAO After Conection");
		SBAutoGen sbAutoGen=new SBAutoGen();
		StringBuffer buff=new StringBuffer();
		try
		{

			Statement st1=con.createStatement();
			ResultSet qryGetEmailInfo=st1.executeQuery(auctionClosing001);
			if(qryGetEmailInfo.next())
			{
				sbAuctionClosingVO.setMailSubject("Auction Close Advice");
			}

			PreparedStatement pst1=con.prepareStatement(commonSlctLoginId);
			pst1.setInt(1, 90);

			ResultSet qryUserId=pst1.executeQuery();
			if(qryUserId.next())
			{
				sbAuctionClosingVO.setLoginId(qryUserId.getString(1));
				//System.out.println("Inside User ID query");
			}
			PreparedStatement sample = con.prepareStatement(auctionClosing002);
			ResultSet qryAllReqs = sample.executeQuery();
			//System.out.println("before qryAllReqs");
			while(qryAllReqs.next())
			{

					int count1=0;
					int temp = 0;
					sbAuctionClosingVO.setReqId(qryAllReqs.getInt(1));
					sbAuctionClosingVO.setReqLineItemId(qryAllReqs.getInt(7));
					sbAuctionClosingVO.setClientID(qryAllReqs.getInt(4));

					PreparedStatement pst2=con.prepareStatement(auctionClosing003);

					pst2.setInt(1, 43);
					pst2.setString(2, sbAuctionClosingVO.getLoginId());
					pst2.setInt(3, sbAuctionClosingVO.getReqLineItemId());
					pst2.setInt(4, sbAuctionClosingVO.getReqId());
					pst2.setInt(5, 33);

					ResultSet qryUpdate43=pst2.executeQuery();

					PreparedStatement pst3=con.prepareStatement(auctionClosing004,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					pst3.setInt(1, sbAuctionClosingVO.getReqLineItemId());
					pst3.setInt(2, sbAuctionClosingVO.getReqId());

					ResultSet qryWinningBids=pst3.executeQuery();
				//Girish 			08-OCT-2007		Changes made for mantis id 0000356  start

					PreparedStatement pst33=con.prepareStatement(auctionClosing0044,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					pst33.setInt(1, sbAuctionClosingVO.getReqLineItemId());
					pst33.setInt(2, sbAuctionClosingVO.getReqId());

					ResultSet qryForConfirmedBids=pst33.executeQuery();

					while(qryForConfirmedBids.next())
					{
						temp++;
					}
//System.out.println("temptemp*****************"+temp);
					//System.out.println("i'm before qryWinningBids");
					qryWinningBids.beforeFirst();
					sbAuctionClosingVO.setWiningBidrecordCountFlag(0);
					sbAuctionClosingVO.setRecordCountFlag(0);
					if(!qryWinningBids.next())
					{
						sbAuctionClosingVO.setWiningBidrecordCountFlag(1);
						PreparedStatement pst4=con.prepareStatement(auctionClosing016,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
						pst4.setInt(1, sbAuctionClosingVO.getReqLineItemId());
						pst4.setInt(2, sbAuctionClosingVO.getReqId());
						ResultSet qryearlywinningbids=pst4.executeQuery();
						qryearlywinningbids.beforeFirst();
						if(!qryearlywinningbids.next()){
							sbAuctionClosingVO.setRecordCountFlag(1);
						}
					}

				    qryWinningBids.beforeFirst();
					while(qryWinningBids.next())
					{
						count1++;
					
					}
//					System.out.println("count1-------------"+count1);
					qryWinningBids.beforeFirst();
					int count2=0;
					String autoKeyGenerationTable="PLACEMENT_INTERVIEW_SESSION";
					int newKeyGenerated=0;

					while(qryWinningBids.next())
					{
						count2++;

						sbAuctionClosingVO.setCandID(qryWinningBids.getInt(6));
						sbAuctionClosingVO.setBidID(qryWinningBids.getInt(5));
						sbAuctionClosingVO.setInterviews(qryWinningBids.getInt(1));
						sbAuctionClosingVO.setMemberID(qryWinningBids.getInt(8));
						sbAuctionClosingVO.setClientID(qryWinningBids.getInt(7));
						sbAuctionClosingVO.setRevisionNumber(qryWinningBids.getInt(4));
						if(sbAuctionClosingVO.getInterviews() < (count1+temp))
						{
							if((temp+count2)<= sbAuctionClosingVO.getInterviews())
							{
				//Girish 			08-OCT-2007		Changes made for mantis id 0000356 end
								PreparedStatement pst5=con.prepareStatement(auctionClosing017);
								pst5.setString(1, sbAuctionClosingVO.getLoginId());
								pst5.setInt(2, sbAuctionClosingVO.getBidID());
								int qryUpdate50=pst5.executeUpdate();

								autoKeyGenerationTable="PLACEMENT_INTERVIEW_SESSION";
								sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
								//System.out.println(sbAuctionClosingVO.getAutoKeyTableName());
								newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

								PreparedStatement pst6=con.prepareStatement(auctionClosing006);
								pst6.setInt(1, sbAuctionClosingVO.getCandID());
								//System.out.println("candidateID:"+sbAuctionClosingVO.getCandID());
								ResultSet qryResume=pst6.executeQuery();

								if(qryResume.next())
									
								{
									if(qryResume.getString(1)==null){
										sbAuctionClosingVO.setResumeFlag(0);
									}
									else{
										sbAuctionClosingVO.setResumeFlag(1);
									}
								}

								PreparedStatement pst7=con.prepareStatement(auctionClosing008);
								pst7.setInt(1,sbAuctionClosingVO.getBidID());
								pst7.setInt(2,newKeyGenerated);
								pst7.setInt(3,sbAuctionClosingVO.getCandID());
								pst7.setInt(4,sbAuctionClosingVO.getMemberID());
								pst7.setInt(5,sbAuctionClosingVO.getRevisionNumber());
								pst7.setInt(6,sbAuctionClosingVO.getClientID());
								pst7.setString(7, sbAuctionClosingVO.getLoginId());

								int qryInsertPlacement=pst7.executeUpdate();

								autoKeyGenerationTable="E_MAIL";

								sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);

								newKeyGenerated = sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

								PreparedStatement pst8=con.prepareStatement(auctionClosing007);

								pst8.setInt(1, newKeyGenerated);
								pst8.setInt(2, sbAuctionClosingVO.getMemberID());
								pst8.setInt(3, sbAuctionClosingVO.getBidID());
								pst8.setInt(4, sbAuctionClosingVO.getRevisionNumber());
								pst8.setInt(5, qryWinningBids.getInt(2));
								pst8.setInt(6, qryWinningBids.getInt(3));
								pst8.setString(7, sbAuctionClosingVO.getLoginId());
								int qryEmailInsert=pst8.executeUpdate();
							}
							else
							{
								PreparedStatement pst9=con.prepareStatement(auctionClosing018);
								pst9.setString(1, sbAuctionClosingVO.getLoginId());
								pst9.setInt(2, sbAuctionClosingVO.getBidID());
								int qryUpdate40=pst9.executeUpdate();

								autoKeyGenerationTable="E_MAIL";
								sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
								newKeyGenerated =sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);
								//System.out.println("The query is");
								PreparedStatement pst10=con.prepareStatement(auctionClosing019);
								pst10.setInt(1, newKeyGenerated);
								pst10.setInt(2, sbAuctionClosingVO.getMemberID());
								pst10.setInt(3, sbAuctionClosingVO.getBidID());
								pst10.setInt(4, sbAuctionClosingVO.getRevisionNumber());
								pst10.setInt(5, sbAuctionClosingVO.getReqId());
								pst10.setInt(6, sbAuctionClosingVO.getReqLineItemId());
								pst10.setString(7, sbAuctionClosingVO.getLoginId());
								pst10.setInt(8, sbAuctionClosingVO.getClientID());
								int qryEmailInsert1=pst10.executeUpdate();
							}

						}
						else
						{
							PreparedStatement pst11=con.prepareStatement(auctionClosing017);
							pst11.setString(1,sbAuctionClosingVO.getLoginId() );
							pst11.setInt(2, sbAuctionClosingVO.getBidID());
							int qryUpdate50=pst11.executeUpdate();

							autoKeyGenerationTable="PLACEMENT_INTERVIEW_SESSION";
							sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
							//System.out.println(sbAuctionClosingVO.getAutoKeyTableName());
							newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

							PreparedStatement pst12=con.prepareStatement(auctionClosing006);
							pst12.setInt(1, sbAuctionClosingVO.getCandID());
							ResultSet qryResume=pst12.executeQuery();
							if(qryResume.next())
							{
								if(qryResume.getString(1)==null){
									sbAuctionClosingVO.setResumeFlag(0);
								}
								else{
									sbAuctionClosingVO.setResumeFlag(1);
								}
							}

							PreparedStatement pst13=con.prepareStatement(auctionClosing020);
							pst13.setInt(1, sbAuctionClosingVO.getBidID());
							pst13.setInt(2, newKeyGenerated);
							pst13.setInt(3, sbAuctionClosingVO.getCandID());
							pst13.setInt(4, sbAuctionClosingVO.getMemberID());
							pst13.setInt(5, sbAuctionClosingVO.getRevisionNumber());
							pst13.setInt(6, sbAuctionClosingVO.getClientID());
							pst13.setString(7, sbAuctionClosingVO.getLoginId());

							int qryInsertPlacement=pst13.executeUpdate();

							autoKeyGenerationTable="E_MAIL";
							sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
							newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

							PreparedStatement pst14=con.prepareStatement(auctionClosing021);
							pst14.setInt(1, newKeyGenerated);
							pst14.setInt(2, sbAuctionClosingVO.getMemberID());
							pst14.setInt(3, sbAuctionClosingVO.getBidID());
							pst14.setInt(4, sbAuctionClosingVO.getRevisionNumber());
							pst14.setInt(5, sbAuctionClosingVO.getReqId());
							pst14.setInt(6, sbAuctionClosingVO.getReqLineItemId());
							pst14.setString(7, sbAuctionClosingVO.getLoginId());
							pst14.setInt(8, sbAuctionClosingVO.getClientID());

							int qryEmailInsert1=pst14.executeUpdate();
						}
					}
					PreparedStatement pst15=con.prepareStatement(auctionClosing009);
					pst15.setInt(1,sbAuctionClosingVO.getReqLineItemId());
					pst15.setInt(2,sbAuctionClosingVO.getReqId());

					int updatereqs=pst15.executeUpdate();

					autoKeyGenerationTable="E_MAIL";
					sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
					newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);
					PreparedStatement pst16=con.prepareStatement(auctionClosing022);
					pst16.setInt(1, newKeyGenerated);
					if(sbAuctionClosingVO.getRecordCountFlag()==1 && sbAuctionClosingVO.getWiningBidrecordCountFlag()==1 ){
						pst16.setInt(2, 408);
					}
					else{
						pst16.setInt(2, 309);
					}
					if(sbAuctionClosingVO.getWiningBidrecordCountFlag()==0){
						pst16.setString(3, null);
					}
					else{
						pst16.setInt(3, sbAuctionClosingVO.getBidID());
					}
					if(sbAuctionClosingVO.getWiningBidrecordCountFlag()==1){
						pst16.setString(4,null);
					}
					else{
						pst16.setInt(4, sbAuctionClosingVO.getRevisionNumber());
					}
					pst16.setInt(5, sbAuctionClosingVO.getReqId());
					pst16.setInt(6, sbAuctionClosingVO.getReqLineItemId());
					if(sbAuctionClosingVO.getWiningBidrecordCountFlag()==1){
						pst16.setString(7, null);
					}
					else{
						pst16.setString(7, sbAuctionClosingVO.getMailSubject());
					}
					pst16.setString(8, sbAuctionClosingVO.getLoginId());
					pst16.setInt(9, sbAuctionClosingVO.getClientID());

					int updatereqs1=pst16.executeUpdate();
			}


			Statement stmt=con.createStatement();
			ResultSet qryBidForwarded=stmt.executeQuery(auctionClosing023);
			if(qryBidForwarded.next())
			{
				sbAuctionClosingVO.setEmailType(1107);
				sbAuctionClosingVO.setReqId(qryBidForwarded.getInt(1));
				sbAuctionClosingVO.setReqLineItemId(qryBidForwarded.getInt(2));
				sbAuctionClosingVO.setClientID(qryBidForwarded.getInt(3));
				PreparedStatement pst17=con.prepareStatement(auctionClosing024);
				pst17.setString(1, sbAuctionClosingVO.getLoginId());
				pst17.setInt(2, sbAuctionClosingVO.getReqId());
				pst17.setInt(3, sbAuctionClosingVO.getReqLineItemId());
				int UPDATEREQSTATUS35=pst17.executeUpdate();
				PreparedStatement pst18=con.prepareStatement(auctionClosing025);
				pst18.setInt(1, sbAuctionClosingVO.getEmailType());
				ResultSet getMailDetail=pst18.executeQuery();
				if(getMailDetail.next()){
					sbAuctionClosingVO.setMailSubject(getMailDetail.getString(2));
					sbAuctionClosingVO.setEmailStatus(getMailDetail.getInt(3));
				}
			}
			Statement stmt1=con.createStatement();
			ResultSet qryreq3035=stmt.executeQuery(auctionClosing026);
			while(qryreq3035.next())
			{
				//System.out.println("qryreq3035 is executing");
				sbAuctionClosingVO.setReqId(qryreq3035.getInt(1));
				sbAuctionClosingVO.setReqLineItemId(qryreq3035.getInt(2));
				sbAuctionClosingVO.setReqOwner(qryreq3035.getString(3));
				/*saurabh modified on 18th June 2008 for Mantis id 0000405 start*/
				sbAuctionClosingVO.setReqstatuscode(qryreq3035.getInt(4));
				/*saurabh modified on 18th June 2008 for Mantis id 0000405 end*/
				//System.out.println("ReqOwner"+qryreq3035.getString(3));
				PreparedStatement pst19=con.prepareStatement(auctionClosing027);
				pst19.setInt(1, sbAuctionClosingVO.getReqId());
				//System.out.println("reqid:" +sbAuctionClosingVO.getReqId());
				pst19.setInt(2, sbAuctionClosingVO.getReqLineItemId());
				//System.out.println("reqlineid:" +sbAuctionClosingVO.getReqLineItemId());
				ResultSet qrygetplaced=pst19.executeQuery();
				if(qrygetplaced.next()){
					sbAuctionClosingVO.setEmpPlaced(qrygetplaced.getInt(1));
				}
				//System.out.println("empplaced:"+sbAuctionClosingVO.getEmpPlaced());
				PreparedStatement pst20=con.prepareStatement(auctionClosing028);
				pst20.setInt(1, sbAuctionClosingVO.getReqId());
				pst20.setInt(2, sbAuctionClosingVO.getReqLineItemId());
				ResultSet qrygetreqd=pst20.executeQuery();
				if(qrygetreqd.next()){
					sbAuctionClosingVO.setStaffing(qrygetreqd.getInt(1));
				}
				if(sbAuctionClosingVO.getEmpPlaced() >= sbAuctionClosingVO.getStaffing())
				{// logger.debug("in SBAuctionClosingDAOHibernate-xxxxxxxxxxxxx---------------");
					PreparedStatement pst21=con.prepareStatement(auctionClosing015);
					pst21.setInt(1, sbAuctionClosingVO.getReqId());
					pst21.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					int qrygetreqd1=pst21.executeUpdate();
					/*saurabh modified on 18th June 2008 for Mantis id 0000405 start*/
					if (sbAuctionClosingVO.getReqstatuscode()== 20){
						try{
						PreparedStatement pst01=con.prepareStatement(auctionClosing050);
						pst01.setInt(1, sbAuctionClosingVO.getReqId());
						pst01.setInt(2, sbAuctionClosingVO.getReqLineItemId());
						int qrygetaucdtchnge=pst01.executeUpdate();
						}
						catch(Exception e){
							e.printStackTrace();
							}
					}
					/*saurabh modified on 18th June 2008 for Mantis id 0000405 end*/
					sbAuctionClosingVO.setEmailType(1104);
					PreparedStatement pst26=con.prepareStatement(auctionClosing025);
					pst26.setInt(1, sbAuctionClosingVO.getEmailType());
					ResultSet getmailDetail=pst26.executeQuery();
					if(getmailDetail.next()){
						sbAuctionClosingVO.setMailSubject(getmailDetail.getString(2));
						sbAuctionClosingVO.setEmailStatus(getmailDetail.getInt(3));
					}
					PreparedStatement pst27=con.prepareStatement(auctionClosing034);
					pst27.setString(1, sbAuctionClosingVO.getReqOwner());
					ResultSet qrycontacttype=pst27.executeQuery();
					if(qrycontacttype.next()){
						sbAuctionClosingVO.setClientID(qrycontacttype.getInt(4));
						sbAuctionClosingVO.setQryContactEmail(qrycontacttype.getString(3));
						sbAuctionClosingVO.setName(qrycontacttype.getString(2));
					}

					PreparedStatement pst28=con.prepareStatement(auctionClosing035);
					pst28.setInt(1, sbAuctionClosingVO.getClientID());
					ResultSet qryspoccontact=pst28.executeQuery();
					if(qryspoccontact.next()){
						sbAuctionClosingVO.setQryspoccontactemail(qryspoccontact.getString(3));
					}
					Statement stmt5=con.createStatement();
					ResultSet qrystdsign1=stmt5.executeQuery(auctionClosing030);
					if(qrystdsign1.next()){
						sbAuctionClosingVO.setSig(qrystdsign1.getString(1));
					}
					PreparedStatement pst29=con.prepareStatement(auctionClosing036);
					pst29.setInt(1, sbAuctionClosingVO.getClientID());
					ResultSet Qrysuppperson=pst29.executeQuery();
					if(Qrysuppperson.next()){
						sbAuctionClosingVO.setSuppName(Qrysuppperson.getString(2));
						sbAuctionClosingVO.setSuppMail(Qrysuppperson.getString(3));
					}
					PreparedStatement pst30=con.prepareStatement(auctionClosing037);
					pst30.setInt(1, sbAuctionClosingVO.getReqId());
					pst30.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					ResultSet qryclispoc=pst30.executeQuery();
					sbAuctionClosingVO.setToAddress(sbAuctionClosingVO.getQryContactEmail());
					//cc address has to be implemented
					String cc="";
					ccAddress = new ArrayList();
					cc =sbAuctionClosingVO.getSuppMail();
					ccAddress.add(cc);

					//System.out.println("To Address:"+sbAuctionClosingVO.getQryContactEmail());
					//System.out.println("From Address:"+sbAuctionClosingVO.getFromAddress());
					//System.out.println("CC Address:"+ccAddress);

					if(sbAuctionClosingVO.getQryspoccontactemail()!=null )
						if(sbAuctionClosingVO.getQryspoccontactemail().trim()!="")
						ccAddress.add(sbAuctionClosingVO.getQryspoccontactemail());
//                    System.out.println("cc"+cc+"sbAuctionClosingVO.getQryspoccontactemail()"+sbAuctionClosingVO.getQryspoccontactemail());
					Statement stmt6=con.createStatement();
					ResultSet qryAdminEMail=stmt6.executeQuery(auctionClosing038);
					if(qryAdminEMail.next()){
						sbAuctionClosingVO.setFromAddress(qryAdminEMail.getString(1));
					}
					PreparedStatement pst31=con.prepareStatement(auctionClosing032);
					pst31.setInt(1, sbAuctionClosingVO.getReqId());
					pst31.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					ResultSet qryrequiremnt1=pst31.executeQuery();
					if(qryrequiremnt1.next()){
						sbAuctionClosingVO.setReqRef(qryrequiremnt1.getString(1));
						sbAuctionClosingVO.setTitle(qryrequiremnt1.getString(2));

					}
					Date date = new Date();
			        buff=new StringBuffer();

//				 01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - start
			        /*date =new java.util.Date(System.currentTimeMillis());
			        String d=date.getMonth()+1 +"/"+date.getDate()+"/"+(date.getYear()+1900);
					String t=date.getHours()+":"+date.getMinutes();
					//buff.append("To Address:"+sbAuctionClosingVO.getToAddress());
					//System.out.println("To Address:"+sbAuctionClosingVO.getToAddress());

					buff.append(d);
					buff.append(" ");
					buff.append(t);
					buff.append("(EST) \n\n");*/
  		   		    java.util.Date d1=new java.util.Date(System.currentTimeMillis());
			   	    String ESTTime=SBDateUtil.getESTTime(d1);
			   	    buff.append(ESTTime+"\n\n");
//			    01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - End
					buff.append(" Dear ");
					buff.append(sbAuctionClosingVO.getName());
					buff.append(", \n");
					buff.append("\n This is to confirm that the Req Reference / Job Title :  ");
					buff.append( sbAuctionClosingVO.getReqRef()+"/" +sbAuctionClosingVO.getTitle()+"has been closed out.");
					buff.append("\n");
					buff.append("\n");
					buff.append(sbAuctionClosingVO.getSuppName());
					buff.append("\n");
					buff.append("\n");
					if(sbAuctionClosingVO.getSig()!=null)
					{
						buff.append(sbAuctionClosingVO.getSig());
					}
					String subject=sbAuctionClosingVO.getMailSubject();
					//String fromAddress ="[e-mail]";
					Properties prop = null;
					try
					{
						prop = SBLoadProps.loadParams("server");
					}
					catch(Exception e){
						e.printStackTrace();
					}
					String bodyEncoding = prop.getProperty("BODY_ENCODING");
					String headerEncoding = prop.getProperty("HEADER_ENCODING");
					Properties contentTypes;

					String fromAddress=sbAuctionClosingVO.getFromAddress();
					toAddress=sbAuctionClosingVO.getQryContactEmail();



					List l1=new ArrayList();
					l1.add(toAddress);
					/*List l2=new ArrayList();
					l2.add(ccAddress);*/
					MailerVO vo=new MailerVO();
					vo.setToAddresses(l1);
					vo.setFromAddress(fromAddress);
					//prashant 			17-Dec-2007  	mantis id-0000375-start
					//vo.setCcAddresses(ccAddress);
					//prashant 			17-Dec-2007  	mantis id-0000375-End
					vo.setHeaderEncoding(headerEncoding);
					vo.setBody(buff.toString());
					vo.setSubject(subject);
					vo.setBodyEncoding(bodyEncoding);
					//System.out.println("Mail has been sending...."+buff.toString());
					try {
						MailerComponent.sendMail(vo);
					} catch (Exception e){
						e.printStackTrace();
						}
					//System.out.println("Mail has been sent");




					sbAuctionClosingVO.setEmailType(1105);
					PreparedStatement pst25=con.prepareStatement(auctionClosing025);
					pst25.setInt(1, sbAuctionClosingVO.getEmailType());
					ResultSet getmaildetail=pst25.executeQuery();
					if(getmaildetail.next()){
						sbAuctionClosingVO.setMailSubject(getmaildetail.getString(2));
						sbAuctionClosingVO.setEmailStatus(getmaildetail.getInt(3));
					}

					sbAuctionClosingVO.setPLoginId(sbAuctionClosingVO.getReqOwner());
					sbAuctionClosingVO.setUserID(sbAuctionClosingVO.getPLoginId());
					PreparedStatement pst22=con.prepareStatement(auctionClosing029);
					pst22.setString(1, sbAuctionClosingVO.getReqOwner());
					//System.out.println("contact-id"+sbAuctionClosingVO.getReqOwner());
					ResultSet qryorgclient=pst22.executeQuery();
					if(qryorgclient.next()){
						//System.out.println("setOrgName"+qryorgclient.getString(3)+":"+qryorgclient.getString(2));
						sbAuctionClosingVO.setOrgName(qryorgclient.getString(3));
						sbAuctionClosingVO.setClientName(qryorgclient.getString(2));
					}
					Statement stmt3=con.createStatement();
					ResultSet qrystdsign=stmt3.executeQuery(auctionClosing030);
					if(qrystdsign.next()){
						sbAuctionClosingVO.setSig(qrystdsign.getString(1));
					}
					else{
						sbAuctionClosingVO.setSig("SKILLBAY");
					}
					PreparedStatement pst23=con.prepareStatement(auctionClosing031);
					pst23.setInt(1, sbAuctionClosingVO.getReqId());
					pst23.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					ResultSet qryVendordetails=pst23.executeQuery();
					if(qryVendordetails.next()){
						//System.out.println("got name"+qryVendordetails.getString(2));
						sbAuctionClosingVO.setToAddress(qryVendordetails.getString(1));
						sbAuctionClosingVO.setName(qryVendordetails.getString(2));

					}

					PreparedStatement pst24=con.prepareStatement(auctionClosing032);
					pst24.setInt(1, sbAuctionClosingVO.getReqId());
					pst24.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					ResultSet qryrequiremnt=pst24.executeQuery();
					if(qryrequiremnt.next()){
						sbAuctionClosingVO.setReqRef(qryrequiremnt.getString(1));
						sbAuctionClosingVO.setTitle(qryrequiremnt.getString(2));

					}

					sbAuctionClosingVO.setDealType(4);

					Statement stmt4=con.createStatement();
					ResultSet qrygetS3DTEMVCEmailAddress=stmt4.executeQuery(auctionClosing033);
					if(qrygetS3DTEMVCEmailAddress.next()){
						sbAuctionClosingVO.setSuppMail(qrygetS3DTEMVCEmailAddress.getString(1));
						sbAuctionClosingVO.setSuppName(qrygetS3DTEMVCEmailAddress.getString(2));
					}
//		prashant            04-Dec-2007            changes made for mantis id 0000371   start


					StringBuffer buffer = new StringBuffer();

					try{
						PreparedStatement pst50=con.prepareStatement(auctionClosing031);
						pst50.setInt(1, sbAuctionClosingVO.getReqId());
						pst50.setInt(2, sbAuctionClosingVO.getReqLineItemId());
						ResultSet objectsList=pst23.executeQuery();
						while(objectsList.next()){
							sbAuctionClosingVO.setToAddressfornotification(objectsList.getString(1));
							sbAuctionClosingVO.setNamefornotification(objectsList.getString(2));

							  // System.out.println("iam in the forloop objectsList*********------------");
							 	 String fullName1=sbAuctionClosingVO.getNamefornotification();
								//System.out.println("fullName1fullName1------------"+fullName1);
								 String toAddress1 =null;
								   toAddress1=sbAuctionClosingVO.getToAddressfornotification();
									//System.out.println("toAddress1toAddress1----------------"+toAddress1);
//									prashant            04-Dec-2007            changes made for mantis id 0000371End
								   date = new Date();
							        buff=new StringBuffer();
//								01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - start
							        /*date =new java.util.Date(System.currentTimeMillis());
							        d=date.getMonth()+1 +"/"+date.getDate()+"/"+(date.getYear()+1900);
									t=date.getHours()+":"+date.getMinutes();
									//buff.append("To Address:"+sbAuctionClosingVO.getToAddress());
									//System.out.println("To Address:"+sbAuctionClosingVO.getToAddress());

									buff.append(d);
									buff.append(" ");
									buff.append(t);
									buff.append("(EST) \n\n");*/
							   		//  java.util.Date d1=new java.util.Date(System.currentTimeMillis());
							   	    //  String ESTTime=SBDateUtil.getESTTime(d1);
							   	    buff.append(ESTTime+"\n\n");
//							    01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - End
									buff.append(" Dear ");
									buff.append(fullName1);
									buff.append(", \n");
									buff.append("\n This is to notify that the client, ");
									buff.append(sbAuctionClosingVO.getOrgName()+":");
									buff.append(sbAuctionClosingVO.getClientName()+","+"has completed the selection of candidates for the Req Reference / Job Title:"+ sbAuctionClosingVO.getReqRef()+"/" +sbAuctionClosingVO.getTitle()+".");
									buff.append("\n");
									buff.append("\n");
									buff.append(sbAuctionClosingVO.getSuppName());
									buff.append("\n");
									buff.append("\n");

									if(sbAuctionClosingVO.getSig()!=null)
									{
										buff.append(sbAuctionClosingVO.getSig());
									}
									subject=sbAuctionClosingVO.getMailSubject();
									//fromAddress ="[e-mail]";
									prop = null;
									try
									{
										prop = SBLoadProps.loadParams("server");
									}
									catch(Exception e){
										e.printStackTrace();
									}
									bodyEncoding = prop.getProperty("BODY_ENCODING");
									headerEncoding = prop.getProperty("HEADER_ENCODING");

									//toAddress1 =sbAuctionClosingVO.getToAddress();

								// prashant            04-Dec-2007            changes made for mantis id 0000371   start
									MailerVO vo1=new MailerVO();
									List l2=new ArrayList();
									l2.add(toAddress1);

									vo1.setToAddresses(l2);
									vo1.setFromAddress(fromAddress);

									vo1.setHeaderEncoding(headerEncoding);
									vo1.setBody(buff.toString());
									vo1.setSubject(subject);
									vo1.setBodyEncoding(bodyEncoding);
									//System.out.println("Mail has been sending....:"+buff.toString());


									String bypassClienIDs = null;
									try
									{
										org.hibernate.Session x =getSession();
										Connection conn = x.connection();
										Statement st=conn.createStatement();
										ResultSet rs = st.executeQuery(SBREQCloseoutNotMailBypass);

										if(rs.next())
										{
											bypassClienIDs=rs.getString(1);
										}
										releaseSession(x);
									}
									catch(Exception e)
									{
										sbErrorBatVO.setPgmID("int_sch_adv");
										sbErrorBatVO.setErrMessage("Query Failed : "+SBREQCloseoutNotMailBypass);
										sbErrorBat.logError(sbErrorBatVO);
										e.printStackTrace();
										throw e;
									}



										boolean bypassFlag = true;
										if(bypassClienIDs != null)
										{
											// logger.debug("in SBAuctionClosingDAOHibernate----------------");
											String arr[] = bypassClienIDs.split(";");
											for(int j=0;j<arr.length;j++)
											{											
												if(sbAuctionClosingVO.getClientID() == Integer.parseInt(arr[j]))
												{
													bypassFlag = false;
													break;
												}
											}
										}
										else
										{

										}
									 if(bypassFlag)
									 {
									 try{

					 System.out.println("iam in catch block For Client Steven---------SbauctionclosingDAohibernate--------");
										 //logger.debug("in  in try block of SBAuctionClosingDAOHibernate----------------");
										 MailerComponent.sendMail(vo1);
									 	}

									 catch(Exception e){
										 e.printStackTrace();

										 //logger.debug("in  in catch block of SBAuctionClosingDAOHibernate----------------");
										}
									 }
//									prashant  04-Dec-2007   changes made for mantis id 0000371   End
									System.out.println("iam in the out of bypassFlag block of the bypassflag");

							  }


					}

					catch(Exception e){
						System.out.println("iam in the  catch block of  the mail body-------------------------**********");
						e.printStackTrace();
					}

//		 prashant            04-Dec-2007            changes made for mantis id 0000371   End
				}

			}
			releaseSession(session);

		}

		catch(Exception e)
		{
			sbErrorBatVO.setPgmID(sbAuctionClosingVO.getPgmId());
			sbErrorBatVO.setErrMessage(sbAuctionClosingVO.getErrMessage());
			sbErrorBat.logError(sbErrorBatVO);
			releaseSession(session);
			e.printStackTrace();
			throw e;
		}

		return sbAuctionClosingVO;
	}
}

i kind of tried the closing the connection in this way , however was facing with sql eception like connection closed out .

the modified code for closing connection to above file was

package com.syntel.sb.arch.batch.dao.hibernate;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import org.hibernate.Session;

import com.syntel.sb.appl.bo.client.SBReqLineItem;
import com.syntel.sb.appl.vo.admin.MailerVO;
import com.syntel.sb.arch.batch.common.SBAutoGen;
import com.syntel.sb.arch.batch.common.SBBatEndLog;
import com.syntel.sb.arch.batch.common.SBBatStartLog;
import com.syntel.sb.arch.batch.common.SBBatchQueries;
import com.syntel.sb.arch.batch.common.SBErrorBat;
import com.syntel.sb.arch.batch.dao.SBAuctionClosingDAO;
import com.syntel.sb.arch.batch.vo.SBAuctionClosingVO;
import com.syntel.sb.arch.batch.vo.SBBatEndVO;
import com.syntel.sb.arch.batch.vo.SBBatStartVO;
import com.syntel.sb.arch.batch.vo.SBClientReqClosedOutConfirmationVO;
import com.syntel.sb.arch.batch.vo.SBErrorBatVO;
import com.syntel.sb.arch.dao.hibernate.SBBaseDAOHibernate;
import com.syntel.sb.arch.util.MailerComponent;
import com.syntel.sb.arch.util.SBDateUtil;
import com.syntel.sb.arch.util.SBLoadProps;

public class SBAuctionClosingDAOHibernate extends SBBaseDAOHibernate implements SBAuctionClosingDAO, SBBatchQueries {

	String toAddress=null;
	List ccAddress=null;

	static int count=0;

	public SBAuctionClosingVO closeAuction(SBAuctionClosingVO sbAuctionClosingVO) throws Exception {
		logger.debug("in SBAuctionClosingDAOHibernate----------------");
		SBBatStartLog batStartLog=new SBBatStartLog();
		SBBatStartVO sbBatStartVO =new SBBatStartVO();
		SBErrorBat sbErrorBat = new SBErrorBat();
		SBErrorBatVO sbErrorBatVO =new SBErrorBatVO();
		SBClientReqClosedOutConfirmationVO sbClientReqClosedOutConfirmationVO=new SBClientReqClosedOutConfirmationVO();
		//System.out.println("In DAO Hibernate");
		Session session = getSession();
		//System.out.println("session.................. "+session.connection());
		Connection con = session.connection();
		//System.out.println("In DAO After Conection");
		SBAutoGen sbAutoGen=new SBAutoGen();
		StringBuffer buff=new StringBuffer();
		try //try Super
		{
			System.out.print("raj says that entered into try super in sbauctionclosing");
			System.out.print("raj says that entered into try A in sbauctionclosing");
				try  // try  A
				{
					System.out.print("raj says that entered into try a");
			Statement st1=null;
			ResultSet qryGetEmailInfo=null,qryUserId=null;
			PreparedStatement pst1 = null;
			try // try a.1
			 
			{
				System.out.print("raj says that entered into try a.1");
				st1=con.createStatement();
				qryGetEmailInfo=st1.executeQuery(auctionClosing001);
			if(qryGetEmailInfo.next())
			{
				sbAuctionClosingVO.setMailSubject("Auction Close Advice");
			}

			pst1=con.prepareStatement(commonSlctLoginId);
			pst1.setInt(1, 90);

			 qryUserId=pst1.executeQuery();
			if(qryUserId.next())
			{
				sbAuctionClosingVO.setLoginId(qryUserId.getString(1));
				//System.out.println("Inside User ID query");
			}
			System.out.print("raj says that exit into try a.1");	
			}
			finally // for try a.1
			{
				System.out.println("raj says that existed from  try a and clsoed all connection in it ");
				qryUserId.close();
				pst1.close();
				qryGetEmailInfo.close();
				st1.close();
			}
				
			System.out.print("raj says that existed into try A in sbauctionclosing");	
				}
				
				
				catch(Exception e) // catch for A
				{
					e.printStackTrace();
					System.out.println("raj says that exception for try A was in sbauctionclosingdaohibernate"+e);
				}
				
			
			
			
			try // try B
			{
				System.out.print(" \n raj says that entered into try B in sbauctionclosing");
				PreparedStatement sample =  null,pst2=null,pst3=null,pst33=null,pst4=null,pst5=null,pst6=null,pst7=null,pst8=null,pst9=null,pst10=null,pst12=null,pst13=null,pst11=null,pst14=null,pst15=null,pst16=null;
				
				ResultSet qryAllReqs = null,qryUpdate43=null,qryWinningBids=null,qryForConfirmedBids=null,qryearlywinningbids=null;
				
				
				System.out.print("raj says that entered into try B in sbauctionclosing");
				
				try{ // b.1
					
					System.out.print(" \n raj says that entered into try B.1 ");
			
			
			sample = con.prepareStatement(auctionClosing002);
			
			qryAllReqs = sample.executeQuery();
			
			
			
			while(qryAllReqs.next())
			{

					int count1=0;
					int temp = 0;
					sbAuctionClosingVO.setReqId(qryAllReqs.getInt(1));
					sbAuctionClosingVO.setReqLineItemId(qryAllReqs.getInt(7));
					sbAuctionClosingVO.setClientID(qryAllReqs.getInt(4));

					//PreparedStatement pst2=con.prepareStatement(auctionClosing003);
					pst2=con.prepareStatement(auctionClosing003);
					pst2.setInt(1, 43);
					pst2.setString(2, sbAuctionClosingVO.getLoginId());
					pst2.setInt(3, sbAuctionClosingVO.getReqLineItemId());
					pst2.setInt(4, sbAuctionClosingVO.getReqId());
					pst2.setInt(5, 33);

					//ResultSet qryUpdate43=pst2.executeQuery();
					qryUpdate43=pst2.executeQuery();

					//PreparedStatement pst3=con.prepareStatement(auctionClosing004,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					pst3=con.prepareStatement(auctionClosing004,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					pst3.setInt(1, sbAuctionClosingVO.getReqLineItemId());
					pst3.setInt(2, sbAuctionClosingVO.getReqId());

					//ResultSet qryWinningBids=pst3.executeQuery();
					qryWinningBids=pst3.executeQuery();
					//Girish 			08-OCT-2007		Changes made for mantis id 0000356  start

					//PreparedStatement pst33=con.prepareStatement(auctionClosing0044,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					pst33=con.prepareStatement(auctionClosing0044,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					pst33.setInt(1, sbAuctionClosingVO.getReqLineItemId());
					pst33.setInt(2, sbAuctionClosingVO.getReqId());

					//ResultSet qryForConfirmedBids=pst33.executeQuery();
					qryForConfirmedBids=pst33.executeQuery();

					while(qryForConfirmedBids.next())
					{
						temp++;
					}
//System.out.println("temptemp*****************"+temp);
					//System.out.println("i'm before qryWinningBids");
					qryWinningBids.beforeFirst();
					sbAuctionClosingVO.setWiningBidrecordCountFlag(0);
					sbAuctionClosingVO.setRecordCountFlag(0);
					if(!qryWinningBids.next())
					{
						sbAuctionClosingVO.setWiningBidrecordCountFlag(1);
						//PreparedStatement pst4=con.prepareStatement(auctionClosing016,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
						pst4=con.prepareStatement(auctionClosing016,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
						pst4.setInt(1, sbAuctionClosingVO.getReqLineItemId());
						pst4.setInt(2, sbAuctionClosingVO.getReqId());
						//ResultSet qryearlywinningbids=pst4.executeQuery();
						qryearlywinningbids=pst4.executeQuery();
						qryearlywinningbids.beforeFirst();
						if(!qryearlywinningbids.next()){
							sbAuctionClosingVO.setRecordCountFlag(1);
						}
					}

				    qryWinningBids.beforeFirst();
					while(qryWinningBids.next())
					{
						count1++;
					
					}
//					System.out.println("count1-------------"+count1);
					qryWinningBids.beforeFirst();
					int count2=0;
					String autoKeyGenerationTable="PLACEMENT_INTERVIEW_SESSION";
					int newKeyGenerated=0;

					while(qryWinningBids.next())
					{
						count2++;

						sbAuctionClosingVO.setCandID(qryWinningBids.getInt(6));
						sbAuctionClosingVO.setBidID(qryWinningBids.getInt(5));
						sbAuctionClosingVO.setInterviews(qryWinningBids.getInt(1));
						sbAuctionClosingVO.setMemberID(qryWinningBids.getInt(8));
						sbAuctionClosingVO.setClientID(qryWinningBids.getInt(7));
						sbAuctionClosingVO.setRevisionNumber(qryWinningBids.getInt(4));
						if(sbAuctionClosingVO.getInterviews() >< (count1+temp))
						{
							if((temp+count2)<= sbAuctionClosingVO.getInterviews())
							{
				//Girish 			08-OCT-2007		Changes made for mantis id 0000356 end
								//PreparedStatement pst5=con.prepareStatement(auctionClosing017);
								pst5=con.prepareStatement(auctionClosing017);
								pst5.setString(1, sbAuctionClosingVO.getLoginId());
								pst5.setInt(2, sbAuctionClosingVO.getBidID());
								//int qryUpdate50=pst5.executeUpdate(); raj kiran  modified
								pst5.executeUpdate();
								autoKeyGenerationTable="PLACEMENT_INTERVIEW_SESSION";
								sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
								//System.out.println(sbAuctionClosingVO.getAutoKeyTableName());
								newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

								//PreparedStatement pst6=con.prepareStatement(auctionClosing006);
								pst6=con.prepareStatement(auctionClosing006);
								pst6.setInt(1, sbAuctionClosingVO.getCandID());
								//System.out.println("candidateID:"+sbAuctionClosingVO.getCandID());
								ResultSet qryResume=pst6.executeQuery();
								//qryResume=pst6.executeQuery();

								if(qryResume.next())
									
								{
									if(qryResume.getString(1)==null){
										sbAuctionClosingVO.setResumeFlag(0);
									}
									else{
										sbAuctionClosingVO.setResumeFlag(1);
									}
								}

							//	PreparedStatement pst7=con.prepareStatement(auctionClosing008);
								pst7=con.prepareStatement(auctionClosing008);
								pst7.setInt(1,sbAuctionClosingVO.getBidID());
								pst7.setInt(2,newKeyGenerated);
								pst7.setInt(3,sbAuctionClosingVO.getCandID());
								pst7.setInt(4,sbAuctionClosingVO.getMemberID());
								pst7.setInt(5,sbAuctionClosingVO.getRevisionNumber());
								pst7.setInt(6,sbAuctionClosingVO.getClientID());
								pst7.setString(7, sbAuctionClosingVO.getLoginId());
								//int qryInsertPlacement=pst7.executeUpdate(); raj kiran modified this
								pst7.executeUpdate();

								autoKeyGenerationTable="E_MAIL";

								sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);

								newKeyGenerated = sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

								//PreparedStatement pst8=con.prepareStatement(auctionClosing007);
								pst8=con.prepareStatement(auctionClosing007);
								pst8.setInt(1, newKeyGenerated);
								pst8.setInt(2, sbAuctionClosingVO.getMemberID());
								pst8.setInt(3, sbAuctionClosingVO.getBidID());
								pst8.setInt(4, sbAuctionClosingVO.getRevisionNumber());
								pst8.setInt(5, qryWinningBids.getInt(2));
								pst8.setInt(6, qryWinningBids.getInt(3));
								pst8.setString(7, sbAuctionClosingVO.getLoginId());
								pst8.executeUpdate();
								//int qryEmailInsert=pst8.executeUpdate(); raj krian modified this
							}
							else
							{
								//PreparedStatement pst9=con.prepareStatement(auctionClosing018);
								pst9=con.prepareStatement(auctionClosing018);
								pst9.setString(1, sbAuctionClosingVO.getLoginId());
								pst9.setInt(2, sbAuctionClosingVO.getBidID());
								pst9.executeUpdate();
								// int qryUpdate40=pst9.executeUpdate(); raj kiran modififed thsi
								autoKeyGenerationTable="E_MAIL";
								sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
								newKeyGenerated =sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);
								//System.out.println("The query is");
							//	PreparedStatement pst10=con.prepareStatement(auctionClosing019);
								pst10=con.prepareStatement(auctionClosing019);
								pst10.setInt(1, newKeyGenerated);
								pst10.setInt(2, sbAuctionClosingVO.getMemberID());
								pst10.setInt(3, sbAuctionClosingVO.getBidID());
								pst10.setInt(4, sbAuctionClosingVO.getRevisionNumber());
								pst10.setInt(5, sbAuctionClosingVO.getReqId());
								pst10.setInt(6, sbAuctionClosingVO.getReqLineItemId());
								pst10.setString(7, sbAuctionClosingVO.getLoginId());
								pst10.setInt(8, sbAuctionClosingVO.getClientID());
								pst10.executeUpdate();
								 // int qryEmailInsert1=pst10.executeUpdate(); raj kiran modified this
							}

						}
						else
						{
							//PreparedStatement pst11=con.prepareStatement(auctionClosing017);
							pst11=con.prepareStatement(auctionClosing017);
							pst11.setString(1,sbAuctionClosingVO.getLoginId() );
							pst11.setInt(2, sbAuctionClosingVO.getBidID());
							pst11.executeUpdate();
							// int qryUpdate50=pst11.executeUpdate(); raj kiran modififed thso
							autoKeyGenerationTable="PLACEMENT_INTERVIEW_SESSION";
							sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
							//System.out.println(sbAuctionClosingVO.getAutoKeyTableName());
							newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

							//PreparedStatement pst12=con.prepareStatement(auctionClosing006);
							pst12=con.prepareStatement(auctionClosing006);
							pst12.setInt(1, sbAuctionClosingVO.getCandID());
							ResultSet qryResume=pst12.executeQuery();
							if(qryResume.next())
							{
								if(qryResume.getString(1)==null){
									sbAuctionClosingVO.setResumeFlag(0);
								}
								else{
									sbAuctionClosingVO.setResumeFlag(1);
								}
							}

							//PreparedStatement pst13=con.prepareStatement(auctionClosing020);
							pst13=con.prepareStatement(auctionClosing020);
							pst13.setInt(1, sbAuctionClosingVO.getBidID());
							pst13.setInt(2, newKeyGenerated);
							pst13.setInt(3, sbAuctionClosingVO.getCandID());
							pst13.setInt(4, sbAuctionClosingVO.getMemberID());
							pst13.setInt(5, sbAuctionClosingVO.getRevisionNumber());
							pst13.setInt(6, sbAuctionClosingVO.getClientID());
							pst13.setString(7, sbAuctionClosingVO.getLoginId());

							pst13.executeUpdate();
							//int qryInsertPlacement=pst13.executeUpdate(); rajj kiran modified this

							autoKeyGenerationTable="E_MAIL";
							sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
							newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

						//	PreparedStatement pst14=con.prepareStatement(auctionClosing021);
							pst14=con.prepareStatement(auctionClosing021);
							pst14.setInt(1, newKeyGenerated);
							pst14.setInt(2, sbAuctionClosingVO.getMemberID());
							pst14.setInt(3, sbAuctionClosingVO.getBidID());
							pst14.setInt(4, sbAuctionClosingVO.getRevisionNumber());
							pst14.setInt(5, sbAuctionClosingVO.getReqId());
							pst14.setInt(6, sbAuctionClosingVO.getReqLineItemId());
							pst14.setString(7, sbAuctionClosingVO.getLoginId());
							pst14.setInt(8, sbAuctionClosingVO.getClientID());

							pst14.executeUpdate();
							// int qryEmailInsert1=pst14.executeUpdate(); rajj kiran modifif ed this
						}
					}
					//PreparedStatement pst15=con.prepareStatement(auctionClosing009);
					pst15=con.prepareStatement(auctionClosing009);
					pst15.setInt(1,sbAuctionClosingVO.getReqLineItemId());
					pst15.setInt(2,sbAuctionClosingVO.getReqId());

					pst15.executeUpdate();
					//int updatereqs=pst15.executeUpdate(); raj kiran modified this

					autoKeyGenerationTable="E_MAIL";
					sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
					newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);
				//	PreparedStatement pst16=con.prepareStatement(auctionClosing022);
					pst16=con.prepareStatement(auctionClosing022);
					pst16.setInt(1, newKeyGenerated);
					if(sbAuctionClosingVO.getRecordCountFlag()==1 && sbAuctionClosingVO.getWiningBidrecordCountFlag()==1 ){
						pst16.setInt(2, 408);
					}
					else{
						pst16.setInt(2, 309);
					}
					if(sbAuctionClosingVO.getWiningBidrecordCountFlag()==0){
						pst16.setString(3, null);
					}
					else{
						pst16.setInt(3, sbAuctionClosingVO.getBidID());
					}
					if(sbAuctionClosingVO.getWiningBidrecordCountFlag()==1){
						pst16.setString(4,null);
					}
					else{
						pst16.setInt(4, sbAuctionClosingVO.getRevisionNumber());
					}
					pst16.setInt(5, sbAuctionClosingVO.getReqId());
					pst16.setInt(6, sbAuctionClosingVO.getReqLineItemId());
					if(sbAuctionClosingVO.getWiningBidrecordCountFlag()==1){
						pst16.setString(7, null);
					}
					else{
						pst16.setString(7, sbAuctionClosingVO.getMailSubject());
					}
					pst16.setString(8, sbAuctionClosingVO.getLoginId());
					pst16.setInt(9, sbAuctionClosingVO.getClientID());

					pst16.executeUpdate();
					 // int updatereqs1=pst16.executeUpdate(); raj kiran modified this
			}
				}
				catch (Exception e) // for b.1
				{
					e.printStackTrace();
					System.out.println("raj is here in b.1 try and exception es"+e);
				}

			finally // for try b.1
			{
				System.out.print(" \n  raj says that existed into try B.1 and in finally block");
				sample.close();
				if(con!=null){
	System.out.println(" \n raj is here in if loop of b.1 try to close connection and entered inside the finally block to close conn");
				pst2.close();
				pst3.close();
				pst33.close();
				pst4.close();
				pst5.close();
				pst6.close();
				pst7.close();
				pst8.close();
				pst9.close();
				pst10.close();
				pst12.close();
				pst13.close();
				pst11.close();
			pst14.close();
				pst15.close();
				pst16.close();
				}
//}
			}
			
			System.out.print("raj says that existed into try B and closed all connections in finally block");
			}
			
						
			
			catch(Exception e) // for try b
			{
				e.printStackTrace();
				System.out.print("raj says that exception at try b was"+e);
			}
			
			
			
			try { // try c raj modified for closing connections
			
			Statement stmt=con.createStatement();
			ResultSet qryBidForwarded=stmt.executeQuery(auctionClosing023);
			ResultSet getMailDetail = null;
			PreparedStatement pst17 =null,pst18= null;
			try //try c.1
			{
			if(qryBidForwarded.next())
			{
				sbAuctionClosingVO.setEmailType(1107);
				sbAuctionClosingVO.setReqId(qryBidForwarded.getInt(1));
				sbAuctionClosingVO.setReqLineItemId(qryBidForwarded.getInt(2));
				sbAuctionClosingVO.setClientID(qryBidForwarded.getInt(3));
				//PreparedStatement pst17=con.prepareStatement(auctionClosing024);
				pst17=con.prepareStatement(auctionClosing024);
				pst17.setString(1, sbAuctionClosingVO.getLoginId());
				pst17.setInt(2, sbAuctionClosingVO.getReqId());
				pst17.setInt(3, sbAuctionClosingVO.getReqLineItemId());
				//int UPDATEREQSTATUS35=pst17.executeUpdate();
				pst17.executeUpdate(); // raj kiran modified it
			//	PreparedStatement pst18=con.prepareStatement(auctionClosing025);
				pst18=con.prepareStatement(auctionClosing025);
				pst18.setInt(1, sbAuctionClosingVO.getEmailType());
				
			//	ResultSet getMailDetail=pst18.executeQuery();
				getMailDetail=pst18.executeQuery();
				if(getMailDetail.next()){
					sbAuctionClosingVO.setMailSubject(getMailDetail.getString(2));
					sbAuctionClosingVO.setEmailStatus(getMailDetail.getInt(3));
				}
			}
			}
			finally // for try c.1
			{
				System.out.print(" \n loop inside the finay c.1 and all are commented");
				stmt.close();
				qryBidForwarded.close();
				pst17.close();
				pst18.close();
				
				
			}
			}
			catch(Exception e) // for try c
			{
			e.printStackTrace();
			System.out.println("raj sayst that the exception at try c was" +e);
			
			}
			
			
			try {  // try D
			
			Statement stmt1=con.createStatement();
			Statement stmt6=null,stmt3=null,stmt4=null,stmt5=null;
			ResultSet qryreq3035=stmt1.executeQuery(auctionClosing026); // raj kiran jv changed the stmt here to stmt1
			PreparedStatement pst19 =null,pst20 =null,pst21= null,pst22= null,pst23= null,pst24= null,pst25= null,pst26= null,pst27= null,pst28= null,pst29= null,pst30= null,pst31= null;
			
			try // try d.1
			
			{
				
			while(qryreq3035.next())
			{
				//System.out.println("qryreq3035 is executing");
				sbAuctionClosingVO.setReqId(qryreq3035.getInt(1));
				sbAuctionClosingVO.setReqLineItemId(qryreq3035.getInt(2));
				sbAuctionClosingVO.setReqOwner(qryreq3035.getString(3));
				/*saurabh modified on 18th June 2008 for Mantis id 0000405 start*/
				sbAuctionClosingVO.setReqstatuscode(qryreq3035.getInt(4));
				/*saurabh modified on 18th June 2008 for Mantis id 0000405 end*/
				//System.out.println("ReqOwner"+qryreq3035.getString(3));
			//	PreparedStatement pst19=con.prepareStatement(auctionClosing027);
				pst19=con.prepareStatement(auctionClosing027);
				pst19.setInt(1, sbAuctionClosingVO.getReqId());
				//System.out.println("reqid:" +sbAuctionClosingVO.getReqId());
				pst19.setInt(2, sbAuctionClosingVO.getReqLineItemId());
				//System.out.println("reqlineid:" +sbAuctionClosingVO.getReqLineItemId());
				ResultSet qrygetplaced=pst19.executeQuery();
				if(qrygetplaced.next()){
					sbAuctionClosingVO.setEmpPlaced(qrygetplaced.getInt(1));
				}
				//System.out.println("empplaced:"+sbAuctionClosingVO.getEmpPlaced());
				//PreparedStatement pst20=con.prepareStatement(auctionClosing028);
				pst20=con.prepareStatement(auctionClosing028);
				pst20.setInt(1, sbAuctionClosingVO.getReqId());
				pst20.setInt(2, sbAuctionClosingVO.getReqLineItemId());
				ResultSet qrygetreqd=pst20.executeQuery();
				if(qrygetreqd.next()){
					sbAuctionClosingVO.setStaffing(qrygetreqd.getInt(1));
				}
				if(sbAuctionClosingVO.getEmpPlaced() >= sbAuctionClosingVO.getStaffing())
				{
					// logger.debug("in SBAuctionClosingDAOHibernate-xxxxxxxxxxxxx---------------");
					
					
					
					 
					//PreparedStatement pst21=con.prepareStatement(auctionClosing015);
					pst21=con.prepareStatement(auctionClosing015);
					pst21.setInt(1, sbAuctionClosingVO.getReqId());
					pst21.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					pst21.executeUpdate();
					//int qrygetreqd1=pst21.executeUpdate(); raj kiran modified this
					/*saurabh modified on 18th June 2008 for Mantis id 0000405 start*/
					if (sbAuctionClosingVO.getReqstatuscode()== 20){
						try{
						PreparedStatement pst01=con.prepareStatement(auctionClosing050);
						pst01.setInt(1, sbAuctionClosingVO.getReqId());
						pst01.setInt(2, sbAuctionClosingVO.getReqLineItemId());
						pst01.executeUpdate();
						//int qrygetaucdtchnge=pst01.executeUpdate(); raj kiran modified this

						}
						catch(Exception e){
							e.printStackTrace();
							}
					}
					/*saurabh modified on 18th June 2008 for Mantis id 0000405 end*/
					sbAuctionClosingVO.setEmailType(1104);
					//PreparedStatement pst26=con.prepareStatement(auctionClosing025);
					pst26=con.prepareStatement(auctionClosing025);
					pst26.setInt(1, sbAuctionClosingVO.getEmailType());
					ResultSet getmailDetail=pst26.executeQuery();
					if(getmailDetail.next()){
						sbAuctionClosingVO.setMailSubject(getmailDetail.getString(2));
						sbAuctionClosingVO.setEmailStatus(getmailDetail.getInt(3));
					}
					//PreparedStatement pst27=con.prepareStatement(auctionClosing034);
					pst27=con.prepareStatement(auctionClosing034);
					pst27.setString(1, sbAuctionClosingVO.getReqOwner());
					ResultSet qrycontacttype=pst27.executeQuery();
					if(qrycontacttype.next()){
						sbAuctionClosingVO.setClientID(qrycontacttype.getInt(4));
						sbAuctionClosingVO.setQryContactEmail(qrycontacttype.getString(3));
						sbAuctionClosingVO.setName(qrycontacttype.getString(2));
					}

					//PreparedStatement pst28=con.prepareStatement(auctionClosing035);
					pst28=con.prepareStatement(auctionClosing035);
					pst28.setInt(1, sbAuctionClosingVO.getClientID());
					ResultSet qryspoccontact=pst28.executeQuery();
					if(qryspoccontact.next()){
						sbAuctionClosingVO.setQryspoccontactemail(qryspoccontact.getString(3));
					}
					//Statement stmt5=con.createStatement();
					stmt5=con.createStatement();
					ResultSet qrystdsign1=stmt5.executeQuery(auctionClosing030);
					if(qrystdsign1.next()){
						sbAuctionClosingVO.setSig(qrystdsign1.getString(1));
					}
			//		PreparedStatement pst29=con.prepareStatement(auctionClosing036);
					pst29=con.prepareStatement(auctionClosing036);
					pst29.setInt(1, sbAuctionClosingVO.getClientID());
					ResultSet Qrysuppperson=pst29.executeQuery();
					if(Qrysuppperson.next()){
						sbAuctionClosingVO.setSuppName(Qrysuppperson.getString(2));
						sbAuctionClosingVO.setSuppMail(Qrysuppperson.getString(3));
					}
					//PreparedStatement pst30=con.prepareStatement(auctionClosing037);
					 pst30=con.prepareStatement(auctionClosing037);
					pst30.setInt(1, sbAuctionClosingVO.getReqId());
					pst30.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					pst30.executeQuery();
					// ResultSet qryclispoc=pst30.executeQuery(); raj kiran modified this
					sbAuctionClosingVO.setToAddress(sbAuctionClosingVO.getQryContactEmail());
					//cc address has to be implemented
					String cc="";
					ccAddress = new ArrayList();
					
					cc =sbAuctionClosingVO.getSuppMail();
					ccAddress.add(cc);

					//System.out.println("To Address:"+sbAuctionClosingVO.getQryContactEmail());
					//System.out.println("From Address:"+sbAuctionClosingVO.getFromAddress());
					//System.out.println("CC Address:"+ccAddress);

					if(sbAuctionClosingVO.getQryspoccontactemail()!=null )
						if(sbAuctionClosingVO.getQryspoccontactemail().trim()!="")
						ccAddress.add(sbAuctionClosingVO.getQryspoccontactemail());
					//ccAddress.add(sbAuctionClosingVO.getQryspoccontactemail());
//                    System.out.println("cc"+cc+"sbAuctionClosingVO.getQryspoccontactemail()"+sbAuctionClosingVO.getQryspoccontactemail());
					//Statement stmt6=con.createStatement();\
					stmt6=con.createStatement();
					ResultSet qryAdminEMail=stmt6.executeQuery(auctionClosing038);
					if(qryAdminEMail.next()){
						sbAuctionClosingVO.setFromAddress(qryAdminEMail.getString(1));
					}
				//PreparedStatement pst31=con.prepareStatement(auctionClosing032);
					pst31=con.prepareStatement(auctionClosing032);
					pst31.setInt(1, sbAuctionClosingVO.getReqId());
					pst31.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					ResultSet qryrequiremnt1=pst31.executeQuery();
					if(qryrequiremnt1.next()){
						sbAuctionClosingVO.setReqRef(qryrequiremnt1.getString(1));
						sbAuctionClosingVO.setTitle(qryrequiremnt1.getString(2));

					}
					Date date = new Date(); //raj commented this
			        buff=new StringBuffer();

//				 01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - start
			        /*date =new java.util.Date(System.currentTimeMillis());
			        String d=date.getMonth()+1 +"/"+date.getDate()+"/"+(date.getYear()+1900);
					String t=date.getHours()+":"+date.getMinutes();
					//buff.append("To Address:"+sbAuctionClosingVO.getToAddress());
					//System.out.println("To Address:"+sbAuctionClosingVO.getToAddress());

					buff.append(d);
					buff.append(" ");
					buff.append(t);
					buff.append("(EST) \n\n");*/
  		   		    java.util.Date d1=new java.util.Date(System.currentTimeMillis());
			   	    String ESTTime=SBDateUtil.getESTTime(d1);
			   	    buff.append(ESTTime+"\n\n");
//			    01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - End
					buff.append(" Dear ");
					buff.append(sbAuctionClosingVO.getName());
					buff.append(", \n");
					buff.append("\n This is to confirm that the Req Reference / Job Title :  ");
					buff.append( sbAuctionClosingVO.getReqRef()+"/" +sbAuctionClosingVO.getTitle()+"has been closed out.");
					buff.append("\n");
					buff.append("\n");
					buff.append(sbAuctionClosingVO.getSuppName());
					buff.append("\n");
					buff.append("\n");
					if(sbAuctionClosingVO.getSig()!=null)
					{
						buff.append(sbAuctionClosingVO.getSig());
					}
					String subject=sbAuctionClosingVO.getMailSubject(); // raj is here-----------------------------
					//String fromAddress ="[e-mail]";
					Properties prop = null;
					try
					{
						prop = SBLoadProps.loadParams("server");
					}
					catch(Exception e){
						e.printStackTrace();
					}
					String bodyEncoding = prop.getProperty("BODY_ENCODING");
					String headerEncoding = prop.getProperty("HEADER_ENCODING");
					Properties contentTypes;

					String fromAddress=sbAuctionClosingVO.getFromAddress();
					toAddress=sbAuctionClosingVO.getQryContactEmail();



					List l1=new ArrayList();
					l1.add(toAddress);
					/*List l2=new ArrayList();
					l2.add(ccAddress);*/
					MailerVO vo=new MailerVO();
					vo.setToAddresses(l1);
					vo.setFromAddress(fromAddress);
					//prashant 			17-Dec-2007  	mantis id-0000375-start
					//vo.setCcAddresses(ccAddress);
					//prashant 			17-Dec-2007  	mantis id-0000375-End
					vo.setHeaderEncoding(headerEncoding);
					vo.setBody(buff.toString());
					vo.setSubject(subject);
					vo.setBodyEncoding(bodyEncoding);
					//System.out.println("Mail has been sending...."+buff.toString());
					try {
						MailerComponent.sendMail(vo);
					} catch (Exception e){
						e.printStackTrace();
						}
					//System.out.println("Mail has been sent");




					sbAuctionClosingVO.setEmailType(1105);
					//PreparedStatement pst25o=con.prepareStatement(auctionClosing025);
					pst25=con.prepareStatement(auctionClosing025);
					pst25.setInt(1, sbAuctionClosingVO.getEmailType());
					ResultSet getmaildetail=pst25.executeQuery();
					if(getmaildetail.next()){
						sbAuctionClosingVO.setMailSubject(getmaildetail.getString(2));
						sbAuctionClosingVO.setEmailStatus(getmaildetail.getInt(3));
					}

					sbAuctionClosingVO.setPLoginId(sbAuctionClosingVO.getReqOwner());
					sbAuctionClosingVO.setUserID(sbAuctionClosingVO.getPLoginId());
					//PreparedStatement pst22o=con.prepareStatement(auctionClosing029);
					pst22=con.prepareStatement(auctionClosing029);
					pst22.setString(1, sbAuctionClosingVO.getReqOwner());
					//System.out.println("contact-id"+sbAuctionClosingVO.getReqOwner());
					ResultSet qryorgclient=pst22.executeQuery();
					if(qryorgclient.next()){
						//System.out.println("setOrgName"+qryorgclient.getString(3)+":"+qryorgclient.getString(2));
						sbAuctionClosingVO.setOrgName(qryorgclient.getString(3));
						sbAuctionClosingVO.setClientName(qryorgclient.getString(2));
					}
				//	Statement stmt3=con.createStatement();
					stmt3=con.createStatement();
					ResultSet qrystdsign=stmt3.executeQuery(auctionClosing030);
					if(qrystdsign.next()){
						sbAuctionClosingVO.setSig(qrystdsign.getString(1));
					}
					else{
						sbAuctionClosingVO.setSig("SKILLBAY");
					}
					//PreparedStatement pst23o=con.prepareStatement(auctionClosing031);
					pst23=con.prepareStatement(auctionClosing031);
					pst23.setInt(1, sbAuctionClosingVO.getReqId());
					pst23.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					ResultSet qryVendordetails=pst23.executeQuery();
					if(qryVendordetails.next()){
						//System.out.println("got name"+qryVendordetails.getString(2));
						sbAuctionClosingVO.setToAddress(qryVendordetails.getString(1));
						sbAuctionClosingVO.setName(qryVendordetails.getString(2));

					}

					//PreparedStatement pst24o=con.prepareStatement(auctionClosing032);
					pst24=con.prepareStatement(auctionClosing032);
					pst24.setInt(1, sbAuctionClosingVO.getReqId());
					pst24.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					ResultSet qryrequiremnt=pst24.executeQuery();
					if(qryrequiremnt.next()){
						sbAuctionClosingVO.setReqRef(qryrequiremnt.getString(1));
						sbAuctionClosingVO.setTitle(qryrequiremnt.getString(2));

					}

					sbAuctionClosingVO.setDealType(4);

					//Statement stmt4=con.createStatement();
					stmt4=con.createStatement();
					ResultSet qrygetS3DTEMVCEmailAddress=stmt4.executeQuery(auctionClosing033);
					if(qrygetS3DTEMVCEmailAddress.next()){
						sbAuctionClosingVO.setSuppMail(qrygetS3DTEMVCEmailAddress.getString(1));
						sbAuctionClosingVO.setSuppName(qrygetS3DTEMVCEmailAddress.getString(2));
					}
//		prashant            04-Dec-2007            changes made for mantis id 0000371   start


					//StringBuffer buffer = new StringBuffer(); commented as not used by raj kiran jv

					try // inner try 4
					{
						PreparedStatement pst50=con.prepareStatement(auctionClosing031);
						pst50.setInt(1, sbAuctionClosingVO.getReqId());
						pst50.setInt(2, sbAuctionClosingVO.getReqLineItemId());
						ResultSet objectsList=pst23.executeQuery();
						while(objectsList.next()){
							sbAuctionClosingVO.setToAddressfornotification(objectsList.getString(1));
							sbAuctionClosingVO.setNamefornotification(objectsList.getString(2));

							  // System.out.println("iam in the forloop objectsList*********------------");
							 	 String fullName1=sbAuctionClosingVO.getNamefornotification();
								//System.out.println("fullName1fullName1------------"+fullName1);
								 String toAddress1 =null;
								   toAddress1=sbAuctionClosingVO.getToAddressfornotification();
									//System.out.println("toAddress1toAddress1----------------"+toAddress1);
//									prashant            04-Dec-2007            changes made for mantis id 0000371End
								   date = new Date();
							        buff=new StringBuffer();
//								01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - start
							        /*date =new java.util.Date(System.currentTimeMillis());
							        d=date.getMonth()+1 +"/"+date.getDate()+"/"+(date.getYear()+1900);
									t=date.getHours()+":"+date.getMinutes();
									//buff.append("To Address:"+sbAuctionClosingVO.getToAddress());
									//System.out.println("To Address:"+sbAuctionClosingVO.getToAddress());

									buff.append(d);
									buff.append(" ");
									buff.append(t);
									buff.append("(EST) \n\n");*/
							   		//  java.util.Date d1=new java.util.Date(System.currentTimeMillis());
							   	    //  String ESTTime=SBDateUtil.getESTTime(d1);
							   	    buff.append(ESTTime+"\n\n");
//							    01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - End
									buff.append(" Dear ");
									buff.append(fullName1);
									buff.append(", \n");
									buff.append("\n This is to notify that the client, ");
									buff.append(sbAuctionClosingVO.getOrgName()+":");
									buff.append(sbAuctionClosingVO.getClientName()+","+"has completed the selection of candidates for the Req Reference / Job Title:"+ sbAuctionClosingVO.getReqRef()+"/" +sbAuctionClosingVO.getTitle()+".");
									buff.append("\n");
									buff.append("\n");
									buff.append(sbAuctionClosingVO.getSuppName());
									buff.append("\n");
									buff.append("\n");

									if(sbAuctionClosingVO.getSig()!=null)
									{
										buff.append(sbAuctionClosingVO.getSig());
									}
									subject=sbAuctionClosingVO.getMailSubject();
									//fromAddress ="[e-mail]";
									prop = null;
									try // inner try 3
									{
										prop = SBLoadProps.loadParams("server");
									}
									catch(Exception e) // inner try 3
									{
										e.printStackTrace();
									}
									bodyEncoding = prop.getProperty("BODY_ENCODING");
									headerEncoding = prop.getProperty("HEADER_ENCODING");

									//toAddress1 =sbAuctionClosingVO.getToAddress();

								// prashant            04-Dec-2007            changes made for mantis id 0000371   start
									MailerVO vo1=new MailerVO();
									List l2=new ArrayList();
									l2.add(toAddress1);

									vo1.setToAddresses(l2);
									vo1.setFromAddress(fromAddress);

									vo1.setHeaderEncoding(headerEncoding);
									vo1.setBody(buff.toString());
									vo1.setSubject(subject);
									vo1.setBodyEncoding(bodyEncoding);
									//System.out.println("Mail has been sending....:"+buff.toString());


									String bypassClienIDs = null;
									try // try inner 1
									{
										org.hibernate.Session x =getSession();
										Connection conn = x.connection();
										Statement st=conn.createStatement();
										ResultSet rs = st.executeQuery(SBREQCloseoutNotMailBypass);

										if(rs.next())
										{
											bypassClienIDs=rs.getString(1);
										}
										releaseSession(x);
										//conn.close(); // raj kiran jv  addd here for colosing conn
									}
									catch(Exception e) // for inner try 1
									{
										sbErrorBatVO.setPgmID("int_sch_adv");
										sbErrorBatVO.setErrMessage("Query Failed : "+SBREQCloseoutNotMailBypass);
										sbErrorBat.logError(sbErrorBatVO);
										e.printStackTrace();
										System.out.println("raj say that there is exception at inner try 1 and was"+e);
										throw e;
									}



										boolean bypassFlag = true;
										if(bypassClienIDs != null)
										{
											// logger.debug("in SBAuctionClosingDAOHibernate----------------");
											String arr[] = bypassClienIDs.split(";");
											for(int j=0;j<arr.length;j++)
											{											
												if(sbAuctionClosingVO.getClientID() == Integer.parseInt(arr[j]))
												{
													bypassFlag = false;
													break;
												}
											}
										}
										else
										{

										}
									 if(bypassFlag)
									 {
									 try{ // for innter try 2

					 System.out.println("iam in catch block For Client Steven---------SbauctionclosingDAohibernate--------");
										 //logger.debug("in  in try block of SBAuctionClosingDAOHibernate----------------");
										 MailerComponent.sendMail(vo1);
									 	}

									 catch(Exception e) // for inner try 2
									 {
										 e.printStackTrace();

										 //logger.debug("in  in catch block of SBAuctionClosingDAOHibernate----------------");
										}
									 }
//									prashant  04-Dec-2007   changes made for mantis id 0000371   End
									System.out.println("iam in the out of bypassFlag block of the bypassflag");

							  }
						objectsList.close(); // raj kiran jv  added here
						pst50.close();// raj kiran jv  added here

					}

					catch(Exception e) // inner try 4
					{
						System.out.println("iam in the  catch block of  the mail body-------------------------**********");
						e.printStackTrace();
						System.out.println("raj says that there is exception inner try 4 was "+e);
					}
					
					

//		 prashant            04-Dec-2007            changes made for mantis id 0000371   End
					}
					
					
					
					
					
					} // while end
			} // end of try d.1
			
			catch (Exception e) // for d.1
			{
				e.printStackTrace();
				System.out.println("raj says that there is exception try d.1 and was "+e);
			}
			finally // for try d.1 
			{
				
				//stmt1.close();
				if(con!=null)
				{
					System.out.println("raj is here inside second if loop of d.1 try and entered inside finally t o close conn");
				stmt3.close();
				stmt4.close();
				stmt5.close();stmt6.close();
				pst19.close();
				pst20.close();
				pst21.close();
				pst22.close();
				pst23.close();				
				pst24.close();
				pst25.close();
				pst26.close();
				pst27.close();								
				pst28.close();
				pst29.close();
				pst30.close();
				pst31.close();
					
				}
								
			}
			}
			

			
			
			catch (Exception e) // for  try d
			{
				e.printStackTrace();
				System.out.print("raj says that there is exception at try D and exception was"+e);
			}
			
			
			

		}

		catch(Exception e)
		{
			sbErrorBatVO.setPgmID(sbAuctionClosingVO.getPgmId());
			sbErrorBatVO.setErrMessage(sbAuctionClosingVO.getErrMessage());
			sbErrorBat.logError(sbErrorBatVO);
			releaseSession(session);
			e.printStackTrace();
			throw e;
		}

		//finally block was added to close connection by raj kiran jv 
		finally
		{
			//releaseSession(session);
			//session.close();
			//con.close();
		}
		return sbAuctionClosingVO;
	}
}

>

[raj chopra]

Any suggestion in resolving this will be greatly helpful.

LOG from tomcat :

raj says that entered into try super in sbauctionclosingraj says that entered into try A in sbauctionclosingraj says that entered into try a
raj says that entered into try a.1raj says that exit into try a.1raj says that existed from  try a and clsoed all connection in it
raj says that existed into try A in sbauctionclosing
 raj says that entered into try B in sbauctionclosingraj says that entered into try B in sbauctionclosing
 raj says that entered into try B.1
  raj says that existed into try B.1 and in finally block
 raj is here in if loop of b.1 try to close connection and entered inside the finally block to close conn
java.lang.NullPointerException
        at com.syntel.sb.arch.batch.dao.hibernate.SBAuctionClosingDAOHibernate.closeAuction(SBAuctionClosingDAOHibernate.java:502)
        at com.syntel.sb.arch.batch.service.impl.SBAuctionClosingDAOManagerImpl.closeAuction(SBAuctionClosingDAOManagerImpl.java:94)
        at com.syntel.sb.arch.batch.jobs.SBAuctionClosingJob.executeInternal(SBAuctionClosingJob.java:77)
        at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:66)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:195)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
raj says that exception at try b wasjava.lang.NullPointerException
 loop inside the finay c.1 and all are commentedjava.lang.NullPointerException
        at com.syntel.sb.arch.batch.dao.hibernate.SBAuctionClosingDAOHibernate.closeAuction(SBAuctionClosingDAOHibernate.java:573)
        at com.syntel.sb.arch.batch.service.impl.SBAuctionClosingDAOManagerImpl.closeAuction(SBAuctionClosingDAOManagerImpl.java:94)
        at com.syntel.sb.arch.batch.jobs.SBAuctionClosingJob.executeInternal(SBAuctionClosingJob.java:77)
        at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:66)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:195)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
raj sayst that the exception at try c wasjava.lang.NullPointerException
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
        at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420)
        at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
        at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
        at com.syntel.sb.arch.batch.dao.hibernate.SBAuctionClosingDAOHibernate.closeAuction(SBAuctionClosingDAOHibernate.java:623)
        at com.syntel.sb.arch.batch.service.impl.SBAuctionClosingDAOManagerImpl.closeAuction(SBAuctionClosingDAOManagerImpl.java:94)
        at com.syntel.sb.arch.batch.jobs.SBAuctionClosingJob.executeInternal(SBAuctionClosingJob.java:77)
        at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:66)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:195)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
raj says that there is exception try d.1 and was java.sql.SQLException: ORA-01000: maximum open cursors exceeded

raj is here inside second if loop of d.1 try and entered inside finally t o close conn
java.lang.NullPointerException
        at com.syntel.sb.arch.batch.dao.hibernate.SBAuctionClosingDAOHibernate.closeAuction(SBAuctionClosingDAOHibernate.java:1060)
        at com.syntel.sb.arch.batch.service.impl.SBAuctionClosingDAOManagerImpl.closeAuction(SBAuctionClosingDAOManagerImpl.java:94)
        at com.syntel.sb.arch.batch.jobs.SBAuctionClosingJob.executeInternal(SBAuctionClosingJob.java:77)
        at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:66)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:195)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
raj says that there is exception at try D and exception wasjava.lang.NullPointerExceptionfile   server
java.sql.SQLException: Connection is closed.
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.checkOpen(PoolingDataSource.java:174)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:179)
        at com.syntel.sb.arch.batch.dao.hibernate.SBWinningBidDAOHibernate.getBypassClientIds(SBWinningBidDAOHibernate.java:806)
        at com.syntel.sb.arch.batch.service.impl.SBWinningBidManagerImpl.process(SBWinningBidManagerImpl.java:661)
        at com.syntel.sb.arch.batch.jobs.SBWinningBidManagerJob.executeInternal(SBWinningBidManagerJob.java:43)
        at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:66)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:195)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)

code related to this error was

package com.syntel.sb.arch.batch.dao.hibernate;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import org.hibernate.Session;

import com.syntel.sb.appl.bo.client.SBReqLineItem;
import com.syntel.sb.appl.vo.admin.MailerVO;
import com.syntel.sb.arch.batch.common.SBAutoGen;
import com.syntel.sb.arch.batch.common.SBBatEndLog;
import com.syntel.sb.arch.batch.common.SBBatStartLog;
import com.syntel.sb.arch.batch.common.SBBatchQueries;
import com.syntel.sb.arch.batch.common.SBErrorBat;
import com.syntel.sb.arch.batch.dao.SBAuctionClosingDAO;
import com.syntel.sb.arch.batch.vo.SBAuctionClosingVO;
import com.syntel.sb.arch.batch.vo.SBBatEndVO;
import com.syntel.sb.arch.batch.vo.SBBatStartVO;
import com.syntel.sb.arch.batch.vo.SBClientReqClosedOutConfirmationVO;
import com.syntel.sb.arch.batch.vo.SBErrorBatVO;
import com.syntel.sb.arch.dao.hibernate.SBBaseDAOHibernate;
import com.syntel.sb.arch.util.MailerComponent;
import com.syntel.sb.arch.util.SBDateUtil;
import com.syntel.sb.arch.util.SBLoadProps;

public class SBAuctionClosingDAOHibernate extends SBBaseDAOHibernate implements SBAuctionClosingDAO, SBBatchQueries {

	String toAddress=null;
	List ccAddress=null;

	static int count=0;

	public SBAuctionClosingVO closeAuction(SBAuctionClosingVO sbAuctionClosingVO) throws Exception {
		logger.debug("in SBAuctionClosingDAOHibernate----------------");
		SBBatStartLog batStartLog=new SBBatStartLog();
		SBBatStartVO sbBatStartVO =new SBBatStartVO();
		SBErrorBat sbErrorBat = new SBErrorBat();
		SBErrorBatVO sbErrorBatVO =new SBErrorBatVO();
		SBClientReqClosedOutConfirmationVO sbClientReqClosedOutConfirmationVO=new SBClientReqClosedOutConfirmationVO();
		//System.out.println("In DAO Hibernate");
		Session session = getSession();
		//System.out.println("session.................. "+session.connection());
		Connection con = session.connection();
		//System.out.println("In DAO After Conection");
		SBAutoGen sbAutoGen=new SBAutoGen();
		StringBuffer buff=new StringBuffer();
		try //try Super
		{
			System.out.print("raj says that entered into try super in sbauctionclosing");
			System.out.print("raj says that entered into try A in sbauctionclosing");
				try  // try  A
				{
					System.out.print("raj says that entered into try a");
			Statement st1=null;
			ResultSet qryGetEmailInfo=null,qryUserId=null;
			PreparedStatement pst1 = null;
			try // try a.1
			 
			{
				System.out.print("raj says that entered into try a.1");
				st1=con.createStatement();
				qryGetEmailInfo=st1.executeQuery(auctionClosing001);
			if(qryGetEmailInfo.next())
			{
				sbAuctionClosingVO.setMailSubject("Auction Close Advice");
			}

			pst1=con.prepareStatement(commonSlctLoginId);
			pst1.setInt(1, 90);

			 qryUserId=pst1.executeQuery();
			if(qryUserId.next())
			{
				sbAuctionClosingVO.setLoginId(qryUserId.getString(1));
				//System.out.println("Inside User ID query");
			}
			System.out.print("raj says that exit into try a.1");	
			}
			finally // for try a.1
			{
				System.out.println("raj says that existed from  try a and clsoed all connection in it ");
				qryUserId.close();
				pst1.close();
				qryGetEmailInfo.close();
				st1.close();
			}
				
			System.out.print("raj says that existed into try A in sbauctionclosing");	
				}
				
				
				catch(Exception e) // catch for A
				{
					e.printStackTrace();
					System.out.println("raj says that exception for try A was in sbauctionclosingdaohibernate"+e);
				}
				
			
			
			
			try // try B
			{
				System.out.print(" \n raj says that entered into try B in sbauctionclosing");
				PreparedStatement sample =  null,pst2=null,pst3=null,pst33=null,pst4=null,pst5=null,pst6=null,pst7=null,pst8=null,pst9=null,pst10=null,pst12=null,pst13=null,pst11=null,pst14=null,pst15=null,pst16=null;
				
				ResultSet qryAllReqs = null,qryUpdate43=null,qryWinningBids=null,qryForConfirmedBids=null,qryearlywinningbids=null;
				
				
				System.out.print("raj says that entered into try B in sbauctionclosing");
				
				try{ // b.1
					
					System.out.print(" \n raj says that entered into try B.1 ");
			
			
			sample = con.prepareStatement(auctionClosing002);
			
			qryAllReqs = sample.executeQuery();
			
			
			
			while(qryAllReqs.next())
			{

					int count1=0;
					int temp = 0;
					sbAuctionClosingVO.setReqId(qryAllReqs.getInt(1));
					sbAuctionClosingVO.setReqLineItemId(qryAllReqs.getInt(7));
					sbAuctionClosingVO.setClientID(qryAllReqs.getInt(4));

					//PreparedStatement pst2=con.prepareStatement(auctionClosing003);
					pst2=con.prepareStatement(auctionClosing003);
					pst2.setInt(1, 43);
					pst2.setString(2, sbAuctionClosingVO.getLoginId());
					pst2.setInt(3, sbAuctionClosingVO.getReqLineItemId());
					pst2.setInt(4, sbAuctionClosingVO.getReqId());
					pst2.setInt(5, 33);

					//ResultSet qryUpdate43=pst2.executeQuery();
					qryUpdate43=pst2.executeQuery();

					//PreparedStatement pst3=con.prepareStatement(auctionClosing004,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					pst3=con.prepareStatement(auctionClosing004,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					pst3.setInt(1, sbAuctionClosingVO.getReqLineItemId());
					pst3.setInt(2, sbAuctionClosingVO.getReqId());

					//ResultSet qryWinningBids=pst3.executeQuery();
					qryWinningBids=pst3.executeQuery();
					//Girish 			08-OCT-2007		Changes made for mantis id 0000356  start

					//PreparedStatement pst33=con.prepareStatement(auctionClosing0044,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					pst33=con.prepareStatement(auctionClosing0044,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					pst33.setInt(1, sbAuctionClosingVO.getReqLineItemId());
					pst33.setInt(2, sbAuctionClosingVO.getReqId());

					//ResultSet qryForConfirmedBids=pst33.executeQuery();
					qryForConfirmedBids=pst33.executeQuery();

					while(qryForConfirmedBids.next())
					{
						temp++;
					}
//System.out.println("temptemp*****************"+temp);
					//System.out.println("i'm before qryWinningBids");
					qryWinningBids.beforeFirst();
					sbAuctionClosingVO.setWiningBidrecordCountFlag(0);
					sbAuctionClosingVO.setRecordCountFlag(0);
					if(!qryWinningBids.next())
					{
						sbAuctionClosingVO.setWiningBidrecordCountFlag(1);
						//PreparedStatement pst4=con.prepareStatement(auctionClosing016,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
						pst4=con.prepareStatement(auctionClosing016,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
						pst4.setInt(1, sbAuctionClosingVO.getReqLineItemId());
						pst4.setInt(2, sbAuctionClosingVO.getReqId());
						//ResultSet qryearlywinningbids=pst4.executeQuery();
						qryearlywinningbids=pst4.executeQuery();
						qryearlywinningbids.beforeFirst();
						if(!qryearlywinningbids.next()){
							sbAuctionClosingVO.setRecordCountFlag(1);
						}
					}

				    qryWinningBids.beforeFirst();
					while(qryWinningBids.next())
					{
						count1++;
					
					}
//					System.out.println("count1-------------"+count1);
					qryWinningBids.beforeFirst();
					int count2=0;
					String autoKeyGenerationTable="PLACEMENT_INTERVIEW_SESSION";
					int newKeyGenerated=0;

					while(qryWinningBids.next())
					{
						count2++;

						sbAuctionClosingVO.setCandID(qryWinningBids.getInt(6));
						sbAuctionClosingVO.setBidID(qryWinningBids.getInt(5));
						sbAuctionClosingVO.setInterviews(qryWinningBids.getInt(1));
						sbAuctionClosingVO.setMemberID(qryWinningBids.getInt(8));
						sbAuctionClosingVO.setClientID(qryWinningBids.getInt(7));
						sbAuctionClosingVO.setRevisionNumber(qryWinningBids.getInt(4));
						if(sbAuctionClosingVO.getInterviews() < (count1+temp))
						{
							if((temp+count2)<= sbAuctionClosingVO.getInterviews())
							{
				//Girish 			08-OCT-2007		Changes made for mantis id 0000356 end
								//PreparedStatement pst5=con.prepareStatement(auctionClosing017);
								pst5=con.prepareStatement(auctionClosing017);
								pst5.setString(1, sbAuctionClosingVO.getLoginId());
								pst5.setInt(2, sbAuctionClosingVO.getBidID());
								//int qryUpdate50=pst5.executeUpdate(); raj kiran  modified
								pst5.executeUpdate();
								autoKeyGenerationTable="PLACEMENT_INTERVIEW_SESSION";
								sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
								//System.out.println(sbAuctionClosingVO.getAutoKeyTableName());
								newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

								//PreparedStatement pst6=con.prepareStatement(auctionClosing006);
								pst6=con.prepareStatement(auctionClosing006);
								pst6.setInt(1, sbAuctionClosingVO.getCandID());
								//System.out.println("candidateID:"+sbAuctionClosingVO.getCandID());
								ResultSet qryResume=pst6.executeQuery();
								//qryResume=pst6.executeQuery();

								if(qryResume.next())
									
								{
									if(qryResume.getString(1)==null){
										sbAuctionClosingVO.setResumeFlag(0);
									}
									else{
										sbAuctionClosingVO.setResumeFlag(1);
									}
								}

							//	PreparedStatement pst7=con.prepareStatement(auctionClosing008);
								pst7=con.prepareStatement(auctionClosing008);
								pst7.setInt(1,sbAuctionClosingVO.getBidID());
								pst7.setInt(2,newKeyGenerated);
								pst7.setInt(3,sbAuctionClosingVO.getCandID());
								pst7.setInt(4,sbAuctionClosingVO.getMemberID());
								pst7.setInt(5,sbAuctionClosingVO.getRevisionNumber());
								pst7.setInt(6,sbAuctionClosingVO.getClientID());
								pst7.setString(7, sbAuctionClosingVO.getLoginId());
								//int qryInsertPlacement=pst7.executeUpdate(); raj kiran modified this
								pst7.executeUpdate();

								autoKeyGenerationTable="E_MAIL";

								sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);

								newKeyGenerated = sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

								//PreparedStatement pst8=con.prepareStatement(auctionClosing007);
								pst8=con.prepareStatement(auctionClosing007);
								pst8.setInt(1, newKeyGenerated);
								pst8.setInt(2, sbAuctionClosingVO.getMemberID());
								pst8.setInt(3, sbAuctionClosingVO.getBidID());
								pst8.setInt(4, sbAuctionClosingVO.getRevisionNumber());
								pst8.setInt(5, qryWinningBids.getInt(2));
								pst8.setInt(6, qryWinningBids.getInt(3));
								pst8.setString(7, sbAuctionClosingVO.getLoginId());
								pst8.executeUpdate();
								//int qryEmailInsert=pst8.executeUpdate(); raj krian modified this
							}
							else
							{
								//PreparedStatement pst9=con.prepareStatement(auctionClosing018);
								pst9=con.prepareStatement(auctionClosing018);
								pst9.setString(1, sbAuctionClosingVO.getLoginId());
								pst9.setInt(2, sbAuctionClosingVO.getBidID());
								pst9.executeUpdate();
								// int qryUpdate40=pst9.executeUpdate(); raj kiran modififed thsi
								autoKeyGenerationTable="E_MAIL";
								sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
								newKeyGenerated =sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);
								//System.out.println("The query is");
							//	PreparedStatement pst10=con.prepareStatement(auctionClosing019);
								pst10=con.prepareStatement(auctionClosing019);
								pst10.setInt(1, newKeyGenerated);
								pst10.setInt(2, sbAuctionClosingVO.getMemberID());
								pst10.setInt(3, sbAuctionClosingVO.getBidID());
								pst10.setInt(4, sbAuctionClosingVO.getRevisionNumber());
								pst10.setInt(5, sbAuctionClosingVO.getReqId());
								pst10.setInt(6, sbAuctionClosingVO.getReqLineItemId());
								pst10.setString(7, sbAuctionClosingVO.getLoginId());
								pst10.setInt(8, sbAuctionClosingVO.getClientID());
								pst10.executeUpdate();
								 // int qryEmailInsert1=pst10.executeUpdate(); raj kiran modified this
							}

						}
						else
						{
							//PreparedStatement pst11=con.prepareStatement(auctionClosing017);
							pst11=con.prepareStatement(auctionClosing017);
							pst11.setString(1,sbAuctionClosingVO.getLoginId() );
							pst11.setInt(2, sbAuctionClosingVO.getBidID());
							pst11.executeUpdate();
							// int qryUpdate50=pst11.executeUpdate(); raj kiran modififed thso
							autoKeyGenerationTable="PLACEMENT_INTERVIEW_SESSION";
							sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
							//System.out.println(sbAuctionClosingVO.getAutoKeyTableName());
							newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

							//PreparedStatement pst12=con.prepareStatement(auctionClosing006);
							pst12=con.prepareStatement(auctionClosing006);
							pst12.setInt(1, sbAuctionClosingVO.getCandID());
							ResultSet qryResume=pst12.executeQuery();
							if(qryResume.next())
							{
								if(qryResume.getString(1)==null){
									sbAuctionClosingVO.setResumeFlag(0);
								}
								else{
									sbAuctionClosingVO.setResumeFlag(1);
								}
							}

							//PreparedStatement pst13=con.prepareStatement(auctionClosing020);
							pst13=con.prepareStatement(auctionClosing020);
							pst13.setInt(1, sbAuctionClosingVO.getBidID());
							pst13.setInt(2, newKeyGenerated);
							pst13.setInt(3, sbAuctionClosingVO.getCandID());
							pst13.setInt(4, sbAuctionClosingVO.getMemberID());
							pst13.setInt(5, sbAuctionClosingVO.getRevisionNumber());
							pst13.setInt(6, sbAuctionClosingVO.getClientID());
							pst13.setString(7, sbAuctionClosingVO.getLoginId());

							pst13.executeUpdate();
							//int qryInsertPlacement=pst13.executeUpdate(); rajj kiran modified this

							autoKeyGenerationTable="E_MAIL";
							sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
							newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);

						//	PreparedStatement pst14=con.prepareStatement(auctionClosing021);
							pst14=con.prepareStatement(auctionClosing021);
							pst14.setInt(1, newKeyGenerated);
							pst14.setInt(2, sbAuctionClosingVO.getMemberID());
							pst14.setInt(3, sbAuctionClosingVO.getBidID());
							pst14.setInt(4, sbAuctionClosingVO.getRevisionNumber());
							pst14.setInt(5, sbAuctionClosingVO.getReqId());
							pst14.setInt(6, sbAuctionClosingVO.getReqLineItemId());
							pst14.setString(7, sbAuctionClosingVO.getLoginId());
							pst14.setInt(8, sbAuctionClosingVO.getClientID());

							pst14.executeUpdate();
							// int qryEmailInsert1=pst14.executeUpdate(); rajj kiran modifif ed this
						}
					}
					//PreparedStatement pst15=con.prepareStatement(auctionClosing009);
					pst15=con.prepareStatement(auctionClosing009);
					pst15.setInt(1,sbAuctionClosingVO.getReqLineItemId());
					pst15.setInt(2,sbAuctionClosingVO.getReqId());

					pst15.executeUpdate();
					//int updatereqs=pst15.executeUpdate(); raj kiran modified this

					autoKeyGenerationTable="E_MAIL";
					sbAuctionClosingVO.setAutoKeyTableName(autoKeyGenerationTable);
					newKeyGenerated=sbAutoGen.getSequenceNumber(session, autoKeyGenerationTable);
				//	PreparedStatement pst16=con.prepareStatement(auctionClosing022);
					pst16=con.prepareStatement(auctionClosing022);
					pst16.setInt(1, newKeyGenerated);
					if(sbAuctionClosingVO.getRecordCountFlag()==1 && sbAuctionClosingVO.getWiningBidrecordCountFlag()==1 ){
						pst16.setInt(2, 408);
					}
					else{
						pst16.setInt(2, 309);
					}
					if(sbAuctionClosingVO.getWiningBidrecordCountFlag()==0){
						pst16.setString(3, null);
					}
					else{
						pst16.setInt(3, sbAuctionClosingVO.getBidID());
					}
					if(sbAuctionClosingVO.getWiningBidrecordCountFlag()==1){
						pst16.setString(4,null);
					}
					else{
						pst16.setInt(4, sbAuctionClosingVO.getRevisionNumber());
					}
					pst16.setInt(5, sbAuctionClosingVO.getReqId());
					pst16.setInt(6, sbAuctionClosingVO.getReqLineItemId());
					if(sbAuctionClosingVO.getWiningBidrecordCountFlag()==1){
						pst16.setString(7, null);
					}
					else{
						pst16.setString(7, sbAuctionClosingVO.getMailSubject());
					}
					pst16.setString(8, sbAuctionClosingVO.getLoginId());
					pst16.setInt(9, sbAuctionClosingVO.getClientID());

					pst16.executeUpdate();
					 // int updatereqs1=pst16.executeUpdate(); raj kiran modified this
			}
				}
				catch (Exception e) // for b.1
				{
					e.printStackTrace();
					System.out.println("raj is here in b.1 try and exception es"+e);
				}

			finally // for try b.1
			{
				System.out.print(" \n  raj says that existed into try B.1 and in finally block");
				sample.close();
				if(con!=null){
	System.out.println(" \n raj is here in if loop of b.1 try to close connection and entered inside the finally block to close conn");
				pst2.close();
				pst3.close();
				pst33.close();
				pst4.close();
				pst5.close();
				pst6.close();
				pst7.close();
				pst8.close();
				pst9.close();
				pst10.close();
				pst12.close();
				pst13.close();
				pst11.close();
			pst14.close();
				pst15.close();
				pst16.close();
				}
//}
			}
			
			System.out.print("raj says that existed into try B and closed all connections in finally block");
			}
			
						
			
			catch(Exception e) // for try b
			{
				e.printStackTrace();
				System.out.print("raj says that exception at try b was"+e);
			}
			
			
			
			try { // try c raj modified for closing connections
			
			Statement stmt=con.createStatement();
			ResultSet qryBidForwarded=stmt.executeQuery(auctionClosing023);
			ResultSet getMailDetail = null;
			PreparedStatement pst17 =null,pst18= null;
			try //try c.1
			{
			if(qryBidForwarded.next())
			{
				sbAuctionClosingVO.setEmailType(1107);
				sbAuctionClosingVO.setReqId(qryBidForwarded.getInt(1));
				sbAuctionClosingVO.setReqLineItemId(qryBidForwarded.getInt(2));
				sbAuctionClosingVO.setClientID(qryBidForwarded.getInt(3));
				//PreparedStatement pst17=con.prepareStatement(auctionClosing024);
				pst17=con.prepareStatement(auctionClosing024);
				pst17.setString(1, sbAuctionClosingVO.getLoginId());
				pst17.setInt(2, sbAuctionClosingVO.getReqId());
				pst17.setInt(3, sbAuctionClosingVO.getReqLineItemId());
				//int UPDATEREQSTATUS35=pst17.executeUpdate();
				pst17.executeUpdate(); // raj kiran modified it
			//	PreparedStatement pst18=con.prepareStatement(auctionClosing025);
				pst18=con.prepareStatement(auctionClosing025);
				pst18.setInt(1, sbAuctionClosingVO.getEmailType());
				
			//	ResultSet getMailDetail=pst18.executeQuery();
				getMailDetail=pst18.executeQuery();
				if(getMailDetail.next()){
					sbAuctionClosingVO.setMailSubject(getMailDetail.getString(2));
					sbAuctionClosingVO.setEmailStatus(getMailDetail.getInt(3));
				}
			}
			}
			finally // for try c.1
			{
				System.out.print(" \n loop inside the finay c.1 and all are commented");
				stmt.close();
				qryBidForwarded.close();
				pst17.close();
				pst18.close();
				
				
			}
			}
			catch(Exception e) // for try c
			{
			e.printStackTrace();
			System.out.println("raj sayst that the exception at try c was" +e);
			
			}
			
			
			try {  // try D
			
			Statement stmt1=con.createStatement();
			Statement stmt6=null,stmt3=null,stmt4=null,stmt5=null;
			ResultSet qryreq3035=stmt1.executeQuery(auctionClosing026); // raj kiran jv changed the stmt here to stmt1
			PreparedStatement pst19 =null,pst20 =null,pst21= null,pst22= null,pst23= null,pst24= null,pst25= null,pst26= null,pst27= null,pst28= null,pst29= null,pst30= null,pst31= null;
			
			try // try d.1
			
			{
				
			while(qryreq3035.next())
			{
				//System.out.println("qryreq3035 is executing");
				sbAuctionClosingVO.setReqId(qryreq3035.getInt(1));
				sbAuctionClosingVO.setReqLineItemId(qryreq3035.getInt(2));
				sbAuctionClosingVO.setReqOwner(qryreq3035.getString(3));
				/*saurabh modified on 18th June 2008 for Mantis id 0000405 start*/
				sbAuctionClosingVO.setReqstatuscode(qryreq3035.getInt(4));
				/*saurabh modified on 18th June 2008 for Mantis id 0000405 end*/
				//System.out.println("ReqOwner"+qryreq3035.getString(3));
			//	PreparedStatement pst19=con.prepareStatement(auctionClosing027);
				pst19=con.prepareStatement(auctionClosing027);
				pst19.setInt(1, sbAuctionClosingVO.getReqId());
				//System.out.println("reqid:" +sbAuctionClosingVO.getReqId());
				pst19.setInt(2, sbAuctionClosingVO.getReqLineItemId());
				//System.out.println("reqlineid:" +sbAuctionClosingVO.getReqLineItemId());
				ResultSet qrygetplaced=pst19.executeQuery();
				if(qrygetplaced.next()){
					sbAuctionClosingVO.setEmpPlaced(qrygetplaced.getInt(1));
				}
				//System.out.println("empplaced:"+sbAuctionClosingVO.getEmpPlaced());
				//PreparedStatement pst20=con.prepareStatement(auctionClosing028);
				pst20=con.prepareStatement(auctionClosing028);
				pst20.setInt(1, sbAuctionClosingVO.getReqId());
				pst20.setInt(2, sbAuctionClosingVO.getReqLineItemId());
				ResultSet qrygetreqd=pst20.executeQuery();
				if(qrygetreqd.next()){
					sbAuctionClosingVO.setStaffing(qrygetreqd.getInt(1));
				}
				if(sbAuctionClosingVO.getEmpPlaced() >= sbAuctionClosingVO.getStaffing())
				{
					// logger.debug("in SBAuctionClosingDAOHibernate-xxxxxxxxxxxxx---------------");
					
					
					
					 
					//PreparedStatement pst21=con.prepareStatement(auctionClosing015);
					pst21=con.prepareStatement(auctionClosing015);
					pst21.setInt(1, sbAuctionClosingVO.getReqId());
					pst21.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					pst21.executeUpdate();
					//int qrygetreqd1=pst21.executeUpdate(); raj kiran modified this
					/*saurabh modified on 18th June 2008 for Mantis id 0000405 start*/
					if (sbAuctionClosingVO.getReqstatuscode()== 20){
						try{
						PreparedStatement pst01=con.prepareStatement(auctionClosing050);
						pst01.setInt(1, sbAuctionClosingVO.getReqId());
						pst01.setInt(2, sbAuctionClosingVO.getReqLineItemId());
						pst01.executeUpdate();
						//int qrygetaucdtchnge=pst01.executeUpdate(); raj kiran modified this

						}
						catch(Exception e){
							e.printStackTrace();
							}
					}
					/*saurabh modified on 18th June 2008 for Mantis id 0000405 end*/
					sbAuctionClosingVO.setEmailType(1104);
					//PreparedStatement pst26=con.prepareStatement(auctionClosing025);
					pst26=con.prepareStatement(auctionClosing025);
					pst26.setInt(1, sbAuctionClosingVO.getEmailType());
					ResultSet getmailDetail=pst26.executeQuery();
					if(getmailDetail.next()){
						sbAuctionClosingVO.setMailSubject(getmailDetail.getString(2));
						sbAuctionClosingVO.setEmailStatus(getmailDetail.getInt(3));
					}
					//PreparedStatement pst27=con.prepareStatement(auctionClosing034);
					pst27=con.prepareStatement(auctionClosing034);
					pst27.setString(1, sbAuctionClosingVO.getReqOwner());
					ResultSet qrycontacttype=pst27.executeQuery();
					if(qrycontacttype.next()){
						sbAuctionClosingVO.setClientID(qrycontacttype.getInt(4));
						sbAuctionClosingVO.setQryContactEmail(qrycontacttype.getString(3));
						sbAuctionClosingVO.setName(qrycontacttype.getString(2));
					}

					//PreparedStatement pst28=con.prepareStatement(auctionClosing035);
					pst28=con.prepareStatement(auctionClosing035);
					pst28.setInt(1, sbAuctionClosingVO.getClientID());
					ResultSet qryspoccontact=pst28.executeQuery();
					if(qryspoccontact.next()){
						sbAuctionClosingVO.setQryspoccontactemail(qryspoccontact.getString(3));
					}
					//Statement stmt5=con.createStatement();
					stmt5=con.createStatement();
					ResultSet qrystdsign1=stmt5.executeQuery(auctionClosing030);
					if(qrystdsign1.next()){
						sbAuctionClosingVO.setSig(qrystdsign1.getString(1));
					}
			//		PreparedStatement pst29=con.prepareStatement(auctionClosing036);
					pst29=con.prepareStatement(auctionClosing036);
					pst29.setInt(1, sbAuctionClosingVO.getClientID());
					ResultSet Qrysuppperson=pst29.executeQuery();
					if(Qrysuppperson.next()){
						sbAuctionClosingVO.setSuppName(Qrysuppperson.getString(2));
						sbAuctionClosingVO.setSuppMail(Qrysuppperson.getString(3));
					}
					//PreparedStatement pst30=con.prepareStatement(auctionClosing037);
					 pst30=con.prepareStatement(auctionClosing037);
					pst30.setInt(1, sbAuctionClosingVO.getReqId());
					pst30.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					pst30.executeQuery();
					// ResultSet qryclispoc=pst30.executeQuery(); raj kiran modified this
					sbAuctionClosingVO.setToAddress(sbAuctionClosingVO.getQryContactEmail());
					//cc address has to be implemented
					String cc="";
					ccAddress = new ArrayList();
					
					cc =sbAuctionClosingVO.getSuppMail();
					ccAddress.add(cc);

					//System.out.println("To Address:"+sbAuctionClosingVO.getQryContactEmail());
					//System.out.println("From Address:"+sbAuctionClosingVO.getFromAddress());
					//System.out.println("CC Address:"+ccAddress);

					if(sbAuctionClosingVO.getQryspoccontactemail()!=null )
						if(sbAuctionClosingVO.getQryspoccontactemail().trim()!="")
						ccAddress.add(sbAuctionClosingVO.getQryspoccontactemail());
					//ccAddress.add(sbAuctionClosingVO.getQryspoccontactemail());
//                    System.out.println("cc"+cc+"sbAuctionClosingVO.getQryspoccontactemail()"+sbAuctionClosingVO.getQryspoccontactemail());
					//Statement stmt6=con.createStatement();\
					stmt6=con.createStatement();
					ResultSet qryAdminEMail=stmt6.executeQuery(auctionClosing038);
					if(qryAdminEMail.next()){
						sbAuctionClosingVO.setFromAddress(qryAdminEMail.getString(1));
					}
				//PreparedStatement pst31=con.prepareStatement(auctionClosing032);
					pst31=con.prepareStatement(auctionClosing032);
					pst31.setInt(1, sbAuctionClosingVO.getReqId());
					pst31.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					ResultSet qryrequiremnt1=pst31.executeQuery();
					if(qryrequiremnt1.next()){
						sbAuctionClosingVO.setReqRef(qryrequiremnt1.getString(1));
						sbAuctionClosingVO.setTitle(qryrequiremnt1.getString(2));

					}
					Date date = new Date(); //raj commented this
			        buff=new StringBuffer();

//				 01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - start
			        /*date =new java.util.Date(System.currentTimeMillis());
			        String d=date.getMonth()+1 +"/"+date.getDate()+"/"+(date.getYear()+1900);
					String t=date.getHours()+":"+date.getMinutes();
					//buff.append("To Address:"+sbAuctionClosingVO.getToAddress());
					//System.out.println("To Address:"+sbAuctionClosingVO.getToAddress());

					buff.append(d);
					buff.append(" ");
					buff.append(t);
					buff.append("(EST) \n\n");*/
  		   		    java.util.Date d1=new java.util.Date(System.currentTimeMillis());
			   	    String ESTTime=SBDateUtil.getESTTime(d1);
			   	    buff.append(ESTTime+"\n\n");
//			    01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - End
					buff.append(" Dear ");
					buff.append(sbAuctionClosingVO.getName());
					buff.append(", \n");
					buff.append("\n This is to confirm that the Req Reference / Job Title :  ");
					buff.append( sbAuctionClosingVO.getReqRef()+"/" +sbAuctionClosingVO.getTitle()+"has been closed out.");
					buff.append("\n");
					buff.append("\n");
					buff.append(sbAuctionClosingVO.getSuppName());
					buff.append("\n");
					buff.append("\n");
					if(sbAuctionClosingVO.getSig()!=null)
					{
						buff.append(sbAuctionClosingVO.getSig());
					}
					String subject=sbAuctionClosingVO.getMailSubject(); // raj is here-----------------------------
					//String fromAddress ="[e-mail]";
					Properties prop = null;
					try
					{
						prop = SBLoadProps.loadParams("server");
					}
					catch(Exception e){
						e.printStackTrace();
					}
					String bodyEncoding = prop.getProperty("BODY_ENCODING");
					String headerEncoding = prop.getProperty("HEADER_ENCODING");
					Properties contentTypes;

					String fromAddress=sbAuctionClosingVO.getFromAddress();
					toAddress=sbAuctionClosingVO.getQryContactEmail();



					List l1=new ArrayList();
					l1.add(toAddress);
					/*List l2=new ArrayList();
					l2.add(ccAddress);*/
					MailerVO vo=new MailerVO();
					vo.setToAddresses(l1);
					vo.setFromAddress(fromAddress);
					//prashant 			17-Dec-2007  	mantis id-0000375-start
					//vo.setCcAddresses(ccAddress);
					//prashant 			17-Dec-2007  	mantis id-0000375-End
					vo.setHeaderEncoding(headerEncoding);
					vo.setBody(buff.toString());
					vo.setSubject(subject);
					vo.setBodyEncoding(bodyEncoding);
					//System.out.println("Mail has been sending...."+buff.toString());
					try {
						MailerComponent.sendMail(vo);
					} catch (Exception e){
						e.printStackTrace();
						}
					//System.out.println("Mail has been sent");




					sbAuctionClosingVO.setEmailType(1105);
					//PreparedStatement pst25o=con.prepareStatement(auctionClosing025);
					pst25=con.prepareStatement(auctionClosing025);
					pst25.setInt(1, sbAuctionClosingVO.getEmailType());
					ResultSet getmaildetail=pst25.executeQuery();
					if(getmaildetail.next()){
						sbAuctionClosingVO.setMailSubject(getmaildetail.getString(2));
						sbAuctionClosingVO.setEmailStatus(getmaildetail.getInt(3));
					}

					sbAuctionClosingVO.setPLoginId(sbAuctionClosingVO.getReqOwner());
					sbAuctionClosingVO.setUserID(sbAuctionClosingVO.getPLoginId());
					//PreparedStatement pst22o=con.prepareStatement(auctionClosing029);
					pst22=con.prepareStatement(auctionClosing029);
					pst22.setString(1, sbAuctionClosingVO.getReqOwner());
					//System.out.println("contact-id"+sbAuctionClosingVO.getReqOwner());
					ResultSet qryorgclient=pst22.executeQuery();
					if(qryorgclient.next()){
						//System.out.println("setOrgName"+qryorgclient.getString(3)+":"+qryorgclient.getString(2));
						sbAuctionClosingVO.setOrgName(qryorgclient.getString(3));
						sbAuctionClosingVO.setClientName(qryorgclient.getString(2));
					}
				//	Statement stmt3=con.createStatement();
					stmt3=con.createStatement();
					ResultSet qrystdsign=stmt3.executeQuery(auctionClosing030);
					if(qrystdsign.next()){
						sbAuctionClosingVO.setSig(qrystdsign.getString(1));
					}
					else{
						sbAuctionClosingVO.setSig("SKILLBAY");
					}
					//PreparedStatement pst23o=con.prepareStatement(auctionClosing031);
					pst23=con.prepareStatement(auctionClosing031);
					pst23.setInt(1, sbAuctionClosingVO.getReqId());
					pst23.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					ResultSet qryVendordetails=pst23.executeQuery();
					if(qryVendordetails.next()){
						//System.out.println("got name"+qryVendordetails.getString(2));
						sbAuctionClosingVO.setToAddress(qryVendordetails.getString(1));
						sbAuctionClosingVO.setName(qryVendordetails.getString(2));

					}

					//PreparedStatement pst24o=con.prepareStatement(auctionClosing032);
					pst24=con.prepareStatement(auctionClosing032);
					pst24.setInt(1, sbAuctionClosingVO.getReqId());
					pst24.setInt(2, sbAuctionClosingVO.getReqLineItemId());
					ResultSet qryrequiremnt=pst24.executeQuery();
					if(qryrequiremnt.next()){
						sbAuctionClosingVO.setReqRef(qryrequiremnt.getString(1));
						sbAuctionClosingVO.setTitle(qryrequiremnt.getString(2));

					}

					sbAuctionClosingVO.setDealType(4);

					//Statement stmt4=con.createStatement();
					stmt4=con.createStatement();
					ResultSet qrygetS3DTEMVCEmailAddress=stmt4.executeQuery(auctionClosing033);
					if(qrygetS3DTEMVCEmailAddress.next()){
						sbAuctionClosingVO.setSuppMail(qrygetS3DTEMVCEmailAddress.getString(1));
						sbAuctionClosingVO.setSuppName(qrygetS3DTEMVCEmailAddress.getString(2));
					}
//		prashant            04-Dec-2007            changes made for mantis id 0000371   start


					//StringBuffer buffer = new StringBuffer(); commented as not used by raj kiran jv

					try // inner try 4
					{
						PreparedStatement pst50=con.prepareStatement(auctionClosing031);
						pst50.setInt(1, sbAuctionClosingVO.getReqId());
						pst50.setInt(2, sbAuctionClosingVO.getReqLineItemId());
						ResultSet objectsList=pst23.executeQuery();
						while(objectsList.next()){
							sbAuctionClosingVO.setToAddressfornotification(objectsList.getString(1));
							sbAuctionClosingVO.setNamefornotification(objectsList.getString(2));

							  // System.out.println("iam in the forloop objectsList*********------------");
							 	 String fullName1=sbAuctionClosingVO.getNamefornotification();
								//System.out.println("fullName1fullName1------------"+fullName1);
								 String toAddress1 =null;
								   toAddress1=sbAuctionClosingVO.getToAddressfornotification();
									//System.out.println("toAddress1toAddress1----------------"+toAddress1);
//									prashant            04-Dec-2007            changes made for mantis id 0000371End
								   date = new Date();
							        buff=new StringBuffer();
//								01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - start
							        /*date =new java.util.Date(System.currentTimeMillis());
							        d=date.getMonth()+1 +"/"+date.getDate()+"/"+(date.getYear()+1900);
									t=date.getHours()+":"+date.getMinutes();
									//buff.append("To Address:"+sbAuctionClosingVO.getToAddress());
									//System.out.println("To Address:"+sbAuctionClosingVO.getToAddress());

									buff.append(d);
									buff.append(" ");
									buff.append(t);
									buff.append("(EST) \n\n");*/
							   		//  java.util.Date d1=new java.util.Date(System.currentTimeMillis());
							   	    //  String ESTTime=SBDateUtil.getESTTime(d1);
							   	    buff.append(ESTTime+"\n\n");
//							    01-FEB-2007 Ganesh - Fix for defect with mantis id 0000075 - End
									buff.append(" Dear ");
									buff.append(fullName1);
									buff.append(", \n");
									buff.append("\n This is to notify that the client, ");
									buff.append(sbAuctionClosingVO.getOrgName()+":");
									buff.append(sbAuctionClosingVO.getClientName()+","+"has completed the selection of candidates for the Req Reference / Job Title:"+ sbAuctionClosingVO.getReqRef()+"/" +sbAuctionClosingVO.getTitle()+".");
									buff.append("\n");
									buff.append("\n");
									buff.append(sbAuctionClosingVO.getSuppName());
									buff.append("\n");
									buff.append("\n");

									if(sbAuctionClosingVO.getSig()!=null)
									{
										buff.append(sbAuctionClosingVO.getSig());
									}
									subject=sbAuctionClosingVO.getMailSubject();
									//fromAddress ="[e-mail]";
									prop = null;
									try // inner try 3
									{
										prop = SBLoadProps.loadParams("server");
									}
									catch(Exception e) // inner try 3
									{
										e.printStackTrace();
									}
									bodyEncoding = prop.getProperty("BODY_ENCODING");
									headerEncoding = prop.getProperty("HEADER_ENCODING");

									//toAddress1 =sbAuctionClosingVO.getToAddress();

								// prashant            04-Dec-2007            changes made for mantis id 0000371   start
									MailerVO vo1=new MailerVO();
									List l2=new ArrayList();
									l2.add(toAddress1);

									vo1.setToAddresses(l2);
									vo1.setFromAddress(fromAddress);

									vo1.setHeaderEncoding(headerEncoding);
									vo1.setBody(buff.toString());
									vo1.setSubject(subject);
									vo1.setBodyEncoding(bodyEncoding);
									//System.out.println("Mail has been sending....:"+buff.toString());


									String bypassClienIDs = null;
									try // try inner 1
									{
										org.hibernate.Session x =getSession();
										Connection conn = x.connection();
										Statement st=conn.createStatement();
										ResultSet rs = st.executeQuery(SBREQCloseoutNotMailBypass);

										if(rs.next())
										{
											bypassClienIDs=rs.getString(1);
										}
										releaseSession(x);
										//conn.close(); // raj kiran jv  addd here for colosing conn
									}
									catch(Exception e) // for inner try 1
									{
										sbErrorBatVO.setPgmID("int_sch_adv");
										sbErrorBatVO.setErrMessage("Query Failed : "+SBREQCloseoutNotMailBypass);
										sbErrorBat.logError(sbErrorBatVO);
										e.printStackTrace();
										System.out.println("raj say that there is exception at inner try 1 and was"+e);
										throw e;
									}



										boolean bypassFlag = true;
										if(bypassClienIDs != null)
										{
											// logger.debug("in SBAuctionClosingDAOHibernate----------------");
											String arr[] = bypassClienIDs.split(";");
											for(int j=0;j<arr.length;j++)
											{											
												if(sbAuctionClosingVO.getClientID() == Integer.parseInt(arr[j]))
												{
													bypassFlag = false;
													break;
												}
											}
										}
										else
										{

										}
									 if(bypassFlag)
									 {
									 try{ // for innter try 2

					 System.out.println("iam in catch block For Client Steven---------SbauctionclosingDAohibernate--------");
										 //logger.debug("in  in try block of SBAuctionClosingDAOHibernate----------------");
										 MailerComponent.sendMail(vo1);
									 	}

									 catch(Exception e) // for inner try 2
									 {
										 e.printStackTrace();

										 //logger.debug("in  in catch block of SBAuctionClosingDAOHibernate----------------");
										}
									 }
//									prashant  04-Dec-2007   changes made for mantis id 0000371   End
									System.out.println("iam in the out of bypassFlag block of the bypassflag");

							  }
						objectsList.close(); // raj kiran jv  added here
						pst50.close();// raj kiran jv  added here

					}

					catch(Exception e) // inner try 4
					{
						System.out.println("iam in the  catch block of  the mail body-------------------------**********");
						e.printStackTrace();
						System.out.println("raj says that there is exception inner try 4 was "+e);
					}
					
					

//		 prashant            04-Dec-2007            changes made for mantis id 0000371   End
					}
					
					
					
					
					
					} // while end
			} // end of try d.1
			
			catch (Exception e) // for d.1
			{
				e.printStackTrace();
				System.out.println("raj says that there is exception try d.1 and was "+e);
			}
			finally // for try d.1 
			{
				
				//stmt1.close();
				if(con!=null)
				{
					System.out.println("raj is here inside second if loop of d.1 try and entered inside finally t o close conn");
				stmt3.close();
				stmt4.close();
				stmt5.close();stmt6.close();
				pst19.close();
				pst20.close();
				pst21.close();
				pst22.close();
				pst23.close();				
				pst24.close();
				pst25.close();
				pst26.close();
				pst27.close();								
				pst28.close();
				pst29.close();
				pst30.close();
				pst31.close();
					
				}
								
			}
			}
			

			
			
			catch (Exception e) // for  try d
			{
				e.printStackTrace();
				System.out.print("raj says that there is exception at try D and exception was"+e);
			}
			
			
			

		}

		catch(Exception e)
		{
			sbErrorBatVO.setPgmID(sbAuctionClosingVO.getPgmId());
			sbErrorBatVO.setErrMessage(sbAuctionClosingVO.getErrMessage());
			sbErrorBat.logError(sbErrorBatVO);
			releaseSession(session);
			e.printStackTrace();
			throw e;
		}

		//finally block was added to close connection by raj kiran jv 
		finally
		{
			//releaseSession(session);
			//session.close();
			//con.close();
		}
		return sbAuctionClosingVO;
	}
}

>

[K. Tsang]

It's really hard to follow all that code.

Your print stack error is NullPointerException somewhere. But it's hard to identify which try block caused this exception since you got 4 sets A,B,C,D.

If the line number 502 is indicator, it's in try block C but not sure.