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

module_hw5/src/main/java/example/org/ClassesRepository.java
160 строк7 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 ClassesRepository {

    Scanner sc = new Scanner(System.in);
    public void insertRecord() throws SQLException{
        try(Connection connection = DatabaseConnect.getConnection()){
            assert connection != null;
            String sql = "INSERT INTO classes(studentid, courseid)" +
                    "VALUES(?,?)";
            System.out.println("Enter Student ID(must be valid): ");
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, Integer.parseInt(sc.nextLine()));
            System.out.println("Enter Course ID (must be valid): ");
            ps.setInt(2, Integer.parseInt(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 student ID to update (Student ID):");
          int id = Integer.parseInt(sc.nextLine());
          System.out.println("Enter course ID to update (Course ID): ");
          int id2 = Integer.parseInt(sc.nextLine());

          String sql = "SELECT * FROM classes WHERE studentid = " +id + " and courseid= " +id2;
          Statement statement = connection.createStatement();

          ResultSet result = statement.executeQuery(sql);
          if(result.next()){
              int studentid= result.getInt("studentid");
              int courseid = result.getInt("courseid");


              System.out.println("Student ID: "+ studentid);
              System.out.println("Course ID: "+ courseid);


              System.out.println("Enter option to update");
              System.out.println("1. Student ID \n2. Course ID");
              int updateChoice = Integer.parseInt(sc.nextLine());
              String sqlQuery = "UPDATE classes SET ";
              switch (updateChoice){
                  case 1:
                      System.out.println("Enter new Student ID");
                      int newId = Integer.parseInt(sc.nextLine());
                      sqlQuery+= "studentid = ? WHERE studentid =" +studentid + " and courseid = "+ courseid;
                      PreparedStatement ps = connection.prepareStatement(sqlQuery);
                      ps.setInt(1, newId);

                      int rows1 = ps.executeUpdate();
                      if(rows1 > 0){
                          System.out.println("Record updated successfully");
                      }
                      break;
                  case 2:
                      System.out.println("Enter new Course ID");
                      int newCourseId =Integer.parseInt(sc.nextLine());
                      sqlQuery+= "courseid = ? WHERE studentid = "+studentid+" and courseid= "+courseid;
                      PreparedStatement ps1= connection.prepareStatement(sqlQuery);
                      ps1.setInt(1,newCourseId);
                      int rows2 = ps1.executeUpdate();
                      if(rows2 > 0){
                          System.out.println("Record updated successfully");
                      }
                      break;
                  default:
                      System.out.println("Wrong Input");
                      break;
              }
          }else{
              System.out.println("Records not found");
          }
      } catch (SQLException 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;
          int studentId;

          switch (choice){
              case "1":
                  String sql = "SELECT * from classes";
                  Statement statement = connection.createStatement();
                  ResultSet result = statement.executeQuery(sql);
                  System.out.printf("| %5s || %-5s %n", "COURSE ID","STUDENT ID");
                  System.out.printf("--------------------%n");
                  while( result.next()){
                      courseId = result.getInt("courseid");
                      studentId = result.getInt("studentid");

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

                  if(filterResult.next()) {
                      courseId = filterResult.getInt("courseid");
                      studentId = filterResult.getInt("studentid");

                      System.out.printf("| %-5s || %-5s %n", "COURSE ID","STUDENT ID");
                      System.out.printf("--------------------------------------------%n");
                      System.out.printf("| %-5s || %-5s %n", courseId,studentId);
                  }
                  else {
                      System.out.println("No matching records found");
                  }
                  break;
              default:
                  System.out.println("Wrong Input!");
          }
      } catch (SQLException e) {
          System.out.println("READ FAILED " +e);
      }

    }
    public void deleteRecord() throws SQLException{
        try {
            Connection connection = DatabaseConnect.getConnection();
            System.out.println("Enter the Student ID to delete: ");
            int studentid = Integer.parseInt(sc.nextLine());
            System.out.println("Enter the Course ID to delete: ");
            int courseid = Integer.parseInt(sc.nextLine());

            String sql = "DELETE FROM classes WHERE studentid ="+studentid+" and courseid="+courseid;

            assert connection != null;
            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 "+ e);
        }

    }
}