*Q5*
import java.sql.*;
import java.util.Scanner;
public class Test {
public static void main(String[] args)
char repeat = 'Y';
Scanner input = new Scanner(System.in);
Connection con = null;
Statement st = null;
ResultSet rs = null;
int id;
String name,address;
int option;
try
con = DriverManager.getConnection("jdbc:ucanaccess://E:/Student Information System.accdb");
st = con.createStatement();
while(repeat == 'Y' || repeat == 'y')
System.out.println("1. Press 1 to View all records");
System.out.println("2. Press 2 to Delete a record");
System.out.println("3. Press 3 to Update a record");
System.out.println("4. Press 4 to Insert new record");
System.out.println("5. Press 5 to Search records by ID");
System.out.println("6. Press 6 to Exit");
option = input.nextInt();
switch(option)
case 1:
rs = st.executeQuery("SELECT * FROM STUDENT");
while(rs.next())
System.out.println(rs.getInt("ID") + "\t" + rs.getString("student_name") + "\t" +
rs.getString("student_address"));
break;
case 2:
System.out.println("Enter the value of student ID to delete its record");
id = input.nextInt();
int r = st.executeUpdate("DELETE FROM STUDENT WHERE ID = " + id);
if (r > 0)
System.out.println("Records deleted successfully");
else
System.out.println("Records did not delete successfully");
break;
case 3:
System.out.println("Enter the updated value of Student Name to update");
name = input.next();
System.out.println("Enter the updated value of Student Address to update");
address = input.next();
System.out.println("Enter the value of student ID to update its record");
id = input.nextInt();
r = st.executeUpdate("UPDATE STUDENT SET STUDENT_NAME = '" + name+"',student_address =
'"+address+"' WHERE ID = " + id);
if (r > 0)
System.out.println("Records Updated successfully");
else
System.out.println("Records did not update successfully");
break;
case 4:
System.out.println("Enter the value of ID for the new student");
id = input.nextInt();
System.out.println("Enter the value of Student Name for the new student");
name = input.next();
System.out.println("Enter the value of Student Address for the new student");
address = input.next();
r = st.executeUpdate("INSERT INTO STUDENT VALUES("+id+",'"+name+"','"+address+"')");
if (r > 0)
System.out.println("Records inserted successfully");
else
System.out.println("Records did not insert successfully");
break;
case 5:
System.out.println("Enter the ID to search record");
id = input.nextInt();
rs = st.executeQuery("SELECT * FROM STUDENT WHERE ID = " + id);
while(rs.next())
System.out.println(rs.getInt("ID") + "\t" + rs.getString("student_name") + "\t" +
rs.getString("student_address"));
break;
case 6:
System.exit(0);
break;
default:
System.out.println("Enter values between 1 and 6 only");
System.out.println("Do you wish to repeat? Press Y for Yes");
repeat = input.next().charAt(0);
}
catch(SQLException sqe)
System.out.println(sqe);
finally
try
if(con!=null)
con.close();
System.out.println("Connection was closed successfully");
else
System.out.println("Connection was not established. I can not close the connection");
catch(SQLException sqe)
System.out.println(sqe);
