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

hw5/src/main/java/courseSber/repositories/TeachersRepository.java
190 строк7 KB
glebka
set model for Prepared Statement & get from result set
19 апр 2025, 13:17
19 апр 2025, 13:176d83cd7
package courseSber.repositories;

import courseSber.enums.FieldsOfTeacher;
import courseSber.models.Teacher;
import courseSber.utils.*;

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

import static courseSber.utils.GetRS.getTeacherFromResultSet;
import static courseSber.utils.SetPS.setTeacherForPreparedStatement;

public class TeachersRepository {

    public List<Teacher> findAll() {
        String sql = "SELECT * FROM teachers";
        List<Teacher> teachers = new ArrayList<>();

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

            while (rs.next()) {
                Teacher teacher = getTeacherFromResultSet(rs);
                teachers.add(teacher);
            }
        } catch (SQLException e) {
            System.out.println("Ошибка при поиске преподавателей: " + e.getMessage());
            e.printStackTrace();
            return null;
        }

        if (teachers.isEmpty()) {
            return null;
        } else {
            return teachers;
        }
    }

    public List<Teacher> findByField(String value, FieldsOfTeacher field) {
        String sql = "SELECT * FROM teachers WHERE " + field + " = ?";
        List<Teacher> teachers = new ArrayList<>();

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

            ps.setString(1, value);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Teacher teacher = getTeacherFromResultSet(rs);
                    teachers.add(teacher);
                }
            }
        } catch (SQLException e) {
            System.out.println("Ошибка при поиске преподавателей: " + e.getMessage());
            e.printStackTrace();
            return null;
        }

        if (teachers.isEmpty()) {
            return null;
        } else {
            return teachers;
        }
    }

    public Teacher findByContract(String contract) {
        String sql = "SELECT * FROM teachers WHERE contract_teacher = ?";
        Teacher teacher = null;

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

            ps.setString(1, contract);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    teacher = getTeacherFromResultSet(rs);
                } else {
                    System.out.println("Преподавателя с таким контрактом не найдено");
                }
            }
        } catch (SQLException e) {
            System.out.println("Ошибка при поиске преподавателя: " + e.getMessage());
            e.printStackTrace();
        }
        return teacher;
    }

    public boolean save(Teacher teacher) {
        String sql = "INSERT INTO teachers (last_name_teacher, first_name_teacher, middle_name_teacher, contract_teacher, department_teacher) VALUES (?, ?, ?, ?, ?)";

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

            setTeacherForPreparedStatement(teacher, ps);
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println("Ошибка при добавлении преподавателя: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    public boolean save(List<Teacher> teachers) {
        if (teachers.isEmpty()) { return false; }

        String sql = "INSERT INTO teachers (last_name_teacher, first_name_teacher, middle_name_teacher, contract_teacher, department_teacher) VALUES (?, ?, ?, ?, ?)";

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

            for (Teacher teacher : teachers) {
                setTeacherForPreparedStatement(teacher, ps);
                ps.addBatch();
            }

            ps.executeBatch();

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

    public boolean delete(Teacher teacher) {
        String sql = "DELETE FROM teachers WHERE contract_teacher = ?";

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

            ps.setObject(1, teacher.getContract());
            int rowsAffected = ps.executeUpdate();

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

    public boolean delete(List<Teacher> teachers) {
        if (teachers == null) { return false; }
        if (teachers.isEmpty()) { return false; }

        String sql = "DELETE FROM teachers WHERE contract_teacher = ?";

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

            for (Teacher teacher : teachers) {
                ps.setObject(1, teacher.getContract());
                ps.addBatch();
            }

            ps.executeBatch();

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

    public boolean update(Teacher teacher, String valueOfField, FieldsOfTeacher field) {
        String sql = "UPDATE teachers SET " + field + " = ? WHERE contract_teacher = ?";

        try (PreparedStatement ps = DBConnector.getConnection().prepareStatement(sql)) {
            ps.setString(1, valueOfField);
            ps.setString(2, teacher.getContract());
            int rowsAffected = ps.executeUpdate();

            if (rowsAffected > 0) {
                switch (field) {
                    case LAST_NAME_TEACHER: teacher.setLastName(valueOfField); break;
                    case FIRST_NAME_TEACHER: teacher.setFirstName(valueOfField); break;
                    case MIDDLE_NAME_TEACHER: teacher.setMiddleName(valueOfField); break;
                    case CONTRACT_TEACHER: teacher.setContract(valueOfField); break;
                    case DEPARTMENT_TEACHER: teacher.setDepartment(valueOfField); break;
                }
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            System.out.println("Ошибка при обновлении преподавателя: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }
}