H2 Database on Raspberry PI over Java

Im letzten Beitrag sind wir auf einen JAVA TCP Server umgestiegen. Ein Problem haben wir aber immer noch. Wenn wir unsere APP alleine verwenden stört uns der „letzte“ Zustand der eingeschalteten Geräte nicht. Wir wissen ja das wir das Licht über das Handy eingeschaltet haben. Was ist aber wenn nun zum Beispiel die Freundin auch auf diese Möglichkeit zugreifen möchte? Dann müssten wir den Zustand ‚AN‘ oder ‚AUS‘ (oder sonst was) zentral speichern und ggf. abrufen können. Wenn wir dann über das Handy das Licht einschalten und ein anderer unsere APP auf seinem Handy startet dann sieht er (zb. anhand des Buttons) das das Licht aus ist. Diesen Zustand werden wir in eine DB speichern und unserer JAVA Server wird sich um den Rest kümmern. Eine kleine Anpassung der APP muss auch vorgenommen werden. Der Beitrag ist nur für alle diejenigen zu 100% Verständlich, die den Beitrag zu dem Java Server gelesen haben und am besten auch die restlichen Beiträge zu der LIRC Application.

Inhaltsverzeichnis
(DB = Datenbank)

1. H2 Datenbank und die Tabelle erstellen
2. TCP Server anpassen
3. APP anpassen

H2 Datenbank

Ich habe lange nach einer passenden Datenbank im Internet gesucht. Für Python und dem Raspberry PI eignet sich hierfür besonders gut die MySQLLite Datenbank an. Die Treiber werden direkt mitgeliefert und Python besitzt passende Bibliotheken. Da wir aber momentan alles in Java entwickeln habe ich mich im Endeffekt für die H2 Datenbank entschieden. Die DB ist komplett JAVA basierend, schnell und vor allem Ressourcen sparend aufgebaut.
Also brauchen wir nur noch die Treiber. Bitte einfach nur die ZIP Datei herunterladen. Wir brauchen nur die passende Bibliothek (Ordner H2 -> BIN -> H2 – versionsnummer.jar). Nun können wir ein neues Java Projekt erstellen (zB. CreateDB). Dieses werden wir nur einmal für das Erstellen der Datenbank benutzten. Natürlich könnte man auch alles in einem Script verpacken. Schließlich wird glaube ich die DB sogar automatisch nach Bedarf erstellt sollte das Script auf eine nicht vorhandene zugreifen, aber wir wollen alles schön übersichtlich halten. Dann müssen wir noch die Jar Datei einbinden. Rechte Maustaste auf das Projekt -> Build Path -> Add External Archives -> Die Jar Datei auswählen. Die main Methode sieht am Ende so aus:

public class Main {
     public static void main(String[] args) throws 
                                            ClassNotFoundException, SQLException {
          
          Class.forName("org.h2.Driver");
 
       Connection conn = DriverManager.getConnection("jdbc:h2:~/RaspiH2;FILE_LOCK=NO");
          String stmt = "CREATE TABLE STATE (ID VARCHAR(255) PRIMARY KEY NOT NULL,
                              STATE INT NOT NULL)";
          PreparedStatement ps = conn.prepareStatement(stmt);
          ps.executeUpdate();
          conn.close();
     }
}

Als erstes binden wir die Klasse org.h2.Driver über die Klassenmethode an. Diese Methode kann uns eine Exception werfen deshalb das ‚throw‘ oben. Danach erstellen wir eine neue Verbindung über den DriverManager und den JDBC Treiber den wir über die angebundene Jar Datei bekommen. Die URL für die Datenbank lautet immer jdbc:h2:<Pfad zu der DB>. In unserem Fall befindet sich nach dem Ausführen des Scripts die DB in unserem Benutzerverzeichnis (unter Windows %HOMEPATH%). Die Datei lautet dann RaspiH2.mv.db. Der String beinhaltet den SQL Ausdruck. Hier wird eine neue Tabelle ‚STATE‘ erstellt. Das Feld ID (String) wird den Namen des Gerätes beinhalten und ist in diesem Fall unser primär Schlüssel. Das Feld STATE (int) beinhaltet den Status also 1 für an und 0 für aus. Natürlich könnt ihr die Datenbank und die Tabellen so nennen wie ihr wollt. Auch können die Typen der Felder ganz nach Bedarf ausgesucht werden. Ich habe mich für String und int entschieden. Danach wird der Ausdruck ausgeführt und die Verbindung zu der Datenbank geschlossen. Das war der erste Schritt. Nun passen wir den TCP Server an. Für den Parameter FILE_LOCK sehe hier.

TCP Server anpassen

Wir müssen mit unserem Server zwei Zustände abdecken. Zum einem muss sich der Datenbank Eintrag ändern wenn man aktiv eine Funktion ausführt. Das bedeutet ich klicke auf „Licht an“ -> DB Eintrag wird neu erstellt falls nicht vorhanden oder aktualisiert – Licht wird eingeschaltet. Der andere Zustand ist, ich rufe mir meine Übersicht auf und will wissen welche Geräte an und welche aus sind. Hier werden die Daten aus der DB an den Client übertragen. Fangen wir zuerst mit dem ersten Zustand an. Hierfür müssen wir den String der vom Client an den Server geschickt wird entsprechend erweitern. Wie im dem Beitrag zu den JAVA Server bereits erwähnt haben wir ein Erkennungsstring und ein Endstring im losgeschicktem Datensatz. Ein Beispiel für „Licht aus“ würde in meinem Fall so lauten:

FUN#11111 1 0#END

Nun müssen wir den String erweitern. Wir einigen uns auf das Zeichen # als allgemeinen „Trenner“ der Informationen die wir übertragen wollen. Für unsere Datenbank brauchen wir zwei zusätzliche Infos:

FUN#11111 1 0#NAS#0#END

Wenn wir nun den Datensatz nach dem Zeichen # splitten (Erkennungsstring und Endstring ausgenommen), erhalten wir folgende Informationen:

[0] = Der zu ausführende Befehl 11111 1 0
[1] = ID Feld in der Datenbank hier 'NAS'
[2] = STATE also der Zustand der NAS hier 0

Mit diesem Ansatz können wir nun unsere executeCommand Methode aus dem Java Server erweitern. Zuerst müssen wir den String ‚command_data‘ als Array abspeichern und alle Datensätze müssen entsprechend auf Index 0 des Array zugreifen. Im Endeffekt also:

Alt: String command_data = data.substring(4).replace(sEnd,"");
Neu: String[] command_data = data.substring(4).replace(sEnd,"").split("#");

Alt: ...command_data...
Neu: ...command_data[0]...

Das waren erstmal die Änderungen die uns die Möglichkeit geben die neue Übertragungsart zu benutzten. Nun können wir uns um die Datenbank kümmern. Da ich in diesem Beispiel die Funk – Anfragen mit der DB verknüpfe werde ich nur die Case Anweisung #FUN modifizieren:

case "FUN#":
     System.out.println("Funk:" + command_data[0] + " -Datenbank ID:"
          + command_data[1] + " State:" + command_data[2]);
     try {
          Runtime.getRuntime().exec("sudo /home/pi/rcswitch-pi/send " + 
               command_data[0]);
     } catch (IOException e) {
          response = e.toString();
     }
     try {
           DBConn db = new DBConn();
 
           response = db.DBUpdate(command_data[1],Integer.parseInt(command_data[2]));
           db.DBClose();
      } catch (ClassNotFoundException | SQLException e) {
           e.printStackTrace();
      }
break;

Der erste try catch Block hat sich nicht verändert bis auf command_data[0]. Was ist aber neu dazu gekommen? Zuerst wurde ein neues Objekt von der Klasse DBConn erzeugt. Diese Klasse werden wir gleich erstellen. Danach wird in den response String der Datensatz von der Methode DBUpdate gespeichert. Dieser Methode übergeben wir als Parameter die ID und STATE (als int). Danach wird die Methode DBClose() aufgerufen. Wie sieht nun die Klasse DBConn aus? Lass uns anfangen sie zu kreieren. Zuerst brauchen wir drei private Strings, ein Objekt vom Typ Connection , eins vom Typ PreparedStatement und eins vom Typ ResultSet. Also:

public class DBConn {
     private String DBUrl = "jdbc:h2://home/pi/java_bobek/RaspiH2";
     private String table= "STATE";
     private String stmt = null;
     private Connection conn = null;
     private PreparedStatement ps = null;
     private ResultSet rs = null;

Der String DBUrl verweist auf unsere DB. Ihr müsst den Pfad natürlich anpassen. Vorsicht! Wenn der JAVA Server bei euch als Dienst läuft wird er unter sudo ausgeführt. Deshalb ist es notwendig den ganzen Pfad einzugeben falls die DB wie bei mir unter anderem Benutzer zu finden ist. Der String table speichert einfach nur den Tabellennamen. String stmt wird für die SQL Ausdrücke verwendet. Mit ‚conn‘ werden wir die Verbindung zu der DB herstellen. Das Objekt ‚ps‘ wird unsere SQL Ausdrücke verwalten. Die Ergebnisse der Datenbank werden im ResultSet gespeichert. Im meinem Fall habe ich das so gelöst das die Verbindung zu der DB direkt im Konsturktor der Klasse aufgebaut wird. Das bedeutet erstell ich ein neues Objekt der Klasse DBConn habe ich direkt eine bestehende Verbindung zu der DB und kann mit ihr weiter arbeiten. Also müsste das in unserem Beispiel so aussehen:

public DBConn() throws ClassNotFoundException, SQLException{
     Class.forName("org.h2.Driver");
     this.conn = DriverManager.getConnection(DBUrl);
}

Wie im obigen Beispiel, zuerst die Klasse „org.h2.Driver“ einbinden. Danach dem Objekt conn die Verbindung über den DriverManager zu der DB übergeben. Natürlich muss der JAVA Server auch über die nötigen Treiber verfügen. Wie man diese einbindet wurde bereits oben beschrieben. Die Exceptions sollen erst dann in Erscheinung treten wenn ich ein entsprechendes Objekt erstellen möchte deshalb das throws. Jetzt müssen wir vier Methoden erstellen. Zum einem wäre das die Methode „DBInsert“. Diese erstellt ein DB Eintrag falls dieser noch nicht vorhanden ist. Die Methode „DBUpdate“ aktualisiert den Datensatz in der Datenbank. Die Methode „DBSelect“ liest die Zustände aus.Am Ende bleibt noch „DBClose“ hier wird nur die Verbindung zu der DB geschlossen. Arbeiten wir uns vor:

public String DBInsert(String id, int state){
     String response = null;
     stmt = "INSERT INTO " + table + " (ID,STATE) " +
                 "VALUES (?,?)";
     try {
          ps = conn.prepareStatement(stmt);
          ps.setString(1,id);
          ps.setInt(2,state);
          ps.executeUpdate();
          ps.close();
          response = "Insert successful";
     } catch (SQLException e) {
          e.printStackTrace();
          response = e.toString();
     }
     return response; 
}

Der Methode übergibt man zwei Parameter (String id uns integer state). Der SQL Ausdruck wird im String stmt gespeichert. Der wird dem preparedStatement übergeben. Hier setzten wir die Werte ein. Klappt das Update wird als response „Insert successful“ zurückgeliefert. Klappt es nicht wird die Exception übergeben. Weiter gehts:

public String DBUpdate(String id, int state){
     String response = null;
     stmt = "UPDATE " + table + " SET STATE = " +
                 state + " WHERE ID = ?";
     try {
          ps = conn.prepareStatement(stmt);
          ps.setString(1,id);
          int update = ps.executeUpdate();
          ps.close();
          if (update == 0){
               response = DBInsert(id,state);
          }
     } catch (SQLException e) {
          e.printStackTrace();
          response = e.toString();
     }
     return response;
}

Der Methode übergibt man zwei Parameter (String id uns integer state). Danach folgt der SQL Ausdruck. Was ist wenn der ausgeführte SQL Ausdruck 0 zurückliefert. Das würde bedeuten, es wurde nichts aktualisiert, weil da kein entsprechender Datensatz vorhanden ist. Ist dies der Fall wird die Methode DBInsert aufgerufen. Am Ende wird response „returned“. Entweder mit ’null‘ also alles gut, mit der Antwort der Methode Insert oder mit einer Exception. Weiter gehts:

public String DBSelect(){
     String response = "";
     stmt = "SELECT * FROM " + table;
     try {
          ps = conn.prepareStatement(stmt);
          rs = ps.executeQuery();
          while (rs.next()){
               response += rs.getString("ID") + 
                    String.valueOf(rs.getInt("STATE")) + "#";
          }
          rs.close();
          ps.close();
     } catch (SQLException e) {
          e.printStackTrace();
          response = e.toString();
     }
     return response.replaceFirst("#$","");
}

Diese Methode wird genutzt um alle Datensätze aus der Tabelle ‚STATE‘ zu liefern. Also wie bereits erwähnt ’stmt‘ speichert den SQL Ausdruck. ResultSet speichert die Ergebnisse der Query. Die ‚while‘ Schleife läuft solange durch bis alle Ergebnisse dran waren. Der ‚response‘ String wird beim jedem Durchgang mit den Daten gefüllt.Der Wert ‚STATE‘ wird aus der DB als int rausgeholt und dann zum String umgewandelt. Zusätzlich wird nach jedem Durchgang ein # drangehängt. Ein Beispiel könnte so aussehen:

NAS0#LIGHTA1#LIGHTB0#

Am Ende der Methode wird das letzte # das wir hier nicht brauchen durch nichts ersetzt und ‚response‘ wird zurückgeliefert. Die letzte Methode:

public void DBClose(){
     try {
          conn.close();
     } catch (SQLException e) {
          e.printStackTrace();
     }
}

schließt die Verbindung zu der DB und sollte immer am Ende aufgerufen werden. Das war die ganze Klasse. Am Ende sieht alles so aus:

public class DBConn {
     private String DBUrl = "jdbc:h2://home/pi/java_bobek/RaspiH2";
     private String table= "STATE";
     private String stmt = null;
     private Connection conn = null;
     private PreparedStatement ps = null;
     private ResultSet rs = null;
     
     public DBConn() throws ClassNotFoundException, SQLException{
          Class.forName("org.h2.Driver");
          this.conn = DriverManager.getConnection(DBUrl);
     }
     
     public String DBInsert(String id, int state){
          String response = null;
          stmt = "INSERT INTO " + table + " (ID,STATE) " +
                      "VALUES (?,?)";
          try {
               ps = conn.prepareStatement(stmt);
               ps.setString(1,id);
               ps.setInt(2,state);
               ps.executeUpdate();
               ps.close();
               response = "Insert successful";
          } catch (SQLException e) {
               e.printStackTrace();
               response = e.toString();
          }
          return response; 
     }
     public String DBUpdate(String id, int state){
          String response = null;
          stmt = "UPDATE " + table + " SET STATE = " +
                 state + " WHERE ID = ?";
          try {
                ps = conn.prepareStatement(stmt);
                ps.setString(1,id);
                int update = ps.executeUpdate();
                ps.close();
                if (update == 0){
                     response = DBInsert(id,state);
                }
          } catch (SQLException e) {
               e.printStackTrace();
               response = e.toString();
          }
          return response;
     }
     
     public String DBSelect(){
          String response = "";
          stmt = "SELECT * FROM " + table;
          try {
               ps = conn.prepareStatement(stmt);
               rs = ps.executeQuery();
               while (rs.next()){
                    response += rs.getString("ID") + 
                         String.valueOf(rs.getInt("STATE")) + "#";
               }
               rs.close();
               ps.close();
          } catch (SQLException e) {
               e.printStackTrace();
               response = e.toString();
          }
          return response.replaceFirst("#$","");
     }
     
     public void DBClose(){
          try {
               conn.close();
          } catch (SQLException e) {
               e.printStackTrace();
          }
     }
} 

Soooo…..mit diesem Wissen müssen wir noch die ExecuteCommand Methode um eine weitere Case Anweisung erweitern. Bei mir habe ich diese DBS (Datenbank Select Anweisung) genannt.Schauen wir uns das einmal an:

case "DBS#":
     System.out.println("Datenbank Select:" + command_data[0]);
     if (command_data[0].contains("STATE")){
          try {
               DBConn db = new DBConn();
               response = db.DBSelect();
               db.DBClose();
          } catch (ClassNotFoundException | SQLException e) {
               e.printStackTrace();
          }
     }
break;

Wenn von einem Client zum Beispiel diese Anfrage kommen sollte:

DBS#STATE#END

Dann bedeutet das für den Server – Der Client braucht die Datensätze aus der Tabelle STATE. Dieses Verhalten wird hier dargestellt. Es wird ein Objekt der Klasse DBConn erstellt. Die Methode DBSelect liefert uns alle Werte aus der DB in den String ‚response‘ zurück. Wie so ein Datensatz aussehen würde habe ich oben bereits gezeigt. Das war jetzt alles was wir an unserem Server anpassen mussten. Natürlich könnte man die Klasse weiter um andere Tabellen und Methoden erweitern. Auch die DBS Case Anweisung ist variabel und kann immer wieder erweitert werden. Wie greifen wir aber nun von unserem Smartphone auf die Daten zu?

APP anpassen

Bei der APP starten wir zuerst mit dem zweiten Zustand. Das heißt wie rufe ich alle Daten ab? Es gibt die Möglichkeit dies direkt beim Start der APP zu machen oder erst dann wenn eine Activity aufgerufen wird die diese Daten benötigt. So oder so die Lösung bleibt gleich. Wir schauen uns aber nochmal an wie so ein String aussehen könnte welcher der Server uns zuschickt:

NAS0#LIGHTA1#LIGHTB0

Wenn du alle Beiträge zu der LIRC App gelesen hast und diese auf deine Weise umgesetzt hast, dann verfügst du über eine Klasse die auf die Superklasse AsyncTask zurückgreift (Bei mir heißt die Klasse ExecuteCommand). AsyncTask besitzt die Methode execute() die entsprechende Daten an den Server schickt. Zusätzlich kann man mit der Methode get() den Main Thread der Applikation so lange anhalten bis die Datensätze am Client vollständig angekommen sind. Und diese Vorgehensweise werden wir hier einsetzten. So sieht die Verarbeitung des obigen Beispiel Strings in unserer APP aus:

...
private boolean nas_status, lighta_status, lightb_status = false;
private Button btn_nas,btn_lighta,btn_lightb;
...
btn_nas = (Button)findViewById(...);
btn_lighta = (Button)findViewById(...);
btn_lightb = (Button)findViewById(...);
...
btn_nas.setOnClickListener(this);
btn_lighta.setOnClickListener(this);
btn_lightb.setOnClickListener(this);
...
try {
     String[] aState = new ExecuteCommand().execute(DBS#STATE).get().split("#");
     for (int i = 0;i < aState.length;i++){ 
          if (aState[i].contains("NAS"){
               if (aState[i].substring(aState[i].length() -1).contains("1")){
                    nas_status = true;
                    btn_nas.setBackgroundResource(R.drawable.switch_on);
                    continue;
               }
          }
          if (aState[i].contains("LIGHTA"){
               if (aState[i].substring(aState[i].length() -1).contains("1")){
                    lighta_status = true;
                    btn_lighta.setBackgroundResource(R.drawable.switch_on);
                    continue;
               }
          }
          if (aState[i].contains("LIGHTB"){
               if (aState[i].substring(aState[i].length() -1).contains("1")){
                    lightb_status = true;
                    btn_lightb.setBackgroundResource(R.drawable.switch_on);
                    continue;
               }
          }
     }
} catch (InterruptedException | ExecutionException e) {
      e.printStackTrace();
}

Ich fange direkt ab der try Anweisung an. Die Sachen oben dienen nur der Übersicht und sollten für jeden Verständlich sein (außer eventuell die boolean Variablen dazu später mehr). Also wie bereits erwähnt holen wir uns den Beispiel String über die Methode Execute vom Server ab. Diese Daten werden direkt über das # Zeichen gesplittet und landen in dem aState Array. Danach folgt eine for Schleife die alle Elemente durchgeht. Wird in einem Element ein entsprechender Begriff gefunden (NAS , LIGHTA oder LIGHTB) so wird das letzte Zeichen auf den Wert „1“ überprüft. Ist dies der Fall wird die entsprechende boolean Variable auf „True“ gesetzt und dem Button wird das entsprechende Background zugewiesen. In unserem Beispiel würde nur LIGHTA verändert. Man muss auch drauf achten das wenn man Buttons mit Hintergrundbild verwendet dieses bei der Erstellung der Activity auf „AUS“ setzt. Das heißt NAS und LIGHTB Buttons hätten beim laden der Activity den Hintergrund „AUS“ gesetzt. LIGHTA zwar auch aber nur bis wir dies über die Methode „setBackgroundResources“ ändern würden. Doch wozu brauchen wir die boolean Variablen? Nun die werden wir einsetzten wenn wir den Button anklicken und den Zustand in der Datenbank aktualisieren. Also:

@Override
public void onClick(View v) {
     switch (v.getId()){
     case R.id.btn_nas:
          if (!nas_status){
               new ExecuteCommand().execute("FUN#11111 1 1#NAS#1");
               btn_nas.setBackgroundResource(R.drawable.switch_on);
               nas_status = true;
          }else{
               new ExecuteCommand().execute("FUN#11111 1 0#NAS#0");
               btn_nas.setBackgroundResource(R.drawable.switch_off);
               nas_status = false;
          }
     break;
     }
}

Ich habe jetzt nur den Beispiel für den Zustand der NAS genommen. Die restlichen Beispiele unterscheiden sich nur durch die Namen der Variablen. Also wenn der Button für die NAS gedrückt wird und die boolean Variable „false“ ist (also die NAS ist aus) wird der Befehl „AN“ an den Server geschickt (zusammen mit den Daten für die DB) die NAS wird eingeschaltet, die DB wird aktualisiert, der Hintergrund des Buttons ändert sich und die boolean Variable wird auf true gesetzt. War die NAS bereits an und der Button wird gedrückt dann erfolgt im Grunde genommen das gleiche nur umgekehrt. Der Befehl für das Ausschalten der NAS wird an den Server geschickt, der Hintergrund wird auch geändert und die boolean Variable wird auf false gesetzt.Nun das wars jetzt aber auch wir sind am Ende. Ich hoffe das alles war einigermaßen verständlich und ihr könnt etwas davon gebrauchen.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.