package com.mycompany.lab_2_magazyn;

/*
 * 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.
 */

import java.awt.BorderLayout;
import java.awt.HeadlessException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Michu
 */
public class Baza {

    private static Connection con = null;

    public Baza() throws SQLException {
        try {
            // Laczenie z baza
            Class.forName("oracle.jdbc.OracleDriver");
            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "hr", "hr");
        } catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, e);
        }
    }




    public boolean Sprawdz_tabele() throws SQLException {
        DatabaseMetaData dbm = con.getMetaData();
        Statement stmt = con.createStatement();
        ResultSet rs = dbm.getTables(null, null, "TEST", null);
        if (rs.next()) {
            System.out.println("Table exists");
            con.close();
            return true;
        } else {
            System.out.println("Table does not exist");
            con.close();
            return false;
        }
    }

    public void Tworz_tabele() throws SQLException {
        try {
            boolean sprawdz = Sprawdz_tabele();
            if (sprawdz == true) {
                System.out.println("tabela istnieje");
            } else {
                PreparedStatement tworz = con.prepareStatement("CREATE TABLE TEST (NR_MAG INTEGER,"
                    + "NRKARTY CHAR(50),"
                    + "NR_ODPADU INTEGER,"
                    + "NR_KLIENTA INTEGER,"
                    + "FIRMA INTEGER,"
                    + "JEDN CHAR(10),"
                    + "MASA FLOAT,"
                    + "DATAD CHAR(30),"
                    + "PRIMARY KEY (NR_MAG))");

                tworz.executeUpdate();
                con.close();
            }
        } catch (HeadlessException e) {
            JOptionPane.showMessageDialog(null, e);
        }
    }

    public void Wstaw_dane_do_tabeli(int NR_MAG,String NRKARTY,int NR_ODPADU,int NR_KLIENTA,int FIRMA,String JEDN,double MASA,String DATAD) throws SQLException {

        try {
            PreparedStatement wstaw = con.prepareStatement("INSERT INTO test (NR_MAG, NRKARTY, NR_ODPADU, NR_KLIENTA, FIRMA, JEDN, MASA, DATAD) VALUES (?,?,?,?,?,?,?,?)");
            wstaw.setInt(1, NR_MAG);
            wstaw.setString(2, NRKARTY);
            wstaw.setInt(3, NR_ODPADU); // typy jak w create
            wstaw.setInt(4, NR_KLIENTA);
            wstaw.setInt(5, FIRMA);
            wstaw.setString(6, JEDN);
            wstaw.setDouble(7, MASA);
            wstaw.setString(8, DATAD);


            wstaw.executeUpdate();
            con.close();
        } catch (HeadlessException e) {
            JOptionPane.showMessageDialog(null, e);
        }
    }

    public void Odczytaj_z_tabeli(String tytul) throws SQLException {
        try (Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT * FROM test")) {

            ResultSetMetaData rsmt = rs.getMetaData();
            int c = rsmt.getColumnCount();
            Vector column = new Vector(c);
            for (int i = 1; i <= c; i++) {
                column.add(rsmt.getColumnName(i));
            }
            Vector data = new Vector();
            Vector row = new Vector();
            while (rs.next()) {
                row = new Vector(c);
                for (int i = 1; i <= c; i++) {
                    row.add(rs.getString(i));
                }
                data.add(row);
            }

            JTable table = new JTable(data, column);
            JFrame frame = new JFrame(tytul);
            frame.setSize(500, 200);
            frame.setLocationRelativeTo(null);
            //frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
            JPanel panel = new JPanel();
            JTable table1 = new JTable(data, column);
            JScrollPane jsp = new JScrollPane(table1);
            panel.setLayout(new BorderLayout());
            panel.add(jsp, BorderLayout.CENTER);
            frame.setContentPane(panel);
            frame.setVisible(true);
            con.close();
        }
    }

    public void Usun_dane_z_tabeli(int NR_MAG) throws SQLException {
        try {
            PreparedStatement wstaw = con.prepareStatement("DELETE FROM test WHERE NR_MAG = ?");
            wstaw.setInt(1, NR_MAG);

            wstaw.executeUpdate();
            con.close();
        } catch (HeadlessException e) {
            JOptionPane.showMessageDialog(null, e);
        }
    }

    public void Aktualizuj_dane_z_tabeli(int NR_MAG,String NRKARTY,int NR_ODPADU,int NR_KLIENTA,int FIRMA,String JEDN,double MASA,String DATAD) throws SQLException {
        con.setAutoCommit(false);//commit trasaction manually
        try {
            PreparedStatement wstaw = con.prepareStatement("UPDATE test SET NRKARTY = ?, NR_ODPADU = ?, NR_KLIENTA = ?, FIRMA = ?, JEDN = ?, MASA = ?, DATAD = ? WHERE NR_MAG = ?");   ;

            wstaw.setString(1, NRKARTY);
            wstaw.setInt(2, NR_ODPADU); // typy jak w create
            wstaw.setInt(3, NR_KLIENTA);
            wstaw.setInt(4, FIRMA);
            wstaw.setString(5, JEDN);
            wstaw.setDouble(6, MASA);
            wstaw.setString(7, DATAD);
            wstaw.setInt(8, NR_MAG);
            wstaw.addBatch();
            wstaw.executeBatch();
            con.commit();
            con.close();

            //wstaw.executeUpdate();
        } catch (HeadlessException e) {
            JOptionPane.showMessageDialog(null, e);
        }
    }
}