package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author FON
 */

public class JDBC {
    public static void main(String[] args) {
//        select();
//          delete(50);
//        selectKACA(2008);
//        createTable();
//           System.out.println(dropTable("Registration") ? "Tabela je obrisana" : "Tabela nije obrisana");
    }
    public static void select(){
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String upit = "SELECT sifraodelj,nazivodelj FROM odeljenje o WHERE o.nazivodelj LIKE 'P%'";
        try(Connection konekcija = DriverManager.getConnection(url, "student", "student");
                Statement st = konekcija.createStatement();
                ResultSet r = st.executeQuery(upit);
                ) {
            System.out.println("SIFRA\tNAZIV\t");
            while(r.next()){
                int sifra = r.getInt(1);
                String naziv = r.getString("nazivodelj");

                System.out.println(sifra + "\t" + naziv);
            }


        } catch (SQLException ex) {
            Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    public static void delete(int sifra){
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String upit = "DELETE FROM odeljenje WHERE sifraodelj = ?";
        try(Connection konekcija = DriverManager.getConnection(url, "student", "student");
                PreparedStatement pst = konekcija.prepareStatement(upit);
                ){
            pst.setInt(1,sifra);
            int br = pst.executeUpdate();
            konekcija.setAutoCommit(false);
            if (br > 0){
                konekcija.commit();
            }
            else{
                konekcija.rollback();
            }
        } catch (SQLException ex) {
            Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void selectKACA(int godina){
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String upit = "SELECT ime,prezime,datzap,plata FROM zaposleni WHERE EXTRACT(year from datzap) = ?";
        try (Connection konekcija = DriverManager.getConnection(url, "student", "student");
            PreparedStatement pst = konekcija.prepareStatement(upit);
                ){
            pst.setInt(1, godina);
            try (ResultSet r = pst.executeQuery()) {
                System.out.println("IME\tPREZIME\tDATUM\t\t\tPLATA\t");
                while(r.next()){
                    String ime = r.getString(1);
                    String prezime = r.getString(2);
                    String dat = r.getString("datzap");
                    double plata = r.getDouble("plata");
                    System.out.println(ime + "\t" + prezime + "\t"+ dat + "\t" + plata);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void createTable(){
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String upit = "CREATE TABLE tijana(" +
                        "sifratijana NUMBER(7,0)," +
                        "naziv VARCHAR(20)," +
                        "PRIMARY KEY (sifratijana)" +
                        ")";
        try(Connection conn = DriverManager.getConnection(url,"student","student");
            Statement st = conn.createStatement();
                ) {
            st.executeUpdate(upit);

        } catch (SQLException ex) {
            Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public static boolean dropTable(String ime){
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String drop = "DROP TABLE " + ime;
        try (Connection conn = DriverManager.getConnection(url,"student","student");
             Statement st = conn.createStatement();
                ){
            st.execute(drop);
        } catch (SQLException ex) {
            Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }
}