1package com.mskinstitute.capstone2;
2
3import java.sql.*;
4import java.util.*;
5
6public class StudentERPService {
7
8 public record Student(int id, String name, String email, String phone) {}
9 public record Enrollment(int studentId, String courseCode, double fee) {}
10
11 private final Connection connection;
12
13 public StudentERPService(Connection connection) {
14 this.connection = connection;
15 }
16
17 /**
18 * Executes atomic student creation and course enrollment within a single transaction boundary.
19 */
20 public int registerAndEnrollStudent(String name, String email, String phone,
21 String courseCode, double fee) throws SQLException {
22 String studentSql = "INSERT INTO students (full_name, email, phone) VALUES (?, ?, ?)";
23 String enrollSql = "INSERT INTO course_enrollments (student_id, course_code, fee_paid) VALUES (?, ?, ?)";
24
25 // 1. Begin Transaction
26 connection.setAutoCommit(false);
27
28 try (PreparedStatement studentStmt = connection.prepareStatement(studentSql, Statement.RETURN_GENERATED_KEYS);
29 PreparedStatement enrollStmt = connection.prepareStatement(enrollSql)) {
30
31 // Insert Student Record
32 studentStmt.setString(1, name);
33 studentStmt.setString(2, email);
34 studentStmt.setString(3, phone);
35 studentStmt.executeUpdate();
36
37 // Retrieve Auto-Generated Primary Key
38 int newStudentId = -1;
39 try (ResultSet generatedKeys = studentStmt.getGeneratedKeys()) {
40 if (generatedKeys.next()) {
41 newStudentId = generatedKeys.getInt(1);
42 } else {
43 throw new SQLException("Failed to obtain generated student ID!");
44 }
45 }
46
47 // Insert Enrollment Record
48 enrollStmt.setInt(1, newStudentId);
49 enrollStmt.setString(2, courseCode);
50 enrollStmt.setDouble(3, fee);
51 enrollStmt.executeUpdate();
52
53 // 2. Commit Transaction
54 connection.commit();
55 System.out.printf("✅ Successfully enrolled student ID #%d into %s!%n", newStudentId, courseCode);
56 return newStudentId;
57
58 } catch (SQLException ex) {
59 // 3. Rollback on Failure
60 System.err.println("❌ Enrollment failed, triggering rollback: " + ex.getMessage());
61 connection.rollback();
62 throw ex;
63 } finally {
64 // 4. Restore Auto-Commit
65 connection.setAutoCommit(true);
66 }
67 }
68
69 /**
70 * Queries active enrollments with safe column mapping.
71 */
72 public List<Student> searchStudentsByName(String searchTerm) throws SQLException {
73 String sql = "SELECT id, full_name, email, phone FROM students WHERE full_name LIKE ? ORDER BY full_name ASC";
74 List<Student> list = new ArrayList<>();
75
76 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
77 pstmt.setString(1, "%" + searchTerm + "%");
78 try (ResultSet rs = pstmt.executeQuery()) {
79 while (rs.next()) {
80 list.add(new Student(
81 rs.getInt("id"),
82 rs.getString("full_name"),
83 rs.getString("email"),
84 rs.getString("phone")
85 ));
86 }
87 }
88 }
89 return list;
90 }
91}