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

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

    Scanner sc = new Scanner(System.in);

    public void insertRecord() throws SQLException {
        try(Connection connection = DatabaseConnect.getConnection()){
            assert connection != null;
            String sql = "INSERT INTO teachers(surname, name, middle_name, department)" +
                    "VALUES(?,?,?,?)";
            System.out.println("Enter Surname: ");
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, sc.nextLine());
            System.out.println("Enter Name: ");
            ps.setString(2, sc.nextLine());
            System.out.println("Enter middle name: ");
            ps.setString(3, sc.nextLine());
            System.out.println("Enter department: ");
            ps.setInt(4, 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 record ID to update:");
            int id = Integer.parseInt(sc.nextLine());

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

            ResultSet result = statement.executeQuery(sql);
            if(result.next()){
                int teacherid = result.getInt("teacherid");
                String surname = result.getString("surname");
                String name = result.getString("name");
                String middleName = result.getString("middle_name");
                String department = result.getString("department");
                System.out.println("Teacher ID: "+teacherid);
                System.out.println("Surname: "+surname);
                System.out.println("Name: "+ name);
                System.out.println("Middle name: " + middleName);
                System.out.println("Department: " + department);

                System.out.println("Enter option to update");
                System.out.println("1. Surname \n2. Name \n3. Middle Name \n4. Department");
                int updateChoice = Integer.parseInt(sc.nextLine());

                String sqlQuery = "UPDATE teachers set ";
                switch (updateChoice){
                    case 1:
                        System.out.println("Enter new surname");
                        String newSurname = sc.nextLine();
                        sqlQuery = sqlQuery + "surname = ? WHERE teacherid = "+id;
                        PreparedStatement ps = connection.prepareStatement(sqlQuery);
                        ps.setString(1,newSurname);

                        int rows1 = ps.executeUpdate();
                        if(rows1 > 0){
                            System.out.println("Record updated successfully");
                        }
                        break;
                    case 2:
                        System.out.println("Enter new name");
                        String newName = sc.nextLine();
                        sqlQuery = sqlQuery + "name = ? WHERE teacherid = "+id;
                        PreparedStatement psNameUpdate = connection.prepareStatement(sqlQuery);
                        psNameUpdate.setString(1,newName);

                        int rows2 = psNameUpdate.executeUpdate();
                        if(rows2 > 0){
                            System.out.println("Record updated successfully");
                        }
                        break;
                    case 3:
                        System.out.println("Enter new Middle Name");
                        String newMiddleName = sc.nextLine();
                        sqlQuery = sqlQuery + "middle_name = ? WHERE teacherid = "+id;
                        PreparedStatement psMiddleName = connection.prepareStatement(sqlQuery);
                        psMiddleName.setString(1,newMiddleName);

                        int rows3 = psMiddleName.executeUpdate();
                        if(rows3 > 0){
                            System.out.println("Record updated successfully");
                        }
                        break;
                    case 4:
                        System.out.println("Enter new department");
                        String newDepartment =sc.nextLine();
                        sqlQuery = sqlQuery + "department = ? WHERE teacherid = "+id;
                        PreparedStatement psNewDepartment = connection.prepareStatement(sqlQuery);
                        psNewDepartment.setString(1,newDepartment);

                        int rows4 = psNewDepartment.executeUpdate();
                        if(rows4 > 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 teacherId;
         String surname;
         String name;
         String middleName;
         String department;

         switch (choice){
             case "1":
                 String sql = "SELECT * from teachers";
                 Statement statement = connection.createStatement();
                 ResultSet result = statement.executeQuery(sql);
                 System.out.printf("| %5s || %-20s || %-20s || %-20s || %-15s %n", "ID","SURNAME","NAME","MIDDLE NAME", "DEPARTMENT");
                 System.out.printf("-------------------------------------------------------------------------------------------%n");
                 while( result.next()){
                     teacherId = result.getInt("teacherid");
                     surname = result.getString("surname");
                     name = result.getString("name");
                     middleName = result.getString("middle_name");
                     department = result.getString("department");

                     System.out.printf("| %5s || %-20s || %-20s || %-20s || %-15s %n", teacherId,surname,name,middleName,department);
                 }
                 break;
             case "2":
                 System.out.println("Enter filter to read");
                 int idNumber = Integer.parseInt(sc.nextLine());
                 String filterSql = "SELECT * from teachers where teacherid =" +idNumber;
                 Statement filterStatement = connection.createStatement();
                 ResultSet filterResult = filterStatement.executeQuery(filterSql);

                 if(filterResult.next()) {
                     teacherId = filterResult.getInt("teacherid");
                     surname = filterResult.getString("surname");
                     name = filterResult.getString("name");
                     middleName = filterResult.getString("middle_name");
                     department = filterResult.getString("department");

                     System.out.printf("| %5s || %-20s || %-20s || %-20s || %-15s %n", "ID","SURNAME","NAME","MIDDLE NAME", "DEPARTMENT");
                     System.out.printf("-------------------------------------------------------------------------------------------%n");
                     System.out.printf("| %5s || %-20s || %-20s || %-20s || %-15s %n", teacherId,surname,name,middleName,department);
                 }
                 else {
                     System.out.println("No matching records found");
                 }
                 break;
             default:
                 System.out.println("Wrong Input!");
         }
     } catch (Exception 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 teachers WHERE teacherid ="+id;

           Statement statement  = connection.createStatement();
           int rows = statement.executeUpdate(sql);
           if(rows>0){
               System.out.println("Record deleted successfully!\n");
           }
       } catch (Exception e){
           System.out.println("DELETE FAILED "+e);
       }
    }
}