source: src/main/java/DAO/impl/ProfessorDAOImpl.java@ 1eb7a55

Last change on this file since 1eb7a55 was 1eb7a55, checked in by Elena Markovska <elena.elenamarkovska@…>, 11 days ago

Initial commit - Scholaris project code
  • Property mode set to 100644
File size: 13.7 KB
Line 
1package DAO.impl;
2
3import DAO.JDBCUtils;
4import DAO.ProfessorDAO;
5import model.Professor;
6import model.Student;
7import model.University;
8
9import java.sql.*;
10import java.util.ArrayList;
11import java.util.HashMap;
12import java.util.List;
13import java.util.Map;
14
15public class ProfessorDAOImpl implements ProfessorDAO {
16
17 public ProfessorDAOImpl() {
18 }
19
20 @Override
21 public List<Professor> getall() throws SQLException {
22 // We use a LEFT JOIN to fetch the affiliated university information if it exists
23 final String SELECT_ALL = "SELECT p.*, a.university_id FROM Professor p " +
24 "LEFT JOIN affiliated a ON p.id = a.professor_id";
25 List<Professor> professorList = new ArrayList<>();
26
27 try (Connection connection = JDBCUtils.getConnection();
28 PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL)) {
29 ResultSet rs = preparedStatement.executeQuery();
30 while (rs.next()) {
31 professorList.add(mapRowToProfessor(rs));
32 }
33 } catch (SQLException e) {
34 System.err.println("Error fetching all professors: " + e.getMessage());
35 throw e;
36 }
37 return professorList;
38 }
39
40 @Override
41 public List<Professor> getAllSortedById() throws SQLException {
42 final String SELECT_SORTED = "SELECT p.*, a.university_id FROM Professor p " +
43 "LEFT JOIN affiliated a ON p.id = a.professor_id ORDER BY p.id ASC";
44 List<Professor> professors = new ArrayList<>();
45
46 try (Connection connection = JDBCUtils.getConnection();
47 PreparedStatement preparedStatement = connection.prepareStatement(SELECT_SORTED)) {
48 ResultSet rs = preparedStatement.executeQuery();
49 while (rs.next()) {
50 professors.add(mapRowToProfessor(rs));
51 }
52 } catch (SQLException e) {
53 System.err.println("Error fetching sorted professors: " + e.getMessage());
54 throw e;
55 }
56 return professors;
57 }
58
59 @Override
60 public Professor getByID(Long id) throws SQLException {
61 final String SQL = "SELECT p.*, a.university_id FROM Professor p " +
62 "LEFT JOIN affiliated a ON p.id = a.professor_id WHERE p.id = ?";
63 try (Connection connection = JDBCUtils.getConnection();
64 PreparedStatement ps = connection.prepareStatement(SQL)) {
65 ps.setLong(1, id);
66 ResultSet rs = ps.executeQuery();
67 if (rs.next()) {
68 return mapRowToProfessor(rs);
69 }
70 return null;
71 }
72 }
73
74 @Override
75 public void update(Professor professor) throws SQLException {
76 final String UPDATE_PROF = "UPDATE Professor SET name = ?, surname = ?, age = ?, facultyid = ? WHERE id = ?";
77 final String DELETE_AFFILIATION = "DELETE FROM affiliated WHERE professor_id = ?";
78 final String INSERT_AFFILIATION = "INSERT INTO affiliated (university_id, professor_id) VALUES (?, ?)";
79
80 try (Connection conn = JDBCUtils.getConnection()) {
81 conn.setAutoCommit(false);
82 try {
83 try (PreparedStatement ps = conn.prepareStatement(UPDATE_PROF)) {
84 ps.setString(1, professor.getName());
85 ps.setString(2, professor.getSurname());
86 ps.setInt(3, professor.getAge());
87 ps.setLong(4, professor.getFacultyid());
88 ps.setLong(5, professor.getId());
89 ps.executeUpdate();
90 }
91
92 try (PreparedStatement psDel = conn.prepareStatement(DELETE_AFFILIATION)) {
93 psDel.setLong(1, professor.getId());
94 psDel.executeUpdate();
95 }
96
97 // CRITICAL FIX: Check if university AND its ID are not null
98 if (professor.getUniversity() != null && professor.getUniversity().getId() != null) {
99 try (PreparedStatement psIns = conn.prepareStatement(INSERT_AFFILIATION)) {
100 psIns.setLong(1, professor.getUniversity().getId());
101 psIns.setLong(2, professor.getId());
102 psIns.executeUpdate();
103 }
104 }
105 conn.commit();
106 } catch (SQLException e) {
107 conn.rollback();
108 throw e;
109 }
110 }
111 }
112
113 @Override
114 public void save(Professor professor) throws SQLException {
115 final String INSERT_PROF = "INSERT INTO Professor (name, surname, age, facultyid) VALUES (?, ?, ?, ?)";
116 final String INSERT_AFFILIATION = "INSERT INTO affiliated (university_id, professor_id) VALUES (?, ?)";
117
118 try (Connection conn = JDBCUtils.getConnection()) {
119 conn.setAutoCommit(false);
120 try (PreparedStatement ps = conn.prepareStatement(INSERT_PROF, Statement.RETURN_GENERATED_KEYS)) {
121 ps.setString(1, professor.getName());
122 ps.setString(2, professor.getSurname());
123 ps.setInt(3, professor.getAge());
124 ps.setLong(4, professor.getFacultyid());
125 ps.executeUpdate();
126
127 ResultSet rs = ps.getGeneratedKeys();
128 if (rs.next()) {
129 long newProfId = rs.getLong(1);
130
131 // CRITICAL FIX: Check if university AND its ID are not null
132 if (professor.getUniversity() != null && professor.getUniversity().getId() != null) {
133 try (PreparedStatement psAff = conn.prepareStatement(INSERT_AFFILIATION)) {
134 psAff.setLong(1, professor.getUniversity().getId());
135 psAff.setLong(2, newProfId);
136 psAff.executeUpdate();
137 }
138 }
139 }
140 conn.commit();
141 } catch (SQLException e) {
142 conn.rollback();
143 throw e;
144 }
145 }
146 }
147
148 @Override
149 public void delete(Long id) {
150 // ON DELETE CASCADE in DB handles the affiliated table automatically
151 String sql = "DELETE FROM Professor WHERE id = ?";
152 try (Connection connection = JDBCUtils.getConnection();
153 PreparedStatement ps = connection.prepareStatement(sql)) {
154 ps.setLong(1, id);
155 ps.executeUpdate();
156 } catch (SQLException e) {
157 System.err.println("Error deleting professor: " + e.getMessage());
158 }
159 }
160
161 private Professor mapRowToProfessor(ResultSet rs) throws SQLException {
162 Professor p = new Professor(
163 rs.getLong("id"),
164 rs.getString("name"),
165 rs.getString("surname"),
166 rs.getInt("age"),
167 rs.getLong("facultyid"),
168 null // Initialize with null University, set below
169 );
170
171 long univId = rs.getLong("university_id");
172 if (!rs.wasNull()) {
173 University u = new University();
174 u.setId(univId);
175 p.setUniversity(u);
176 }
177 return p;
178 }
179
180 @Override
181 public Map<String, Double> findAverageAgeByFaculty() {
182 String query = "SELECT f.name as faculty_name, AVG(p.age) as average_age " +
183 "FROM professor p JOIN faculty f ON f.id = p.facultyid " +
184 "GROUP BY f.name";
185 Map<String, Double> result = new HashMap<>();
186 try (Connection connection = JDBCUtils.getConnection();
187 PreparedStatement stmt = connection.prepareStatement(query)) {
188 ResultSet rs = stmt.executeQuery();
189 while (rs.next()) {
190 result.put(rs.getString("faculty_name"), rs.getDouble("average_age"));
191 }
192 } catch (SQLException e) {
193 e.printStackTrace();
194 }
195 return result;
196 }
197
198 @Override
199 public List<Student> findStudentsByUniversityId(int universityId) {
200 String query = "SELECT s.id, s.name, s.surname, s.location, s.studentindex, s.facultyid " +
201 "FROM student s " +
202 "JOIN faculty f ON s.facultyid = f.id " +
203 "JOIN university u ON f.university_id = u.id " +
204 "WHERE u.id = ?";
205 List<Student> students = new ArrayList<>();
206 try (Connection conn = JDBCUtils.getConnection();
207 PreparedStatement stmt = conn.prepareStatement(query)) {
208 stmt.setInt(1, universityId);
209 ResultSet rs = stmt.executeQuery();
210 while (rs.next()) {
211 students.add(new Student(
212 rs.getLong("id"), rs.getString("name"), rs.getString("surname"),
213 rs.getString("location"), rs.getInt("studentindex"), rs.getLong("facultyid")
214 ));
215 }
216 } catch (SQLException e) {
217 throw new RuntimeException(e);
218 }
219 return students;
220 }
221
222 @Override
223 public List<Student> findStudentsByFacultyId(int facultyId) {
224 final String QUERY = "SELECT id, name, surname, location, studentindex, facultyid FROM student WHERE facultyid = ?";
225 List<Student> students = new ArrayList<>();
226 try (Connection conn = JDBCUtils.getConnection();
227 PreparedStatement stmt = conn.prepareStatement(QUERY)) {
228 stmt.setInt(1, facultyId);
229 ResultSet rs = stmt.executeQuery();
230 while (rs.next()) {
231 students.add(new Student(
232 rs.getLong("id"), rs.getString("name"), rs.getString("surname"),
233 rs.getString("location"), rs.getInt("studentindex"), rs.getLong("facultyid")
234 ));
235 }
236 } catch (SQLException e) {
237 System.err.println("Error in findStudentsByFacultyId: " + e.getMessage());
238 }
239 return students;
240 }
241
242 @Override
243 public Map<String, Integer> findProfessorCountBySubject() {
244 String query = "SELECT s.name AS subject_name, COUNT(p.id) AS professor_count " +
245 "FROM subject s LEFT JOIN professor p ON s.professorid = p.id GROUP BY s.name";
246 Map<String, Integer> result = new HashMap<>();
247 try (Connection connection = JDBCUtils.getConnection();
248 PreparedStatement stmt = connection.prepareStatement(query);
249 ResultSet rs = stmt.executeQuery()) {
250 while (rs.next()) {
251 result.put(rs.getString("subject_name"), rs.getInt("professor_count"));
252 }
253 } catch (SQLException e) {
254 e.printStackTrace();
255 }
256 return result;
257 }
258
259 @Override
260 public Map<Integer, Double> findCompletedSemesterGPA() {
261 String query = "SELECT s.studentindex, AVG(ss.final_grade) AS semester_gpa " +
262 "FROM student s " +
263 "JOIN student_subject ss ON s.id = ss.student_id " +
264 "JOIN subject sub ON ss.subject_id = sub.id " +
265 "WHERE sub.semester = 2 " +
266 " AND ss.status = 'Completed' " +
267 " AND ss.final_grade IS NOT NULL " +
268 "GROUP BY s.id, s.studentindex, s.name, s.surname " +
269 "HAVING COUNT(ss.subject_id) = ( " +
270 " SELECT COUNT(*) " +
271 " FROM student_subject ss2 " +
272 " WHERE ss2.student_id = s.id " +
273 ") " +
274 "ORDER BY semester_gpa DESC";
275
276 Map<Integer, Double> result = new HashMap<>();
277 try (Connection connection = JDBCUtils.getConnection();
278 PreparedStatement stmt = connection.prepareStatement(query);
279 ResultSet rs = stmt.executeQuery()) {
280 while (rs.next()) {
281 result.put(rs.getInt("studentindex"), rs.getDouble("semester_gpa"));
282 }
283 } catch (SQLException e) {
284 System.err.println("Error in findCompletedSemesterGPA: " + e.getMessage());
285 e.printStackTrace();
286 }
287 return result;
288 }
289 @Override
290 public List<Map<String, Object>> findTopPerformersByEnrollmentYear() {
291 String query = "WITH StudentGPA AS ( " +
292 " SELECT s.studentindex, s.name || ' ' || s.surname AS student_name, " +
293 " EXTRACT(YEAR FROM ss.enrollment_date) AS enrollment_year, " +
294 " AVG(ss.final_grade) AS cumulative_gpa, " +
295 " RANK() OVER (PARTITION BY EXTRACT(YEAR FROM ss.enrollment_date) ORDER BY AVG(ss.final_grade) DESC) as gpa_rank " +
296 " FROM student s " +
297 " JOIN student_subject ss ON s.id = ss.student_id " +
298 " WHERE ss.final_grade IS NOT NULL " +
299 " GROUP BY s.studentindex, s.name, s.surname, EXTRACT(YEAR FROM ss.enrollment_date) " +
300 ") " +
301 "SELECT enrollment_year, studentindex, student_name, cumulative_gpa " +
302 "FROM StudentGPA " +
303 "WHERE gpa_rank = 1 " +
304 "ORDER BY enrollment_year DESC";
305
306 List<Map<String, Object>> resultList = new ArrayList<>();
307 try (Connection connection = JDBCUtils.getConnection();
308 PreparedStatement stmt = connection.prepareStatement(query);
309 ResultSet rs = stmt.executeQuery()) {
310 while (rs.next()) {
311 Map<String, Object> row = new HashMap<>();
312 row.put("enrollment_year", rs.getInt("enrollment_year"));
313 row.put("student_index", rs.getInt("studentindex"));
314 row.put("student_name", rs.getString("student_name"));
315 row.put("cumulative_gpa", rs.getDouble("cumulative_gpa"));
316 resultList.add(row);
317 }
318 } catch (SQLException e) {
319 System.err.println("Error in findTopPerformersByEnrollmentYear: " + e.getMessage());
320 e.printStackTrace();
321 }
322 return resultList;
323 }
324}
Note: See TracBrowser for help on using the repository browser.