import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.*;
import javax.swing.event.ChangeEvent;
import javax.swing.event.ChangeListener;

import java.sql.*;

public class EmployeeEdit extends JPanel{
    JLabel labelCode = new JLabel("Employee Code");
    JLabel labelName = new JLabel("Employee Name");
    JLabel labelSalary = new JLabel("Employee Salary");
    JTextField textCode = new JTextField(10);
    JTextField textName = new JTextField(20);
    JTextField textSalary = new JTextField(10);
    JButton edit = new JButton("Edit");
    JButton refresh = new JButton("Refresh");
    ResultSet resultSet = null;
    Connection con = null;
    String tableName = "EmpTable";
    int NumRows = 0;
    int currentSpinVal;
    public EmployeeEdit(){

        con = null;
        try {
            con = getConnection();

            NumRows = countRows(con, tableName);
            System.out.println("rowCount=" + NumRows);
            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            resultSet = stmt.executeQuery("SELECT * FROM EmpTable");
            SpinnerModel model = new SpinnerNumberModel(1, 1, NumRows, 1);
            JSpinner box = new JSpinner(model);
            box.addChangeListener(new SpinnerListener());
            add(box);
            } catch (Exception e) {
                e.printStackTrace();
                System.exit(1);
            }
                add(labelCode);
                add(textCode);
                add(labelName);
                add(textName);
                add(labelSalary);
                add(textSalary);

                try {
                    resultSet.absolute(1);
                    textCode.setText(resultSet.getString(2));
                    textName.setText(resultSet.getString(3));
                    textSalary.setText(resultSet.getString(4));
                } catch (SQLException e2) {
                    e2.printStackTrace();
                }


                add(refresh);
                refresh.addActionListener(new ActionListener() {

                    public void actionPerformed(ActionEvent e)
                    {
                        try {
                            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
                            resultSet = stmt.executeQuery("SELECT * FROM EmpTable");
                            NumRows = countRows(con, tableName);
                            EmployeeEdit.this.revalidate();
                        } catch (SQLException e1) {
                            e1.printStackTrace();
                        }
                    }
                });
                add(edit);
                edit.addActionListener(new ActionListener() {

                    public void actionPerformed(ActionEvent e)
                    {
                        try {
                            resultSet.absolute(currentSpinVal);
                            resultSet.updateString(2, textCode.getText());
                            resultSet.updateString(3, textName.getText());
                            resultSet.updateString(4, textSalary.getText());
                            resultSet.updateRow();
                            System.out.println("Row " + currentSpinVal + " updated!");

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

      public static Connection getConnection() throws Exception {
            String url = "jdbc:mysql://mer.hosted.nfoservers.com:3306/";
            String dbName = "mer_junk";
            String driverName = "com.mysql.jdbc.Driver";
            String userName = "NO";
            String password = "BAD";
            System.out.println("Connected");
            Class.forName(driverName).newInstance();
            Connection con = DriverManager.getConnection(url+dbName, userName, password);

            return con;
      }

      public static int countRows(Connection con, String tableName) throws SQLException {
            Statement stmt = null;
            ResultSet rs = null;
            int rowCount = -1;
            try {
                stmt = con.createStatement();
                rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
                rs.next();
                rowCount = rs.getInt(1);
            } finally {
                rs.close();
                stmt.close();
            }
            return rowCount;
      }

      public class SpinnerListener implements ChangeListener{

        public void stateChanged(ChangeEvent e) {
            JSpinner spinner = (JSpinner) e.getSource();
            Object value = spinner.getValue();
            try {
                currentSpinVal = (Integer) value;
                resultSet.absolute((Integer) value);
                textCode.setText(resultSet.getString(2));
                textName.setText(resultSet.getString(3));
                textSalary.setText(resultSet.getString(4));
            } catch (SQLException e1) {
                e1.printStackTrace();
            }


        }

      }



}