14

package application;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

public class DBUtil {

    // otvaranje konekcije pomocu Connection objekta

    private static java.sql.Connection con = null;
    private static String url = "jdbc:mysql://localhost/zaposleni"; // url
                                                                    // konekije
    private static String user = "root"; // user
    private static String password = ""; // pasvord

    // otvaranje konekcije

    public static void openConnection() throws SQLException {
        con = DriverManager.getConnection(url, user, password);
    }

    // zatvaranje konekcije
    public static void closeConnection() throws SQLException {
        con.close();
    }

    // kreiranje baze
    public static void createDatabase() throws SQLException {
        openConnection(); // otvaranje konekcije
        Statement sql = con.createStatement(); // kreiranje objekta Statement
        // SQL komanda za kreiranje baze
        sql.execute("create database if not exists zaposleni");
        closeConnection(); // zatvaranje konekcije
    }

    // kreiranje tabele Zaposleni u bazi
    public static void createTable() throws SQLException {
        openConnection(); // otvaranje konekcije
        Statement sql = con.createStatement(); // kreiranje objekta Statement
        // SQL komanda za kreiranje tabele
        // id,ime, prezime, godine starosti i zvanje.
        sql.execute("create table if not exists zaposleni( id int auto_increment, "
                + "ime varchar(20), prezime varchar(20),    "
                + "starost varchar(20)  ,zvanje varchar (20),primary key(id))");
        closeConnection(); // zatvaranje konekcije
    }

    // dodavanje objekta Zaposleni u tabelu
    public static void addZaposleni(Zaposleni zaposleni) throws SQLException {
        openConnection();
        // /id,ime, prezime, godine starosti i zvanje.
        PreparedStatement sql = con.prepareStatement(
                "INSERT INTO `zaposleni`( `ime`, `prezime`," + " `starost` ,`zvanje`) VALUES (?,?,?,?)");
        sql.setString(1, zaposleni.getIme());
        sql.setString(2, zaposleni.getPrezime());
        sql.setInt(3, zaposleni.getStarost());
        sql.setString(4, zaposleni.getZvanje());

        sql.execute(); // izvrsi

        closeConnection();
    }

    public static List<Zaposleni> getAll() throws SQLException {
        openConnection();
        Statement sql = con.createStatement();
        ResultSet rs = sql.executeQuery("select * from zaposleni order by id asc");
        List<Zaposleni> zaposleni = new ArrayList<>();

        while (rs.next()) {

            zaposleni.add(new Zaposleni(rs.getString(2), rs.getString(2), rs.getInt(4), rs.getString(5)));

        }

        closeConnection();
        return zaposleni;

    }

    public static ObservableList<Zaposleni> getAllForTable() throws SQLException {
        openConnection();
        Statement sql = con.createStatement();
        ResultSet rs = sql.executeQuery("select * from zaposleni");
        ObservableList<Zaposleni> zaposleni = FXCollections.observableArrayList();

        while (rs.next()) {

            zaposleni.add(new Zaposleni(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getString(5)));
        }

        closeConnection();
        return zaposleni;

    }

    // azuriranje podataka Zaposlenih u tabeli
    public static void editZaposleni(Zaposleni zaposleni) throws SQLException {
        openConnection();

        PreparedStatement sql = con.prepareStatement(
                "UPDATE `zaposleni` SET `ime`=?, `prezime` = ?," + " `starost` = ?,`zvanje` = ?, WHERE id =?");
        sql.setString(2, zaposleni.getIme());
        sql.setString(3, zaposleni.getPrezime());
        sql.setInt(4, zaposleni.getStarost());
        sql.setString(5, zaposleni.getZvanje());

        sql.execute(); // izvrsi

        closeConnection();
    }

    public static void deleteZaposleni(Zaposleni zaposleni) throws SQLException {
        openConnection();
        PreparedStatement sql = con.prepareStatement("DELETE FROM `zaposleni` WHERE id =? ");
        sql.setInt(1, zaposleni.getId());

        sql.execute();

        closeConnection();
    }

    public static int getNextId() throws SQLException {
        openConnection();
        Statement sql = con.createStatement();
        ResultSet rs = sql.executeQuery("select max(id) from zaposleni");
        int id = 0;
        while (rs.next()) {
            // 2,3,4,1
            id = rs.getInt(1);
        }

        closeConnection();
        return id + 1;

    }

}