Hi Ranchers
I am getting an maximum open cursors exception in my DAO.I am using Oracle as my backend.
please suggest me a solution to this problem....
thanks in advance.....
package com.jps.dao;
import java.util.Date;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Date;
import com.jps.utils.DbBean;
import com.jps.bean.ViewRemittanceDOB;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.io.PrintWriter;
public class ViewRemittanceDao {
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
DbBean db=new DbBean ();
String strFormattedBusinessDate=null;
public ArrayList viewRemittance(String View,String year,String month,String day) throws ParseException {
String query=null;
ArrayList all_al_remittance=new ArrayList();
int i_count=0;
try{
con=db.dbConnection();
if(View==null || year==null || month==null || day==null){
//to get the remittance date
ps=con.prepareStatement("SELECT BUSINESS_DT FROM JPS_BUSINESS_DATE");
rs=ps.executeQuery();
while(rs.next()){
strFormattedBusinessDate=getDateInFormat(rs.getDate(1));
}
query="SELECT MOC_ID,MOC_CONTRACT_NO,TOT_ADVANCE_PAYMENT_AMT,TOT_CANCELLATION_AMT,TOT_REMITTANCE_AMT,COUNTING_NO FROM JPS_MO_REMITTANCE_TRNS WHERE JPS_MO_REMITTANCE_TRNS.REMITTANCE_DT=(SELECT BUSINESS_DT FROM JPS_BUSINESS_DATE) AND JPS_MO_REMITTANCE_TRNS.VALIDATED_FLG='Y' ORDER BY MOC_ID ASC";
}else{
String str_date=year+month+day;
query="SELECT MOC_ID,MOC_CONTRACT_NO,TOT_ADVANCE_PAYMENT_AMT,TOT_CANCELLATION_AMT,TOT_REMITTANCE_AMT,COUNTING_NO FROM JPS_MO_REMITTANCE_TRNS WHERE JPS_MO_REMITTANCE_TRNS.REMITTANCE_DT=to_date('"+str_date+"'"+",'yyyymmdd') AND JPS_MO_REMITTANCE_TRNS.VALIDATED_FLG='Y' ORDER BY MOC_ID ASC";
}
System.out.println("main query in ViewRemittanceDao:"+query);
ps=con.prepareStatement("select count(*) from JPS_MO_REMITTANCE_TRNS");
rs=ps.executeQuery();
while(rs.next()){
i_count=rs.getInt(1);
}
ps=con.prepareStatement(query);
rs=ps.executeQuery();
String str_moc_id="";
String str_moc_contract_no="";
double d_tot_advance_payment_amt=0;
double d_tot_cancellation_amt=0;
double d_tot_remittance_amt=0;
String str_countNo="";
for(int i=0;i<i_count;i++){
while(rs.next()){
str_moc_id=rs.getString(1);
str_moc_contract_no=rs.getString(2);
d_tot_advance_payment_amt=rs.getDouble(3);
d_tot_cancellation_amt=rs.getDouble(4);
d_tot_remittance_amt=rs.getDouble(5);
str_countNo=rs.getString(6);
ViewRemittanceDOB viewRemittanceDOB=new ViewRemittanceDOB(str_moc_id,str_moc_contract_no,d_tot_advance_payment_amt,d_tot_cancellation_amt,d_tot_remittance_amt,str_countNo);
all_al_remittance.add(i,viewRemittanceDOB);
}
}
return all_al_remittance;
}catch(Exception e){
e.printStackTrace();
System.out.println("Exception caught in the method viewRemittance of ViewRemittanceDao-------------");
}
finally{
try{
if (ps!=null)rs.close();
if (ps!=null)ps.close();
if (con!=null)con.close();
}catch(SQLException sqe){sqe.printStackTrace();System.out.println("Exception caught in the method viewRemittance of ViewRemittanceDao of ps.close --------------------");}
}
return all_al_remittance;
}
public void saveAsFile(PrintWriter pwOut,String moc_id, String saveAsHtml,String year,String month,String day,String str_countNo){
if(moc_id!=null && saveAsHtml.equalsIgnoreCase("true")){
String str_date=year+month+day;
String str_moc_id="";
String str_moc_contract_no="";
String str_counting_no="";
String str_large_prcel_no="";
String str_shipping_dt="";
String str_data_confirmed_dt="";
String str_remittance_dt="";
String str_cacellation_dt="";
double d_cod_amt=0;
double d_service_chrg=0;
double d_cancellation_amt=0;
double d_advance_payment_amt=0;
String query1=null;
query1="SELECT JPS_MO_REMITTANCE_TRNS.MOC_ID,JPS_MO_REMITTANCE_TRNS.MOC_CONTRACT_NO,JPS_MO_REMITTANCE_TRNS.COUNTING_NO,JPS_APLUS_CONTROL.LARGE_PARCEL_NO,JPS_APLUS_CONTROL.SHIPPING_DT,JPS_APLUS_CONTROL.DATA_CONFIRMED_DT,JPS_MO_REMITTANCE_TRNS.REMITTANCE_DT,JPS_APLUS_CONTROL.CANCELLATION_DT,JPS_APLUS_CONTROL.COD_AMT,JPS_APLUS_CONTROL.SERVICE_CHRG,JPS_APLUS_CONTROL.CANCELLATION_AMT,JPS_APLUS_CONTROL.ADVANCE_PAYMENT_AMT FROM JPS_MO_REMITTANCE_TRNS,JPS_APLUS_CONTROL WHERE JPS_APLUS_CONTROL.COUNTING_NO=JPS_MO_REMITTANCE_TRNS.COUNTING_NO AND JPS_APLUS_CONTROL.MOC_ID=JPS_MO_REMITTANCE_TRNS.MOC_ID AND JPS_APLUS_CONTROL.COUNTING_NO='"+str_countNo+"' AND JPS_APLUS_CONTROL.MOC_ID='"+moc_id+"' ORDER BY JPS_APLUS_CONTROL.LARGE_PARCEL_NO";
System.out.println("save as csv query in ViewRemittanceDao:"+query1);
try{
con=db.dbConnection();
ps=con.prepareStatement(query1);
rs=ps.executeQuery();
PrintWriter output=pwOut;
output.println("MOC identification key,MOC no. for reference,Counting no,Large parcel number(Tracking key),Shipping date,Data confirmed date,Remittance date,Cancellation date,Amount of COD(Advance money amount),Service charge for COD.,Total amount of cancellation.,Total amount of remittance.");
while(rs.next()){
str_moc_id=initiliseToEmptyIfNull(rs.getString(1));
str_moc_contract_no=initiliseToEmptyIfNull(rs.getString(2));
str_counting_no=initiliseToEmptyIfNull(rs.getString(3));
str_large_prcel_no=initiliseToEmptyIfNull(rs.getString(4));
str_shipping_dt=getDateInFormat(rs.getDate(5));
str_data_confirmed_dt=getDateInFormat(rs.getDate(6));
str_remittance_dt=getDateInFormat(rs.getDate(7));
str_cacellation_dt=getDateInFormat(rs.getDate(8));
d_cod_amt=rs.getDouble(9);
d_service_chrg=rs.getDouble(10);
d_cancellation_amt=rs.getDouble(11);
d_advance_payment_amt=rs.getDouble(12);
output.println(str_moc_id+","+str_moc_contract_no+","+str_counting_no+","+str_large_prcel_no+","+str_shipping_dt+","+str_data_confirmed_dt+","+str_remittance_dt+","+str_cacellation_dt+","+d_cod_amt+","+d_service_chrg+","+d_cancellation_amt+","+d_advance_payment_amt);
}
output.flush();
}catch(Exception e){
e.printStackTrace();
System.out.println("------------Exception caught in the method saveAsHtml the DAO-------------");
}
finally{
try{
if (rs!=null)rs.close();
if (ps!=null)ps.close();
if (con!=null)con.close();
}catch(SQLException sqe){sqe.printStackTrace();System.out.println("Exception caught in method saveAsHtml of ViewRemittanceDao of ps.close --------------------");}
}
}//end of if
}//end of method saveAsHtml
public String getDateInFormat(Date date){
if(date!=null){
String strDate=date.toString();
String dateInFormat=strDate.substring(0,4)+"/"+strDate.substring(5,7)+"/"+strDate.substring(8,10);
return dateInFormat;
}else{
return "";
}
}
public String getFormattedBusinessDate(){
return this.strFormattedBusinessDate;
}
public boolean checkBusinessDate(String year,String month,String day) {
boolean returnValue=false;
try{
con=db.dbConnection();
ps=con.prepareStatement("SELECT BUSINESS_DT FROM JPS_BUSINESS_DATE");
rs=ps.executeQuery();
Date businessDate=null;
while(rs.next()){
businessDate=rs.getDate(1);
}
DateFormat df=new SimpleDateFormat("yyyy-MM-dd");
Date paramDate=df.parse(year+"-"+month+"-"+day);
int compareValue=businessDate.compareTo(paramDate);
if(compareValue >= 0){
returnValue =true;
}
else
{
returnValue=false;
}
}catch(Exception e){
e.printStackTrace();
System.out.println("------------Exception caught in the method saveAsHtml the DAO-------------");
}
finally{
try{if (rs!=null)rs.close();
if (ps!=null)ps.close();
if (con!=null)con.close();
}catch(SQLException sqe){sqe.printStackTrace();System.out.println("Exception caught in method saveAsHtml of ViewRemittanceDao of ps.close --------------------");}
}
return returnValue;
}
public String initiliseToEmptyIfNull(String value){
if(value==null){
value="";
}
return value;
}
}