AI-powered Student Management System

package com.robertjp.dao;

import com.robertjp.model.Enrollment;
import com.robertjp.util.DBConnection;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * EnrollmentDAO: the data access layer for student-course enrollments.
 *
 * Two things to notice about this file:
 *
 *   1. Every query uses PreparedStatement with parameter binding (?). No
 *      string concatenation, no SQL injection surface. Even calculateGPA,
 *      which is doing math at the database level, binds its student_id
 *      parameter rather than splicing it into the query string.
 *
 *   2. calculateGPA performs a weighted-average GPA computation in a single
 *      SQL query with a JOIN. SUM(grade_points * credits) / SUM(credits)
 *      gives the credit-weighted GPA, which is the academically correct
 *      formula, not just the arithmetic mean of grade points. Computing this
 *      in SQL means MySQL does the aggregation, and Java gets a single
 *      BigDecimal back instead of fetching every enrollment row and summing
 *      in memory.
 *
 * The buildEnrollment helper at the bottom eliminates the duplicate
 * ResultSet-to-object mapping that would otherwise live in every method.
 */
public class EnrollmentDAO {

    public boolean enrollStudent(Enrollment enrollment) {
        String sql = "INSERT INTO enrollments (student_id, course_id, semester, grade, grade_points) " +
                "VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, enrollment.getStudentId());
            stmt.setInt(2, enrollment.getCourseId());
            stmt.setString(3, enrollment.getSemester());
            stmt.setString(4, enrollment.getGrade());
            if (enrollment.getGradePoints() != null) {
                stmt.setBigDecimal(5, enrollment.getGradePoints());
            } else {
                stmt.setNull(5, Types.DECIMAL);
            }

            return stmt.executeUpdate() > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // JOIN query - combines data from 3 tables
    public List<Enrollment> getEnrollmentsByStudent(int studentId) {
        List<Enrollment> enrollments = new ArrayList<>();
        String sql = "SELECT e.*, " +
                "CONCAT(s.first_name, ' ', s.last_name) AS student_name, " +
                "c.course_code, c.course_name, c.credits " +
                "FROM enrollments e " +
                "JOIN students s ON e.student_id = s.id " +
                "JOIN courses c ON e.course_id = c.id " +
                "WHERE e.student_id = ? " +
                "ORDER BY e.semester DESC, c.course_code";

        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, studentId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Enrollment enrollment = buildEnrollment(rs);
                enrollments.add(enrollment);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return enrollments;
    }

    public boolean updateGrade(int enrollmentId, String grade, BigDecimal gradePoints) {
        String sql = "UPDATE enrollments SET grade = ?, grade_points = ? WHERE id = ?";

        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, grade);
            stmt.setBigDecimal(2, gradePoints);
            stmt.setInt(3, enrollmentId);

            return stmt.executeUpdate() > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // Credit-weighted GPA computed in a single query
    public BigDecimal calculateGPA(int studentId) {
        String sql = "SELECT SUM(e.grade_points * c.credits) / SUM(c.credits) AS gpa " +
                "FROM enrollments e " +
                "JOIN courses c ON e.course_id = c.id " +
                "WHERE e.student_id = ? AND e.grade_points IS NOT NULL";

        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, studentId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                BigDecimal gpa = rs.getBigDecimal("gpa");
                if (gpa != null) {
                    return gpa.setScale(2, BigDecimal.ROUND_HALF_UP);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return BigDecimal.ZERO;
    }

    // Single mapping function reused by every read method
    private Enrollment buildEnrollment(ResultSet rs) throws SQLException {
        Enrollment enrollment = new Enrollment();
        enrollment.setId(rs.getInt("id"));
        enrollment.setStudentId(rs.getInt("student_id"));
        enrollment.setCourseId(rs.getInt("course_id"));
        enrollment.setSemester(rs.getString("semester"));
        enrollment.setGrade(rs.getString("grade"));
        enrollment.setGradePoints(rs.getBigDecimal("grade_points"));
        enrollment.setEnrolledAt(rs.getTimestamp("enrolled_at").toLocalDateTime());
        enrollment.setStudentName(rs.getString("student_name"));
        enrollment.setCourseCode(rs.getString("course_code"));
        enrollment.setCourseName(rs.getString("course_name"));
        enrollment.setCredits(rs.getInt("credits"));
        return enrollment;
    }
}