Untitled

package uebung;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Blatt2 {

    /**
     * @param args
     */
    public static void main(String[] args) {
        Connection con;
        String driver = "org.postgresql.Driver";
        String DB_SERVER = "localhost:5433";
        String DB_NAME = "Musik";

        String password = "";
        String user = "postgres";
        String url = "jdbc:postgresql://" + DB_SERVER + "/" + DB_NAME;

        String stmt0 = "SELECT Count(*) FROM person WHERE nationalitaet = 'USA'";
        String stmt1 = "SELECT name FROM musiker NATURAL JOIN person NATURAL JOIN"
                + " lied NATURAL JOIN medium WHERE role ILIKE '%Vocals' AND"
                + " nationalitaet = 'USA' AND medium_art = 'MD' GROUP BY name";
        String stmt2 = "SELECT medium_titel, name FROM person NATURAL JOIN "
                + "musiker NATURAL JOIN lied NATURAL JOIN medium WHERE name = "
                + "'Robbie Williams' AND medium_art = 'LP' GROUP BY medium_titel,"
                + " name";
        String stmt3 = "SELECT medium_id, medium_titel, medium_art, preis, "
                + "erscheinungsjahr FROM lied NATURAL JOIN medium WHERE medium_art "
                + "= 'CD' AND preis < 10 ORDER BY laenge DESC LIMIT 1";
        String stmt4 = "SELECT m.medium_id, m.medium_titel, sum(l.laenge) "
                + "FROM medium m NATURAL JOIN lied l WHERE m.medium_art='LP' "
                + "AND lied_id in (SELECT lied_id FROM lied WHERE medium_id = "
                + "m.medium_id ORDER BY laenge DESC LIMIT 2) GROUP BY m.medium_id, "
                + "m.medium_titel";
        String stmt5 = "SELECT DISTINCT m.medium_titel, m.medium_art FROM "
                + "medium m WHERE m.medium_id NOT IN (SELECT DISTINCT medium_id "
                + "FROM lied WHERE laenge < '00:05:00')";
        String stmt6 = "-- SELECT * \n SELECT avg(preis) FROM medium " +
                "WHERE medium_id IN (SELECT medium_id FROM medium as m " +
                "NATURAL JOIN lied l NATURAL JOIN musiker mu NATURAL JOIN " +
                "person p WHERE m.medium_art = 'CD' GROUP BY m.medium_id " +
                "HAVING COUNT(DISTINCT nationalitaet) >= 3)";

        ResultSet rs = null;
        String[] s = {stmt0, stmt1, stmt2, stmt3, stmt4, stmt5, stmt6};
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, user, password);
            FileWriter fw = new FileWriter("erg.htm");
            BufferedWriter bw = new BufferedWriter(fw);
            bw.write("<html>");
            bw.write("<body>");

            for (int i = 0; i <= 6; i++) {
                int abfrage = i +1;
                bw.write("<br>Abfrage " + abfrage + ":</br>");
                rs = sqlAbfrage(con, s[i]);
                while (rs.next()) {
                    bw.write("<br>" + rs.getString(1) + "</br>");
                }
                bw.write("<br> </br>");
            }

            bw.write("</body>");
            bw.write("</html>");

            bw.close();
            fw.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public static ResultSet sqlAbfrage(Connection con, String stmt) {
        ResultSet rs = null;
        try {
            PreparedStatement ptsmt = con.prepareStatement(stmt);
            rs = ptsmt.executeQuery();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }

}