hw5/src/main/java/Main.java - hw_4 - Elyumusa/SberJavaCourseBeginner

hw5/src/main/java/Main.java
315 строк16 KB
Elyumusa
Домашнее задание 5 выполнено
24 апр 2025, 01:37
24 апр 2025, 01:37648799b
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Main {


    public static void main(String[] args)  {
        String url = "jdbc:postgresql://localhost:5432/university";
        String user = "postgres";
        String password = "postgres";
        try{
        Connection con = DriverManager.getConnection(url, user, password);
        Scanner input = new Scanner(System.in);
        while(true){
            int userInput = 0;
            System.out.println("Enter 1 to add a teacher");
            System.out.println("Enter 2 to add a student");
            System.out.println("Enter 3 to add a course");
            System.out.println("Enter 4 to enroll a student in a course");
            System.out.println("Enter 5 to view Teachers Table");
            System.out.println("Enter 6 to view Students Table");
            System.out.println("Enter 7 to view Courses Table");
            System.out.println("Enter 8 to view Courses and Student Count");
            System.out.println("Enter 9 to update a student's record");
            System.out.println("Enter 10 to update a Teacher's record");
            System.out.println("Enter 11 to update a Course record");
            System.out.println("Enter 12 to delete a Student");
            System.out.println("Enter 13 to delete a Teacher");
            System.out.println("Enter 14 to delete a Course");
            System.out.println("Enter 15 to exit");
            userInput = input.nextInt();
            input.nextLine();
            switch (userInput){
                case 1:
                    insertIntoTeachers(input, con);
                    break;
                case 2:
                    insertIntoStudents(input, con);
                    break;
                case 3:
                    insertIntoCourses(input, con);
                    break;
                case 4:
                    enrollStudentIntoCourse(input, con);
                    break;
                case 5:
                    viewAllTeachers(con);
                    break;
                case 6:
                    viewAllStudents(con);
                    break;
                case 7:
                    viewAllCourses(con);
                    break;
                case 8:
                    viewStudentsCoursesTeachers(con);
                    break;
                case 9:
                    updateStudentRecord(input, con);
                    break;
                case 10:
                    updateTeacherRecord(input, con);
                    break;
                case 11:
                    updateCourseRecord(input, con);
                    break;
                case 12:
                    deleteStudentRecord(input, con);
                    break;
                case 13:
                    deleteTeacherRecord(input, con);
                    break;
                case 14:
                    deleteCourseRecord(input, con);
                    break;
                case 15:
                    System.exit(0);
                    break;
            }
        }
    }catch(Exception e){
            System.out.println("Error Occurred: "+ e.getMessage());
        }}

    private static void deleteStudentRecord(Scanner input, Connection con) {
        System.out.println("Enter student's id");
        int studentId = input.nextInt();
        String query= String.format("delete from students where student_id = '%s'",studentId);
        StudentsRepository.createUpdateDeleteFromStudentsTable(con, query,"Student deleted successfully!");
    }


    private static void deleteTeacherRecord(Scanner input, Connection con) {
        System.out.println("Enter teacher's id");
        int teacherId = input.nextInt();
        String query= String.format("delete from teachers where teacher_id = '%s'",teacherId);
        TeachersRepository.insertUpdateDeleteFromTeachersTable(con, query,"Teacher deleted successfully!");
    }

    private static void deleteCourseRecord(Scanner input, Connection con) {
        System.out.println("Enter course's id");
        int courseId = input.nextInt();
        String query= String.format("delete from courses where course_id = '%s'",courseId);
        CoursesRepository.insertUpdateDeleteFromCourses(con, query,"Course deleted successfully!");
    }
    private static void insertIntoTeachers(Scanner input, Connection con) {
        System.out.println("Enter teacher's full name");
        String fullName= input.nextLine();
        System.out.println("Enter teacher's email");
        String email= input.nextLine();
        System.out.println("Enter teacher's hire date(YYYY-MM-DD)");
        String hireDate= input.nextLine();
        String query= String.format("insert into teachers(full_name, email, hire_date) values('%s','%s','%s')",fullName,email,hireDate);
        TeachersRepository.insertUpdateDeleteFromTeachersTable(con, query,"Teachers table successfully added to!");
    }

    private static void insertIntoStudents(Scanner input, Connection con) {
        System.out.println("Enter student's full name");
        String studentFullName = input.nextLine();
        System.out.println("Enter student's enrollment date into the university in the form of yyyy-mm-dd");
        String enrollmentDate= input.nextLine();
        System.out.println("Enter student's program of study");
        String programOfStudy= input.nextLine();
        System.out.println("Enter student's date of birth");
        String dob= input.nextLine();
        String query2= String.format("insert into students(full_name, enrollment_date,program_of_study,DOB) values('%s','%s','%s','%s')", studentFullName,enrollmentDate,programOfStudy,dob);
        StudentsRepository.createUpdateDeleteFromStudentsTable(con, query2,"Students table successfully added to!");
    }

    private static void insertIntoCourses(Scanner input, Connection con) {
        System.out.println("Enter course's name");
        String courseName= input.nextLine();
        System.out.println("Enter course's description");
        String description= input.nextLine();
        System.out.println("Enter course's teacher id");
        int teacherId= input.nextInt();
        String query3= String.format("insert into courses(course_name, description, teacher_id) values('%s','%s','%s')",courseName,description,teacherId);
        CoursesRepository.insertUpdateDeleteFromCourses(con, query3,"Courses table successfully added to!");
    }

    private static void enrollStudentIntoCourse(Scanner input, Connection con) {
        System.out.println("Enter student id");
        int studentId= input.nextInt();
        System.out.println("Enter course id");
        int courseId= input.nextInt();
        input.nextLine();
        System.out.println("Enter student's enrollment date in the form of yyyy-mm-dd");
        String studentEnrollmentDate = input.nextLine();
        String query4= String.format("insert into student_courses(student_id,course_id,enrollment_date) values('%s','%s','%s')",studentId,courseId, studentEnrollmentDate);
        StudentCoursesRepository.createUpdateDeleteFromStudentsCoursesTable(con, query4,"Students and Courses successfully added to!");
    }

    private static void viewAllTeachers(Connection con) throws Exception {
        String selectAllTeachers = "SELECT * FROM teachers;";
        ResultSet teachersSet = TeachersRepository.selectTeachers(con, selectAllTeachers);
        System.out.println("ID\tName\tEmail\tHire Date");
        System.out.println("===========================================================");
        while (teachersSet.next()){
            int id=teachersSet.getInt("teacher_id");
            String name=teachersSet.getString("full_name");
            String teacherEmail=teachersSet.getString("email");
            String teacherDate=teachersSet.getString("hire_date");
            System.out.println(id+"\t"+name+"\t"+teacherEmail+"\t"+teacherDate);
            }
        System.out.println("===========================================================");
    }

    private static void viewAllStudents(Connection con) throws Exception {
        String selectAllStudents = "SELECT * FROM students;";
        ResultSet studentsSet = StudentsRepository.selectStudents(con, selectAllStudents);
        System.out.println("ID\tName\tEnrollment Date\tProgram of Study\tDate of Birth");
        System.out.println("===========================================================");
        while (studentsSet.next()){
            int id=studentsSet.getInt("student_id");
            String name=studentsSet.getString("full_name");
            String teacherEmail=studentsSet.getString("enrollment_date");
            String teacherDate=studentsSet.getString("program_of_study");
            String studentDOB=studentsSet.getString("DOB");
            System.out.println(id+"\t"+name+"\t"+teacherEmail+"\t"+teacherDate+"\t"+studentDOB);
        }
        System.out.println("===========================================================");
    }

    private static void viewAllCourses(Connection con) throws Exception {
        String selectAllCourses = "SELECT * FROM courses;";
        ResultSet set= CoursesRepository.selectCourses(con, selectAllCourses);
        System.out.println("ID\tCourse Name\tDescription\tTeacher ID");
        System.out.println("===========================================================");
        while (set.next()){
            int id=set.getInt("course_id");
            String course=set.getString("course_name");
            String courseDescription=set.getString("description");
            String course_id=set.getString("teacher_id");
            System.out.println(id+"\t"+course+"\t"+courseDescription+"\t"+course_id);
        }
        System.out.println("===========================================================");
    }

    private static void viewStudentsCoursesTeachers(Connection con) throws Exception {
        String selectCoursesAndTeachers = """
                select c.course_name, ts.full_name, count(sc.student_id)  as student_count\s
                 from courses c
                 join teachers ts on c.teacher_id= ts.teacher_id
                 join student_courses sc on c.course_id=sc.course_id
                 group by c.course_name, ts.full_name;
                """;
        ResultSet resultSet = StudentCoursesRepository.selectFromStudentsCoursesTable(con, selectCoursesAndTeachers);
        System.out.println("Course Name\t\tTeacher's Name\t\tStudent Count");
        System.out.println("===========================================================");
        while (resultSet.next()) {
            String course_name = resultSet.getString("course_name");
            String teacherName = resultSet.getString("full_name");
            int studentCount = resultSet.getInt("student_count");
            System.out.println(course_name + "\t" + teacherName + "\t" + studentCount);
        }
        System.out.println("===========================================================");

    }

    private static void updateStudentRecord(Scanner input, Connection con) {
        System.out.println("Enter student's id");
        int studentId=input.nextInt();
        System.out.println("Enter 1 to edit student's full name");
        System.out.println("Enter 2 to edit student's program_of_study");
        System.out.println("Enter 3 to edit teacher's enrollment_date");
        int option= input.nextInt();
        String q="";
        input.nextLine();
        switch (option){
            case 1:
                System.out.println("Enter new teacher's full name");
                String newName= input.nextLine();
                q= String.format("update students set full_name='%s' where student_id=%s",newName,studentId);
                CoursesRepository.insertUpdateDeleteFromCourses(con, q,"Student's name successfully updated!   ");
                break;
            case 2:
                System.out.println("Enter new Student's program_of_study");
                String program_of_study= input.nextLine();
                q= String.format("update students set program_of_study='%s' where student_id=%s",program_of_study,studentId);
                CoursesRepository.insertUpdateDeleteFromCourses(con, q,"Student's program_of_study successfully updated!   ");
                break;
            case 3:
                System.out.println("Enter new Student's enrollment date");
                String hireDate = input.nextLine();
                q= String.format("update students set enrollment_date='%s' where student_id=%s", hireDate,studentId);
                CoursesRepository.insertUpdateDeleteFromCourses(con, q,"Student's enrollment_date successfully updated!   ");
                break;
        }
    }
    private static void updateTeacherRecord(Scanner input, Connection con) {
        System.out.println("Enter teacher's id");
        int teacherId = input.nextInt();
        System.out.println("Enter 1 to edit teacher's full name");
        System.out.println("Enter 2 to edit teacher's email");
        System.out.println("Enter 3 to edit teacher's hire date(YYYY-MM-DD)");
        int option= input.nextInt();
        String q="";
        input.nextLine();
        switch (option){
            case 1:
                System.out.println("Enter new teacher's full name");
                String newName= input.nextLine();
                q= String.format("update teachers set full_name='%s' where teacher_id=%s",newName, teacherId);
                CoursesRepository.insertUpdateDeleteFromCourses(con, q,"Teacher's name successfully updated!   ");
                break;
            case 2:
                System.out.println("Enter new teacher's email");
                String email= input.nextLine();
                q= String.format("update teachers set email='%s' where teacher_id=%s",email, teacherId);
                CoursesRepository.insertUpdateDeleteFromCourses(con, q,"Teacher's email successfully updated!   ");
                break;
            case 3:
                System.out.println("Enter new teacher's hire date");
                String hireDate = input.nextLine();
                q= String.format("update teachers set hire_date='%s' where teacher_id=%s", hireDate, teacherId);
                CoursesRepository.insertUpdateDeleteFromCourses(con, q,"Teacher's hire_date successfully updated!     ");
                break;
        }
    }
    private static void updateCourseRecord(Scanner input, Connection con) {
        System.out.println("Enter course's id");
        int currentCourseId= input.nextInt();
        System.out.println("Enter 1 to edit course's name");
        System.out.println("Enter 2 to edit course's description");
        System.out.println("Enter 3 to edit course teacher's id");
        int option= input.nextInt();
        String q="";
        input.nextLine();
        switch (option){
            case 1:
                System.out.println("Enter new course name");
                String newName= input.nextLine();
                q= String.format("update courses set course_name='%s' where course_id=%s",newName,currentCourseId);
                CoursesRepository.insertUpdateDeleteFromCourses(con, q,"Course name successfully updated!   ");
                break;
            case 2:
                System.out.println("Enter new course description");
                String newDescription= input.nextLine();
                q= String.format("update courses set description='%s' where course_id=%s",newDescription,currentCourseId);
                CoursesRepository.insertUpdateDeleteFromCourses(con, q,"Course description successfully updated!    ");
                break;
            case 3:
                System.out.println("Enter new teacher id");
                String newTeacherId = input.nextLine();
                q= String.format("update courses set teacher_id='%s' where course_id=%s", newTeacherId,currentCourseId);
                CoursesRepository.insertUpdateDeleteFromCourses(con, q,"Course teacher_id successfully updated!     ");
                break;
        }
    }


}