Add saving and loading to/from database

1 files changed, 132 insertions, 0 deletions

diff --git a/src/de/fhswf/in/inf/se/projektthemenvergabe/Main.java b/src/de/fhswf/in/inf/se/projektthemenvergabe/Main.java
index 9e9d8d3..35b0f8a 100644
--- a/src/de/fhswf/in/inf/se/projektthemenvergabe/Main.java
+++ b/src/de/fhswf/in/inf/se/projektthemenvergabe/Main.java
@@ -3,11 +3,20 @@
  */
 package de.fhswf.in.inf.se.projektthemenvergabe;
 
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.HashMap;
+
 import de.fhswf.in.inf.se.projektthemenvergabe.model.Ansprechpartner;
 import de.fhswf.in.inf.se.projektthemenvergabe.model.Organisation;
 import de.fhswf.in.inf.se.projektthemenvergabe.view.AnsprechpartnerListeController;
 import javafx.application.Application;
+import javafx.application.Platform;
 import javafx.collections.FXCollections;
+import javafx.collections.ListChangeListener;
 import javafx.collections.ObservableList;
 import javafx.fxml.FXMLLoader;
 import javafx.scene.Scene;
@@ -30,6 +39,8 @@ public class Main extends Application
 
    private Stage primaryStage;
 
+   private Connection dbConnection;
+
    /*
     * (non-Javadoc)
     * 
@@ -40,6 +51,19 @@ public class Main extends Application
    {
       this.primaryStage = primaryStage;
 
+      connectToDatabase();
+      loadData();
+
+      ansprechpartner.addListener((
+            ListChangeListener.Change<? extends Ansprechpartner> listener) -> {
+         saveData();
+      });
+
+      organisation.addListener(
+            (ListChangeListener.Change<? extends Organisation> listener) -> {
+               saveData();
+            });
+
       try
       {
          FXMLLoader loader = new FXMLLoader(
@@ -102,4 +126,112 @@ public class Main extends Application
       launch(args);
    }
 
+   /**
+    * TODO Add method comment here
+    *
+    */
+   private void connectToDatabase()
+   {
+      try
+      {
+         Class.forName("com.mysql.jdbc.Driver").newInstance();
+         dbConnection = DriverManager
+               .getConnection("jdbc:mysql://localhost/projekte", "root", "");
+      }
+      catch (ClassNotFoundException | InstantiationException
+            | IllegalAccessException | SQLException e)
+      {
+         e.printStackTrace();
+         Platform.exit();
+      }
+   }
+
+   /**
+    * TODO Add method comment here
+    *
+    */
+   private void loadData()
+   {
+      try
+      {
+         ansprechpartner.clear();
+         organisation.clear();
+
+         HashMap<String, Organisation> orga = new HashMap<>();
+
+         ResultSet rs = dbConnection.createStatement()
+               .executeQuery("SELECT * from organisation");
+         while (rs.next())
+         {
+            Organisation organisation = new Organisation();
+            organisation.setName(rs.getString("name"));
+
+            orga.put(organisation.getName(), organisation);
+         }
+
+         rs = dbConnection.createStatement()
+               .executeQuery("SELECT * from ansprechpartner");
+         while (rs.next())
+         {
+            Organisation organisation = orga
+                  .get(rs.getString("organisation"));
+            if (organisation != null)
+            {
+               Ansprechpartner ansprechpartner = new Ansprechpartner(
+                     organisation);
+               ansprechpartner.setVorname(rs.getString("vorname"));
+               ansprechpartner.setNachname(rs.getString("nachname"));
+
+               this.ansprechpartner.add(ansprechpartner);
+            }
+         }
+
+         organisation.addAll(orga.values());
+      }
+      catch (SQLException e)
+      {
+         e.printStackTrace();
+         Platform.exit();
+      }
+   }
+
+   private void saveData()
+   {
+      System.out.println("Ich wurde erreicht!");
+      try
+      {
+         dbConnection.createStatement()
+               .execute("SET FOREIGN_KEY_CHECKS = 0;");
+         dbConnection.createStatement()
+               .execute("TRUNCATE TABLE ansprechpartner");
+         dbConnection.createStatement()
+               .execute("TRUNCATE TABLE organisation");
+         dbConnection.createStatement()
+               .execute("SET FOREIGN_KEY_CHECKS = 1;");
+
+         PreparedStatement ps = dbConnection.prepareStatement(
+               "INSERT into organisation (name) VALUES (?)");
+         for (Organisation org : organisation)
+         {
+            ps.setString(1, org.getName());
+
+            ps.execute();
+         }
+
+         ps = dbConnection.prepareStatement(
+               "INSERT into ansprechpartner (vorname, nachname, organisation) VALUES (?, ?, ?)");
+         for (Ansprechpartner ans : ansprechpartner)
+         {
+            ps.setString(1, ans.getVorname());
+            ps.setString(2, ans.getNachname());
+            ps.setString(3, ans.getOrganisation().getName());
+
+            ps.execute();
+         }
+      }
+      catch (SQLException e)
+      {
+         e.printStackTrace();
+      }
+   }
 }