import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JButton;
import javax.swing.JTextArea;
import javax.swing.JPanel;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.awt.event.ActionEvent;
import javax.swing.JTextField;

public class Crud {

	private JFrame frame;
	Connection conn = null;
	private JTextField textFname;
	private JTextField textLname;
	private JTextField textAge;
	private JTextField textSalary;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					Crud window = new Crud();
					window.frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the application.
	 */
	public Crud() {
		initialize();
		conn = SqlConnect.dbConnect();
	}

	/**
	 * Initialize the contents of the frame.
	 */
	private void initialize() {
		frame = new JFrame();
		frame.setBounds(100, 100, 795, 594);
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.getContentPane().setLayout(null);

		JLabel lblDatabaseCrudOperation = new JLabel("Database CRUD Operation Demo");
		lblDatabaseCrudOperation.setBounds(195, 16, 420, 26);
		frame.getContentPane().add(lblDatabaseCrudOperation);

		JTextArea textArea = new JTextArea();
		textArea.setBounds(0, 0, 304, 359);

		JTextArea textArea_1 = new JTextArea();
		textArea_1.setBounds(508, 119, 240, 366);
		frame.getContentPane().add(textArea_1);

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

				try {

					String query = "Select * from Employee";
					PreparedStatement pst = conn.prepareStatement(query);

					ResultSet rs = pst.executeQuery();
					String display = "";

					while(rs.next()) {
						display += rs.getString("Fname")+ " ";
						display += rs.getString("Lname")+ " ";

						Integer age = rs.getInt("Age");
						display += rs.getInt("Age")+ " ";

						Double sal = rs.getDouble("Salary");
						display += sal.toString()+ " ";
						display += "\n";
						//JOptionPane.showMessageDialog(null,  display); //to display every new entry
					}//end of while


					textArea_1.setText(display);

				} catch (Exception e) {
					JOptionPane.showMessageDialog(null,  "Error reading Person Table");
				}

			}
		});
		btnLoad.setBounds(607, 63, 141, 35);
		frame.getContentPane().add(btnLoad);

		JLabel lblNewLabel = new JLabel("FirstName:");
		lblNewLabel.setBounds(21, 88, 122, 26);
		frame.getContentPane().add(lblNewLabel);

		JLabel lblLNewLabel_1 = new JLabel("LastName:");
		lblLNewLabel_1.setBounds(21, 151, 122, 26);
		frame.getContentPane().add(lblLNewLabel_1);

		JLabel lblNewLabel_2 = new JLabel("Age:");
		lblNewLabel_2.setBounds(21, 221, 122, 26);
		frame.getContentPane().add(lblNewLabel_2);

		JLabel lblNewLabel_3 = new JLabel("Salary:");
		lblNewLabel_3.setBounds(21, 295, 122, 26);
		frame.getContentPane().add(lblNewLabel_3);

		textFname = new JTextField();
		textFname.setBounds(146, 85, 186, 32);
		frame.getContentPane().add(textFname);
		textFname.setColumns(10);

		textLname = new JTextField();
		textLname.setBounds(146, 148, 186, 32);
		frame.getContentPane().add(textLname);
		textLname.setColumns(10);

		textAge = new JTextField();
		textAge.setBounds(146, 218, 186, 32);
		frame.getContentPane().add(textAge);
		textAge.setColumns(10);

		textSalary = new JTextField();
		textSalary.setBounds(146, 292, 186, 32);
		frame.getContentPane().add(textSalary);
		textSalary.setColumns(10);

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

				textArea_1.setText("");
				textFname.setText("");
				textLname.setText("");
				textAge.setText("");
				textSalary.setText("");

			}
		});
		btnClear.setBounds(21, 394, 141, 35);
		frame.getContentPane().add(btnClear);

		JButton btnFind = new JButton("Find");
		btnFind.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {

				String query = "select * from Employee where Fname = ? and Lname = ?";
				try {
					PreparedStatement pst = conn.prepareStatement(query);

					pst.setString(1,  textFname.getText()); //first name
					pst.setString(2,  textLname.getText()); //last name

					ResultSet rs = pst.executeQuery();
					int count = 0;

					rs.next();
					count ++;

					if(count == 1) {

						Integer iage = rs.getInt("Age");
						Double dsal = rs.getDouble("Salary");
						textAge.setText(iage.toString());
						textSalary.setText(dsal.toString());
					}
					else JOptionPane.showMessageDialog(null,  "Invalid First/Last name");

				} catch (SQLException e1) {

					e1.printStackTrace();
				}

			}
		});
		btnFind.setBounds(191, 394, 141, 35);
		frame.getContentPane().add(btnFind);

		JButton btnInsert = new JButton("Insert");
		btnInsert.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {

				String query = "Insert into Employee (Fname, Lname, Age, Salary) Values (?,?,?,?)";

				try {
					PreparedStatement pst = conn.prepareStatement(query);
					pst.setString(1, textFname.getText());
					pst.setString(2, textLname.getText());

					String sage = textAge.getText();
					String ssal = textSalary.getText();

					int iage = Integer.parseInt(sage);
					double dsal = Double.parseDouble(ssal);

					pst.setInt(3, iage);
					pst.setDouble(4, dsal);

					pst.execute();

					JOptionPane.showMessageDialog(null,  "Record added to Employee table");

				} catch (Exception e2) {
					JOptionPane.showMessageDialog(null,  "Error Inserting record to Employee table");
					//JOptionPane.showMessageDialog(null,  e2.getMessage());

				}


			}
		});
		btnInsert.setBounds(353, 394, 141, 35);
		frame.getContentPane().add(btnInsert);

		JButton btnDelete = new JButton("Delete");
		btnDelete.setBounds(21, 450, 141, 35);
		frame.getContentPane().add(btnDelete);

		JButton btnUpdate = new JButton("Update");
		btnUpdate.setBounds(191, 450, 141, 35);
		frame.getContentPane().add(btnUpdate);

	}
}