// Programm 4-14:  ./JDBCelementar/EinfacherClient.java
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;

public class EinfacherClient extends Frame {
  // GUI-Variable
  Label status = new Label("bereit ...", Label.LEFT);
  Panel ein = new Panel(new GridLayout(0, 2));
    TextField query = new TextField();
    TextField cod   = new TextField();
    TextField vor   = new TextField();
    TextField nac   = new TextField();
    Button next, first, alter, erase, insert;
  // JDBC-Variable
  String drv = "oracle.jdbc.driver.OracleDriver";
  String url = "jdbc:oracle:thin:@p30:1521:orcl";
  String uid="Kurse", passwd="Oracle";
  PreparedStatement sel, upd, ins, del;
  int code;
  Connection c;
  ResultSet rs;
  Statement s;

  public EinfacherClient() {
    // Fenster
    add("Center", ein);
      ein.add(new Label("Suchbegriff (Nachname):"));
      ein.add(query);
      ein.add(new Label("Code"));
      ein.add(cod);
      ein.add(new Label("Vorname"));
      ein.add(vor);
      ein.add(new Label("Nachname"));
      ein.add(nac);
      ein.add(alter = new Button("Ändern"));
      ein.add(next  = new Button("Nächster"));
      ein.add(erase  = new Button("Löschen"));
      ein.add(first  = new Button("Erster"));
      ein.add(insert = new Button("Einfügen"));
    add("South",  status);
    setSize(400, 250);
    setVisible(true);

    try {
      Class.forName(drv);
      c = DriverManager.getConnection(url, uid, passwd);
      sel = c.prepareStatement("SELECT" +
          " * FROM Personen WHERE nachname LIKE ?");
      ins = c.prepareStatement("INSERT INTO " +
          "Personen(pcode,vorname,nachname) VALUES (?, ?, ?)");
      upd = c.prepareStatement("UPDATE " +
          "Personen SET vorname=?, nachname=? WHERE pcode=?");
      del = c.prepareStatement("DELETE " +
          "FROM Personen WHERE pcode=?");
      query(query.getText());
    }
    catch (Exception ex) {ex.printStackTrace();}

    addWindowListener(new WindowAdapter() {
      public void windowClosing(WindowEvent e) {
        System.exit(0); }} );
    query.addTextListener(new TextListener() {
      public void textValueChanged(TextEvent e) {
        query(query.getText());}});
    next.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        nächst(query.getText()); }});
    first.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        query(query.getText()); }});
    alter.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        alter(); }});
    erase.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        erase(); }});
    insert.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        insert(); }});
  }

  private void alter() {                 // UPDATE
    try {
      upd.setString(1, vor.getText());
      upd.setString(2, nac.getText());
      upd.setInt(3, Integer.parseInt(cod.getText()));
      upd.execute();
      query(nac.getText());
      status.setText(upd.getUpdateCount()+" Zeilen gelöscht");
    }
    catch (Exception ex) {ex.printStackTrace();}
  }
  private void erase() {                 // DELETE
    try {
      del.setInt(1, Integer.parseInt(cod.getText()));
      del.execute();
      query(nac.getText());
      status.setText(del.getUpdateCount()+" Zeilen gelöscht");
    }
    catch (Exception ex) {ex.printStackTrace();}
  }
  private void insert() {                // INSERT
    try {
      ins.setInt(1, Integer.parseInt(cod.getText()));
      ins.setString(2, vor.getText());
      ins.setString(3, nac.getText());
      ins.execute();
      query(nac.getText());
      status.setText(ins.getUpdateCount()+" Zeilen eingefügt");
    }
    catch (Exception ex) {ex.printStackTrace();}
  }
  private void query(String q) {         // SELECT
    try {
      status.setText("Query: " + q + "%");
      sel.setString(1, q + "%");
      sel.execute();
      rs = sel.getResultSet();
    }
    catch (Exception ex) {ex.printStackTrace();}
    nächst(q);
  }
  private void nächst(String q) {        // nächste Zeile
    try {
      if (rs.next()) {
        code = rs.getInt("pcode");
        cod.setText("" + code);
        vor.setText(rs.getString("vorname"));
        nac.setText(rs.getString("nachname"));
      }
      else { 
        cod.setText(""); vor.setText(""); nac.setText("");
        sel.setString(1, q + "%");
        sel.execute();
        rs = sel.getResultSet();
      }
    }
    catch (Exception ex) {ex.printStackTrace();}
  }

  public static void main(String[] args) {
    new EinfacherClient();
  }
}                                 // Ende class EinfacherClient