FOR - Schleifen Ignoranz

thinwath · 22. August 2007

Hallo Zusammen,

ich stehe hier vor einem Problem, was mich etwas grübeln lässt und ich nicht ganz nachvollziehen kann, warum und wieso dieses Problem entsteht. Sicherlich hat es noch mit meiner nicht ausreichenden Erfahrung in Sachen Java zu tun (3 Monate auf der FH - das wars).

Also es geht um ein kleines Programm, was Werte aus einer Excel Tabelle holt und diese per JDBC in eine MySQL DB schreibt. Das funktioniert bis auf eine Ausnahme ganz gut.

Das Programm ignoriert meine Schleifen. Ich habe jetzt jegliche Kontrollmechanismen ausprobiert (do - while, if, if-else if), irgendwie passiert aber nichts, das Programm läuft die ersten 2 Schleifen durch und beendet sich dann ohne jegliche Meldung.

Ich poste mal den Source hier und bin sicher, dass jener sicher eleganter bzw. das ganze Thema eleganter durchfürhbar wäre. =)

Code:

package de.host.exim;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.text.DateFormat;
import java.util.Properties;
import jxl.*;
import jxl.write.*;





public class Import {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		
		// Aktuelles Datum holen & formatieren
		Date heute = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
		String sql_heute  = format.format(heute);
		
		try {
			String url = "jdbc:mysql://127.0.0.1/exim_analyse";
			Properties conprops = new Properties();
			conprops.put("user", "root");
			conprops.put("password", "root");

			Class.forName("org.gjt.mm.mysql.Driver");
			Connection con = DriverManager.getConnection(url,conprops);
			Statement st = con.createStatement();

			
			Workbook workbook_mx3 = Workbook.getWorkbook(new File("\\H:"+sql_heute+"-exim_log_mx3.xls"));  
			Workbook workbook_mx4 = Workbook.getWorkbook(new File("\\H:"+sql_heute+"-exim_log_mx4.xls"));
			  
			  
			
			   	// Arbeitsblatt 1
		        Sheet sheet_mx3 = workbook_mx3.getSheet(0); 
		        Sheet sheet_mx4 = workbook_mx4.getSheet(0);
		        
		        // a1=spam, a2=ham, a3=all_bytes, a4=all, a5=spamall_bytes, a6=spamall
		        Cell a1 = sheet_mx3.getCell(2,9);
		        Cell a2 = sheet_mx3.getCell(2,10);
		        Cell a3 = sheet_mx3.getCell(1,16);
		        Cell a4 = sheet_mx3.getCell(2,16);
		        Cell a5 = sheet_mx3.getCell(1,17);
		       
		        Cell b1 = sheet_mx4.getCell(2,9);
		        Cell b2 = sheet_mx4.getCell(2,10);
		        Cell b3 = sheet_mx4.getCell(1,16);
		        Cell b4 = sheet_mx4.getCell(2,16);
		        Cell b5 = sheet_mx4.getCell(1,17);

		        String stringa1 = a1.getContents();
		        String stringa2 = a2.getContents();
		        String stringa3 = a3.getContents();
		        String stringa4 = a4.getContents();
		        String stringa5 = a5.getContents();
		        
		        String stringb1 = b1.getContents();
		        String stringb2 = b2.getContents();
		        String stringb3 = b3.getContents();
		        String stringb4 = b4.getContents();
		        String stringb5 = b5.getContents();
		       
		        String sql_a = "Insert into stats (received_date,complete,complete_bytes,spam,ham,spam_ham_bytes,d_relay) values ("+sql_heute+","+stringa4+","+stringa3+","+stringa1+","+stringa2+","+stringa5+",\"mx3\")";
		        st.execute(sql_a);
		        String sql_b = "Insert into stats (received_date,complete,complete_bytes,spam,ham,spam_ham_bytes,d_relay) values ("+sql_heute+","+stringb4+","+stringb3+","+stringb1+","+stringb2+","+stringb5+",\"mx4\")";
		        st.execute(sql_b);
		        
		        
		        // Delivery per hour
		        String stringbb[] = new String[45];
		        Cell bb[] = new Cell[45];
		       
		        int j = 0;
		        for(int i = 20; i <= 43; i++){
		        	bb[i] = sheet_mx3.getCell(1, i);
		        	stringbb[i] = bb[i].getContents();
		        	String sql = "Update stats set dph_"+j+"="+stringbb[i]+" where complete="+stringa4+" and received_date="+sql_heute+"";
		        	st.execute(sql);
		        	j++;
		        }
		        
		        j = 0;
		        for(int i = 20; i <= 43; i++){
		        	bb[i] = sheet_mx4.getCell(1, i);
		        	stringbb[i] = bb[i].getContents();
		        	String sql = "Update stats set dph_"+j+"="+stringbb[i]+" where complete="+stringb4+" and received_date="+sql_heute+"";
		        	st.execute(sql);
		        	j++;
		        }
		        
		     
		        
			        // Arbeitsblatt 2
			        Sheet sheet2_mx3 = workbook_mx3.getSheet(1);
			        Sheet sheet2_mx4 = workbook_mx4.getSheet(1);
			        
				       		       
			        String stringc[] = new String[50];
			        String stringc1[] = new String[50];
			        String stringc2[] = new String[50];
			        String stringd[] = new String[200];
			        String stringd1[] = new String[200];
			        String stringd2[] = new String[200];
			        Cell c [] = new Cell[50];
			        Cell c1 [] = new Cell[50];
			        Cell c2 [] = new Cell[50];
			        Cell d[] = new Cell[200];
			        Cell d1[] = new Cell[200];
			        Cell d2[] = new Cell[200];
			        int z = 1;
					int x = 1;	
					int q = 1;
					int w = 1;
				        
							// Suche nach Eintraganfang
					
		        			Cell s_host_mx3 = sheet2_mx3.findLabelCell("Top 50 sending hosts by message count");
		        			Cell r_dest_mx3 = sheet2_mx3.findLabelCell("Top 50 host destinations by message count");
		        			Cell s_host_mx4 = sheet2_mx4.findLabelCell("Top 50 sending hosts by message count");
		        			Cell r_dest_mx4 = sheet2_mx4.findLabelCell("Top 50 host destinations by message count");
		        			int s_host_row_mx3 = s_host_mx3.getRow();
		        			int r_dest_row_mx3 = r_dest_mx3.getRow();
		        			int s_host_row_mx4 = s_host_mx4.getRow();
		        			int r_dest_row_mx4 = r_dest_mx4.getRow();
		 		        			
		 		        				
		 		
		 	   	        	if(x == 1){
			 					for(int y = r_dest_row_mx3 +2; y <= r_dest_row_mx3 +51; y++){
			 					d[y] = sheet2_mx3.getCell(0, y);
					        	d1[y] = sheet2_mx3.getCell(2, y);
					        	d2[y] = sheet2_mx3.getCell(4, y);
					        	stringd[y] = d[y].getContents();
				       			stringd1[y] = d1[y].getContents();
				       			stringd2[y] = d2[y].getContents();
				       			String sql_r = "Insert into deliver (received_date,d_relay,d_all_count,d_ranking,d_type,d_total_bytes,d_ip) values ("
				       							+ sql_heute
				       							+ ",\"mx3\","
				       							+ stringd[y]
				       							+ ","
				       							+ x
				       							+ ",\"r\","
				       							+ stringd1[y]
				       							+ ",\"" + stringd2[y] + "\")";
				       			        	st.execute(sql_r);
				       			         	x++;
			 					}
			 					if(z == 1){
				 		        	for (int i = s_host_row_mx3 + 2; i <= s_host_row_mx3 + 51; i++) {
									c[i] = sheet2_mx3.getCell(0, i);
									c1[i] = sheet2_mx3.getCell(1, i);
									c2[i] = sheet2_mx3.getCell(3, i);
									stringc[i] = c[i].getContents();
									stringc1[i] = c1[i].getContents();
									stringc2[i] = c2[i].getContents();
									String sql_s = "Insert into deliver (received_date,d_relay,d_all_count,d_ranking,d_type,d_total_bytes,d_ip) values ("
											+ sql_heute
											+ ",\"mx3\","
											+ stringc[i]
											+ ","
											+ z
											+ ",\"s\","
											+ stringc1[i]
											+ ",\"" + stringc2[i] + "\")";
									st.execute(sql_s);
									z++;
									}
			 		        	}
			 					if(w == 1){
				 					for(int y = r_dest_row_mx4 +2; y <= r_dest_row_mx4 +51; y++){
				 					d[y] = sheet2_mx4.getCell(0,y);
						        	d1[y] = sheet2_mx4.getCell(2,y);
						        	d2[y] = sheet2_mx4.getCell(4,y);
						        	stringd[y] = d[y].getContents();
					       			stringd1[y] = d1[y].getContents();
					       			stringd2[y] = d2[y].getContents();
					       			String sql_r = "Insert into deliver (received_date,d_relay,d_all_count,d_ranking,d_type,d_total_bytes,d_ip) values ("
					       							+ sql_heute
					       							+ ",\"mx4\","
					       							+ stringd[y]
					       							+ ","
					       							+ x
					       							+ ",\"r\","
					       							+ stringd1[y]
					       							+ ",\"" + stringd2[y] + "\")";
					       			        	st.execute(sql_r);
					       			         	w++;
				 					}
			 	   	        	}
			 				  	if(q == 1){
				 		        	for (int i = s_host_row_mx4 + 2; i <= s_host_row_mx4 + 51; i++) {
									c[i] = sheet2_mx4.getCell(0, i);
									c1[i] = sheet2_mx4.getCell(1, i);
									c2[i] = sheet2_mx4.getCell(3, i);
									stringc[i] = c[i].getContents();
									stringc1[i] = c1[i].getContents();
									stringc2[i] = c2[i].getContents();
									String sql_s = "Insert into deliver (received_date,d_relay,d_all_count,d_ranking,d_type,d_total_bytes,d_ip) values ("
											+ sql_heute
											+ ",\"mx4\","
											+ stringc[i]
											+ ","
											+ z
											+ ",\"s\","
											+ stringc1[i]
											+ ",\"" + stringc2[i] + "\")";
									st.execute(sql_s);
									q++;
									}
			 		        	}
			 	   	        	
		 	   	        	}
		 	   	        	
		 		        	
						
		 		        	
		 		      
		 		
	
	        
		}catch (Exception e){
		//System.out.println(e.getMessage());
	}
		
		
		System.out.println("ENDE HIER!");
	
		
	}
}

Für einen kleinen Hinweis wäre ich dankbar.

Vielen Dank fürs lesen.

Grüße

torax13 · 22. August 2007

Ein paar Anmerkungen:

Tue NIE (fast nie

) Exception fangen ohne sie zu behandeln. Ich vermute mal, das irgendwo eine Exception geworfen wird und Du das nur einfach nicht siehst ..

Verwende PreparedStatements wann immer möglich. Deine Datenbank wird es Dir danken (SQL Parse Cache)
Schließe Resourcen die Du geöffnet hast. In Deinem Beispiel sind das zumindest Die Statemant und die Connection (in der Reihenfolge). Ein guter Platz dafür ist ein finally{} Block nach dem catch(). Auch wenn Du dafür die Objekte leider vor dem try{ deklarieren mußt:

Java:

Connsction con = null;
PreparedStatement pst = null;
try{
   con = ...
} catch (Exception e) {
   e.printStacktrace();
} finally{
    if(null != pst) {
        try{pst.close();} catch(Exception ex) {}
    }
    if(null != con) {
        try{con.close();} catch(Exception ex) {}
    }
}

thinwath · 22. August 2007

Hallo Torax,

erstmal Danke für deine Antwort. Er hat tatsächlich eine Exception ausgeworfen (mein erstes Array war zu klein) - Ich habe den Code jetzt soweit angepasst, habe jetzt aber das seltsame Phänomen, dass er die Schleife (ich meine die letzten 4 for schleifen) 1 und 3 auslässt und 2 und 4 abarbeitet. (jene mit der statischen "s" Übergabe)

Code:

package de.host.exim;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.text.DateFormat;
import java.util.Properties;
import jxl.*;
import jxl.write.*;





public class Import {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		
		// Aktuelles Datum holen & formatieren
		Date heute = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
		String sql_heute  = format.format(heute);
		Connection con = null;
		PreparedStatement pst = null;
		
		         
		
		try {
			String url = "jdbc:mysql://127.0.0.1/exim_analyse";
			Properties conprops = new Properties();
			conprops.put("user", "root");
			conprops.put("password", "root");

			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url,conprops);
			Statement st = con.createStatement();

			
			Workbook workbook_mx3 = Workbook.getWorkbook(new File("\\H:"+sql_heute+"-exim_log_mx3.xls"));  
			Workbook workbook_mx4 = Workbook.getWorkbook(new File("\\H:"+sql_heute+"-exim_log_mx4.xls"));
			  
			  
			
			   	// Arbeitsblatt 1
		        Sheet sheet_mx3 = workbook_mx3.getSheet(0); 
		        Sheet sheet_mx4 = workbook_mx4.getSheet(0);
		        
		        // a1=spam, a2=ham, a3=all_bytes, a4=all, a5=spamall_bytes, a6=spamall
		        Cell a1 = sheet_mx3.getCell(2,9);
		        Cell a2 = sheet_mx3.getCell(2,10);
		        Cell a3 = sheet_mx3.getCell(1,16);
		        Cell a4 = sheet_mx3.getCell(2,16);
		        Cell a5 = sheet_mx3.getCell(1,17);
		       
		        Cell b1 = sheet_mx4.getCell(2,9);
		        Cell b2 = sheet_mx4.getCell(2,10);
		        Cell b3 = sheet_mx4.getCell(1,16);
		        Cell b4 = sheet_mx4.getCell(2,16);
		        Cell b5 = sheet_mx4.getCell(1,17);

		        String stringa1 = a1.getContents();
		        String stringa2 = a2.getContents();
		        String stringa3 = a3.getContents();
		        String stringa4 = a4.getContents();
		        String stringa5 = a5.getContents();
		        
		        String stringb1 = b1.getContents();
		        String stringb2 = b2.getContents();
		        String stringb3 = b3.getContents();
		        String stringb4 = b4.getContents();
		        String stringb5 = b5.getContents();
		       
		        String sql_a = "Insert into stats (received_date,complete,complete_bytes,spam,ham,spam_ham_bytes,d_relay) values ("+sql_heute+","+stringa4+","+stringa3+","+stringa1+","+stringa2+","+stringa5+",\"mx3\")";
		        st.execute(sql_a);
		        String sql_b = "Insert into stats (received_date,complete,complete_bytes,spam,ham,spam_ham_bytes,d_relay) values ("+sql_heute+","+stringb4+","+stringb3+","+stringb1+","+stringb2+","+stringb5+",\"mx4\")";
		        st.execute(sql_b);
		        
		        
		        // Delivery per hour
		        String stringbb[] = new String[45];
		        Cell bb[] = new Cell[45];
		       
		        int j = 0;
		        for(int i = 20; i <= 43; i++){
		        	bb[i] = sheet_mx3.getCell(1, i);
		        	stringbb[i] = bb[i].getContents();
		        	String sql = "Update stats set dph_"+j+"="+stringbb[i]+" where complete="+stringa4+" and received_date="+sql_heute+"";
		        	st.execute(sql);
		        	j++;
		        }
		        
		        j = 0;
		        for(int i = 20; i <= 43; i++){
		        	bb[i] = sheet_mx4.getCell(1, i);
		        	stringbb[i] = bb[i].getContents();
		        	String sql = "Update stats set dph_"+j+"="+stringbb[i]+" where complete="+stringb4+" and received_date="+sql_heute+"";
		        	st.execute(sql);
		        	j++;
		        }
		        
		     
		        
			        // Arbeitsblatt 2
			        Sheet sheet2_mx3 = workbook_mx3.getSheet(1);
			        Sheet sheet2_mx4 = workbook_mx4.getSheet(1);
			        
				       		       
			        String stringc[] = new String[200];
			        String stringc1[] = new String[200];
			        String stringc2[] = new String[200];
			        String stringd[] = new String[200];
			        String stringd1[] = new String[200];
			        String stringd2[] = new String[200];
			        Cell c [] = new Cell[200];
			        Cell c1 [] = new Cell[200];
			        Cell c2 [] = new Cell[200];
			        Cell d[] = new Cell[200];
			        Cell d1[] = new Cell[200];
			        Cell d2[] = new Cell[200];
			        int z = 1;
					int x = 1;	
					int q = 1;
					int w = 1;
				        
							// Suche nach Eintraganfang
					
		        			Cell s_host_mx3 = sheet2_mx3.findLabelCell("Top 50 sending hosts by message count");
		        			Cell s_hostend_mx3 = sheet2_mx3.findLabelCell("Top 50 local senders by message count");
		        			Cell r_dest_mx3 = sheet2_mx3.findLabelCell("Top 50 host destinations by message count");
		        			Cell r_destend_mx3 = sheet2_mx3.findLabelCell("Top 50 local destinations by message count");
		        			
		        			Cell s_host_mx4 = sheet2_mx4.findLabelCell("Top 50 sending hosts by message count");
		        			Cell s_hostend_mx4 = sheet2_mx4.findLabelCell("Top 50 local senders by message count");
		        			Cell r_dest_mx4 = sheet2_mx4.findLabelCell("Top 50 host destinations by message count");
		        			Cell r_destend_mx4 = sheet2_mx4.findLabelCell("Top 50 local destinations by message count");
		        			
		        			int s_host_row_mx3 = s_host_mx3.getRow();
		        			int r_dest_row_mx3 = r_dest_mx3.getRow();
		        			int s_hostend_row_mx3 = s_hostend_mx3.getRow();
		        			int r_destend_row_mx3 = r_destend_mx3.getRow();
		        			
		        			int s_host_row_mx4 = s_host_mx4.getRow();
		        			int r_dest_row_mx4 = r_dest_mx4.getRow();
		        			int s_hostend_row_mx4 = s_hostend_mx4.getRow();
		        			int r_destend_row_mx4 = r_destend_mx4.getRow();
		 		        			
		        					 		
		 	   	        	
			 					if (x == 1) {
									for (int y = r_dest_row_mx3 + 2; y <= ((r_destend_row_mx3 - 2) - (r_dest_row_mx3 + 2)); y++) {
										d[y] = sheet2_mx3.getCell(0, y);
										d1[y] = sheet2_mx3.getCell(2, y);
										d2[y] = sheet2_mx3.getCell(4, y);
										stringd[y] = d[y].getContents();
										stringd1[y] = d1[y].getContents();
										stringd2[y] = d2[y].getContents();
										String sql_r = "Insert into deliver (received_date,d_relay,d_all_count,d_ranking,d_type,d_total_bytes,d_ip) values ("
												+ sql_heute
												+ ",\"mx3\","
												+ stringd[y]
												+ ","
												+ x
												+ ",\"r\","
												+ stringd1[y]
												+ ",\"" + stringd2[y] + "\")";
										st.execute(sql_r);
										x++;
									}
								}
			 					
				 		        	if (z == 1) {
										for (int i = s_host_row_mx3 + 2; i <= ((s_hostend_row_mx3 -2) - (s_host_row_mx3 + 2)); i++) {
											c[i] = sheet2_mx3.getCell(0, i);
											c1[i] = sheet2_mx3.getCell(1, i);
											c2[i] = sheet2_mx3.getCell(3, i);
											stringc[i] = c[i].getContents();
											stringc1[i] = c1[i].getContents();
											stringc2[i] = c2[i].getContents();
											String sql_s = "Insert into deliver (received_date,d_relay,d_all_count,d_ranking,d_type,d_total_bytes,d_ip) values ("
												+ sql_heute
												+ ",\"mx3\","
												+ stringc[i]
												          + ","
												          + z
												          + ",\"s\","
												          + stringc1[i]
												                     + ",\"" + stringc2[i] + "\")";
											st.execute(sql_s);
											z++;
										}
									}
			 		        	
			 					
			 				
			 					
				 					if (w == 1) {
										for (int y = r_dest_row_mx4 + 2; y <= ((r_destend_row_mx4 - 2) - (r_dest_row_mx4 + 2)); y++) {
											d[y] = sheet2_mx4.getCell(0, y);
											d1[y] = sheet2_mx4.getCell(2, y);
											d2[y] = sheet2_mx4.getCell(4, y);
											stringd[y] = d[y].getContents();
											stringd1[y] = d1[y].getContents();
											stringd2[y] = d2[y].getContents();
											String sql_r = "Insert into deliver (received_date,d_relay,d_all_count,d_ranking,d_type,d_total_bytes,d_ip) values ("
													+ sql_heute
													+ ",\"mx4\","
													+ stringd[y]
													+ ","
													+ w
													+ ",\"r\","
													+ stringd1[y]
													+ ",\""
													+ stringd2[y]
													+ "\")";
											st.execute(sql_r);
											w++;
										}
									}
			 	   	        	
			 				  	
				 		        	if (q == 1) {
										for (int i = s_host_row_mx4 + 2; i <= ((s_hostend_row_mx4 - 2) - (s_host_row_mx4 + 2)); i++) {
											c[i] = sheet2_mx4.getCell(0, i);
											c1[i] = sheet2_mx4.getCell(1, i);
											c2[i] = sheet2_mx4.getCell(3, i);
											stringc[i] = c[i].getContents();
											stringc1[i] = c1[i].getContents();
											stringc2[i] = c2[i].getContents();
											String sql_s = "Insert into deliver (received_date,d_relay,d_all_count,d_ranking,d_type,d_total_bytes,d_ip) values ("
													+ sql_heute
													+ ",\"mx4\","
													+ stringc[i]
													+ ","
													+ q
													+ ",\"s\","
													+ stringc1[i]
													+ ",\""
													+ stringc2[i]
													+ "\")";
											st.execute(sql_s);
											q++;
										}
									}
			        	
			        
		}catch (Exception e){
			e.printStackTrace();
			   
			      } finally{
			   if(null != pst) {
			      try{pst.close();} catch(Exception ex) {}
			  }
			  if(null != con) {
			   try{con.close();} catch(Exception ex) {}
			  }
			 } 
	
		
	}
}

Er wirft jetzt auch keine Exceptions mehr aus.

torax13 · 22. August 2007

Lass Dir doch einfach mal die Schleifenparameter ausgeben um zu sehen, was er da ausrechnet. Ich kann jetzt so nicht auf Anhieb nachvollziehen, was die Start - und Endwerte darstellen.

Gruß

thinwath · 22. August 2007

Es geht! Heruika!

Die Bedingungen in der For - Schleife bzw. die dort verwendete Variable war falsch.
Ausserdem mag die Schleife keine Bedingungen wie: x == y. Ist das richtig? Sollte doch normalerweise auch funktionieren oder?

Nichtsdetrotz, nochmal danke Torax ohne Dich hätte ich wohl etwas länger gegrübelt.

=)

Bedankt.

Grüße und schöneres Wetter

torax13 · 22. August 2007

Schön das es geht. For Schleifen werden solange abgearbeitet, wie die Bedingung true ist. Also auch ein x == y sollte gehen, hier wird halt die Schleife abgearbeitet, solange x 'gleich' y ist ..

Gruß Torax

FOR - Schleifen Ignoranz

thinwath

Grünschnabel

torax13

Erfahrenes Mitglied

thinwath

Grünschnabel

torax13

Erfahrenes Mitglied

thinwath

Grünschnabel

torax13

Erfahrenes Mitglied

Neue Beiträge