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

module_hw5/src/main/java/example/org/StudentsRepository.java
194 строки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 StudentsRepository {
    Scanner sc = new Scanner(System.in);

    public void insertRecord() throws SQLException {
        try(Connection connection = DatabaseConnect.getConnection()) {
            assert connection != null;
            String sql = "insert into students(surname, name, middle_name, student_group)" +
                    "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 group: ");
            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 students WHERE studentid = "+id;
            Statement statement = connection.createStatement();

            ResultSet result = statement.executeQuery(sql);
            if(result.next()){
                int studentId = result.getInt("studentid");
                String surname = result.getString("surname");
                String name = result.getString("name");
                String middleName = result.getString("middle_name");
                int group = result.getInt("student_group");
                System.out.println("Student ID: "+studentId);
                System.out.println("Surname: "+surname);
                System.out.println("Name: "+ name);
                System.out.println("Middle name: " + middleName);
                System.out.println("Group: " + group);

                System.out.println("Enter option to update");
                System.out.println("1. Surname \n2. Name \n3. Middle Name \n4. Group");
                int updateChoice = Integer.parseInt(sc.nextLine());
                String sqlQuery = "UPDATE students set ";
                switch (updateChoice){
                    case 1:
                        System.out.println("Enter new surname");
                        String newSurname = sc.nextLine();
                        sqlQuery = sqlQuery + "surname = ? WHERE studentid = "+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 studentid = "+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 studentid = "+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 group");
                        int newGroup = Integer.parseInt(sc.nextLine());
                        sqlQuery = sqlQuery + "group = ? WHERE studentid = "+id;
                        PreparedStatement psNewGroup = connection.prepareStatement(sqlQuery);
                        psNewGroup.setInt(1,newGroup);

                        int rows4 = psNewGroup.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 studentId;
            String surname;
            String name;
            String middleName;
            int group;

            switch (choice){
                case "1":
                    String sql = "SELECT * from students";
                    Statement statement = connection.createStatement();
                    ResultSet result = statement.executeQuery(sql);
                    System.out.printf("| %5s || %-20s || %-20s || %-20s || %-10s %n", "ID","SURNAME","NAME","MIDDLE NAME", "GROUP");
                    System.out.printf("-------------------------------------------------------------------------------------------%n");
                    while( result.next()){
                        studentId = result.getInt("studentid");
                        surname = result.getString("surname");
                        name = result.getString("name");
                        middleName = result.getString("middle_name");
                        group = result.getInt("student_group");

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

                    if(filterResult.next()) {
                        studentId = filterResult.getInt("studentid");
                        surname = filterResult.getString("surname");
                        name = filterResult.getString("name");
                        middleName = filterResult.getString("middle_name");
                        group = filterResult.getInt("student_group");

                        System.out.printf("| %5s || %-20s || %-20s || %-20s || %-10s %n", "ID","SURNAME","NAME","MIDDLE NAME", "GROUP");
                        System.out.printf("-------------------------------------------------------------------------------------------%n");
                        System.out.printf("| %5s || %-20s || %-20s || %-20s || %-10s %n", studentId,surname,name,middleName,group);
                    }
                    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()){
            assert connection !=null;
            System.out.println("Enter the ID to delete: ");
            int id = Integer.parseInt(sc.nextLine());
            String sql = "DELETE FROM students WHERE studentid ="+id;

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

    }
}