/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package jbdc_nogalski;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author student
 */
public class JBDC_Nogalski {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        Connection conn = null;
        Properties connectionProps = new Properties();
        connectionProps.put("user", "inf132294");
        connectionProps.put("password", "inf132294");
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@//admlab2.cs.put.poznan.pl:1521/dblab02_students.cs.put.poznan.pl",connectionProps);
            System.out.println("Połączono z bazą danych");
        } catch (SQLException ex) {
            Logger.getLogger(JBDC_Nogalski.class.getName()).log(Level.SEVERE,   "nie udało się połączyć z bazą danych", ex);
            System.exit(-1);
        }

        //Zadanie 1
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select count(*) " + "from pracownicy");
            while (rs.next()) {
                System.out.println("Zatrudniono " + rs.getInt(1) + " pracowników, w tym:");
            }
        } catch (SQLException ex) {
            System.out.println("Bład wykonania polecenia" + ex.toString());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    /* kod obsługi */
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    /* kod obsługi */ }
            }
        }
        System.out.println("Zadanie 1");
        System.out.println("");
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT p.nazwisko, z.nazwa FROM pracownicy p natural join zespoly z");
            while (rs.next()) {
                System.out.println(rs.getString(1) + "w zespole " + rs.getString(2) + ",");
            }
        } catch (SQLException ex) {
            System.out.println("Bład wykonania polecenia" + ex.toString());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    /* kod obsługi */
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    /* kod obsługi */ }
            }
        }
        System.out.println("");
        System.out.println("Zadanie 2");
        System.out.println("");
        //zadanie 2
        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery("SELECT nazwisko, placa_pod FROM pracownicy WHERE etat = 'ASYSTENT' ORDER BY placa_pod DESC");
            rs.last();
                System.out.println(rs.getString(1) + ": " + rs.getFloat(2) + ",");

            rs.absolute(-3);
                System.out.println(rs.getString(1) + ": " + rs.getFloat(2) + ",");

            rs.absolute(2);
                System.out.println(rs.getString(1) + ": " + rs.getFloat(2) + ",");

        } catch (SQLException ex) {
            System.out.println("Bład wykonania polecenia" + ex.toString());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    /* kod obsługi */
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    /* kod obsługi */ }
            }
        }

        //zadanie 3
        System.out.println("");
        try {
            int changes = 0;
            int [] zwolnienia={150, 200, 230};
            String [] zatrudnienia={"Kandefer", "Rygiel", "Boczar"};
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            for(int i = 0; i < zwolnienia.length; i++){
               //changes += stmt.executeUpdate("DELETE FROM pracownicy WHERE id_prac = " + zwolnienia[i]);
            }
            System.out.println("Usunieto " + changes + " pracownikow");

            Statement stmtSEQ = conn.createStatement();

            ResultSet seq = null;
            ArrayList<Integer> ids = new ArrayList<>();
            for (int i = 0; i < zatrudnienia.length; i++){
                seq = stmtSEQ.executeQuery("SELECT my_seq.NEXTVAL FROM dual");
                seq.next();
                int id = seq.getInt(1);
                ids.add(id);
            }
            seq.close();
            System.out.println(ids.size());
            changes = 0;
            for (int i = 0; i < zatrudnienia.length; i++){
               String query = "INSERT INTO pracownicy(id_prac, nazwisko) " + "VALUES(" + ids.get(i) + ",'" + zatrudnienia[i] + "')";
               System.out.println(query);
               changes += stmt.executeUpdate(query);
            }
        } catch (SQLException ex) {
            System.out.println("Bład wykonania polecenia" + ex.toString());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    /* kod obsługi */
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    /* kod obsługi */ }
            }
        }
        //zadanie 4
        System.out.println("");
        System.out.println("Zadanie 4");
        System.out.println("");

        try {
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select *" + "from etaty");
            System.out.println("Przed dodaniem");
            while (rs.next()) {
                System.out.println("nazwa " + rs.getString(1) + " placa min: " + rs.getFloat(2) + " placa max: " + rs.getFloat(3));
            }
            System.out.println("");
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            int changes = stmt.executeUpdate("INSERT INTO etaty(nazwa, placa_min, placa_max) " + "VALUES('test123', 123, 456)");

            stmt = conn.createStatement();
            rs = stmt.executeQuery("select *" + "from etaty");
            System.out.println("Po dodaniu");
            while (rs.next()) {
                System.out.println("nazwa " + rs.getString(1) + " placa min: " + rs.getFloat(2) + " placa max: " + rs.getFloat(3));
            }
            System.out.println("");
            conn.rollback();
            System.out.println("po rollbacku");
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select *" + "from etaty");
            while (rs.next()) {
                System.out.println("nazwa " + rs.getString(1) + " placa min: " + rs.getFloat(2) + " placa max: " + rs.getFloat(3));
            }
            System.out.println("");
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            System.out.println("przed Insertem");
            changes = stmt.executeUpdate("INSERT INTO etaty(nazwa, placa_min, placa_max) " + "VALUES('test123', 123, 456)");
            conn.commit();
            stmt = conn.createStatement();
            System.out.println("Po insercie");
            rs = stmt.executeQuery("select *" + "from etaty");
            while (rs.next()) {
                System.out.println("nazwa " + rs.getString(1) + " placa min: " + rs.getFloat(2) + " placa max: " + rs.getFloat(3));
            }
        } catch (SQLException ex) {
            System.out.println("Bład wykonania polecenia" + ex.toString());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    /* kod obsługi */
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    /* kod obsługi */ }
            }
        }

        try {
            conn.close();
            System.out.println("Rozłączono z bazą danych");
        } catch (SQLException ex) {
            Logger.getLogger(JBDC_Nogalski.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

}