src/Manage.java - main - Maksim_Shchelochok/Course_Project

src/Manage.java
59 строк3 KB
Maksim
Создание класса для вывода разных данных из БД в соответствии с их уровнем доступа
19 июн 2026, 18:33
19 июн 2026, 18:337adaab7
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Manage {
    private Connection getConnection(){
        return ConnectDatabase.getInstance().getConnection();
    }

    public List<Report> getReportsForUser(User user){
        List<Report> list = new ArrayList<>();
        String sql = "";

        if ("ADMIN".equalsIgnoreCase(user.getRole())){
            sql = "SELECT c.name AS course_name, s.name AS subject_name, lt.name AS lesson_type_name, slt.price " +
                    "FROM subject_lesson_types slt " +
                    "JOIN courses c ON slt.course_id = c.course_id " +
                    "JOIN subjects s ON slt.subject_id = s.subject_id " +
                    "JOIN lesson_types lt ON slt.lesson_type_id = lt.lesson_type_id";
        } else if ("TEACHER".equalsIgnoreCase(user.getRole())) {
            sql = "SELECT c.name AS course_name, s.name AS subject_name, lt.name AS lesson_type_name, slt.price " +
                    "FROM subject_lesson_types slt " +
                    "JOIN courses c ON slt.course_id = c.course_id " +
                    "JOIN subjects s ON slt.subject_id = s.subject_id " +
                    "JOIN lesson_types lt ON slt.lesson_type_id = lt.lesson_type_id " +
                    "WHERE c.direction_id = (SELECT direction_id FROM teachers WHERE user_id = ?)";
        } else if ("STUDENT".equalsIgnoreCase(user.getRole())) {
            sql = "SELECT c.name AS course_name, s.name AS subject_name, lt.name AS lesson_type_name, slt.price " +
                    "FROM subject_lesson_types slt " +
                    "JOIN courses c ON slt.course_id = c.course_id " +
                    "JOIN group_courses gc ON c.course_id = gc.course_id " +
                    "JOIN group_students gs ON gc.group_id = gs.group_id " +
                    "WHERE gs.student_id = ?";
        }
        try (PreparedStatement pstmt = getConnection().prepareStatement(sql)) {
            // Если это не админ, подставляем ID авторизованного пользователя для фильтрации строк
            if (!"ADMIN".equalsIgnoreCase(user.getRole())) {
                pstmt.setInt(1, user.getUserId());
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    list.add(new Report(
                            rs.getString("course_name"),
                            rs.getString("subject_name"),
                            rs.getString("lesson_type_name"),
                            rs.getDouble("price"))
                    );
                }
            }
        } catch (SQLException e) {
            System.out.println("Ошибка разделения доступа на уровне БД: " + e.getMessage());
        }
        return list;
    }
}