hw5/src/main/java/courseSber/repositories/StudentsRepository.java - master - eelamimi/SberJavaCourseBeginner

hw5/src/main/java/courseSber/repositories/StudentsRepository.java
218 строк8 KB
glebka
fixed bugs
19 апр 2025, 15:26
19 апр 2025, 15:26f35dbdc
package courseSber.repositories;

import courseSber.enums.FieldsOfCourse;
import courseSber.enums.FieldsOfStudent;
import courseSber.models.Student;
import courseSber.utils.*;

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

import static courseSber.utils.GetRS.getStudentFromResultSet;
import static courseSber.utils.SetPS.setStudentForPreparedStatement;

public class StudentsRepository {

    public List<Student> findAll() {
        String sql = "SELECT * FROM students " +
                "INNER JOIN courses ON students.course_student = courses.id_course " +
                "INNER JOIN teachers ON courses.teacher_course = teachers.contract_teacher " +
                "ORDER BY student_card_student";
        List<Student> students = new ArrayList<>();

        try (PreparedStatement ps = DBConnector.getConnection().prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                Student student = getStudentFromResultSet(rs);
                students.add(student);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return students;
    }

    public List<Student> findByField(String value, FieldsOfStudent field) {
        String sql = "SELECT * FROM students " +
                "INNER JOIN courses ON students.course_student = courses.id_course " +
                "INNER JOIN teachers ON courses.teacher_course = teachers.contract_teacher " +
                "WHERE " + field + " = ?";
        List<Student> students = new ArrayList<>();

        try (PreparedStatement ps = DBConnector.getConnection().prepareStatement(sql)) {

            switch (field) {
                case ID_STUDENT, COURSE_STUDENT -> ps.setObject(1, UUID.fromString(value));
                default -> ps.setString(1, value);
            }

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Student student = getStudentFromResultSet(rs);
                    students.add(student);
                }
            }
        } catch (SQLException e) {
            System.out.println("Произошла ошибка при поиске студентов по полю: " + e.getMessage());
            e.printStackTrace();
            return null;
        }
        return students.isEmpty() ? null : students;
    }

    public boolean save(Student student) {
        if (CheckInDB.courseNotInDB(student)) {
            return false;
        }
        String sql = "INSERT INTO students (id_student, last_name_student, first_name_student, middle_name_student, group_student, student_card_student, course_student) VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (PreparedStatement ps = DBConnector.getConnection().prepareStatement(sql)) {
            setStudentForPreparedStatement(student, ps);
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println("Произошла ошибка при сохранении студента: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    public boolean save(List<Student> students) {
        if (CheckInDB.coursesNotInDB(students)) {
            return false;
        }
        String sql = "INSERT INTO students (id_student, last_name_student, first_name_student, middle_name_student, group_student, student_card_student, course_student) VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (PreparedStatement ps = DBConnector.getConnection().prepareStatement(sql)) {
            for (Student student : students) {
                setStudentForPreparedStatement(student, ps);
                ps.addBatch();
            }
            ps.executeBatch();
            return true;
        } catch (SQLException e) {
            System.out.println("Произошла ошибка при сохранении студентов: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    /** Удаляет ВСЕ записи о студенте
     * @param studentCard
     * @return boolean
     */
    public boolean deleteByStudentCard(String studentCard) {
        String sql = "DELETE FROM students WHERE student_card_student = ?";

        try (PreparedStatement ps = DBConnector.getConnection().prepareStatement(sql)) {
            ps.setString(1, studentCard);
            return ps.executeUpdate() > 0;

        } catch (SQLException e) {
            System.out.println("Произошла ошибка при удалении студента: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    /** Удаляет запись о студенте
     * @param student
     * @return boolean
     */
    public boolean delete(Student student) {
        String sql = "DELETE FROM students WHERE id_student = ?";

        try (PreparedStatement ps = DBConnector.getConnection().prepareStatement(sql)) {
            ps.setObject(1, student.getId());
            return ps.executeUpdate() > 0;

        } catch (SQLException e) {
            System.out.println("Произошла ошибка при удалении студента: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    /** Удаляет записи о студентах
     * @param students
     * @return boolean
     */
    public boolean delete(List<Student> students) {
        if (students.isEmpty()) {
            return false;
        }

        String sql = "DELETE FROM students WHERE id_student = ?";

        try (PreparedStatement ps = DBConnector.getConnection().prepareStatement(sql)) {
            for (Student student : students) {
                ps.setObject(1, student.getId());
                ps.addBatch();
            }
            ps.executeBatch();
            return true;
        } catch (SQLException e) {
            System.out.println("Произошла ошибка при удалении студентов: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    public boolean update(Student student, String value, FieldsOfStudent field) {
        if (CheckInDB.courseNotInDB(student)) {
            return false;
        }
        if (field == FieldsOfStudent.COURSE_STUDENT) {
            if (CheckInDB.courseNotInDB(value)) {
                return false;
            }
        }
        if (field == FieldsOfStudent.STUDENT_CARD_STUDENT) {
            if (CheckInDB.studentCardInDB(value)) {
                return false;
            }
        }

        String sql = switch (field) {
            case ID_STUDENT, COURSE_STUDENT -> "UPDATE students SET " + field + " = ? WHERE id_student = ?";
            default -> "UPDATE students SET " + field + " = ? WHERE student_card_student = ?";
        };

        try (PreparedStatement ps = DBConnector.getConnection().prepareStatement(sql)) {

            switch (field) {
                case ID_STUDENT, COURSE_STUDENT -> {
                    ps.setObject(1, UUID.fromString(value));
                    ps.setObject(2, student.getId());
                }
                default -> {
                    ps.setString(1, value);
                    ps.setString(2, student.getStudentCard());
                }
            }

            int rowsAffected = ps.executeUpdate();
            if (rowsAffected > 0) {
                switch (field) {
                    case ID_STUDENT -> student.setId(UUID.fromString(value));
                    case LAST_NAME_STUDENT -> student.setLastName(value);
                    case FIRST_NAME_STUDENT -> student.setFirstName(value);
                    case MIDDLE_NAME_STUDENT -> student.setMiddleName(value);
                    case GROUP_STUDENT -> student.setGroup(value);
                    case STUDENT_CARD_STUDENT -> student.setStudentCard(value);
                    case COURSE_STUDENT -> student.setCourse((new CoursesRepository()).findByField(value, FieldsOfCourse.ID_COURSE).get(0));
                }
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
            System.out.println("Произошла ошибка при обновлении студента: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }
}