import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Polka {
    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
        }
        Connection connection = null;
        try {
            String serverName = "oracle4.icis.pcz.pl";
            String portNumber = "1521";
            String sid = "oracle4";
            String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
            String username = "SKUPRZ";
            String password = "Prz2019";
            connection = DriverManager.getConnection(url, username, password);
            if (connection != null) System.out.println("OK");
        } catch (SQLException e) {
        }

        // operacje na bazie danych

        try {
            String sql0 = "DROP TABLE polka CASCADE CONSTRAINTS";
            String sql01 = "DROP TABLE przedmioty CASCADE CONSTRAINTS";
            String sql02 = "DROP TABLE KLIENCI_POLKA CASCADE CONSTRAINTS";
            String sql03 = "DROP TABLE wypozyczenia CASCADE CONSTRAINTS";

            String sql1 = "CREATE TABLE PRZEDMIOTY (" +
                    "przedmiot_id NUMBER(4)" +
                    "CONSTRAINT przedmioty_id_nn NOT NULL," +
                    "CONSTRAINT przedmioty_id_pr PRIMARY KEY ( przedmiot_id )," +
                    "NAZWA varchar2(30)" +
                    "CONSTRAINT przedmioty_nazwa_nn NOT NULL" +
                    ")";

            String sql2 = "CREATE TABLE POLKA (" +
                    "polka_id NUMBER(4)" +
                    "CONSTRAINT polka_id_nn NOT NULL," +
                    "CONSTRAINT polka_pr_pr PRIMARY KEY ( polka_id )," +
                    "PRZEDMIOT_ID NUMBER(4)" +
                    "CONSTRAINT polka_pr_nn NOT NULL," +
                    "CONSTRAINT polka_fr_id FOREIGN KEY ( PRZEDMIOT_ID ) REFERENCES PRZEDMIOTY ( przedmiot_id )" +
                    ")";

            String sql3 = "CREATE TABLE KLIENCI_POLKA (" +
                    "klient_ID number(4)" +
                    "CONSTRAINT klienci_id_nn NOT NULL," +
                    "CONSTRAINT klienci_id_pr PRIMARY KEY ( klient_id )," +
                    "IMIE varchar2(30)" +
                    "CONSTRAINT klienci_im_nn NOT NULL," +
                    "NAZWISKO varchar2(30)" +
                    "CONSTRAINT klienci_na_nn NOT NULL" +
                    ")";

            String sql4 = "CREATE TABLE WYPOZYCZENIA (" +
                    "wypozyczenie_id number(4)" +
                    "CONSTRAINT wypozyczenia_id_nn NOT NULL," +
                    "CONSTRAINT wypozyczenia_id_pr PRIMARY KEY ( wypozyczenia_id )," +
                    "KLIENT_ID number(4)" +
                    "CONSTRAINT wypozyczenia_kl_nn NOT NULL," +
                    "CONSTRAINT wypozyczenia _fr_kl FOREIGN KEY ( KLIENT_ID ) REFERENCES KLIENCI_POLKA ( klient_ID )," +
                    "PRZEDMIOT_ID number(4)" +
                    "CONSTRAINT wypozyczenia_pr_nn NOT NULL," +
                    "CONSTRAINT polka_fr_pr FOREIGN KEY ( PRZEDMIOT_ID  ) REFERENCES PRZEDMIOTY ( przedmiot_ID )" +
                    "DATA TYPE DATE" +
                    ")";

            Statement stmt = connection.createStatement();
//            stmt.executeUpdate(sql0);
//            stmt.executeUpdate(sql01);
//            stmt.executeUpdate(sql02);
//            stmt.executeUpdate(sql03);
//            stmt.executeUpdate(sql1);
//            stmt.executeUpdate(sql2);
//            stmt.executeUpdate(sql3);
            stmt.executeUpdate(sql4);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}