import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.Vector;
import javax.swing.event.TableModelEvent;
import javax.swing.table.*;


public class wew extends JFrame implements ActionListener {



	ResultSet rs;
	static String url = "jdbc:odbc:first";
	private Connection con;

	JFrame jframe = new JFrame("My First JDBC Program");
	JScrollPane scrollPane = new JScrollPane();
	JPanel panel=new JPanel();


	JTable table = new JTable();

	JTextField txtName = new JTextField(6);
	JTextField txtAge = new JTextField(3);
	JTextField txtSubj = new JTextField(10);
	JTextField txtRec = new JTextField(3);


	JLabel lblName = new JLabel("Enter your Name:");
	JLabel lblAge = new JLabel("Enter your Age:");
	JLabel lblSubj = new JLabel("Enter your Favorite Subject:");
	JLabel lblRec = new JLabel("Record ID: ");

	JButton btnAdd = new JButton("Add");
	JButton btnSave = new JButton("Save");
	JButton btnClear = new JButton("Clear");
	JButton btnEdit = new JButton("Edit");
	JButton btnUpdate = new JButton("Update");
	JButton btnDelete = new JButton("Delete");
	JButton btnRefresh = new JButton("Refresh");






	public wew() throws SQLException

	{

		FlowLayout fl = new FlowLayout();
		jframe.setLayout(fl);


		jframe.add(lblRec);
		jframe.add(txtRec);
		jframe.add(lblName);
		jframe.add(txtName);
		jframe.add(lblAge);
		jframe.add(txtAge);
		jframe.add(lblSubj);
		jframe.add(txtSubj);

		jframe.add(btnAdd);
		jframe.add(btnClear);
		jframe.add(btnSave);
		jframe.add(btnEdit);
		jframe.add(btnUpdate);
		jframe.add(btnDelete);
		jframe.add(btnRefresh);


		jframe.add(panel);
		jframe.add(scrollPane);






		btnAdd.addActionListener(this);
		btnClear.addActionListener(this);
		btnSave.addActionListener(this);
		btnEdit.addActionListener(this);
		btnUpdate.addActionListener(this);
		btnDelete.addActionListener(this);
		btnRefresh.addActionListener(this);




		jframe.setSize(650,235);


		txtName.setEditable(false);
		txtAge.setEditable(false);
		txtSubj.setEditable(false);
		lblRec.setVisible(false);
		txtRec.setVisible(false);

		btnUpdate.setEnabled(false);
		btnSave.setEnabled(false);
		btnDelete.setEnabled(false);



		//set the Connection to database
		getConnection();
		FillTable();

	}

	public void actionPerformed (ActionEvent ae)
	{
		if (ae.getSource() == btnAdd)
		{
			txtName.setEditable(true);
			txtAge.setEditable(true);
			txtSubj.setEditable(true);
			txtName.grabFocus();

			btnSave.setEnabled(true);
		}

		else if (ae.getSource() == btnClear)
		{
			txtName.setText(null);
			txtAge.setText(null);
			txtSubj.setText(null);
			txtName.grabFocus();
		}

		else if ( ae.getSource() == btnSave)
		{
			try {
				Save();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		else if (ae.getSource() == btnEdit)
		{
			try {
				Edit();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		else if (ae.getSource() == btnUpdate)
		{
			try {
				Update();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		else if (ae.getSource() == btnDelete)
		{
			try {
				Delete();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		else if (ae.getSource() == btnRefresh)
		{
			table.repaint();



		}


	}


	//		 code
	public void Save() throws SQLException
	{

		String Sname = txtName.getText();
		String Sage = txtAge.getText();
		String Ssubject = txtSubj.getText();

		String sql = "INSERT INTO Table1 (Name, Age,Subject)"
			+ " VALUES ('" + Sname + "','"+ Sage +"','" + Ssubject +"')";

		PreparedStatement ps = con.prepareStatement(sql);

		//create a statement for the INSERT Query
		ps.executeUpdate();

		JOptionPane.showMessageDialog(null,"Record successfully added.");


		txtName.setText(null);
		txtAge.setText(null);
		txtSubj.setText(null);

		txtName.setEditable(false);
		txtAge.setEditable(false);
		txtSubj.setEditable(false);

		btnSave.setEnabled(false);


	}

	//edit code
	public void Edit() throws SQLException
	{


		//SEARCH ID number ONLY
		String input = JOptionPane.showInputDialog("Enter ID Number");


		//Query for SEARCH
		String sql = "SELECT * from Table1 where [ID] = " + input + "";



		Statement s = con.createStatement();

		ResultSet rs = s.executeQuery(sql);// get any ResultSet that came from our query


		rs.next();

		txtRec.setText(rs.getString(1));
		txtName.setText(rs.getString(2));
		txtAge.setText(rs.getString(3));
		txtSubj.setText(rs.getString(4));

		txtName.setEditable(true);
		txtAge.setEditable(true);
		txtSubj.setEditable(true);
		txtName.grabFocus();

		btnUpdate.setEnabled(true);
		btnDelete.setEnabled(true);



	}

	public void Update()throws SQLException
	{

		JTable table = new JTable();
		Statement s = con.createStatement();

		String ID = txtRec.getText();
		String Sname = txtName.getText();
		String Sage = txtAge.getText();
		String Ssubj = txtSubj.getText();

		int col1=table.getSelectedColumn();
		int row1=table.getSelectedRow();



		PreparedStatement ps = con.prepareStatement("UPDATE Table1 SET Name='" + Sname + "',Age='" + Sage + "',Subject ='" + Ssubj  + "' where ID =" + ID + "");

		//String sql = "UPDATE Table1 SET ID = "+ ID + " , Name='" + Sname + "',Age='" + Sage + "',Subject ='" + Ssubj  + "' where ID =" + ID + "";



       ps.executeUpdate();





		JOptionPane.showMessageDialog(null,"Record Successfully updated!.");
		con.close();

		txtName.setEditable(false);
		txtAge.setEditable(false);
		txtSubj.setEditable(false);
		btnUpdate.setEnabled(false);




	}

	public void Delete()throws SQLException
	{

		Statement s = con.createStatement();
		String ID = txtRec.getText();


		String sql = "DELETE FROM Table1 WHERE ID =" + ID + "";
		s.execute(sql);// get any ResultSet that came from our query


		JOptionPane.showMessageDialog(null,"Record Successfully deleted!.");

		txtName.setText(null);
		txtAge.setText(null);
		txtSubj.setText(null);


	}
	public void FillTable()throws SQLException{


		Vector<String> columnNames = new Vector<String>();
		Vector<Vector<Object>> data = new Vector<Vector<Object>>();
		JPanel panel=new JPanel();

		try {

			String sql = "Select * from Table1";
			Statement stmt = con.createStatement();

			ResultSet rs = stmt.executeQuery( sql );

			ResultSetMetaData md = rs.getMetaData();

			int columns = md.getColumnCount();

			for (int i = 1; i <= columns; i++) {
			columnNames.addElement( md.getColumnName(i) );
			}
			while (rs.next()) {
			Vector<Object> row = new Vector<Object>(columns);
			for (int i = 1; i <= columns; i++){
			row.addElement( rs.getObject(i) );
			}
			data.addElement( row );
			}
			rs.close();
			stmt.close();
			}
			catch(Exception e){
			System.out.println(e);
			}
			JTable table = new JTable(data, columnNames);
			TableColumn col;





			for (int i = 0; i < table.getColumnCount(); i++) {
			col = table.getColumnModel().getColumn(i);
			col.setMaxWidth(250);
			}

	        JScrollPane scrollPane = new JScrollPane( table );
			panel.add( scrollPane );
			jframe.add(panel);
			jframe.setVisible(true);



	}





	//Connection Code
	public Connection getConnection(){

		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}

		try {
			//single program
			//con = DriverManager.getConnection(url);
			//for jar file
			con = DriverManager.getConnection("jdbc:odbc:;DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=C:\\Users\\Gerald\\Desktop\\Database3.accdb");
		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}

		return con;


	}


	public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub

		wew  jdbc = new wew();


	}

}