/*
Run by:
javac Geheim.java
java -cp jdbc.jar:. Geheim
*/

import java.sql.*;

public class Geheim {
  public static void main(String[] args) throws ClassNotFoundException, SQLException {
    Class.forName("org.postgresql.Driver");
    Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:54320/petersk09?user=petersk09&password=geheim");
    con.setAutoCommit(false);
    Statement s = con.createStatement();
    ResultSet m = s.executeQuery("SELECT beer, price FROM Sells WHERE bar = 'C.Ch.';");
    while (m.next()) {
      String b = m.getString(1);
      float p = m.getFloat(2);
      System.out.println(b+" is sold for "+p);
      if (p >= 1000) {
        System.out.println("doh, that's expensive!");
      } else {
        System.out.println("too cheap ... let's raise the price!");
        float np = p+15;
        // need a new statement because s yielded m and m is still open!
        Statement ns = con.createStatement();
        int rc = ns.executeUpdate("UPDATE Sells SET price = "+np+" WHERE bar = 'C.Ch.' AND beer = '"+b+"';");
        System.out.println("we changed "+rc+" rows!");
        ns.close();
      }
    }
    m.close();
    // we can reuse s, as we closed m
    s.execute("DELETE FROM Sells WHERE beer like 'h%';");
    s.close();
    con.commit();
    con.close();
  }
}