import java.io.*;
import java.sql.*;
import java.util.*;;

public class DBDataHandler  {
protected Connection con = null;
private static String sql;
private static PrintWriter out;
	
public DBDataHandler() {	
	try{
	Class.forName("org.postgresql.Driver");
        String URL = "jdbc:postgresql://ws-eg-002.dcs.fh-heidelberg.de/savio";
        con = DriverManager.getConnection(URL, "savio", "asdfgh"); 
		}
	catch (SQLException se) {System.out.println(se); }
	catch (Exception e)     {e.printStackTrace(); }
	}

public void makeXML(String sql, PrintWriter out, String myURL)throws IOException {
System.out.println("\nIn the makeXML method of DBDataGenerator");
this.sql = sql;	 // The Query to the database
	
//To write to an XML File
FileWriter file = new FileWriter(myURL);
out = new PrintWriter(file);
this.out = out;
	try{
	executeSQL(sql, out);	
		} catch (SQLException se) {
		System.out.println(se);
	 	} 
}

public void executeSQL(String sql, PrintWriter out)throws SQLException
	{
		Statement stmt = con.createStatement();
		System.out.println("\nInside the connector and executing.. \n" + sql);
		stmt.executeQuery(sql);
		ResultSet rs2 = stmt.getResultSet();
		out.println("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>");
		out.println("<root>");
		
		//For all records
		try{
		if(rs2 != null){
		while(rs2.next()){
		System.out.println("\n This is in the while loop " + rs2.getString("immat"));
			out.println("<student>");
			out.println("<immat>"      + rs2.getString("immat")   + "</immat>");
			out.println("<firstname>"  + rs2.getString("name")    + "</firstname>");	
			out.println("<city>"       + rs2.getString("city")    + "</city>");
			out.println("<zip>"        + rs2.getString("zip")     + "</zip>");
			out.println("<country>"    + rs2.getString("country") + "</country>");
			out.println("<email>"      + rs2.getString("email")   + "</email>");
			out.println("</student>");
			}	
		    }
		} catch (SQLException se) {
			System.out.println(se); 
			} 
		
		// Close the XML File
		out.println("</root>");
		out.close();		
	}
	
} //end of class