public static void main(String args[]) throws SQLException {
String url = "jdbc:oracle:oci8:@";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null) url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
}
// Create a OracleDataSource instance and set properties
OracleDataSource ods = new OracleDataSource();
ods.setUser("hr");
ods.setPassword("hr");
ods.setURL(url);
// Connect to the database
Connection conn = ods.getConnection();
Statement stmt = conn.createStatement();
stmt.execute("delete from departments where department_id > 2000");
// Default batch value set to 50 for all prepared statements
// belonging to this connection.
((OracleConnection) conn).setDefaultExecuteBatch(50);
PreparedStatement ps = conn.prepareStatement("insert into departments values (?, ?, ?, ?)");
ps.setInt(1, 2010);
ps.setString(2, "Import");
ps.setInt(3, 114);
ps.setInt(4, 1700);
// this execute does not actually happen at this point
System.out.println(ps.executeUpdate());
ps.setInt(1, 2020);
ps.setString(2, "Export");
ps.setInt(3, 145);
ps.setInt(4, 2500);
// this execute does not actually happen at this point
int rows = ps.executeUpdate();
System.out.println("Number of rows updated before calling sendBatch: " + rows);
// Execution of both previously batched executes will happen
// at this point. The number of rows updated will be
// returned by sendBatch.
rows = ((OraclePreparedStatement) ps).sendBatch();
System.out.println("Number of rows updated by calling sendBatch: " + rows);
ps.close();
conn.close();
}
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
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!!!");
}
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();
}
}