import java.io.*;
import java.util.Scanner;
import java.sql.*;

class database {
	Connection con;
	Statement st;
	ResultSet res;

	database() {
		try {

			/* Connect database with JDBC and MySql bridge connection */
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcmysql", user, pass);
		}
		catch(Exception ex) {
			System.out.println(ex);
		}
	}

	void insert(String name, String ph) throws Exception {

		/* Insert record into the database */
		String sql="INSERT INTO friend (f_name, f_ph) VAlUES('"+name+"','"+ph+"')";
		st = con.createStatement();
		st.execute(sql);
		System.out.println("Data Inserted Successfully!");
		st.close();
	}

	void select(String name) throws Exception {

		/* Fetch record from database */
		String sql="SELECT * FROM friend WHERE f_name ='"+name+"'";
		st = con.createStatement();
		res = st.executeQuery(sql);
		boolean rec = res.next();
		while(rec) {
			System.out.println("Name: "+res.getString("f_name"));
			System.out.println("Phone: "+res.getString("f_ph"));
			rec=res.next();
		}
		res.close();
		st.close();
	}

	void select() throws Exception {

		/* Fetch records from database */
		String sql="SELECT * FROM friend";
		st = con.createStatement();
		res = st.executeQuery(sql);
		boolean rec = res.next();
		while(rec) {
			System.out.println("Name: "+res.getString("f_name"));
			System.out.println("Phone: "+res.getString("f_ph"));
			rec=res.next();
		}
		res.close();
		st.close();
	}

	void update(String name, String ph) throws Exception {

		/* Update record in the database */
		String sql="UPDATA friend SET f_ph = '"+ph+"' WHERE f_name = '"+name+"'";
		st = con.createStatement();
		st.execute(sql);
		System.out.println("Data Updated Successfully!");
		st.close();
	}

	void delete(String name) throws Exception {

		/* Delete record from database */
		String sql="DELETE FROM friend WHERE f_name = '"+name+"'";
		st = con.createStatement();
		st.execute(sql);
		System.out.println("Data Deleted Successfully!");
		st.close();
	}
}

class jdbcmysql {
	public static void main(String args[ ]) throws Exception {
		database db = new database();
		Scanner scan = new Scanner(System.in);
		String name, ph;
		int opt = 0;
		while(opt != 6) {
			System.out.println("1. Insert 2. Select by Name 3. Select All 4. Update 5. Delete 6. Exit");
			System.out.println("Enter your choice:");
			opt = Integer.parseInt(scan.nextLine());
			switch(opt) {
				case 1:
					System.out.println("Please Enter Name:");
					name = scan.nextLine();
					System.out.println("Please Enter Phone:");
					ph = scan.nextLine();
					db.insert(name, ph);
					break;
				case 2:
					System.out.println("Please Enter the Name:");
					db.select(scan.nextLine());
					break;
				case 3:
					db.select();
					break;
				case 4:
					System.out.println("Please Enter Name which you want to update:");
					name = scan.nextLine();
					System.out.println("Please Enter new Phone no:");
					ph = scan.nextLine();
					db.update(name, ph);
					break;
				case 5:
					System.out.println("Please Enter Name which you want to delete:");
					name = scan.nextLine();
					db.delete(name);
					break;
				case 6:
					System.out.println("Bye Bye!");
					db.con.close();
					System.exit(0);
					break;
				default:
					System.out.println("Invalid choice!");
			}
		}
	}
}


          /****** Output ******/
          
1. Insert 2. Select by Name 3. Select All 4. Update 5. Delete 6. Exit

1

Please Enter Name:

ABC

Please Enter Phone:

1234

Data Inserted Successfully!

1. Insert 2. Select by Name 3. Select All 4. Update 5. Delete 6. Exit

1

Please Enter Name:

DEF

Please Enter Phone:

5678

1. Insert 2. Select by Name 3. Select All 4. Update 5. Delete 6. Exit

2

Please Enter the Name:

ABC

Name: ABC

Phone: 1234

1. Insert 2. Select by Name 3. Select All 4. Update 5. Delete 6. Exit

3

Name: ABC

Phone: 1234

Name: DEF

Phone: 5678

1. Insert 2. Select by Name 3. Select All 4. Update 5. Delete 6. Exit

4

Please Enter Name which you want to update:

ABC

Please Enter new Phone no:

4321

Data Updated Successfully!

1. Insert 2. Select by Name 3. Select All 4. Update 5. Delete 6. Exit

5

Please Enter Name which you want to delete:

ABC

Data Deleted Successfully!

1. Insert 2. Select by Name 3. Select All 4. Update 5. Delete 6. Exit

6

Bye Bye!