import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
import java.util.concurrent.ExecutionException;
public class StudentImplementation {
//method to add a record
void add() {
try {
//establishing connection with database
Connection con = ConnectionProvider.getConnection();
//getting scanner to take input from user
Scanner sc = new Scanner(System.in);
System.out.println("Enter the Roll No.");
int rollNo = sc.nextInt();
Boolean status = search(rollNo);
if (status == false) {
System.out.println("Enter First Name");
String first_Name = sc.next();
System.out.println("Enter Last Name");
String last_Name = sc.next();
System.out.println("Enter Course Name");
String course = sc.next();
System.out.println("Enter fee");
int fee = sc.nextInt();
PreparedStatement ps = con.prepareStatement(
"Insert into Student(RollNo,FirstName,LastName,Course,Fee)values(?,?,?,?,?)"
);
ps.setInt(1, rollNo);
ps.setString(2, first_Name);
ps.setString(3, last_Name);
ps.setString(4, course);
ps.setInt(5, fee);
ps.executeUpdate();
} else {
System.out.println("Roll No. already exists");
}
} catch (Exception e) {
System.out.println(e);
}
}
//method to search a record
Boolean search(int rollNo) {
try {
Connection con = ConnectionProvider.getConnection();
PreparedStatement ps = con.prepareStatement(
"select *from student where RollNo=?"
);
ps.setInt(1, rollNo);
ResultSet rs = ps.executeQuery();
//System.out.println(rs.next());
return rs.next();
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
//method to display all records in the table
void display() {
try {
Connection con = ConnectionProvider.getConnection();
PreparedStatement ps = con.prepareStatement("select * from student");
ResultSet rs = ps.executeQuery();
System.out.println("Record is as follow:");
while (rs.next()) {
System.out.println(
"Roll No. " +
rs.getInt(1) +
" " +
"First Name " +
rs.getString(2) +
" " +
"Last Name :" +
rs.getString(3) +
" " +
"Course : " +
rs.getString(4) +
" " +
"Fee" +
rs.getInt(5)
);
}
} catch (Exception e) {
e.printStackTrace();
}
}
//method to delete a record
void delete(int rollNo) {
Boolean status = search(rollNo);
if (status) {
try {
Connection con = ConnectionProvider.getConnection();
PreparedStatement ps = con.prepareStatement(
"Delete from student where rollNo=?"
);
ps.setInt(1, rollNo);
ps.executeUpdate();
System.out.println(
"Record associated with RollNo. " +
rollNo +
" has been deleted Successfully"
);
} catch (Exception e) {
e.printStackTrace();
}
} else {
System.out.println("Roll No. does not exists");
}
}
//method to update the existing record
void Update(int rollNo) {
Boolean status = search(rollNo);
if (status) {
try {
//establishing connection with database
Connection con = ConnectionProvider.getConnection();
//getting scanner to take input from user
Scanner sc = new Scanner(System.in);
System.out.println("Enter First Name");
String first_Name = sc.next();
System.out.println("Enter Last Name");
String last_Name = sc.next();
System.out.println("Enter Course Name");
String course = sc.next();
System.out.println("Enter fee");
int fee = sc.nextInt();
PreparedStatement ps = con.prepareStatement(
"update student set FirstName=?,LastName=?,Course=?,Fee=? where RollNo=?"
);
ps.setString(1, first_Name);
ps.setString(2, last_Name);
ps.setString(3, course);
ps.setInt(4, fee);
ps.setInt(5, rollNo);
ps.executeUpdate();
System.out.println("Record updated Successfully");
} catch (Exception e) {
e.printStackTrace();
}
} else {
System.out.println("Roll No. does not exists");
}
}
//driver class
public static void main(String[] args) {
StudentImplementation stmp = new StudentImplementation();
Scanner sc = new Scanner(System.in);
int num;
while (true) {
System.out.println("Press 1 to add a record");
System.out.println("Press 2 to delete a record");
System.out.println("Press 3 to update an existing record");
System.out.println("Press 4 to display all the records available");
System.out.println("Press 5 to search a record in the given table");
System.out.println("Press 0 to exit");
num = sc.nextInt();
switch (num) {
case 0:
{
System.exit(0);
}
break;
case 1:
{
stmp.add();
}
break;
case 2:
{
System.out.println("Enter Roll No");
int rollNo = sc.nextInt();
stmp.delete(rollNo);
}
break;
case 3:
{
System.out.println("Enter Roll No");
int rollNo = sc.nextInt();
stmp.Update(rollNo);
}
break;
case 4:
{
stmp.display();
}
break;
case 5:
{
System.out.println("Enter Roll No");
int rollNo = sc.nextInt();
Boolean status = stmp.search(rollNo);
if (status) {
System.out.println("Record exist");
} else {
System.out.println("Record does not exist!");
}
}
break;
default:
{
System.out.println("Invalid Entry");
}
}
}
}
}