Core Domain Model and Schema Improvements

The system manages two primary roles: administrators and general employees. Administrators oversee reservations, meeting lists, department structures, employee approvals, and room creation. Regular employees can book meetings, view upcoming events, check room availability, read notifications, and cancel existing reservations.

Below is an optimized SQL foundation with improved naming, character sets, and structure clarity for the entities: counter, department, employee, meeting, meeting_employee, and room.

-- Database initialization with InnoDB engine and utf8mb4 support
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Visit counter table
DROP TABLE IF EXISTS `counter`;
CREATE TABLE `counter` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visit_count` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Site visit counter';

INSERT INTO `counter` VALUES (1, 5);

-- Department structure
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT 'Department name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Organization departments';

INSERT INTO `department` VALUES 
(13, 'Engineering'),
(14, 'Finance'),
(15, 'Marketing'),
(16, 'Business'),
(17, 'Sales'),
(20, 'Manufacturing');

-- Employee accounts
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL COMMENT 'Hashed credential',
  `real_name` varchar(50) NOT NULL,
  `phone` varchar(30) DEFAULT '',
  `email` varchar(100) DEFAULT '',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '0=active, 1=pending, 2=rejected',
  `dept_id` int(11) NOT NULL,
  `role` tinyint NOT NULL DEFAULT 1 COMMENT '1=employee, 2=admin',
  PRIMARY KEY (`id`),
  KEY `idx_dept` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee profiles';

INSERT INTO `employee` VALUES 
(37, 'admin', 'hashed_123456', 'System Admin', '11044445555', 'admin@company.com', 0, 13, 2);

-- Meeting room catalog
DROP TABLE IF EXISTS `room`;
CREATE TABLE `room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room_number` varchar(20) NOT NULL COMMENT 'Physical room identifier',
  `name` varchar(100) NOT NULL,
  `capacity` int(11) NOT NULL DEFAULT 0,
  `status` tinyint DEFAULT 0 COMMENT '0=available, 1=occupied',
  `description` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_room_number` (`room_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Meeting rooms';

-- Meeting reservations
DROP TABLE IF EXISTS `meeting`;
CREATE TABLE `meeting` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `room_id` int(11) NOT NULL,
  `organizer_id` int(11) NOT NULL COMMENT 'Employee who created the meeting',
  `attendee_count` int(11) DEFAULT 0,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `description` varchar(500) DEFAULT '',
  `status` tinyint DEFAULT 0 COMMENT '0=scheduled, 1=cancelled',
  `cancelled_at` datetime DEFAULT NULL,
  `cancel_reason` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_room_time` (`room_id`, `start_time`, `end_time`),
  KEY `idx_organizer` (`organizer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Meetings and reservations';

-- Meeting participants
DROP TABLE IF EXISTS `meeting_participant`;
CREATE TABLE `meeting_participant` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `meeting_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_meeting_employee` (`meeting_id`, `employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Meeting participant mapping';

SET FOREIGN_KEY_CHECKS = 1;

Servlet Request Handling Architecture

The application is built with a servlet-based controller layer that delegates to service classes. Each HTTP endpoint corresponds to a distinct servlet, described below.

Authentication and Account Management

LoginServlet accepts credentials, hashes the password, and authenticates via EmployeeService. It redirects to the dashboard on success or back to the login form with an error message.

ChangePasswordServlet validates the current password using EmployeeService, enforces password complexity rules, and updates the credential hash stored in the database before redirecting.

Employee and Department Administration

SearchEmployeeServlet accepts filter parameters (name, department, status) and invokes EmployeeService.search(). Results are forwarded to employee_list.jsp for rendering.

UpdateEmpStatusServlet modifies an employee's approval state. It toggles between pending, active, and rejected statuses using EmployeeService.updateStatus(empId, newStatus).

AddDepServlet and UpdateDepServlet manage the department catalog. AddDepServlet inserts a new department name and redirects upon success, while UpdateDepServlet applies name changes and writes a JSON acknowledgment via PrintWriter.

DeleteDepServlet removes a department antry if no employees are assigned, using a pre-check in DepartmentService.

Meeting Room Infrastructure

MeetingRoomServlet supports both room creation and editing. It reads parameters like roomNumber, capacity, and description, then delegates to MeetingRoomService.addOrUpdate(). The method distinguishes between insert and update based on the presence of a room ID.

Reservation and Notification Workflow

DoBookMeetingServlet processes booking requests. It validates room availability by checking timestamp overlaps via MeetingService.isRoomAvailable(roomId, start, end), then creates the meeting record along with participant entries from selected employee IDs. On conflict, it returns an error view.

MeetingDetailsServlet loads a meeting's full record plus the participant list through MeetingService.getDetail(meetingId) and forwards data to meeting_details.jsp.

NotificationsServlet aggregates two datasets for an employee: upcoming meetings within seven days and recently cancelled meetings. It calls MeetingService.getUpcomingForUser(empId) and MeetingService.getCancelledForUser(empId), then forwards the combined list to notifications.jsp.

Backend Logic and Service Interaction

The flow can be summarized in the following decision path, which all servlets generally follow:

1. Extract request parameters.
2. Delegate business rules to the relevant service (EmployeeService, MeetingService, DepartmentService, MeetingRoomService).
3. Store outcome or domain objects in the request scope.
4. Dispatch to a JSP view for rendering or redirect to avoid duplicate submissions.

Database Connectivity Enhancements

The original sources embedded connection strings and tool‑specific comments directly in SQL dumps. The refactored approach externalizes configuraton:

• Database credentials, pool sizes, and driver class are moved to database.properties or environment variables.
• Connection management is centralized in a DataSourceFactory that creates a HikariCP or DBCP pool.
• SQL scripts are separated into schema.sql and data_init.sql to simplify migration and version control.

This encapsulation improves maintainability and prevents acccidental exposure of sensitive connection details.

Sample Revised Java Implementation

A modernized servlet example for updating department information demonstrates parameter validation, service delegation, and a clean response structure.

@WebServlet("/admin/updateDepartment")
public class DepartmentUpdateServlet extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        
        String deptIdParam = req.getParameter("deptId");
        String newName = req.getParameter("deptName");
        
        Map<String, String> result = new HashMap<>();
        resp.setContentType("application/json");
        
        if (deptIdParam == null || newName == null || newName.trim().isEmpty()) {
            result.put("status", "error");
            result.put("message", "Department ID and name are required.");
            writeJsonResponse(resp, result);
            return;
        }
        
        try {
            int deptId = Integer.parseInt(deptIdParam);
            DepartmentService service = DepartmentService.getInstance();
            boolean updated = service.renameDepartment(deptId, newName.trim());
            
            if (updated) {
                result.put("status", "success");
                result.put("message", "Department name updated.");
            } else {
                result.put("status", "error");
                result.put("message", "No department found with the given ID.");
            }
        } catch (NumberFormatException ex) {
            result.put("status", "error");
            result.put("message", "Invalid department ID format.");
        }
        
        writeJsonResponse(resp, result);
    }
    
    private void writeJsonResponse(HttpServletResponse resp, Map<String, String> data) 
            throws IOException {
        PrintWriter out = resp.getWriter();
        out.print(new Gson().toJson(data));
        out.flush();
    }
}

This revised servlet avoids direct coupling to a view layer and returns structured JSON, making it easier to integrate with a frontend built with AJAX or modern frameworks.