/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package javaapplication2;
import java.sql.*;
/**
 *
 * @author db2inst1
 */
public class Praktikum2 {
    private Connection con;
    private PreparedStatement pstmt;
    private String query;

    public static void main(String[] args) throws SQLException {
         try{
            Praktikum2 pra = new Praktikum2();
            //pra.aufgabe2();
            //pra.aufgabe2b();
            pra.aufgabe3();
         }
         catch(Exception ex){
             ex.printStackTrace();
         }


    }

    public Praktikum2() throws ClassNotFoundException, SQLException {
        Class.forName("com.ibm.db2.jcc.DB2Driver");
        con = DriverManager.getConnection("jdbc:db2://localhost:50000/PUPS", "db2inst1", "hda");
        con.setAutoCommit(false);
    }

    public void aufgabe2() throws SQLException{
        Long starttime = System.currentTimeMillis();
            //Aufgabe 1 a)
            query = "SELECT KNR, COUNT(KNR) FROM BESTELLUNG GROUP BY KNR ORDER BY COUNT(KNR) DESC";
            pstmt = con.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            con.commit();

            while(rs.next());

            //Aufgabe 1 b)
            query = "SELECT KNR, COUNT(KNR) FROM BESTELLUNG GROUP BY KNR ORDER BY COUNT(KNR) ASC";
            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();
            con.commit();

            while(rs.next());

            //Aufgabe 1 c)
            query = "SELECT PID, COUNT(PID) FROM BESTELLUNG GROUP BY PID ORDER BY COUNT(PID) DESC";
            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();
            con.commit();

            while(rs.next());

            //Aufgabe 1 d)
            query = "SELECT PID, COUNT(PID) FROM BESTELLUNG GROUP BY PID ORDER BY COUNT(PID) ASC";
            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();
            con.commit();

            while(rs.next());

            Long endtime = System.currentTimeMillis();
            Long totaltime = endtime - starttime;
            System.out.println("Zeit: " + totaltime.toString());
    }

    public void aufgabe2b() throws SQLException{
        Long starttime = System.currentTimeMillis();
        int groesseBestellung = 100000;
        ResultSet rs;

            //Aufgabe 1 a)
            query = "SELECT KNR, COUNT(KNR) FROM BESTELLUNG WHERE BID > ? AND BID < ? GROUP BY KNR ORDER BY COUNT(KNR) DESC";
            pstmt = con.prepareStatement(query);
            for(int i=0;i<(1000000/groesseBestellung);i++){
                pstmt.setInt(1, i*groesseBestellung);
                pstmt.setInt(2, (i+1)*groesseBestellung);
                rs = pstmt.executeQuery();
                con.commit();
                while(rs.next());
            }
            //Aufgabe 1 b)
            query = "SELECT KNR, COUNT(KNR) FROM BESTELLUNG WHERE BID > ? AND BID < ? GROUP BY KNR ORDER BY COUNT(KNR) ASC";
            pstmt = con.prepareStatement(query);
            for(int i=0;i<(1000000/groesseBestellung);i++){
                pstmt.setInt(1, i*groesseBestellung);
                pstmt.setInt(2, (i+1)*groesseBestellung);
                rs = pstmt.executeQuery();
                con.commit();
                while(rs.next());
            }

            //Aufgabe 1 c)
            query = "SELECT PID, COUNT(PID) FROM BESTELLUNG WHERE BID > ? AND BID < ? GROUP BY PID ORDER BY COUNT(PID) DESC";
            pstmt = con.prepareStatement(query);
            for(int i=0;i<(1000000/groesseBestellung);i++){
                pstmt.setInt(1, i*groesseBestellung);
                pstmt.setInt(2, (i+1)*groesseBestellung);
                rs = pstmt.executeQuery();
                con.commit();
                while(rs.next());
            }

            //Aufgabe 1 d)
            query = "SELECT PID, COUNT(PID) FROM BESTELLUNG WHERE BID > ? AND BID < ? GROUP BY PID ORDER BY COUNT(PID) ASC";
            pstmt = con.prepareStatement(query);
            for(int i=0;i<(1000000/groesseBestellung);i++){
                pstmt.setInt(1, i*groesseBestellung);
                pstmt.setInt(2, (i+1)*groesseBestellung);
                rs = pstmt.executeQuery();
                con.commit();
                while(rs.next());
            }

            Long endtime = System.currentTimeMillis();
            Long totaltime = endtime - starttime;
            System.out.println("Zeit: " + totaltime.toString());
    }

    public void aufgabe3() throws SQLException{
        Long starttime = System.currentTimeMillis();
        Statement stmt;
        int groesseBestellung = 1000;
            //Aufgabe 1 a)
        for(int i=0;i<(1000000/groesseBestellung);i++){
            query = "SELECT KNR, COUNT(KNR) FROM BESTELLUNG WHERE BID > " + String.valueOf(i*groesseBestellung) + " AND BID < " + String.valueOf((i+1)*groesseBestellung) +" GROUP BY KNR ORDER BY COUNT(KNR) DESC";
            stmt = con.createStatement();

            ResultSet rs = stmt.executeQuery(query);
            con.commit();

            while(rs.next());

            stmt.close();

        }
        Long endtime = System.currentTimeMillis();
        Long totaltime = endtime - starttime;
        System.out.println("Zeit: " + totaltime.toString());
    }
}