[SunaiSelva]

I've a problem to execute following code if conditions used for check the fields with sql server database. middle if condition not works properly. In this following method if(!dealerName... code not function properly for both place in this code.If more date will get for processing it run undefined loop of following

System.out.println("\n\n\n query for primary sales----------->"+query);

resu=stmt.executeQuery(query);
ArrayList tempMainList=new ArrayList();
rs=resu.getMetaData();
System.out.println("\n\n\n Column Count :"+ rs.getColumnCount());

result for this only run indefinite lop not end ...

 public ArrayList getSalesReport(String pName,String dealerName,String repName,String fDate,String tDate)
		   {

			   
				  ArrayList dateList1=new ArrayList();
				  ArrayList mainList=new ArrayList();
				  ArrayList colNameList=new ArrayList();
				  ArrayList colNameList1=new ArrayList();
				  ArrayList returnList=new ArrayList();
				  int count=0, i=0;
				  int rowCount=0;

				 try
				 {

					conn=DBConnection.getJNDIConnection();
					stmt=conn.createStatement();

*/


				
		//Secondary Sales :

					
					query="SELECT DISTINCT DATE_SALES FROM SALES_ENTRY_DETAILS WHERE CONVERT(date,DATE_SALES,105) IN ";
					query+=" (select * from ExplodeDates(convert(date,'" + fDate + "',105),CONVERT(date,'" + tDate + "',105)))";
				
					query+="  ORDER BY DATE_SALES";





					System.out.println("\n\n\n date selection query for Secondary Sales----------->\n"+query);
					
					resu=stmt.executeQuery(query);
					while (resu.next())
					{
						System.out.println("\n ~~~~");
						dateList1.add((String)resu.getString(1));
						System.out.println("\n\n dateList1 for Secondary Sales : "+ resu.getString(1));
					  
					}

					System.out.println("\n\n All dateList1=========>"+dateList1);

						Collections.sort(dateList1);

					System.out.println("\n\n Date List after Sorted : "+ dateList1);

					query="SELECT COUNT(*) FROM SALES_ENTRY_DETAILS";

					resu=stmt.executeQuery(query);
					if (resu.next())
					{
						
						count=resu.getInt(1);
					  
					}

								System.out.println("\n count=========>\n"+count);
												
							
					
							
					if(count>0)
					{
							System.out.println("\n\n\n No of date containing dateList1 ArrayList : \n"+ dateList1.size());
			
					  for( i=0;i<dateList1.size();i++)
					  {
						
						query="SELECT  DATE_SALES  \" DATE \"  ,DEALER_NAME  \" DEALER \", REPRESENTATIVE_NAME  \"  REP \", PRODUCT_NAME  \"  PRODUCT \"";
						query+=",OPENING_STOCK  \" OPENING \"  , PRIMARY_SALES \"PRIMARY\" , PRI_SALES_AMT  \" PRI_AMT \", SECONDARY_SALES \" SECONDARY  \" ";
						query+=" ,SEC_SALES_AMT \"SEC AMT  \" , CLOSING_STOCK  \" CLOSING \"   FROM SALES_ENTRY_DETAILS  WHERE CONVERT(DATE,DATE_SALES,105) = convert(date,'" + (String)dateList1.get(i) + "',105) "; 
						
						if(!pName.equals("ALL"))
					 {

							String[] pNameArray=pName.split(",");
							query+=" AND  MAAMIS.TRIM(UPPER(PRODUCT_NAME)) IN (";
							for( i=0;i<pNameArray.length;i++)
							{	
							System.out.println("the String array values are" +pNameArray[i]);
							query+="'"+(pNameArray[i].toUpperCase()).trim()+"',";
							}
							query=query.substring(0,query.lastIndexOf(","));
							query+=")";


					 }

						if(!dealerName.equals("ALL"))
					 {

							String[] dealerNameArray=dealerName.split(",");
							query+=" AND  MAAMIS.TRIM(UPPER(Dealer_Name)) IN (";
							for( i=0;i<dealerNameArray.length;i++)
							{	
							System.out.println("the String array values are : \n"+dealerNameArray[i]);
							query+="'"+(dealerNameArray[i].toUpperCase()).trim()+"',";
							}
							query=query.substring(0,query.lastIndexOf(","));
							query+=")";


					 }
						if(!repName.equals("ALL"))
					 {

							String[] repNameArray=repName.split(",");
							query+=" AND  MAAMIS.TRIM(UPPER(Representative_Name)) IN (";
							for(  i=0;i<repNameArray.length;i++)
							{	
							System.out.println("the String array values are : \n"+repNameArray[i]);
							query+="'"+(repNameArray[i].toUpperCase()).trim()+"',";
							}
							query=query.substring(0,query.lastIndexOf(","));
							query+=")";


					 }

		
						query+=" Order by Dealer_Name";




						System.out.println("\n\n query for secondary sales----------->"+query);

						resu=stmt.executeQuery(query);
						ArrayList tempMainList=new ArrayList();
						rs=resu.getMetaData();
							System.out.println("\n\n  Column Count :"+ rs.getColumnCount());
					
						if(i==0)
						{
						
							
							for(int col=1;col<=(rs.getColumnCount());col++)
							{
							System.out.println("\n\n\n\n Column Count : "+ col + " : \t  "+ rs.getColumnCount());
							System.out.println("\n\n\n COL NAME WITHIN FOR --> "+(String)rs.getColumnName(col));
							colNameList.add((String)rs.getColumnName(col));
							}

											
						}
						
						while (resu.next())
						{
						

							
							System.out.println("\n\n\n\n\n\n\n\n\n\n\n No of Column in the database \n"+ rs.getColumnCount());
							ArrayList tempList=new ArrayList();
							

							for(int t=1;t<=rs.getColumnCount();t++)
							{
							  System.out.println("\n\n\n\n\n t : "+ t);
							  String tempString=(String)resu.getString(t);
							  System.out.println("\n\n\n\n\n String of resultset \n" + tempString);
							if(!resu.getString(t).equals("")){
							
								tempList.add(tempString);


							}
							
							else 
							   tempList.add("0");
							}
							System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n\n tempList for secondary sales----------->"+tempList);

							tempMainList.add(tempList);
							System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n tempMainList for secondary sales within while----------->"+tempMainList);
							


						}
						

						System.out.println("tempMainList for secondary sales----------->"+tempMainList);

						for(int temp=0;temp<tempMainList.size();temp++)
						{
						   

							ArrayList subList=new ArrayList();
							subList=(ArrayList)tempMainList.get(temp);
							System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n ~~~~~~~~~~\nsubList : " + subList);
							System.out.println("\n\n\n\n\n Size for subList : --------->"+subList.size());

						

							
								System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n ArrayList subList size : "+ subList.size());
								System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n ArrayList subList  : "+ subList);		
								

								System.out.println("\n\n\n\n\n\n Main List :  "+ mainList);
							for(int s=0;s<(subList.size());s++)
							{

							 if(s>3)
								mainList.add((String)nf.format(Double.parseDouble((String)subList.get(s))));
													
							 else
								mainList.add((String)subList.get(s));
								System.out.println("\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n ArrayList mainList  : "+ mainList);
								
								//mainList.add((String)subList.get(s));
							
							}
							
								

		//					mainList.add(subList);

							}
				  
					  }
					
				/*	} catch(Exception e){

		System.out.println("Exception : " + e);
		}*/
			//Primary Sales :
			
					

			   ArrayList dateList=new ArrayList();
			   ArrayList mainList1=new ArrayList();
			   
					query="SELECT DISTINCT DATE_T FROM SALES_DETAILS WHERE CONVERT(date,DATE_T,105) IN ";
					query+="(select * from ExplodeDates(convert(date,'" + fDate + "',105),CONVERT(date,'" + tDate + "',105)))";
					
								
					query+="  ORDER BY DATE_T ";




					System.out.println("\n\n ~~~~~~~~~~~\n\n\n date selection query for Primary Sales----------->\n"+query);
			  
					resu=stmt.executeQuery(query);
					
					while (resu.next())
					{
						System.out.println("\n\n\n\n\n\n\n\n\n\n ~~~~~~~~~");
						dateList.add((String)resu.getString(1));
						System.out.println("\n\n\n dateList for Primary Sales : "+ dateList);
					  
					}
					
					
					
					System.out.println("\n\n\n dateList=========>"+dateList);
					
					query="SELECT COUNT(*) FROM SALES_DETAILS";

					resu=stmt.executeQuery(query);
					if (resu.next())
					{
						
						count=resu.getInt(1);
					  
					}

								System.out.println("\n  count=========>\n"+count);

					if(count>0)
					{
							System.out.println("\n\n\n\n\n\n\n\n No of date containing dateList ArrayList : \n"+ dateList.size());
					 
					  for(i=0;i<dateList.size();i++)
					  {

				
						query="SELECT  DATE_T \" DATE \", ACCOUNT \" DEALER \", '-'  \" REP \", PRODUCT  \" PRODUCT \"";
						query+=",'0' \" OPENING \" ,QUANTITY  \"  PRIMARY \", REPLACE(GROSS_AMOUNT,',','') \" PRI_AMT \" , '0'";
						query+=" \" SECONDARY \", '0' \"SEC_AMT\"  , QUANTITY  \" CLOSING \"  FROM SALES_DETAILS";
					query+=" WHERE CONVERT(DATE,DATE_T,105)= CONVERT(DATE,'" + (String)dateList.get(i) + "',105)";

					
					if(!pName.equals("ALL"))
					 {

							String[] pNameArray=pName.split(",");
							query+=" AND  MAAMIS.TRIM(UPPER(PRODUCT)) IN (";
							for(i=0;i<pNameArray.length;i++)
							{	
							System.out.println("the String array values are : \n"+pNameArray[i]);
							query+="'"+(pNameArray[i].toUpperCase()).trim()+"',";
							}
							query=query.substring(0,query.lastIndexOf(","));
							query+=")";


					 }

					if(!dealerName.equals("ALL"))
					 {

							String[] dealerNameArray=dealerName.split(",");
							query+=" AND  MAAMIS.TRIM(UPPER(ACCOUNT)) IN (";
							for(i=0;i<dealerNameArray.length;i++)
							{	
							System.out.println("the String array values are : \n"+dealerNameArray[i]);
							query+="'"+(dealerNameArray[i].toUpperCase()).trim()+"',";
							}
							query=query.substring(0,query.lastIndexOf(","));
							query+=")";


					 }

					query+="order by Account";
						System.out.println("\n\n\n query for primary sales----------->"+query);
		
						resu=stmt.executeQuery(query);
						ArrayList tempMainList=new ArrayList();
						rs=resu.getMetaData();
							System.out.println("\n\n\n Column Count :"+ rs.getColumnCount());
										
							if(i==0)
						{
						
							
							for(int col=1;col<=(rs.getColumnCount());col++)
							{
							System.out.println("\n\n\n\n Column Count : "+ col + " : \t  "+ rs.getColumnCount());
							System.out.println("\n\n\n COL NAME WITHIN FOR --> "+(String)rs.getColumnName(col));
							colNameList1.add((String)rs.getColumnName(col));
							System.out.println("\n\n ColName List  :"  +colNameList1 );
							}

											
						}
						try{
						while (resu.next())
						{
						
						
							
							System.out.println("\n\n\n No of Column in the database : \n"+ rs.getColumnCount());
							ArrayList tempList=new ArrayList();
							

							for(int t=1;t<rs.getColumnCount();t++)
							{
							 
				    		  System.out.println("\n\n t : "+ t);
							  String tempString=(String)resu.getString(t);
							  System.out.println("\n\n String of resultset : \n" + tempString);
							if(!resu.getString(t).equals("")){
							 if(t>4)
							 {      
								
									if(t==(rs.getColumnCount()))
									{
								
									  Double priSales=Double.parseDouble(tempString);
									  t=t+1;
									  String temp1=(String)resu.getString(t);
									  System.out.println("\n\n temp1 : "+ temp1);		
									  if(temp1.equals("-"))
										temp1="0";
									 
										  System.out.println("\n\n tempList : "+ tempList);								
									
									 
									}
									else
									{
										tempList.add((String)nf.format(Double.parseDouble(tempString)));
									}

								}
							 else
								tempList.add(tempString);


							}
							
							else 
							   tempList.add("0");
							}
							System.out.println("\n\n\n\n\n\n tempList for Primary sales----------->"+tempList);

							tempMainList.add(tempList);
							System.out.println("\n\n\n\n\n\n tempMainList for Primary sales within while----------->"+tempMainList);
					



						}
						}
						catch(Exception ll){
						
						System.out.println("\n\n\n\n Exception Occurs :  " + ll);
						}

							System.out.println("tempMainList for primary sales----------->"+tempMainList);

						for(int temp=0;temp<tempMainList.size();temp++)
						{
						   

							ArrayList subList=new ArrayList();
							subList=(ArrayList)tempMainList.get(temp);
						

							
								System.out.println("\n\n\n\n ArrayList subList size : "+ subList.size());
								System.out.println("\n\n\n\n ArrayList subList  : "+ subList);		
								
						   
								
							for(int s=0;s<(subList.size());s++)
							{

							 if(s>3)
								mainList1.add((String)nf.format(Double.parseDouble((String)subList.get(s))));
													
							 else
								mainList1.add((String)subList.get(s));
								System.out.println("\n\n\n\n ArrayList mainList  : "+ mainList1);
								
								//mainList.add((String)subList.get(s));
							
							}
							
							mainList1.add((String)subList.get(5));

		//					mainList.add(subList);

							}
				  
					  }
			/*		  } catch(Exception e){

		System.out.println("Exception : " + e);
		}*/
					
				
					System.out.println("colNameList1  ----------->"+colNameList1);
					
					System.out.println("colNameList------------->"+colNameList);
					System.out.println("mainList------------->"+mainList);
					System.out.println("mainList1------------->"+mainList1);


		}
					returnList.add(0,colNameList);
					System.out.println(" Report List : "+ returnList);
					returnList.add(1,mainList);
					System.out.println(" Report List : "+ returnList);
					returnList.add(2,colNameList1);
					System.out.println(" Report List : "+ returnList);
					returnList.add(3,mainList1);
					System.out.println(" Report List : "+ returnList);
					
					stmt.close();
					conn.close();
				
		}
			 
			 }
			 
				catch(Exception e)
				 {
					System.out.println("Exception during insertion  :  " +e);
					
					
					}
				return returnList;
			}			
			  
		   }
		   

[g00se]

I don't really see how you can be getting an infinite loop there. Can you please attach the debug trace - at least before you kill the looping?

[datakeyword]

I think your SQL computation is too complicated.
You may try esProc, a free tools to do mass data computation, and easily invoked by java. Read below link may some help.
Improve the Computation and Analysis Ability in Java

[macosxnerd101]

Also, remember that Java Collections like ArrayLists are generic, and should be declared with the appropriate generics. You will get a deprecation warning for use of non-generic Collections.