File 26: Attendance Management Backend (attendance.jsp)
Folder: OnlineClassAttendance/admin/attendance.jsp
1. Purpose
This JSP file connects the attendance management page with Oracle Database. It uses JDBC to load students, save attendance, and retrieve attendance records.
2. Complete JSP Source Code
<%@ page import="java.sql.*" %>
<%@ page import="org.json.*" %>
<%@ page contentType="application/json; charset=UTF-8" pageEncoding="UTF-8" %>
<%
// Check administrator session
Integer userId = (Integer) session.getAttribute("userId");
String role = (String) session.getAttribute("role");
if (userId == null || !"ADMIN".equals(role)) {
response.setStatus(401);
out.print("{\"success\":false,\"message\":\"Unauthorized access\"}");
return;
}
String action = request.getParameter("action");
String sessionText = request.getParameter("sessionId");
String dbURL = "jdbc:oracle:thin:@localhost:1521/XEPDB1";
String dbUser = "YOUR_DB_USERNAME";
String dbPassword = "YOUR_DB_PASSWORD";
try {
Class.forName("oracle.jdbc.OracleDriver");
try (Connection con = DriverManager.getConnection(
dbURL, dbUser, dbPassword)) {
// Validate session ID
int sessionId;
try {
sessionId = Integer.parseInt(sessionText);
if (sessionId <= 0) {
throw new NumberFormatException();
}
} catch (Exception ex) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"Valid session ID is required\"}");
return;
}
// Check whether session exists
String checkSessionSQL =
"SELECT COUNT(*) FROM SESSIONS WHERE SESSION_ID = ?";
try (PreparedStatement ps =
con.prepareStatement(checkSessionSQL)) {
ps.setInt(1, sessionId);
try (ResultSet rs = ps.executeQuery()) {
rs.next();
if (rs.getInt(1) == 0) {
response.setStatus(404);
out.print("{\"success\":false,\"message\":\"Class session not found\"}");
return;
}
}
}
// =====================================
// SAVE ATTENDANCE
// =====================================
if ("save".equalsIgnoreCase(action)) {
String attendanceData =
request.getParameter("attendanceData");
if (attendanceData == null ||
attendanceData.trim().isEmpty()) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"Attendance data is required\"}");
return;
}
JSONArray attendanceArray;
try {
attendanceArray = new JSONArray(attendanceData);
} catch (JSONException ex) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"Invalid attendance data\"}");
return;
}
if (attendanceArray.length() == 0) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"No attendance records supplied\"}");
return;
}
// Get enrolled students for this session
String enrolledSQL =
"SELECT E.STUDENT_ID " +
"FROM ENROLLMENTS E " +
"JOIN SESSIONS S ON E.OFFERING_ID = S.OFFERING_ID " +
"WHERE S.SESSION_ID = ?";
java.util.Set<Integer> enrolledStudents =
new java.util.HashSet<Integer>();
try (PreparedStatement ps =
con.prepareStatement(enrolledSQL)) {
ps.setInt(1, sessionId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
enrolledStudents.add(rs.getInt("STUDENT_ID"));
}
}
}
if (enrolledStudents.isEmpty()) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"No enrolled students found for this session\"}");
return;
}
// Validate every attendance entry before saving
java.util.Set<Integer> submittedStudents =
new java.util.HashSet<Integer>();
for (int i = 0; i < attendanceArray.length(); i++) {
JSONObject item = attendanceArray.getJSONObject(i);
int studentId = item.getInt("studentId");
String status = item.getString("status");
if (!enrolledStudents.contains(studentId) ||
!submittedStudents.add(studentId)) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"Invalid or duplicate student in attendance data\"}");
return;
}
if (!status.equals("PRESENT") &&
!status.equals("ABSENT") &&
!status.equals("LATE") &&
!status.equals("EXCUSED")) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"Invalid attendance status\"}");
return;
}
}
// Save or update attendance
String mergeSQL =
"MERGE INTO ATTENDANCE A " +
"USING (SELECT ? SESSION_ID, ? STUDENT_ID FROM DUAL) X " +
"ON (A.SESSION_ID = X.SESSION_ID " +
"AND A.STUDENT_ID = X.STUDENT_ID) " +
"WHEN MATCHED THEN UPDATE SET " +
"A.STATUS = ?, A.REMARKS = ? " +
"WHEN NOT MATCHED THEN INSERT " +
"(SESSION_ID, STUDENT_ID, STATUS, REMARKS) " +
"VALUES (X.SESSION_ID, X.STUDENT_ID, ?, ?)";
boolean originalAutoCommit = con.getAutoCommit();
try {
con.setAutoCommit(false);
try (PreparedStatement ps =
con.prepareStatement(mergeSQL)) {
for (int i = 0; i < attendanceArray.length(); i++) {
JSONObject item =
attendanceArray.getJSONObject(i);
int studentId = item.getInt("studentId");
String status = item.getString("status");
String remarks =
item.optString("remarks", "");
if (remarks.trim().isEmpty()) {
remarks = null;
}
ps.setInt(1, sessionId);
ps.setInt(2, studentId);
ps.setString(3, status);
ps.setString(4, remarks);
ps.setString(5, status);
ps.setString(6, remarks);
ps.addBatch();
}
ps.executeBatch();
}
con.commit();
out.print("{\"success\":true,\"message\":\"Attendance saved successfully\"}");
} catch (Exception ex) {
con.rollback();
throw ex;
} finally {
con.setAutoCommit(originalAutoCommit);
}
} else {
// =====================================
// LOAD STUDENTS OR VIEW RECORDS
// =====================================
boolean viewRecords =
"records".equalsIgnoreCase(
request.getParameter("view"));
String sql;
if (viewRecords) {
sql =
"SELECT S.STUDENT_ID, S.STUDENT_NAME, " +
"A.STATUS, A.REMARKS " +
"FROM ENROLLMENTS E " +
"JOIN SESSIONS CS ON E.OFFERING_ID = CS.OFFERING_ID " +
"JOIN STUDENTS S ON E.STUDENT_ID = S.STUDENT_ID " +
"LEFT JOIN ATTENDANCE A ON " +
"A.SESSION_ID = CS.SESSION_ID " +
"AND A.STUDENT_ID = S.STUDENT_ID " +
"WHERE CS.SESSION_ID = ? " +
"ORDER BY S.STUDENT_ID";
} else {
sql =
"SELECT S.STUDENT_ID, S.STUDENT_NAME, S.BATCH, " +
"A.STATUS, A.REMARKS " +
"FROM ENROLLMENTS E " +
"JOIN SESSIONS CS ON E.OFFERING_ID = CS.OFFERING_ID " +
"JOIN STUDENTS S ON E.STUDENT_ID = S.STUDENT_ID " +
"LEFT JOIN ATTENDANCE A ON " +
"A.SESSION_ID = CS.SESSION_ID " +
"AND A.STUDENT_ID = S.STUDENT_ID " +
"WHERE CS.SESSION_ID = ? " +
"ORDER BY S.STUDENT_ID";
}
StringBuilder json = new StringBuilder();
if (viewRecords) {
json.append("{\"success\":true,\"records\":[");
} else {
json.append("{\"success\":true,\"students\":[");
}
try (PreparedStatement ps =
con.prepareStatement(sql)) {
ps.setInt(1, sessionId);
try (ResultSet rs = ps.executeQuery()) {
boolean first = true;
while (rs.next()) {
if (!first) {
json.append(",");
}
first = false;
json.append("{");
json.append("\"studentId\":")
.append(rs.getInt("STUDENT_ID"))
.append(",");
json.append("\"studentName\":")
.append(JSONObject.quote(
rs.getString("STUDENT_NAME")))
.append(",");
if (!viewRecords) {
json.append("\"batch\":")
.append(JSONObject.quote(
rs.getString("BATCH")))
.append(",");
}
String status = rs.getString("STATUS");
json.append("\"status\":");
if (status == null) {
json.append("null");
} else {
json.append(JSONObject.quote(status));
}
json.append(",");
String remarks = rs.getString("REMARKS");
json.append("\"remarks\":");
if (remarks == null) {
json.append("null");
} else {
json.append(JSONObject.quote(remarks));
}
json.append("}");
}
}
}
json.append("]}");
out.print(json.toString());
}
}
} catch (Exception e) {
application.log("Error in attendance.jsp", e);
if (!response.isCommitted()) {
response.setStatus(500);
out.print("{\"success\":false,\"message\":\"An internal server error occurred\"}");
}
}
%>3. How the Code Works
- Session verification: Checks whether the selected class session exists.
- Student retrieval: Retrieves students enrolled in the selected course offering.
- Attendance validation: Checks student IDs and attendance status values.
- MERGE statement: Updates existing attendance records or inserts new records.
- Transaction management: Saves all submitted attendance records together. If an error occurs, the transaction is rolled back.
- Attendance retrieval: Uses SQL JOIN operations to retrieve saved attendance information.
- JSON response: Sends student and attendance information to attendance.html.
4. Expected Database Table
Execute this SQL statement if the ATTENDANCE table has not already been created:
CREATE TABLE ATTENDANCE (
ATTENDANCE_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
SESSION_ID NUMBER NOT NULL,
STUDENT_ID NUMBER NOT NULL,
STATUS VARCHAR2(20) NOT NULL,
REMARKS VARCHAR2(300),
CONSTRAINT FK_ATT_SESSION
FOREIGN KEY (SESSION_ID)
REFERENCES SESSIONS(SESSION_ID),
CONSTRAINT FK_ATT_STUDENT
FOREIGN KEY (STUDENT_ID)
REFERENCES STUDENTS(STUDENT_ID),
CONSTRAINT UK_SESSION_STUDENT
UNIQUE (SESSION_ID, STUDENT_ID),
CONSTRAINT CK_ATT_STATUS
CHECK (STATUS IN ('PRESENT', 'ABSENT', 'LATE', 'EXCUSED'))
);5. How to Test
- Start Oracle Database and Apache Tomcat.
- Log in as an administrator.
- Open admin/attendance.html.
- Select an existing class session.
- Click Load Students.
- Assign attendance statuses to students.
- Click Save Attendance.
- Click View Attendance to verify the saved records.
- Change a student's attendance status and save again. The existing record should be updated instead of duplicated.
6. Important Configuration
- Replace YOUR_DB_USERNAME and YOUR_DB_PASSWORD with your Oracle credentials.
- Ensure ojdbc8.jar and json.jar are available in Tomcat's library folder.
- Use the same database schema as the previous project files.
- This implementation assumes the Oracle SESSIONS, OFFERINGS, ENROLLMENTS, STUDENTS, and ATTENDANCE tables have been created.
Important concept: The SQL MERGE statement is useful when an application needs to insert a record if it does not exist or update it if it already exists.
Next File: File 27 of 28 — admin/reports.html
In the next tutorial, we will create an attendance reporting page to view student attendance summaries and records.
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন