File 16 of 28: courses.jsp

File 16 of 28: courses.jsp

Folder: OnlineClassAttendance/admin/courses.jsp

1. Purpose

This file receives course information from courses.html, saves it in the Oracle COURSES table and returns the course list in JSON format.

2. Complete Source Code

<%@ page import="java.sql.*" %>
<%@ page import="org.json.JSONObject" %>
<%@ page contentType="application/json" pageEncoding="UTF-8" %>

<%
    response.setCharacterEncoding("UTF-8");

    // Check admin 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 dbURL = "jdbc:oracle:thin:@localhost:1521/XEPDB1";
    String dbUser = "YOUR_DB_USERNAME";
    String dbPassword = "YOUR_DB_PASSWORD";

    String action = request.getParameter("action");

    try {

        Class.forName("oracle.jdbc.OracleDriver");

        try (Connection con = DriverManager.getConnection(
                dbURL, dbUser, dbPassword)) {

            // Add a new course
            if ("add".equals(action)) {

                String courseCode = request.getParameter("courseCode");
                String courseTitle = request.getParameter("courseTitle");
                String creditText = request.getParameter("credit");
                String deptIdText = request.getParameter("deptId");

                // Validate required fields
                if (courseCode == null || courseCode.trim().isEmpty()
                    || courseTitle == null || courseTitle.trim().isEmpty()
                    || creditText == null || creditText.trim().isEmpty()
                    || deptIdText == null || deptIdText.trim().isEmpty()) {

                    response.setStatus(400);
                    out.print("{\"success\":false,\"message\":\"Please fill in all required fields\"}");
                    return;
                }

                double credit;
                int deptId;

                try {
                    credit = Double.parseDouble(creditText);
                    deptId = Integer.parseInt(deptIdText);

                    if (credit <= 0 || credit > 6 || deptId <= 0) {
                        throw new NumberFormatException();
                    }

                } catch (NumberFormatException e) {
                    response.setStatus(400);
                    out.print("{\"success\":false,\"message\":\"Invalid credit or department value\"}");
                    return;
                }

                String sql =
                    "INSERT INTO COURSES " +
                    "(COURSE_CODE, COURSE_TITLE, CREDIT, DEPT_ID) " +
                    "VALUES (?, ?, ?, ?)";

                try (PreparedStatement ps = con.prepareStatement(sql)) {

                    ps.setString(1, courseCode.trim());
                    ps.setString(2, courseTitle.trim());
                    ps.setDouble(3, credit);
                    ps.setInt(4, deptId);

                    ps.executeUpdate();

                    out.print("{");
                    out.print("\"success\":true,");
                    out.print("\"message\":\"Course added successfully\"");
                    out.print("}");
                }

            } else {

                // Retrieve all courses
                String sql =
                    "SELECT C.COURSE_ID, C.COURSE_CODE, " +
                    "C.COURSE_TITLE, C.CREDIT, D.DEPT_NAME " +
                    "FROM COURSES C " +
                    "JOIN DEPARTMENTS D ON C.DEPT_ID = D.DEPT_ID " +
                    "ORDER BY C.COURSE_ID";

                try (PreparedStatement ps = con.prepareStatement(sql);
                     ResultSet rs = ps.executeQuery()) {

                    StringBuilder json = new StringBuilder();

                    json.append("{\"success\":true,\"courses\":[");

                    boolean first = true;

                    while (rs.next()) {

                        if (!first) {
                            json.append(",");
                        }

                        first = false;

                        json.append("{");

                        json.append("\"id\":")
                            .append(rs.getInt("COURSE_ID")).append(",");

                        json.append("\"code\":")
                            .append(JSONObject.quote(
                                rs.getString("COURSE_CODE"))).append(",");

                        json.append("\"title\":")
                            .append(JSONObject.quote(
                                rs.getString("COURSE_TITLE"))).append(",");

                        json.append("\"credit\":")
                            .append(rs.getDouble("CREDIT")).append(",");

                        json.append("\"department\":")
                            .append(JSONObject.quote(
                                rs.getString("DEPT_NAME")));

                        json.append("}");
                    }

                    json.append("]}");

                    out.print(json.toString());
                }
            }
        }

    } catch (Exception e) {

        application.log("Error in courses.jsp", e);

        response.setStatus(500);

        out.print("{\"success\":false,\"message\":\"A server error occurred\"}");
    }
%>

3. How the Code Works

Step 1: Admin verification
The JSP checks the session and confirms that the logged-in user has the ADMIN role.

Step 2: Receive course information
The JSP receives courseCode, courseTitle, credit and deptId from the HTML form.

Step 3: Insert course data
A PreparedStatement inserts the information into the COURSES table. The department ID establishes the relationship between COURSES and DEPARTMENTS.

Step 4: Retrieve course records
A SQL JOIN retrieves the course information together with the corresponding department name.

Step 5: Return JSON
The JSP sends the result to courses.html, where JavaScript displays the records in the course table.

4. Important SQL Concepts

SELECT C.COURSE_TITLE, D.DEPT_NAME
FROM COURSES C
JOIN DEPARTMENTS D
ON C.DEPT_ID = D.DEPT_ID;

This query demonstrates the relationship between two database tables through a foreign key.

5. How to Test

  1. Start Oracle Database and Tomcat.
  2. Log in using an administrator account.
  3. Open admin/courses.html.
  4. Select a department from the dropdown.
  5. Enter a course code, title and credit.
  6. Click Add Course.
  7. Verify the new record in the course list and Oracle COURSES table.

6. Required Configuration

  • Replace YOUR_DB_USERNAME and YOUR_DB_PASSWORD.
  • Ensure ojdbc8.jar and json.jar are available in Tomcat/lib.
  • Ensure the COURSES and DEPARTMENTS tables exist.
  • Restart Tomcat if you have added new JAR files.

Note: The HTML page performs basic input checks, but the JSP backend also validates incoming values. Database constraints should additionally prevent duplicate course codes and invalid department references.

Next File

File 17 of 28 — admin/terms.html
The next page will manage academic terms or semesters, including term name, start date and end date.

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: পোস্টগুলি (Atom)