src/app/service/AdminService.java - master - evernotenkai/DataBaseXJavaProject

src/app/service/AdminService.java
300 строк14 KB
Makson4ik
Весь проект с src db и прочим делом
12 май 2026, 22:43
12 май 2026, 22:43431f4aa
package app.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import app.db.ConnectionManager;
import app.model.CourseItem;
import app.model.EnrollmentItem;
import app.model.StudentItem;
import app.model.TeacherItem;

public class AdminService {
    private final ConnectionManager connectionManager;

    public AdminService(ConnectionManager connectionManager) {
        this.connectionManager = connectionManager;
    }

    public List<TeacherItem> findTeachers() {
        String sql = "SELECT t.teacher_id, t.full_name, t.department, t.email, COALESCE(t.phone, ''), COALESCE(u.login, '') " +
                "FROM teachers t LEFT JOIN users u ON u.teacher_id = t.teacher_id ORDER BY t.full_name";
        List<TeacherItem> list = new ArrayList<TeacherItem>();
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                list.add(new TeacherItem(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)));
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки преподавателей", e);
        }
        return list;
    }

    public List<StudentItem> findStudents() {
        String sql = "SELECT s.student_id, s.full_name, s.group_name, s.email, COALESCE(s.phone, ''), COALESCE(u.login, '') " +
                "FROM students s LEFT JOIN users u ON u.student_id = s.student_id ORDER BY s.full_name";
        List<StudentItem> list = new ArrayList<StudentItem>();
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                list.add(new StudentItem(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)));
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки студентов", e);
        }
        return list;
    }

    public List<CourseItem> findCourses() {
        String sql = "SELECT c.course_id, c.course_name, COALESCE(c.description, ''), t.full_name, c.teacher_id " +
                "FROM courses c JOIN teachers t ON t.teacher_id = c.teacher_id ORDER BY c.course_name";
        List<CourseItem> list = new ArrayList<CourseItem>();
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                list.add(new CourseItem(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5)));
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки курсов", e);
        }
        return list;
    }

    public List<EnrollmentItem> findEnrollmentsByCourse(int courseId) {
        String sql = "SELECT e.enrollment_id, s.student_id, s.full_name, s.group_name, e.enrollment_date " +
                "FROM enrollments e JOIN students s ON s.student_id = e.student_id WHERE e.course_id = ? ORDER BY s.full_name";
        List<EnrollmentItem> list = new ArrayList<EnrollmentItem>();
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, courseId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new EnrollmentItem(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4), String.valueOf(rs.getDate(5))));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки записей на курс", e);
        }
        return list;
    }

    public List<StudentItem> findAvailableStudentsForCourse(int courseId) {
        String sql = "SELECT s.student_id, s.full_name, s.group_name, s.email, COALESCE(s.phone, ''), COALESCE(u.login, '') " +
                "FROM students s LEFT JOIN users u ON u.student_id = s.student_id " +
                "WHERE s.student_id NOT IN (SELECT student_id FROM enrollments WHERE course_id = ?) ORDER BY s.full_name";
        List<StudentItem> list = new ArrayList<StudentItem>();
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, courseId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new StudentItem(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("Ошибка загрузки доступных студентов", e);
        }
        return list;
    }

    public int createTeacher(String fullName, String department, String email, String phone, String login, String password) {
        try (Connection connection = connectionManager.getConnection()) {
            connection.setAutoCommit(false);
            int teacherId;
            try (PreparedStatement ps = connection.prepareStatement(
                    "INSERT INTO teachers (full_name, department, email, phone) VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) {
                ps.setString(1, fullName);
                ps.setString(2, department);
                ps.setString(3, email);
                ps.setString(4, phone);
                ps.executeUpdate();
                teacherId = generatedId(ps);
            }
            try (PreparedStatement ps = connection.prepareStatement(
                    "INSERT INTO users (login, password, role, teacher_id, student_id) VALUES (?, ?, 'teacher', ?, NULL)")) {
                ps.setString(1, login);
                ps.setString(2, password);
                ps.setInt(3, teacherId);
                ps.executeUpdate();
            }
            connection.commit();
            return teacherId;
        } catch (Exception e) {
            throw new RuntimeException("Ошибка создания преподавателя", e);
        }
    }

    public void updateTeacher(int teacherId, String fullName, String department, String email, String phone, String login, String password) {
        try (Connection connection = connectionManager.getConnection()) {
            connection.setAutoCommit(false);
            try (PreparedStatement ps = connection.prepareStatement(
                    "UPDATE teachers SET full_name = ?, department = ?, email = ?, phone = ? WHERE teacher_id = ?")) {
                ps.setString(1, fullName);
                ps.setString(2, department);
                ps.setString(3, email);
                ps.setString(4, phone);
                ps.setInt(5, teacherId);
                ps.executeUpdate();
            }
            try (PreparedStatement ps = connection.prepareStatement(
                    "UPDATE users SET login = ?, password = ? WHERE teacher_id = ?")) {
                ps.setString(1, login);
                ps.setString(2, password);
                ps.setInt(3, teacherId);
                ps.executeUpdate();
            }
            connection.commit();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка обновления преподавателя", e);
        }
    }

    public void deleteTeacher(int teacherId) {
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement("DELETE FROM teachers WHERE teacher_id = ?")) {
            ps.setInt(1, teacherId);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка удаления преподавателя", e);
        }
    }

    public int createStudent(String fullName, String groupName, String email, String phone, String login, String password) {
        try (Connection connection = connectionManager.getConnection()) {
            connection.setAutoCommit(false);
            int studentId;
            try (PreparedStatement ps = connection.prepareStatement(
                    "INSERT INTO students (full_name, group_name, email, phone) VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) {
                ps.setString(1, fullName);
                ps.setString(2, groupName);
                ps.setString(3, email);
                ps.setString(4, phone);
                ps.executeUpdate();
                studentId = generatedId(ps);
            }
            try (PreparedStatement ps = connection.prepareStatement(
                    "INSERT INTO users (login, password, role, teacher_id, student_id) VALUES (?, ?, 'student', NULL, ?)")) {
                ps.setString(1, login);
                ps.setString(2, password);
                ps.setInt(3, studentId);
                ps.executeUpdate();
            }
            connection.commit();
            return studentId;
        } catch (Exception e) {
            throw new RuntimeException("Ошибка создания студента", e);
        }
    }

    public void updateStudent(int studentId, String fullName, String groupName, String email, String phone, String login, String password) {
        try (Connection connection = connectionManager.getConnection()) {
            connection.setAutoCommit(false);
            try (PreparedStatement ps = connection.prepareStatement(
                    "UPDATE students SET full_name = ?, group_name = ?, email = ?, phone = ? WHERE student_id = ?")) {
                ps.setString(1, fullName);
                ps.setString(2, groupName);
                ps.setString(3, email);
                ps.setString(4, phone);
                ps.setInt(5, studentId);
                ps.executeUpdate();
            }
            try (PreparedStatement ps = connection.prepareStatement(
                    "UPDATE users SET login = ?, password = ? WHERE student_id = ?")) {
                ps.setString(1, login);
                ps.setString(2, password);
                ps.setInt(3, studentId);
                ps.executeUpdate();
            }
            connection.commit();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка обновления студента", e);
        }
    }

    public void deleteStudent(int studentId) {
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement("DELETE FROM students WHERE student_id = ?")) {
            ps.setInt(1, studentId);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка удаления студента", e);
        }
    }

    public int createCourse(String courseName, String description, int teacherId) {
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(
                     "INSERT INTO courses (course_name, description, teacher_id) VALUES (?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, courseName);
            ps.setString(2, description);
            ps.setInt(3, teacherId);
            ps.executeUpdate();
            return generatedId(ps);
        } catch (Exception e) {
            throw new RuntimeException("Ошибка создания курса", e);
        }
    }

    public void updateCourse(int courseId, String courseName, String description, int teacherId) {
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(
                     "UPDATE courses SET course_name = ?, description = ?, teacher_id = ? WHERE course_id = ?")) {
            ps.setString(1, courseName);
            ps.setString(2, description);
            ps.setInt(3, teacherId);
            ps.setInt(4, courseId);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка обновления курса", e);
        }
    }

    public void deleteCourse(int courseId) {
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement("DELETE FROM courses WHERE course_id = ?")) {
            ps.setInt(1, courseId);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка удаления курса", e);
        }
    }

    public void enrollStudent(int studentId, int courseId) {
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement(
                     "INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES (?, ?, CURRENT_DATE)")) {
            ps.setInt(1, studentId);
            ps.setInt(2, courseId);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка зачисления студента", e);
        }
    }

    public void unenroll(int enrollmentId) {
        try (Connection connection = connectionManager.getConnection();
             PreparedStatement ps = connection.prepareStatement("DELETE FROM enrollments WHERE enrollment_id = ?")) {
            ps.setInt(1, enrollmentId);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Ошибка удаления записи о курсе", e);
        }
    }

    private int generatedId(PreparedStatement ps) throws Exception {
        try (ResultSet rs = ps.getGeneratedKeys()) {
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        throw new IllegalStateException("Не удалось получить сгенерированный идентификатор");
    }
}