import java.awt.FlowLayout;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.event.ListSelectionEvent;
import javax.swing.table.DefaultTableModel;
import javax.swing.JTable;
import javax.swing.JLabel;
import java.awt.Font;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.JTextField;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;

import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;

public class GUI extends JDialog {

    private JTextField txtID;
    private JTextField txtName;
    private JTextField txtDep;
    private JTextField txtGender;
    private JTextField txtPosition;
    private JTextField txtSalary;
    private JTable table;
    ResultSet rs;
    Connection conn;
    Statement statement;

    public GUI() throws SQLException {

        setBounds(100, 100, 671, 448);
        getContentPane().setLayout(null);
         try {
                // Set System L&F
                UIManager.setLookAndFeel(
                        UIManager.getSystemLookAndFeelClassName());
            } catch (UnsupportedLookAndFeelException | ClassNotFoundException | InstantiationException | IllegalAccessException ex) {
                System.out.println(ex.getMessage());
            }


        table = new JTable();
        JScrollPane scrollPane = new JScrollPane();
        scrollPane.setBounds(10, 11, 635, 205);
        getContentPane().add(scrollPane);

        scrollPane.setViewportView(table);

        String dbURl = "jdbc:mysql://localhost:3306/employees";
        try {
            conn = DriverManager.getConnection(dbURl, "root", "1234");
            if (conn != null) {
                System.out.println("Connected");

                String s1 = "SELECT * FROM employees";
                statement = conn.createStatement();

                rs = statement.executeQuery(s1);

                table.setModel(buildTableModel(rs));
                table.setBounds(10, 11, 634, 216);

            }

        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }

        table.getSelectionModel().addListSelectionListener((ListSelectionEvent event) -> {
            try {

                if (table.getSelectedRow() >= 0) {
                    Object employee_id = table.getValueAt(table.getSelectedRow(), 0);
                    Object full_name = table.getValueAt(table.getSelectedRow(), 1);
                    Object gender = table.getValueAt(table.getSelectedRow(), 2);
                    Object department = table.getValueAt(table.getSelectedRow(), 3);
                    Object position = table.getValueAt(table.getSelectedRow(), 4);
                    Object salary = table.getValueAt(table.getSelectedRow(), 5);

                    txtID.setText(employee_id.toString());
                    txtName.setText(full_name.toString());
                    txtGender.setText(gender.toString());
                    txtDep.setText(department.toString());
                    txtPosition.setText(position.toString());
                    txtSalary.setText(salary.toString());
                }
            } catch (Exception ex) {
                System.out.println(ex.getMessage());
            }
        });

        JPanel buttonPane = new JPanel();
        buttonPane.setBounds(0, 376, 655, 33);
        FlowLayout fl_buttonPane = new FlowLayout(FlowLayout.LEFT);
        fl_buttonPane.setHgap(10);
        buttonPane.setLayout(fl_buttonPane);
        getContentPane().add(buttonPane);

        JButton btnAdd = new JButton("ADD");
        btnAdd.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                String sql_stmt = "INSERT INTO employees (full_name,gender,department,position,salary)";
                sql_stmt += " VALUES ('" + txtName.getText() + "','" + txtGender.getText() + "','" + txtDep.getText()
                        + "','" + txtPosition.getText() + "','" + txtSalary.getText() + "')";

                try {

                    statement.executeUpdate(sql_stmt);
                    rs = statement.executeQuery("select * from employees");
                    table.setModel(buildTableModel(rs));

                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                }

                clearInputBoxes();
            }
        });
        buttonPane.add(btnAdd);

        JButton btnUpdate = new JButton("UPDATE");
        btnUpdate.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                String sql_stmt3 = "UPDATE employees SET full_name = '" + txtName.getText() + "'";
                sql_stmt3 += ",gender = '" + txtGender.getText() + "'";
                sql_stmt3 += ",department = '" + txtDep.getText() + "'";
                sql_stmt3 += ",position = '" + txtPosition.getText() + "'";
                sql_stmt3 += ",salary = '" + txtSalary.getText() + "'";
                sql_stmt3 += " WHERE employee_id = '" + txtID.getText() + "'";

                try {

                    statement.executeUpdate(sql_stmt3);
                    rs = statement.executeQuery("select * from employees");
                    table.setModel(buildTableModel(rs));

                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                }

                clearInputBoxes();
            }
        });
        btnUpdate.setActionCommand("OK");
        buttonPane.add(btnUpdate);
        getRootPane().setDefaultButton(btnUpdate);

        JButton btnDel = new JButton("DELETE");
        btnDel.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {

                String sql_stmt2 = "DELETE FROM employees WHERE employee_id = '" + txtID.getText() + "'";
                try {

                    statement.executeUpdate(sql_stmt2);
                    rs = statement.executeQuery("select * from employees");
                    table.setModel(buildTableModel(rs));

                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                }

                clearInputBoxes();
            }

        });

        btnDel.setActionCommand("Cancel");
        buttonPane.add(btnDel);

        JLabel lblID = new JLabel("ID");
        lblID.setFont(new Font("Verdana", Font.BOLD, 14));
        lblID.setBounds(26, 237, 79, 23);
        getContentPane().add(lblID);

        JLabel lblFullname = new JLabel("Fullname");
        lblFullname.setFont(new Font("Verdana", Font.BOLD, 14));
        lblFullname.setBounds(26, 271, 79, 23);
        getContentPane().add(lblFullname);

        JLabel lblGender = new JLabel("Gender");
        lblGender.setFont(new Font("Verdana", Font.BOLD, 14));
        lblGender.setBounds(340, 237, 79, 23);
        getContentPane().add(lblGender);

        JLabel lblPosition = new JLabel("Position");
        lblPosition.setFont(new Font("Verdana", Font.BOLD, 14));
        lblPosition.setBounds(340, 271, 79, 23);
        getContentPane().add(lblPosition);

        JLabel lblDep = new JLabel("DEP");
        lblDep.setFont(new Font("Verdana", Font.BOLD, 14));
        lblDep.setBounds(26, 305, 79, 23);
        getContentPane().add(lblDep);

        JLabel lblSalary = new JLabel("Salary");
        lblSalary.setFont(new Font("Verdana", Font.BOLD, 14));
        lblSalary.setBounds(340, 305, 79, 23);
        getContentPane().add(lblSalary);

        txtID = new JTextField();
        txtID.setBounds(115, 240, 215, 20);
        getContentPane().add(txtID);
        txtID.setColumns(10);

        txtName = new JTextField();
        txtName.setColumns(10);
        txtName.setBounds(115, 274, 215, 20);
        getContentPane().add(txtName);

        txtDep = new JTextField();
        txtDep.setColumns(10);
        txtDep.setBounds(115, 308, 215, 20);
        getContentPane().add(txtDep);

        txtGender = new JTextField();
        txtGender.setColumns(10);
        txtGender.setBounds(429, 240, 107, 20);
        getContentPane().add(txtGender);

        txtPosition = new JTextField();
        txtPosition.setColumns(10);
        txtPosition.setBounds(429, 274, 215, 20);
        getContentPane().add(txtPosition);

        txtSalary = new JTextField();
        txtSalary.setColumns(10);
        txtSalary.setBounds(429, 308, 215, 20);
        getContentPane().add(txtSalary);

    }

    public static DefaultTableModel buildTableModel(ResultSet rs) throws SQLException {

        ResultSetMetaData metaData = rs.getMetaData();

        // names of columns
        Vector<String> columnNames = new Vector<String>();

        int columnCount = metaData.getColumnCount();

        for (int column = 1; column <= columnCount; column++) {
            columnNames.add(metaData.getColumnName(column));

        }

        // data of the table
        Vector<Vector<Object>> data = new Vector<Vector<Object>>();

        while (rs.next()) {

            Vector<Object> vector = new Vector<Object>();

            for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
                vector.add(rs.getObject(columnIndex));
            }
            data.add(vector);
        }

        return new DefaultTableModel(data, columnNames);

    }

    private void clearInputBoxes() {
        txtID.setText("");
        txtName.setText("");
        txtGender.setText("");
        txtDep.setText("");
        txtPosition.setText("");
        txtSalary.setText("");
    }

    public static void main(String[] args) throws Exception {
        GUI g = new GUI();
        g.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
        g.setVisible(true);
    }
}