/*
 * 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 lab_jdbc;

import java.sql.*;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author student
 */
public class Lab_jdbc {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // TODO code application logic here


        try {
            Connection conn = createConnection();
            ex1(conn);
            ex2(conn);
            conn.rollback();
            conn.close();
            System.out.println("Rozłączono z bazą");
        } catch (SQLException ex) {
            Logger.getLogger(Lab_jdbc.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    public static Connection createConnection() throws SQLException
    {
        Connection conn = null;
        Properties connectionProps = new Properties();
        connectionProps.put("user","inf132218");
        connectionProps.put("password","inf132218");
        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");
        }catch (SQLException ex)
        {
            Logger.getLogger(Lab_jdbc.class.getName()).log(Level.SEVERE,"nie udało się połączyć z bazą danych", ex);
            System.exit(-1);
        }
        conn.setAutoCommit(false);
        return conn;
    }
    public static void ex1(Connection conn) throws SQLException
    {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("Select Count(*) from Pracownicy");
        rs.next();
        System.out.println("Zatrudniono "+rs.getInt(1)+" w tym:");
        rs.close();
        rs = stmt.executeQuery("Select Count(p.id_prac), z.NAZWA From Pracownicy p JOIN ZESPOLY z ON(z.id_zesp = p.id_zesp) GROUP BY z.NAZWA");
        while(rs.next())
        {
            System.out.println(rs.getInt(1)+" w zespole "+rs.getString(2));

        }
        rs.close();
        stmt.close();
    }
    public static void test1(Connection conn) throws SQLException
    {
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

        ResultSet rs = stmt.executeQuery("Select Count(p.id_prac), z.NAZWA From Pracownicy p JOIN ZESPOLY z ON(z.id_zesp = p.id_zesp) GROUP BY z.NAZWA");
        rs.afterLast();
        while(rs.previous())
        {
            System.out.println(rs.getInt(1)+" w zespole "+rs.getString(2));

        }
        rs.close();
        stmt.close();
    }
        public static void ex2(Connection conn) throws SQLException
    {
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery("Select NAZWISKO FROM PRACOWNICY WHERE ETAT = 'ASYSTENT' ORDER BY PLACA_POD+NVL(PLACA_DOD,0) DESC");

        rs.absolute(-1);
        System.out.println(rs.getString(1));
        rs.absolute(-2);
        System.out.println(rs.getString(1));
        rs.absolute(-3);
        System.out.println(rs.getString(1));

        rs.close();
        stmt.close();

        rs.close();
        stmt.close();
    }


}