// Programm 6-3a:  ./OracleUndSwing/App/KurseTabelle.java
import java.awt.*;       import javax.swing.*;
import java.awt.event.*; import javax.swing.table.*;
import java.sql.*;       import javax.swing.event.*;

// UI Delegate (View und Controller)  
class KurseTabelle extends JFrame {

  KurseTabelleModel m = new KurseTabelleModel();
  
  JTable table = new JTable(m);

  public KurseTabelle() {        
    super("MVC Tabelle (GUI Kurse)");
    Panel oben = new Panel(new GridLayout(1, 5));
    Button reset = new Button("Reset");
    Button neuZeile = new Button("neu");
    Button delZeile = new Button("löschen");
    Button speicher = new Button("speichern");
    Button refresh  = new Button("refresh");
    getContentPane().setLayout(new BorderLayout());
    getContentPane().add("North", oben);
    oben.add(reset);
    oben.add(neuZeile);
    oben.add(delZeile);
    oben.add(speicher);
    oben.add(refresh);
    getContentPane().add("Center", new JScrollPane(table));
    table.setColumnSelectionAllowed(false);

    table.setSelectionMode(
                        ListSelectionModel.SINGLE_SELECTION);
    table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
    for (int i = 0; i < m.cols; i++)
      table.getColumn(
             m.colnames[i]).setPreferredWidth(m.colwidths[i]);

    m.con.init();
    refresh();
    combobox(m.dozenten, m.colnames[1]);
    combobox(m.typen, m.colnames[2]);

    neuZeile.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        table.editingStopped(new ChangeEvent(this));
        neueZeile(); }});
    reset.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        table.editingStopped(new ChangeEvent(this));
        new InitialisiereTabellen();
        refresh(); }});
    delZeile.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        table.editingStopped(new ChangeEvent(this));
        löscheZeile(table.getSelectedRow()); }});
    refresh.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        table.editingStopped(new ChangeEvent(this));
        refresh(); }});
    speicher.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        table.editingStopped(new ChangeEvent(this));
        speichereZeile(table.getSelectedRow()) ; }});
    addWindowListener(new WindowAdapter() {
      public void windowClosing(WindowEvent e) {
        System.exit(0); } });
    setSize(500, 400);
    setVisible(true);
  }

    private void combobox(Object[] items, String spalte) {
      JComboBox box = new JComboBox();
      for (int i = 0; i < items.length; i++)
        box.addItem(items[i]);
      table.getColumn(spalte).setCellEditor(
                              new DefaultCellEditor(box));
    }

  void neueZeile(Object[] o) {
    m.addRow(o);
    table.setRowSelectionInterval(m.getRowCount()-1,
                                  m.getRowCount()-1);
  }
  void neueZeile() {
    Object[] temp = new Object[m.cols];
    for (int i = 0; i < m.cols; i++)
      temp[i] = "";
    neueZeile(temp);
  }
  void löscheZeile(int row) {
    if (row < 0 || row >= m.getRowCount())
      return;
    if (JOptionPane.showOptionDialog(this, "Wirklich kcode=" + 
             m.getValueAt(row, 0) + " löschen?",
             "KurseTabelle", JOptionPane.DEFAULT_OPTION,
             JOptionPane.WARNING_MESSAGE, null,
             new Object[]{"Ja", "Nein"}, "Nein") != 0) return;
    m.con.delete(m.getValueAt(row, 0));
    m.removeRow(row);
    table.setRowSelectionInterval(m.getRowCount()-1,
                                  m.getRowCount()-1);
  }
  void speichereZeile(int row) {
    if ("".equals(m.getValueAt(row, 0))) {
      m.con.insert(m, row);
    }
    else {
      m.con.update(m, row);
    }
    refresh();
  }

  void refresh() {
    Object[] temp = new Object[m.cols];
    m.con.select();
    m.setNumRows(0);
    while ((temp = m.con.nächste()) != null)
      neueZeile(temp);
    combobox(m.dozenten, m.colnames[1]);
  }

  public static void main(String[] args) {
    new KurseTabelle();
  }
}                                    // Ende Class KurseTabelle
// Programm 6-3b:
// Model
class KurseTabelleModel extends DefaultTableModel {
  KurseTabelleModelConnector con;

  String[] colnames = new String[] {"kcode","dcode","Typ",
      "Kursbezeichnung","Zeit","Nachname","Vorname"};
  String[] columns = new String[] {"kcode","Kurse.dcode",
      "typ","bezeichnung","zeit","nachname","vorname"};
  boolean[] editable =
         {false, true, true, true, true, false, false};
  int[] colwidths = {50, 50, 50, 200, 50, 70, 70};
  static final int cols = 7;
  Object[] dozenten;
  Object[] typen = new Object[]{"P", "S", "Ü", "V"};

  public KurseTabelleModel() {
    super(0, cols);
    con = new KurseTabelleModelConnector(this);
    setColumnIdentifiers(colnames);
  }
  public boolean isCellEditable(int row, int col) {
    if (!editable[col]) return false;
    else return super.isCellEditable(row, col);
  }
}                               // Ende Class KurseTabelleModel

// Datenbank-Connector
class KurseTabelleModelConnector {
  static String treiber = "oracle.jdbc.driver.OracleDriver";
  static String jdbcurl = "jdbc:oracle:thin:@p30:1521:orcl";
  static String uid = "Kurse", pwd = "Oracle";
  static String database = "Kurse";
  static Connection c;
  String sqlsel; Statement sel;
  String sqlupd; PreparedStatement upd;
  String sqldel; PreparedStatement del;
  String sqlins; CallableStatement ins;
  String sqldoz; Statement doz;
  ResultSet rsel, rdoz;
  KurseTabelleModel m;    

  static {                          // statischer Konstruktor
    try {
      Class.forName(treiber);
      c = DriverManager.getConnection(jdbcurl, uid, pwd);
    }
    catch (Exception ex) {
      ex.printStackTrace(); System.exit(0);
    }
  }
  public KurseTabelleModelConnector(KurseTabelleModel m) { 
    this.m = m;
  }

  void select() {
    System.out.println("SQL " + sqlsel);
    try {
      rsel = sel.executeQuery(sqlsel);
      rdoz = doz.executeQuery(sqldoz);
      m.dozenten = null;
      while (rdoz.next()) {
        int x = m.dozenten == null ? 0 : m.dozenten.length;
        Object[] temp = new Object[x + 1];
        for (int i = 0; i < x; i++) {
          temp[i] = m.dozenten[i]; }
        temp[x] = rdoz.getString(1);
        m.dozenten = temp;
      }
    }
    catch (Exception ex) { ex.printStackTrace();}
  }
  Object[] nächste() {
    Object[] temp = new Object[m.cols];
    try {
      if (!rsel.next()) return null;
      for (int i = 0; i < m.cols; i++)
        temp[i] = rsel.getString(i + 1);
      return temp;
    }
    catch (Exception ex) { ex.printStackTrace();}
    return null;
  }

  void update(KurseTabelleModel m, int row) {
    try {
      System.out.println("SQL " + sqlupd);
      upd.setString(5, (String) m.getValueAt(row, 0));
      upd.setString(1, (String) m.getValueAt(row, 1));
      upd.setString(2, (String) m.getValueAt(row, 2));
      upd.setString(3, (String) m.getValueAt(row, 3));
      upd.setString(4, (String) m.getValueAt(row, 4));
      upd.execute();
    }
    catch (Exception ex) { ex.printStackTrace();}
  }

  void insert(KurseTabelleModel m, int row) {
    try {
      System.out.println("SQL " + sqlins);
      ins.registerOutParameter(1, Types.INTEGER);
      ins.setString(2, (String) m.getValueAt(row, 1));
      ins.setString(3, (String) m.getValueAt(row, 2));
      ins.setString(4, (String) m.getValueAt(row, 3));
      ins.setString(5, (String) m.getValueAt(row, 4));
      ins.execute();
    }
    catch (Exception ex) { ex.printStackTrace();}
  }

  void delete(Object kcode) {
    try {
      System.out.println("SQL " + sqldel);
      del.setInt(1, Integer.parseInt((String)kcode));
      del.execute();
    }
    catch (Exception ex) { ex.printStackTrace();}
  }

  void init() {
    try {
   // SELECT 
      sel = c.createStatement();
      sqlsel = "SELECT ";
      for (int i = 0; i < m.cols; i++)
        sqlsel += (i == 0 ? "" : ",") + m.columns[i];
      sqlsel += " FROM Kurse, Dozenten " +
                " WHERE Kurse.dcode = Dozenten.dcode (+)" +
                " ORDER BY kcode";
      doz = c.createStatement();
      sqldoz = "SELECT dcode,nachname,vorname FROM Dozenten";
   // UPDATE
      sqlupd = "UPDATE Kurse " + 
               "SET dcode=?, typ=?, bezeichnung=?, zeit=? " +
               "WHERE kcode=?";
      upd = c.prepareStatement(sqlupd);
   // INSERT
      String proz =
          "CREATE OR REPLACE FUNCTION InsertKurse \n" +
          "(dc IN INTEGER, typ IN STRING, " +
          " tit IN STRING, zeit IN INTEGER) \n" +
          " RETURN INTEGER IS \n t NUMBER; \n" +
          " BEGIN\n SELECT max(kcode) INTO t FROM Kurse;\n" +
          " IF t IS NULL THEN t := 0; END IF;" +
          " INSERT INTO Kurse(kcode, dcode, typ," +
          " bezeichnung, zeit)\n" +
          " VALUES(t+1,dc,typ,tit,zeit); \n" +
          " RETURN t+1; \n END;";
      c.createStatement().execute(proz);
      sqlins = "{? = call InsertKurse(?, ?, ?, ?)}";
      ins = c.prepareCall(sqlins);
   // DELETE
      sqldel = "DELETE FROM Kurse WHERE kcode=?";
      del = c.prepareStatement(sqldel);
    }
    catch (Exception ex) {
      ex.printStackTrace(); System.exit(0);
    }
  }
}                      // Ende Class KurseTabelleModelConnector