module_hw5/src/main/java/example/org/CoursesRepository.java - hw5 - caleb412/SberJavaCourseBeginner

module_hw5/src/main/java/example/org/CoursesRepository.java
184 строки8 KB
Caleb Chanda
Created hw5 and completed work
27 апр 2025, 15:33
27 апр 2025, 15:33cff7cba
package example.org;

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

public class CoursesRepository {

    Scanner sc = new Scanner(System.in);

    public void insertRecord() throws SQLException {
        try(Connection connection = DatabaseConnect.getConnection()){
            assert connection != null;
            String sql = "INSERT INTO courses(course_name, teacherid, speciality)" +
                    "VALUES(?,?,?)";
            System.out.println("Enter Course Name: ");
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, sc.nextLine());
            System.out.println("Enter TeacherID (must be valid): ");
            ps.setInt(2, Integer.parseInt(sc.nextLine()));
            System.out.println("Enter Speciality: ");
            ps.setString(3, sc.nextLine());
            int rows = ps.executeUpdate();
            if(rows > 0){
                System.out.println("Record inserted successfully!");
            }
        } catch (SQLException e){
            System.out.println("INSERT FAILED "+e);
        }

    }
    public void updateRecord() throws SQLException{
        try(Connection connection = DatabaseConnect.getConnection()){
            assert connection != null;
            System.out.println("Enter record ID to update:");
            int id = Integer.parseInt(sc.nextLine());

            String sql = "SELECT * FROM courses WHERE courseid = " +id;
            Statement statement = connection.createStatement();

            ResultSet result = statement.executeQuery(sql);
            if(result.next()){
                int courseId = result.getInt("courseid");
                String courseName = result.getString("course_name");
                int teacherId = result.getInt("teacherid");
                String speciality = result.getString("speciality");

                System.out.println("Course ID: "+courseId);
                System.out.println("Course Name: "+ courseName);
                System.out.println("Teacher ID: "+ teacherId);
                System.out.println("Speciality: " + speciality);


                System.out.println("Enter option to update");
                System.out.println("1. Course Name \n2. Teacher ID \n3. Speciality");
                int updateChoice = Integer.parseInt(sc.nextLine());

                String sqlQuery = "UPDATE courses set ";
                switch (updateChoice){
                    case 1:
                        System.out.println("Enter new Course Name");
                        String newCourseName = sc.nextLine();
                        sqlQuery = sqlQuery + "course_name = ? WHERE courseid = "+id;
                        PreparedStatement ps = connection.prepareStatement(sqlQuery);
                        ps.setString(1,newCourseName);

                        int rows1 = ps.executeUpdate();
                        if(rows1 > 0){
                            System.out.println("Record updated successfully");
                        }
                        break;
                    case 2:
                        System.out.println("Enter new Teacher ID");
                        int newTeacherID =Integer.parseInt(sc.nextLine());
                        sqlQuery = sqlQuery + "name = ? WHERE teacherid = " +id;
                        PreparedStatement psTeacherID= connection.prepareStatement(sqlQuery);
                        psTeacherID.setInt(1,newTeacherID);

                        int rows2 = psTeacherID.executeUpdate();
                        if(rows2 > 0){
                            System.out.println("Record updated successfully");
                        }
                        break;
                    case 3:
                        System.out.println("Enter new Speciality");
                        String newSpeciality = sc.nextLine();
                        sqlQuery = sqlQuery + "speciality = ? WHERE teacherid = "+id;
                        PreparedStatement psSpeciality = connection.prepareStatement(sqlQuery);
                        psSpeciality.setString(1,newSpeciality);

                        int rows3 = psSpeciality.executeUpdate();
                        if(rows3 > 0){
                            System.out.println("Record updated successfully");
                        }
                        break;
                    default:
                        System.out.println("Wrong Input");
                        break;
                }
            }else{
                System.out.println("Records not found");
            }

        } catch (Exception e) {
            System.out.println("UPDATE FAILED "+e);
        }

    }
    public void readRecord() throws SQLException{
        try(Connection connection = DatabaseConnect.getConnection()){
            assert connection != null;
            System.out.println("1. Show all records \n2. Filter by Id");
            String choice = sc.nextLine();

            int courseId;
            String courseName;
            int teacherId;
            String speciality;

            switch (choice){
                case "1":
                    String sql = "SELECT * from courses";
                    assert connection != null;
                    Statement statement = connection.createStatement();
                    ResultSet result = statement.executeQuery(sql);
                    System.out.printf("| %5s || %-20s || %-20s || %-20s %n", "ID","COURSE_NAME","TEACHER_ID","SPECIALITY");
                    System.out.printf("----------------------------------------------------------------------------------%n");
                    while( result.next()){
                        courseId = result.getInt("courseid");
                        courseName = result.getString("course_name");
                        teacherId = result.getInt("teacherid");
                        speciality = result.getString("speciality");

                        System.out.printf("| %5s || %-20s || %-20s || %-20s%n", courseId,courseName,teacherId,speciality);
                    }
                    break;
                case "2":
                    System.out.println("Enter filter to read");
                    int idNumber = Integer.parseInt(sc.nextLine());
                    String filterSql = "SELECT * from courses WHERE courseid =" +idNumber;
                    Statement filterStatement = connection.createStatement();
                    ResultSet filterResult = filterStatement.executeQuery(filterSql);

                    if(filterResult.next()) {
                        courseId = filterResult.getInt("courseid");
                        courseName = filterResult.getString("course_name");
                        teacherId =  filterResult.getInt("teacherid");
                        speciality = filterResult.getString("speciality");


                        System.out.printf("| %5s || %-20s || %-20s || %-20s%n", "ID","COURSE_NAME","TEACHER_ID","SPECIALITY");
                        System.out.printf("--------------------------------------------------------------------------------%n");
                        System.out.printf("| %5s || %-20s || %-20s || %-20s%n", courseId,courseName,teacherId,speciality);
                    }
                    else {
                        System.out.println("No matching records found");
                    }
                    break;
                default:
                    System.out.println("Wrong Input!");
                    break;

            }
        }catch (SQLException e){
            System.out.println("READ FAILED"+e);
        }

    }
    public void deleteRecord() throws SQLException{
        try(Connection connection = DatabaseConnect.getConnection()){
            assert connection != null;
            System.out.println("Enter the ID to delete: ");
            int id = Integer.parseInt(sc.nextLine());
            String sql = "DELETE FROM courses WHERE courseid ="+id;
            Statement statement  = connection.createStatement();
            int rows = statement.executeUpdate(sql);
            if(rows>0) {
                System.out.println("Record deleted successfully!\n");
            }
        } catch (SQLException e){
            System.out.println("DELETE FAILED");
        }

    }
}