/* JdbcApplet (c) Stefan Rauch 1999          */
/* Applet, das den Umgang mit dem JDBC zeigt */


import java.awt.*;
import java.awt.event.*;
import java.applet.*;
import java.sql.*;


public class JdbcApplet extends Applet {


  BorderLayout layout = new BorderLayout();
  TextArea outputArea = new TextArea();
  TextField inputField = new TextField();
  Panel p;

  Button qu1 = new Button("Professoren");      /* Button fuer Professoren   */
  Button qu2 = new Button("Assistenten");      /* Button fuer Assistenten   */
  Button qu3 = new Button("Studenten");        /* Button fuer Studenten     */
  Button qu4 = new Button("Vorlesungen");      /* Button fuer Vorlesungen   */
  Button qu5 = new Button("hoeren");           /* Button fuer hoeren        */
  Button qu6 = new Button("voraussetzen");     /* Button fuer voraussetzen  */
  Button qu7 = new Button("pruefen");          /* Button fuer pruefen       */
  Button qu8 = new Button("User-Tabellen");    /* Button fuer User-Tabellen */



  Connection con;         /* Verbindungsobjekt zur Verbindung mit dem DBMS  */      
  Statement stmt;            /* Statement-Objekt zur Kommunikation mit DBMS */


  public JdbcApplet(){}                          /* Konstruktor fuer Applet */


  public void init() {                         /* Das Applet initialisieren */
    try {
      this.setLayout(layout);
      this.setSize(700,400);
      inputField.setBackground(Color.gray);
      inputField.setFont(new Font("Serif",1,14));

      inputField.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(ActionEvent e) {    /* ActionListener   */
          String q = inputField.getText();              /* fuer Eingabefeld */
          execQuery(q);                                 /* Gibt Query an    */     
        }                                               /* execQuery weiter */
      });
      outputArea.setBackground(Color.white);
      outputArea.setEditable(false);
      outputArea.setFont(new Font("Monospaced",1,14));

      /* ActionListener fuer jeweiligen Button      */
      /* gibt Abfrage an Methode execQuery() weiter */
      qu1.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(ActionEvent e) {
          execQuery("select persnr,name,rang,raum," +
                    "gebdatum as Geburtsdatum from Professoren");
        }
      });

      qu2.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(ActionEvent e) {
          execQuery("select persnr, name, fachgebiet, boss," +
                    "gebdatum as Geburtsdatum from Assistenten");
        }
      });

      qu3.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(ActionEvent e) {
          execQuery("select matrnr, name, semester," +
                    "gebdatum as Geburtsdatum from Studenten");
        }
      });

      qu4.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(ActionEvent e) {
          execQuery("select * from Vorlesungen");
        }

      });
      qu5.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(ActionEvent e) {
          execQuery("select * from hoeren");
        }
      });

      qu6.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(ActionEvent e) {
          execQuery("select * from voraussetzen");
        }
      });

      qu7.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(ActionEvent e) {
          execQuery("select * from pruefen");
        }
      });

      qu8.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(ActionEvent e) {
          execQuery("select name from sysobjects where xtype='U'");
        }
      });

      this.add(outputArea,BorderLayout.CENTER); /* Hinzufuegen und Anordnen */
      this.add(inputField,BorderLayout.NORTH);  /* der Elemente des Applets */
      this.add(p= new Panel(new GridLayout(8,1)), BorderLayout.WEST);



      p.add(qu1);
      p.add(qu2);
      p.add(qu3);
      p.add(qu4);
      p.add(qu5);
      p.add(qu6);
      p.add(qu7);
      p.add(qu8);


    }
    catch (Exception e) {e.printStackTrace();
    }



    String url    = "jdbc:odbc:dbs";      /* Verbindungsaufbau mit dem DBMS */

    String user   = "erika";

    String passwd = "mustermann";


    try {                                             /* probiere           */

      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  /* Brueckentreiber    */

    }

    catch(java.lang.ClassNotFoundException ex) {

      System.err.print("ClassNotFoundException: ");
      System.err.println(ex.getMessage());

    }

    try {                                                    /* probiere    */
      con = DriverManager.getConnection(url, user, passwd);  /* Verbindung  */
    }

    catch(SQLException ex) {

      outputArea.setText("SQLException: " + ex.getMessage());

    }
  }

  void execQuery(String query) {       /* SQL-Query an DBMS uergeben        */
                                       /* und Ergebnis in TextArea anzeigen */
    try {
      stmt = con.createStatement();       /* Statement-Objekt instantiieren */
      ResultSet rs = stmt.executeQuery(query);           /* Resultset holen */      

      int z = rs.getMetaData().getColumnCount();/* Zahl der Ergebnisspalten */
      outputArea.setText("\n");
      outputArea.setVisible(false);

      for (int i=1;i<=z;i++) {     /* alle Spaltennamen formatiert ausgeben */
        String lab=rs.getMetaData().getColumnLabel(i);
        outputArea.append(lab);
        int y = rs.getMetaData().getColumnDisplaySize(i)+4;
        for (int j=0;j<(y-lab.length());j++)
          outputArea.append(" ");
        }
        outputArea.append("\n");

        String arg;                         /* Inhalt der Ergebnismenge    */
        while(rs.next()) {                  /* wird zeilenweise formatiert */
          outputArea.append("\n");          /* in der TextArea ausgegeben  */
          for (int i=1;i<=z;i++) {
            arg=rs.getString(i);
            int len;
            if (arg != null) {
              len=arg.length();
            if(rs.getMetaData().getColumnName(i).equals("Geburtsdatum")) {
              outputArea.append(arg.substring(0,10));
            }
            else{
                outputArea.append(arg);
            }
            }
            else {
              len=4;
              outputArea.append("null");
            }
            int y = rs.getMetaData().getColumnDisplaySize(i)+4;
            for (int j=0;j<(y-len);j++) outputArea.append(" ");
          }
        }
        outputArea.setVisible(true);
        stmt.close();

      }catch(SQLException ex) {        /* Abfangen von SQL-Fehlermeldungen */
        outputArea.setText("SQLException: " + ex.getMessage());
    }
  }
}