예제 #1
0
  public void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {

    response.setContentType("text/html");
    PrintWriter out = response.getWriter();

    String dbUser = "******"; // enter your username here
    String dbPassword = "******"; // enter your password here

    try {
      OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
      ods.setURL("jdbc:oracle:thin:@//w4111b.cs.columbia.edu:1521/ADB");
      ods.setUser(dbUser);
      ods.setPassword(dbPassword);

      Connection conn = ods.getConnection();

      String query = new String();
      Statement s = conn.createStatement();

      query = "select * from events";

      ResultSet r = s.executeQuery(query);
      while (r.next()) {
        out.println("Today's Date: " + r.getString(1) + " ");
      }
      r.close();
      s.close();

      conn.close();

    } catch (Exception e) {
      out.println("The database could not be accessed.<br>");
      out.println("More information is available as follows:<br>");
      e.printStackTrace(out);
    }
  } // end doGet method
예제 #2
0
  public static void main(
      String args[]) { // String args[] ist ein Feld von Stringreferenzen, die beim Programmaufruf
    // erhalten werden k�nnen (siehe auch:
    // http://www.gailer-net.de/tutorials/java/Notes/chap49B/ch49B_9.html)

    boolean fertig = false; // beendet switch-case Anweisung
    String name = "dbsys34"; // Variablen f�r Name und Passwort
    String passwd = "spion!";
    BufferedReader in =
        new BufferedReader(new InputStreamReader(System.in)); // zum Einlesen von Tastatur n�tig
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;

    String line = null; // Variable f�r Benutzereingabe
    int wahl = 0; // Variable f�r SwitchCase

    // AUSGABE auf die Konsole
    System.out.println(""); // Leerzeile ausgeben
    System.out.println("--- Dies ist Datenbank-Java-Aufgabe ---");
    System.out.println("");

    // --- DIALOG mit Benutzer ---
    /*		try {
        		System.out.print("Benutzername: ");
        		name = in.readLine();					// Einlesen der Eingabe "Benutzername"
    	System.out.print("Passwort: ");
    	passwd = in.readLine();					// Einlesen der Eingabe "Passwort"
    }
    // --- Abfangen des m�glichen Fehlers ---
    catch (IOException e) {
    	System.out.println("Fehler beim Lesen der Eingabe: " + e);
    	System.exit(-1);
      	}*/

    // --- Block f�r Verbindung mit Datenbank ---
    try {
      DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); // Treiber laden
      String url = "jdbc:oracle:thin:@rzdb01.fh-konstanz.de:1521:o9204"; // String f�r DB-Connection
      // (vgl.Skript)
      conn = DriverManager.getConnection(url, name, passwd); // Verbindung erstellen

      conn.setTransactionIsolation(
          conn.TRANSACTION_SERIALIZABLE); // Transaction Isolations-Level setzen: Serialisierbarkeit
      // als Korrektheitskriterium
      conn.setAutoCommit(false); // Kein automatisches Commit nach jedem SQL statement
      System.out.println("... zur Datenbank verbunden!"); // Ausgabe f�r den Benutzer
      conn.setAutoCommit(true); // Automatisches speichern aktivieren
      stmt = conn.createStatement(); // Statement-Objekt erzeugen

      while (!fertig) {
        System.out.println("\nHallo Benutzer! Was wollen Sie tun?");
        System.out.println("1: Kunde einfuegen");
        System.out.println("2: Kunde suchen");
        System.out.println("3: Ferienwohnung einfuegen");
        System.out.println("4: Ferienwohnung suchen");
        System.out.println("5: Ferienwohnung buchen");
        System.out.println("Zum Beenden bitte \"0\" druecken");

        // Einlesen der Benutzerwahl
        try {
          line = in.readLine();
        }
        // Fehler beim Einlesen
        catch (IOException ioe) {
          System.out.println("Fehler beim Lesen der Eingabe: " + ioe);
          fertig = true;
          continue;
        }
        // Konvertierung in int-Wert
        try {
          wahl = Integer.parseInt(line);
        } catch (NumberFormatException nfe) {
          System.out.println("Falsche Eingabe!");
          continue;
        }
        switch (wahl) {
          case 0:
            fertig = true;
            break;

            // {{{ Kunde einfuegen
          case 1:
            String vorname = null,
                nachname = null,
                plz = null,
                stadt = null,
                strasse = null,
                hsnr = null,
                blz = null,
                kontonr = null,
                landcode = null;

            try {
              System.out.println("Bitte Daten des Kunden eingeben.");
              System.out.print("Vorname: ");
              vorname = in.readLine();
              System.out.print("Nachname: ");
              nachname = in.readLine();
              System.out.print("PLZ (max. 5 Stellen): ");
              plz = in.readLine();
              System.out.print("Stadt: ");
              stadt = in.readLine();
              System.out.print("Strasse: ");
              strasse = in.readLine();
              System.out.print("Hausnr.: ");
              hsnr = in.readLine();
              System.out.print("BLZ: ");
              blz = in.readLine();
              System.out.print("Kontonr.: ");
              kontonr = in.readLine();
              System.out.print("Landkuerzel (de, fr, it, ch): ");
              landcode = in.readLine();
            } catch (IOException e) {
              System.out.println("Fehler beim Lesen der Eingabe: " + e);
              System.exit(-1);
            }

            // Daten einfuegen
            stmt.executeUpdate(
                "INSERT INTO kunde "
                    + "VALUES ( (SELECT MAX(kdnr)+1 FROM kunde), '"
                    + vorname
                    + "', "
                    + "         '"
                    + nachname
                    + "', "
                    + "         "
                    + Integer.parseInt(plz)
                    + ", "
                    + "         '"
                    + stadt
                    + "', "
                    + "         '"
                    + strasse
                    + "', "
                    + "         '"
                    + hsnr
                    + "', "
                    + "         "
                    + Integer.parseInt(blz)
                    + ", "
                    + "         "
                    + Integer.parseInt(kontonr)
                    + ", "
                    + "         '"
                    + landcode
                    + "' )");

            System.out.println("Der Kunde wurde erfolgreich eingefuegt!");

            break;
            // }}}

            // {{{ Kunde suchen
          case 2:
            String suchVorname = null, suchNachname = null;

            try {
              System.out.println("Bitte Namen des zu suchenden Kunden eingeben.");
              System.out.print("Vorname: ");
              suchVorname = in.readLine();
              System.out.print("Nachname: ");
              suchNachname = in.readLine();
            } catch (IOException e) {
              System.out.println("Fehler beim Lesen der Eingabe: " + e);
              System.exit(-1);
            }

            // ... und Abfrage senden
            String sSql =
                "SELECT * FROM kunde "
                    + "WHERE vorname LIKE '%"
                    + suchVorname
                    + "%' AND "
                    + "nachname LIKE '%"
                    + suchNachname
                    + "%'";
            rset = stmt.executeQuery(sSql);

            System.out.println("[DEBUG] " + sSql);
            System.out.println("Suchergebnisse: ");
            while (rset.next()) {
              System.out.println(
                  rset.getInt("kdnr")
                      + " "
                      + rset.getString("vorname")
                      + " "
                      + rset.getString("nachname")
                      + " "
                      + rset.getInt("zip")
                      + " "
                      + rset.getString("stadt")
                      + " "
                      + rset.getString("strasse")
                      + " "
                      + rset.getString("hsnr")
                      + " "
                      + rset.getInt("blz")
                      + " "
                      + rset.getInt("kontonr")
                      + " "
                      + rset.getString("landcode"));
              /*
                                          System.out.format("5%d 25%s 25%s 5%d 25%s 25%s 4%s 15%d 15%d 2%s",
                                                            rset.getInt("kdnr"),
                                                            rset.getString("vorname"),
                                                            rset.getString("nachname"),
                                                            rset.getInt("zip"),
                                                            rset.getString("stadt"),
                                                            rset.getString("strasse"),
                                                            rset.getString("hsnr"),
                                                            rset.getInt("blz"),
                                                            rset.getInt("kontonr"),
                                                            rset.getString("landcode"));
              */
            }
            break;
            // }}}

            // {{{ Wohnung einfuegen
          case 3:
            String zimmer = null, groesse = null, preis = null, sauna = null, schwbad = null;
            plz = null;
            landcode = null;

            try {
              System.out.println("Bitte Daten der Wohnung eingeben.");
              System.out.print("Anzahl Zimmer: ");
              zimmer = in.readLine();
              System.out.print("Groesse [qm]: ");
              groesse = in.readLine();
              System.out.print("Preis/Tag [EUR]: ");
              preis = in.readLine();
              System.out.print("Landkuerzel (de, fr, it, ch): ");
              landcode = in.readLine();
              System.out.print("Sauna? [0 = nein, 1 = ja]: ");
              sauna = in.readLine();
              System.out.print("Schwimmbad? [0 = nein, 1 = ja]: ");
              sauna = in.readLine();
            } catch (IOException e) {
              System.out.println("Fehler beim Lesen der Eingabe: " + e);
              System.exit(-1);
            }

            // Daten einfuegen
            stmt.executeUpdate(
                "INSERT INTO ferienwohnung "
                    + "VALUES ( (SELECT MAX(fwnr)+1 FROM ferienwohnung), "
                    + "         "
                    + Integer.parseInt(zimmer)
                    + ", "
                    + "         "
                    + Integer.parseInt(groesse)
                    + ", "
                    + "         "
                    + Integer.parseInt(plz)
                    + ", "
                    + "         "
                    + Integer.parseInt(preis)
                    + ", "
                    + "         '"
                    + landcode
                    + "', "
                    + "         "
                    + Integer.parseInt(sauna)
                    + ", "
                    + "         "
                    + Integer.parseInt(schwbad)
                    + ")");

            System.out.println("Die Wohnung wurde erfolgreich eingefuegt!");
            break;
            // }}}

            // {{{ Wohnung suchen
          case 4:
            String datumAnreise = null, datumAbreise = null;
            zimmer = null;

            try {
              System.out.println("Bitte Daten der zu suchenden Wohnung eingeben.");
              System.out.print("Landkuerzel (de, fr, it, ch): ");
              landcode = in.readLine();
              System.out.print("Anzahl Zimmer: ");
              zimmer = in.readLine();
              System.out.print("Anreisedatum [YYYY-MM-DD]: ");
              datumAnreise = in.readLine();
              System.out.print("Abreisedatum [YYYY-MM-DD]: ");
              datumAbreise = in.readLine();
            } catch (IOException e) {
              System.out.println("Fehler beim Lesen der Eingabe: " + e);
              System.exit(-1);
            }

            // Abfrage senden
            sSql =
                "SELECT f.* FROM ferienwohnung f "
                    + "LEFT OUTER JOIN buchung b "
                    + "ON ( b.fwnr = f.fwnr ) "
                    + "WHERE "
                    + "("
                    + " (     b.von < TO_DATE('"
                    + datumAnreise
                    + "', 'YYYY-MM-DD') "
                    + "   AND b.bis < TO_DATE('"
                    + datumAbreise
                    + "', 'YYYY-MM-DD') ) "
                    + " OR "
                    + " (     b.von > TO_DATE('"
                    + datumAnreise
                    + "', 'YYYY-MM-DD') "
                    + " AND b.bis > TO_DATE('"
                    + datumAbreise
                    + "', 'YYYY-MM-DD') ) "
                    + " OR "
                    + " ( b.bunr IS NULL ) "
                    + ")"
                    + "AND f.anz_zimmer = "
                    + Integer.parseInt(zimmer);
            rset = stmt.executeQuery(sSql);

            System.out.println("[DEBUG] " + sSql);
            System.out.println("Suchergebnisse: ");
            while (rset.next()) {
              System.out.println(
                  rset.getInt("fwnr")
                      + " "
                      + rset.getString("anz_zimmer")
                      + " "
                      + rset.getString("groesse")
                      + " "
                      + rset.getInt("preis")
                      + " "
                      + rset.getString("landcode")
                      + " "
                      + rset.getString("hat_sauna")
                      + " "
                      + rset.getString("hat_schwb"));
            }
            break;
            // }}}

            // {{{ Wohnung buchen
          case 5:
            String kdnr = null, fwnr = null;
            datumAnreise = null;
            datumAbreise = null;

            try {
              System.out.println("Bitte Daten der Buchung eingeben.");
              System.out.print("Kunden-Nr.: ");
              kdnr = in.readLine();
              System.out.print("Ferienwohnung-Nr.: ");
              fwnr = in.readLine();
              System.out.print("Anreisedatum [YYYY-MM-DD]: ");
              datumAnreise = in.readLine();
              System.out.print("Abreisedatum [YYYY-MM-DD]: ");
              datumAbreise = in.readLine();
            } catch (IOException e) {
              System.out.println("Fehler beim Lesen der Eingabe: " + e);
              System.exit(-1);
            }

            // Pruefen, ob Kunde/Fw existent?

            // Daten einfuegen
            stmt.executeUpdate(
                "INSERT INTO buchung "
                    + "VALUES ( (SELECT MAX(bunr)+1 FROM buchung), "
                    + "         SYSDATE, "
                    + "         TO_DATE('"
                    + datumAnreise
                    + "', 'YYYY-MM-DD'), "
                    + "         TO_DATE('"
                    + datumAbreise
                    + "', 'YYYY-MM-DD'), "
                    + "         "
                    + Integer.parseInt(kdnr)
                    + ", "
                    + "         "
                    + Integer.parseInt(fwnr)
                    + " )");

            System.out.println("Die Buchung wurde erfolgreich eingefuegt!");
            break;
            // }}}
        }
      }
      // Verbindung trennen
      stmt.close();
      conn.close();
    } catch (SQLException se) // Fehler abfangen
    {
      System.out.println("");
      System.out.println(
          "SQL Exception occurred while establishing connection to DBS: " + se.getMessage());
      System.out.println("- SQL state  : " + se.getSQLState());
      System.out.println("- Message    : " + se.getMessage());
      System.out.println("- Vendor code: " + se.getErrorCode());
      System.out.println("");
      System.out.println("EXITING WITH FAILURE ... !!!");
      System.out.println("");
      System.exit(-1);
    }
    System.out.println("");
    System.out.println("PROGRAM FINISHED!!!");
  }
예제 #3
0
  protected void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    // Variable initializations.
    HttpSession session = request.getSession();
    FileItem image_file = null;
    int record_id = 0;
    int image_id;

    // Check if a record ID has been entered.
    if (request.getParameter("recordID") == null || request.getParameter("recordID").equals("")) {
      // If no ID has been entered, send message to jsp.
      response_message =
          "<p><font color=FF0000>No Record ID Detected, Please Enter One.</font></p>";
      session.setAttribute("msg", response_message);
      response.sendRedirect("UploadImage.jsp");
    }

    try {
      // Parse the HTTP request to get the image stream.
      DiskFileUpload fu = new DiskFileUpload();
      // Will get multiple image files if that happens and can be accessed through FileItems.
      List<FileItem> FileItems = fu.parseRequest(request);

      // Connect to the database and create a statement.
      conn = getConnected(drivername, dbstring, username, password);
      stmt = conn.createStatement();

      // Process the uploaded items, assuming only 1 image file uploaded.
      Iterator<FileItem> i = FileItems.iterator();

      while (i.hasNext()) {
        FileItem item = (FileItem) i.next();

        // Test if item is a form field and matches recordID.
        if (item.isFormField()) {
          if (item.getFieldName().equals("recordID")) {
            // Covert record id from string to integer.
            record_id = Integer.parseInt(item.getString());

            String sql = "select count(*) from radiology_record where record_id = " + record_id;
            int count = 0;

            try {
              rset = stmt.executeQuery(sql);

              while (rset != null && rset.next()) {
                count = (rset.getInt(1));
              }
            } catch (SQLException e) {
              response_message = e.getMessage();
            }

            // Check if recordID is in the database.
            if (count == 0) {
              // Invalid recordID, send message to jsp.
              response_message =
                  "<p><font color=FF0000>Record ID Does Not Exist In Database.</font></p>";
              session.setAttribute("msg", response_message);
              // Close connection.
              conn.close();
              response.sendRedirect("UploadImage.jsp");
            }
          }
        } else {
          image_file = item;

          if (image_file.getName().equals("")) {
            // No file, send message to jsp.
            response_message = "<p><font color=FF0000>No File Selected For Record ID.</font></p>";
            session.setAttribute("msg", response_message);
            // Close connection.
            conn.close();
            response.sendRedirect("UploadImage.jsp");
          }
        }
      }

      // Get the image stream.
      InputStream instream = image_file.getInputStream();

      BufferedImage full_image = ImageIO.read(instream);
      BufferedImage thumbnail = shrink(full_image, 10);
      BufferedImage regular_image = shrink(full_image, 5);

      // First, to generate a unique img_id using an SQL sequence.
      rset1 = stmt.executeQuery("SELECT image_id_sequence.nextval from dual");
      rset1.next();
      image_id = rset1.getInt(1);

      // Insert an empty blob into the table first. Note that you have to
      // use the Oracle specific function empty_blob() to create an empty blob.
      stmt.execute(
          "INSERT INTO pacs_images VALUES("
              + record_id
              + ","
              + image_id
              + ", empty_blob(), empty_blob(), empty_blob())");

      // to retrieve the lob_locator
      // Note that you must use "FOR UPDATE" in the select statement
      String cmd = "SELECT * FROM pacs_images WHERE image_id = " + image_id + " FOR UPDATE";
      rset = stmt.executeQuery(cmd);
      rset.next();
      BLOB myblobFull = ((OracleResultSet) rset).getBLOB(5);
      BLOB myblobThumb = ((OracleResultSet) rset).getBLOB(3);
      BLOB myblobRegular = ((OracleResultSet) rset).getBLOB(4);

      // Write the full size image to the blob object.
      OutputStream fullOutstream = myblobFull.getBinaryOutputStream();
      ImageIO.write(full_image, "jpg", fullOutstream);
      // Write the thumbnail size image to the blob object.
      OutputStream thumbOutstream = myblobThumb.getBinaryOutputStream();
      ImageIO.write(thumbnail, "jpg", thumbOutstream);
      // Write the regular size image to the blob object.
      OutputStream regularOutstream = myblobRegular.getBinaryOutputStream();
      ImageIO.write(regular_image, "jpg", regularOutstream);

      // Commit the changes to database.
      stmt.executeUpdate("commit");
      response_message = "<p><font color=00CC00>Upload Successful.</font></p>";
      session.setAttribute("msg", response_message);

      instream.close();
      fullOutstream.close();
      thumbOutstream.close();
      regularOutstream.close();

      // Close connection.
      conn.close();
      response.sendRedirect("UploadImage.jsp");

      instream.close();
      fullOutstream.close();
      thumbOutstream.close();
      regularOutstream.close();

      // Close connection.
      conn.close();
    } catch (Exception ex) {
      response_message = ex.getMessage();
    }
  }