public class DBStudyCourseSerializer implements ISerializer<IStudyCourse> {

	String     driver     = null;

	  // --------------------------------------------------------------------------

	  String     host       = null;

	  String     database   = null;

	  String     port       = null;

	  String     user       = null;

	  String     password   = null;

	  Connection connection = null;


	  // --------------------------------------------------------------------------

	public DBStudyCourseSerializer(String jdbcDriverClass, String dbUrl,
          String user, String pw){

		driver = jdbcDriverClass;
		host = dbUrl;
		this.user = user;
		password=pw;

		loadJdbcDriver ();
	    openConnection ();

		try
	    {
	      Statement statement = connection.createStatement ();

	    try
	    { String str1 = "DROP TABLE studycourses;";
	      statement.executeUpdate (str1);
	    }
	    catch (SQLException e)
	    {
	    }

	      String str2 = "CREATE TABLE studycourses" +
	      		       "(id integer PRIMARY KEY," +
	      		       "type char(20)," +
	      		       "title char(20)," +
	      		       "lecturer integer," +
	      		       "room integer," +
	      		       "maxstuds integer," +
	      		       "description char(100)," +
	      		       "lecture integer);";
	      statement.executeUpdate (str2);

	      try{
	    	  String str3 = "DROP TABLE study_times";
		      statement.executeUpdate (str3);
	      }catch(SQLException e){
	      }
	      try{
	    	  String str4 = "CREATE TABLE study_times" +
 		       "(SC_ID integer," +
 		        "TS_ID integer," +
 		        "PRIMARY KEY (SC_ID, TS_ID));";
              statement.executeUpdate (str4);
	      }catch(SQLException e){
	      }
	      statement.close ();
	      close();
	    }catch(SQLException e){
	    	e.printStackTrace ();
	}
	}

	@Override
	public void close() {
		 try
		    {
		      connection.close ();
		    }
		    catch (SQLException e)
		    {
		      e.printStackTrace ();
		      System.exit (1);
		    }

		    System.out.println ("\nconnection closed");
	}

	@Override
	public void serialize(IStudyCourse study) {
		try
	    {
		  loadJdbcDriver ();
		  openConnection ();
	      int lec = -1;
	      String type=null;
	      String str=null;
		  Statement statement = connection.createStatement ();

	      if (study instanceof ILecture) {
	            type = "ILecture";
	        } else if (study instanceof ISeminar) {
	            type = "ISeminar";
	        } else if (study instanceof IExercise) {
	        	type = "IExercise";
	            lec=((IExercise)study).getLecture().getID();
	        }
	      if(lec==-1){
	      str = "INSERT INTO studycourses " +
	      		       "VALUES("+study.getID()+",'"+type+"','"+study.getTitle()+"',"+study.getLecturer().getID()+","+study.getRoom().getID()+
	      		       ","+study.getMaxStudents()+",'"+study.getDescription()+"',null);";
	      }else{
	    	  str = "INSERT INTO studycourses " +
 		       "VALUES("+study.getID()+",'"+type+"','"+study.getTitle()+"',"+study.getLecturer().getID()+","+study.getRoom().getID()+
 		       ","+study.getMaxStudents()+",'"+study.getDescription()+"',"+lec+");";
	      }
	      statement.executeUpdate (str);

	      statement.close ();
	    }
	    catch (SQLException e)
	    {
	    	e.printStackTrace ();
	    }
	    try
	    {
	     for(ITimeSlot ts : study.getTimeSlots()){
	      Statement statement = connection.createStatement ();
	      String str5 = "INSERT INTO study_times " +
	      		       "VALUES("+study.getID()+","+ts.getID()+");";

	      statement.executeUpdate (str5);

	      statement.close ();
	     }
	    }
	    catch (SQLException e)
	    {
	    }
	      close();
	}

	@Override
	public void serialize(List<IStudyCourse> studys) {

	      for(IStudyCourse tss : studys){
	    	  serialize(tss);
	      }
	    }

	  private void openConnection ()
	  {
	    try
	    {
	      connection = DriverManager.getConnection (host,
	                                                user,
	                                                password);
	    }
	    catch (SQLException e)
	    {
	      e.printStackTrace ();
	      System.exit (1);
	    }

	    System.out.println ("connection opened");
	  }

	  private void loadJdbcDriver ()
	  {
	    try
	    {
	      Class.forName (driver);
	    }
	    catch (ClassNotFoundException e)
	    {
	      e.printStackTrace ();
	      System.exit (1);
	    }

	    System.out.println ("driver loaded");
	  }


}