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

import java.io.DataInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

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

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws IOException {
        // TODO code application logic here
        System.out.println("-------Polaczenie z baza danych Oracle-------");
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            System.out.println("Blad sciezki sterwonika Oracle JDBC?");
            e.printStackTrace();
            return;
        }

        System.out.println("Rejsetracja sterownika Oracle JDBC!");
        Connection connection = null;
        try {

            DataInputStream d = new DataInputStream(System.in);
            System.out.println("Podaj nazwe uzytkownika");
            String user = d.readLine();
            System.out.println("Podaj haslo");
            String pass = d.readLine();

            connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.56.101:1521:xe", user, pass);
            Statement stmt = connection.createStatement();

            System.out.println("Wybierz operacje:");
            System.out.println("a) Pracownik - ID:");
            System.out.println("b) Pracownik na stanowisku:");
            System.out.println("c) Lista 3");
            String zmienna = d.readLine();
            switch(zmienna){
                case "a":
                    System.out.println("Podaj ID pracownika");
                    String ID = d.readLine();
                    ResultSet rs = stmt.executeQuery("select e.first_name, e.last_name, d.department_name from employees e, "
                            + "departments d where d.department_id=e.department_id AND e.employee_id="+ID);
                    while(rs.next()){
                        System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
                    }
                    break;
                case "b":
                    ResultSet rs2 = stmt.executeQuery("select e.first_name, e.last_name, e.email, e.phone_number, e.hire_date,"
                            + "e.salary, j.job_title from employees e, jobs j where e.job_id=j.job_id AND e.job_id = 'IT_PROG'");
                    while(rs2.next()){
                        System.out.println(rs2.getString(1)+" "+rs2.getString(2)+" "+rs2.getString(3)+" "+rs2.getString(4)+" "+rs2.getDate(5)+" "+rs2.getInt(6));
                    }
                    break;

                case "c":
                    System.out.println("Podaj id wykluczonego departamentu");
                    String dep_id = d.readLine();
                    ResultSet rs3 = stmt.executeQuery("select e.first_name, e.last_name, e.salary from employees e where e.salary>5000 AND e.department_id!="+dep_id);
                    while(rs3.next()){
                        System.out.println(rs3.getString(1)+" "+rs3.getString(2)+" "+rs3.getInt(3));
                    }
                    break;
                default: System.out.println("Wybrales zla opcje");
            }


        } catch (SQLException e) {
            System.out.println("Blad polaczenia. Sprawdz konsole");
            e.printStackTrace();
            return;
        }
        if (connection != null) {
            System.out.println("Polaczyles sie z baza danych oracle!");
        } else {
            System.out.println("Blad polaczenia!");
        }

        try {
            connection.close();
            System.out.println("Połaczenie z baza zakończone");
        } catch (SQLException e) {
            System.out.println("Blad zakonczenia połaczenia");
            e.printStackTrace();
            return;
        }

    }

}