// Programm 3-3  .\Oracle\InitialisiereTabellen.java
import java.sql.*;
import oracle.jdbc.driver.*;

public class InitialisiereTabellen {
  public static void main(String[] args) throws Exception {
    new InitialisiereTabellen();
  }
  public InitialisiereTabellen() {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection c = DriverManager.getConnection(
                       "jdbc:oracle:thin:@p30:1521:orcl",
                       "Kurse", "Oracle");
      Statement s = c.createStatement();
//=== Dozenten =====================
      System.out.println("Tabelle Dozenten ...");
      String sql = "DROP TABLE Dozenten";
      try { s.execute(sql); } catch (Exception ex) {}
      sql = "CREATE TABLE Dozenten " +
                     "(dcode NUMBER(11), " +
                      "vorname VARCHAR2(25), " +
                      "nachname VARCHAR2(25))" ;
      try { s.execute(sql); } catch (Exception ex) {}
      String a[][] = new String[][] {{"2", "Leutner", "Brigitte"},
                                     {"3", "Gernhardt", "Wolfgang"},
                                     {"4", "Weizenbaum", "Josephine"},
                                     {"5", "Duffing", "Julienne"},
                                     {"9", "Mergel", "Börries"},
                                     {"10", "Ludwig", "Luigi"},
                                     {"27", "Mayer-Böricke", "Julius"}};
      PreparedStatement ps = c.prepareStatement(
            "INSERT INTO Dozenten (dcode,nachname,vorname) " +
            "VALUES (?,?,?)");
      for (int i = 0; i < a.length; i++) {
        for (int j = 0; j < a[i].length; j++) 
          ps.setString(j + 1, a[i][j]);
        ps.execute();
      }
//=== Personen =====================
      System.out.println("Tabelle Personen ...");
      sql = "DROP TABLE Personen";
      try { s.execute(sql); } catch (Exception ex) {}
      sql = "CREATE TABLE Personen " +
                     "(pcode NUMBER(11), " +
                      "vorname VARCHAR2(25), " +
                      "nachname VARCHAR2(25))" ;
      try { s.execute(sql); } catch (Exception ex) {}
      a = new String[][] {{"23", "Müller", "Hanne"},
                          {"24", "Schmidt", "Lothar"},
                          {"34", "Hintze", "Franz"},
                          {"88", "Khan", "Dschingis"},
                          {"91", "Kaiser", "Leo"},
                          {"101", "Kunze", "Sieglinde"}};
      ps = c.prepareStatement(
            "INSERT INTO Personen (pcode,nachname,vorname) " +
            "VALUES (?,?,?)");
      for (int i = 0; i < a.length; i++) {
        for (int j = 0; j < a[i].length; j++) 
          ps.setString(j + 1, a[i][j]);
        ps.execute();
      }
//=== Kurse =====================
      System.out.println("Tabelle Kurse ...");
      sql = "DROP TABLE Kurse";
      try { s.execute(sql); } catch (Exception ex) {}
      sql = "CREATE TABLE Kurse " +
                     "(kcode NUMBER(11), " +
                     " dcode NUMBER(11), " +
                      "typ VARCHAR2(1), " +
                      "bezeichnung VARCHAR2(100), " +
                      "zeit NUMBER(15,1), " +
                      "datum DATE)" ;
      try { s.execute(sql); } catch (Exception ex) {}
      Object o[][] = new Object[][] {{"1", "P", "10", "Objektorientierte Programmierung mit Java", new Date(98,4,27), "10"},
                          {"2", "S", "3", "JavaScript", new Date(98,6,29), "5"},
                          {"3", "P", "2", "JDBC", new Date(98,6,30), "7.5"},
                          {"4", "S", "3", "HTML", new Date(98,7,13), "5"},
                          {"5", "S", "5", "GUI-Programmierung mit Java", new Date(98,6,9), "7.5"},
                          {"6", "V", "", "Servlets", new Date(98,6,10), "7.5"}};
      ps = c.prepareStatement(
            "INSERT INTO Kurse (kcode,typ,dcode,bezeichnung,datum,zeit) " +
            "VALUES (?,?,?,?,?,?)");
      for (int i = 0; i < o.length; i++) {
        for (int j = 0; j < o[i].length; j++) 
          ps.setObject(j + 1, o[i][j]);
        ps.execute();
      }
//=== Teilnehmer =====================
      System.out.println("Tabelle Teilnehmer ...");
      sql = "DROP TABLE Teilnehmer";
      try { s.execute(sql); } catch (Exception ex) {}
      sql = "CREATE TABLE Teilnehmer " +
            "(pcode NUMBER(11), " +
            " kcode NUMBER(11) )";
      try { s.execute(sql); } catch (Exception ex) {}
      a = new String[][] {{"23", "10"},
                          {"34", "10"},
                          {"24", "10"},
                          {"91",  "3"},
                          {"24",  "2"},
                          {"101", "2"},
                          {"101", "3"},
                          {"91",  "2"}};
      ps = c.prepareStatement(
            "INSERT INTO Teilnehmer (pcode,kcode) " +
            "VALUES (?,?)");
      for (int i = 0; i < a.length; i++) {
        for (int j = 0; j < a[i].length; j++) 
          ps.setString(j + 1, a[i][j]);
        ps.execute();
      }
    }
    catch (Exception ex) {ex.printStackTrace();}
  }
}                          // Ende class InitialisiereTabellen