File 24: Class Session Backend (sessions.jsp)
Folder: OnlineClassAttendance/admin/sessions.jsp
1. Purpose
This JSP file performs two operations:
- POST: Saves a new class session.
- GET: Retrieves all class sessions and returns JSON data.
2. Complete JSP Source Code
<%@ page import="java.sql.*" %>
<%@ page import="org.json.JSONObject" %>
<%@ 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 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)) {
// CREATE NEW CLASS SESSION
if ("add".equalsIgnoreCase(action)) {
String offeringText = request.getParameter("offeringId");
String dateText = request.getParameter("sessionDate");
String startTime = request.getParameter("startTime");
String endTime = request.getParameter("endTime");
String topic = request.getParameter("topic");
if (offeringText == null || dateText == null ||
startTime == null || endTime == null ||
topic == null ||
offeringText.trim().isEmpty() ||
dateText.trim().isEmpty() ||
startTime.trim().isEmpty() ||
endTime.trim().isEmpty() ||
topic.trim().isEmpty()) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"All fields are required\"}");
return;
}
int offeringId;
try {
offeringId = Integer.parseInt(offeringText);
if (offeringId <= 0) {
throw new NumberFormatException();
}
} catch (NumberFormatException ex) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"Invalid course offering ID\"}");
return;
}
// Validate date and time
java.sql.Date sessionDate;
try {
sessionDate = java.sql.Date.valueOf(dateText);
java.time.LocalTime start =
java.time.LocalTime.parse(startTime);
java.time.LocalTime end =
java.time.LocalTime.parse(endTime);
if (!end.isAfter(start)) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"End time must be later than start time\"}");
return;
}
} catch (Exception ex) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"Invalid date or time\"}");
return;
}
// Insert session
String insertSQL =
"INSERT INTO SESSIONS " +
"(OFFERING_ID, SESSION_DATE, START_TIME, END_TIME, TOPIC) " +
"VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement ps =
con.prepareStatement(insertSQL)) {
ps.setInt(1, offeringId);
ps.setDate(2, sessionDate);
ps.setString(3, startTime);
ps.setString(4, endTime);
ps.setString(5, topic.trim());
ps.executeUpdate();
out.print("{\"success\":true,\"message\":\"Class session created successfully\"}");
}
} else {
// RETRIEVE ALL CLASS SESSIONS
String sql =
"SELECT S.SESSION_ID, " +
"C.COURSE_CODE, C.COURSE_TITLE, " +
"O.SECTION, S.SESSION_DATE, " +
"S.START_TIME, S.END_TIME, S.TOPIC " +
"FROM SESSIONS S " +
"JOIN OFFERINGS O ON S.OFFERING_ID = O.OFFERING_ID " +
"JOIN COURSES C ON O.COURSE_ID = C.COURSE_ID " +
"ORDER BY S.SESSION_DATE DESC, S.SESSION_ID DESC";
StringBuilder json = new StringBuilder();
json.append("{\"success\":true,\"sessions\":[");
try (PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
boolean first = true;
while (rs.next()) {
if (!first) {
json.append(",");
}
first = false;
json.append("{");
json.append("\"id\":")
.append(rs.getInt("SESSION_ID"))
.append(",");
String course =
rs.getString("COURSE_CODE") + " - " +
rs.getString("COURSE_TITLE");
json.append("\"course\":")
.append(JSONObject.quote(course))
.append(",");
json.append("\"section\":")
.append(JSONObject.quote(rs.getString("SECTION")))
.append(",");
json.append("\"sessionDate\":")
.append(JSONObject.quote(
rs.getDate("SESSION_DATE").toString()))
.append(",");
json.append("\"startTime\":")
.append(JSONObject.quote(rs.getString("START_TIME")))
.append(",");
json.append("\"endTime\":")
.append(JSONObject.quote(rs.getString("END_TIME")))
.append(",");
json.append("\"topic\":")
.append(JSONObject.quote(rs.getString("TOPIC")));
json.append("}");
}
}
json.append("]}");
out.print(json.toString());
}
}
} catch (Exception e) {
application.log("Error in sessions.jsp", e);
if (!response.isCommitted()) {
response.setStatus(500);
out.print("{\"success\":false,\"message\":\"An internal server error occurred\"}");
}
}
%>3. How the Code Works
- Admin verification: Checks whether the user has an administrator session.
- Input validation: Checks all required fields before inserting a session.
- Date and time validation: Ensures the date and time values are valid and the end time is later than the start time.
- Database insertion: Saves the session details using JDBC PreparedStatement.
- SQL JOIN: Retrieves course code, course title, and section information.
- JSON response: Returns the session information to sessions.html.
4. Expected Database Table
Execute this SQL statement if the SESSIONS table has not already been created:
CREATE TABLE SESSIONS (
SESSION_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
OFFERING_ID NUMBER NOT NULL,
SESSION_DATE DATE NOT NULL,
START_TIME VARCHAR2(10),
END_TIME VARCHAR2(10),
TOPIC VARCHAR2(200),
CONSTRAINT FK_SESSION_OFFERING
FOREIGN KEY (OFFERING_ID)
REFERENCES OFFERINGS(OFFERING_ID)
);5. How to Test
- Start Oracle Database and Apache Tomcat.
- Log in as an administrator.
- Open admin/sessions.html.
- Select a course offering.
- Enter the session date, start time, end time, and topic.
- Click Create Session.
- Check whether the session appears in the table.
Important: The database credentials must match your Oracle configuration. The ojdbc8.jar and json.jar libraries must also be available to Tomcat.
Next File: File 25 of 28 — admin/attendance.html
In the next tutorial, we will create the attendance management page where administrators can select a class session and record student attendance.
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন