Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.util.*;
- import java.sql.*;
- public class StudentApplication
- {
- private static Scanner scanner;
- private static Connection conn = null;
- private static Statement stmt = null;
- private static Throwable e;
- public static boolean ConnectToDB() {
- try {
- Class.forName("com.mysql.jdbc.Driver");
- String url = "jdbc:mysql://localhost:3306/University";
- conn = DriverManager.getConnection(url);
- System.out.println("Connection Successful!");
- stmt = conn.createStatement();
- return true;
- }catch(Exception e){
- System.err.println(e.getMessage());
- }
- return false;
- }
- public static void main (String[] args) {
- boolean dbConnection = false;
- try{
- dbConnection = ConnectToDB();
- }
- catch(Exception e)
- {
- System.out.println("Sorry could not connect to DB.");
- return;
- }
- if(!dbConnection){
- System.out.println("Exiting System as unable to connect to DB");
- return;
- }
- int choice;
- scanner = new Scanner (System.in);
- choice = menu();
- while (choice != 7) {
- switch (choice) {
- case 1: choice1(); break;
- case 2: choice2(); break;
- case 3: choice3(); break;
- case 4: choice4(); break;
- case 5: choice5(); break;
- case 6: choice6(); break;
- default: break;
- }
- choice = menu();
- };
- }
- public static int menu() {
- int choice;
- System.out.println ("\n\n");
- System.out.println ("1. Add a new student to the system");
- System.out.println ("2. Display information about a student with a given student ID");
- System.out.println ("3. Change a student's telephone number");
- System.out.println ("4. List all the students in the system");
- System.out.println ("5. Delete a student from the system with a given Student ID");
- System.out.println ("6. Register a Student in a Course");
- System.out.println ("7. Exit");
- System.out.print ("\n\nPleasemake a selection: ");
- choice = scanner.nextInt();
- return choice;
- }
- //Add a new student to the Database
- public static void choice1() {
- String firstName, lastName;
- String phone;
- System.out.println ("Enter the student's first name, last name, and telephone number: ");
- firstName = scanner.next();
- lastName = scanner.next();
- phone = scanner.next();
- // create and insert Student in Student Table
- try{
- String sql = "SELECT MAX(StudentID) as maxID FROM Student";
- ResultSet rs = stmt.executeQuery(sql);
- int maxID = 0;
- while(rs.next())
- {
- maxID=rs.getInt("maxID");
- System.out.println(maxID);
- }
- int nextID = maxID + 10;
- //NOTE THE CONSTRUCTION OF SQL STATEMENT
- sql = "INSERT INTO STUDENT VALUES(" + nextID +
- ",'" + firstName + "','"+lastName+ "','"+phone +"')";
- System.out.println("INSERT STATEMENT IS :\n " + sql);
- stmt.execute(sql);
- System.out.println("New Student created with ID : "+ nextID);
- }
- catch(Exception e)
- {
- e.printStackTrace();
- System.out.println("Sorry unable to create Student");
- return;
- }
- }
- //Display information about a student with a given student ID
- public static void choice2() {
- int studentID;
- StudentTwo student;
- System.out.print("Please enter a student ID: ");
- studentID = scanner.nextInt();
- student = getStudent(studentID);
- if (student == null)
- System.out.println ("There is no student with this ID.");
- else
- System.out.println (student.toString());
- }
- //Change a student's telephone number
- public static void choice3() {
- int studentID;
- StudentTwo student;
- System.out.print("Please enter a student ID: ");
- studentID = scanner.nextInt();
- student = getStudent(studentID);
- if (student == null)
- System.out.println ("There is no student with this ID.");
- else {
- String newPhone;
- System.out.print ("Please enter the new telephone number: ");
- newPhone = scanner.next();
- //student.setPhone(newPhone);
- String sql = "UPDATE Student SET Phone = '"+newPhone+"' WHERE StudentID = "+ studentID;
- try{
- stmt.executeUpdate(sql);
- }
- catch(Exception e){
- e.printStackTrace();
- System.out.println("Sorry could not update the phone number");
- return;
- }
- System.out.println ("Student's telephone number has been updated.");
- }
- }
- //List all the students in the system
- public static void choice4() {
- StudentTwo st = new StudentTwo();
- try{
- String sql = "SELECT * FROM Student";
- ResultSet rs = stmt.executeQuery(sql);
- System.out.println("List of Students:");
- while(rs.next())
- {
- st.setID(rs.getInt("StudentID"));//get by column name
- st.setFirstName(rs.getString("FirstName"));
- st.setLastName(rs.getString(3));//get by column index
- st.setPhone(rs.getString(4));
- System.out.println(st.toString());
- }
- }
- catch(Exception e){
- e.printStackTrace();
- System.out.println("Sorry unable to fetch Students from DB");
- st = null;
- }
- }
- //Delete a Student from the DB
- public static void choice5() {
- int studentID;
- System.out.print("Please enter a student ID: ");
- studentID = scanner.nextInt();
- try{
- String sql = "DELETE FROM Student WHERE StudentID = "+ studentID;
- int res = stmt.executeUpdate(sql);
- System.out.println(res + " Student deleted");
- }
- catch(Exception e){
- e.printStackTrace();
- System.out.println("Sorry Unable to delete Student");
- }
- }
- //Register a Student in a Course
- public static void choice6() {
- int studentID;
- int courseID;
- System.out.print("Please enter a student ID: \n");
- studentID = scanner.nextInt();
- System.out.print("Please select a Course(Enter 1 or 2 or 3) : ");
- System.out.print("1. OOP with Java\n");
- System.out.print("2. Intro to C#\n");
- System.out.print("3. Introduction to JDBC\n");
- courseID = scanner.nextInt();
- if(courseID != 1 &&courseID != 2 &&courseID != 3)
- {
- System.out.println("Please enter correct choice");
- return;
- }
- try{
- String sql = "INSERT INTO Registration(StudentID, CourseID) VALUES("+ studentID+","+courseID+")";
- boolean res = stmt.execute(sql);
- System.out.println("Student Registered \n");
- }
- catch(Exception e){
- e.printStackTrace();
- System.out.println("Sorry Unable to register Student");
- }
- }
- public static StudentTwo getStudent(int studentID) {
- StudentTwo s = new StudentTwo();
- try{
- String sql = "SELECT * FROM Student WHERE StudentID = "+ studentID;
- ResultSet rs = stmt.executeQuery(sql);
- while(rs.next())
- {
- s.setID(rs.getInt("StudentID"));//get by column name
- s.setFirstName(rs.getString("FirstName"));
- s.setLastName(rs.getString(3));//get by column index
- s.setPhone(rs.getString(4));
- }
- }
- catch(Exception e){
- e.printStackTrace();
- System.out.println("Sorry unable to fetch Student from DB");
- s = null;
- }
- return s;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement