Database Tible Setup

CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(255),
    location VARCHAR(255)
);

INSERT INTO department(dept_id, dept_name, location) VALUES(10, 'Sales', 'Beijing');
INSERT INTO department(dept_id, dept_name, location) VALUES(20, 'R&D', 'Shanghai');
INSERT INTO department(dept_id, dept_name, location) VALUES(30, 'Technical', 'Guangzhou');
INSERT INTO department(dept_id, dept_name, location) VALUES(40, 'Media', 'Shenzhen');

Application Architecture

HTML pages required for the appplication:

• Welcome page: index.html
• Department list: list.html
• Add department: add.html
• Edit department: edit.html
• Department details: detail.html

Core Functionalities

• View department list
• Add new department
• Delete department
• View department details
• Navigate to edit page
• Update department information

Development Environment Setup

Database Connection Utility

public class DatabaseManager {
    private static Properties config = loadConfig();
    
    static {
        try {
            Class.forName(config.getProperty("db.driver"));
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Database driver not found", e);
        }
    }
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(
            config.getProperty("db.url"),
            config.getProperty("db.user"),
            config.getProperty("db.password")
        );
    }
    
    public static void releaseResources(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Department List Implementation

Servlet Configuration

<servlet>
    <servlet-name>DepartmentList</servlet-name>
    <servlet-class>com.example.web.DepartmentListServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>DepartmentList</servlet-name>
    <url-pattern>/department/list</url-pattern>
</servlet-mapping>

Servlet Implementation

public class DepartmentListServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter writer = response.getWriter();
        String appContext = request.getContextPath();
        
        writer.write("<html><head><title>Department List</title>");
        writer.write("<script>");
        writer.write("function confirmDelete(id) {");
        writer.write("  if(confirm('Delete this department?')) {");
        writer.write("    location.href='" + appContext + "/department/delete?id=' + id;");
        writer.write("  }");
        writer.write("}");
        writer.write("</script></head>");
        writer.write("<body><h1>Department List</h1>| " + (++counter) + " | " + id + " | " + name + " | "); writer.write("[Delete](javascript:void(0)) "); writer.write("[Edit](<" + appContext + "/department/edit?id=" + id + ">) "); writer.write("[Details](<" + appContext + "/department/details?id=" + id + ">)"); writer.write(" |
|---|---|---|---|");
        writer.write("<a href='" + appContext + "/department/add'>Add Department</a>");
        writer.write("</body></html>");
    }
}

Department Details Implemantation

public class DepartmentDetailsServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter writer = response.getWriter();
        String departmentId = request.getParameter("id");
        
        writer.write("<html><body><h1>Department Details</h1>");
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet result = null;
        
        try {
            conn = DatabaseManager.getConnection();
            stmt = conn.prepareStatement("SELECT dept_id, dept_name, location FROM department WHERE dept_id = ?");
            stmt.setString(1, departmentId);
            result = stmt.executeQuery();
            
            if(result.next()) {
                writer.write("Department ID: " + result.getString("dept_id") + "<br>");
                writer.write("Department Name: " + result.getString("dept_name") + "<br>");
                writer.write("Location: " + result.getString("location") + "<br>");
            }
        } catch (SQLException e) {
            throw new ServletException("Database error", e);
        } finally {
            DatabaseManager.releaseResources(conn, stmt, result);
        }
        
        writer.write("<button onclick='history.back()'>Back</button>");
        writer.write("</body></html>");
    }
}

Department Deletion Implementation

public class DepartmentDeleteServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        String departmentId = request.getParameter("id");
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DatabaseManager.getConnection();
            stmt = conn.prepareStatement("DELETE FROM department WHERE dept_id = ?");
            stmt.setString(1, departmentId);
            
            int affectedRows = stmt.executeUpdate();
            if (affectedRows > 0) {
                String referer = request.getHeader("Referer");
                response.sendRedirect(referer);
            }
        } catch (SQLException e) {
            throw new ServletException("Delete operation failed", e);
        } finally {
            DatabaseManager.releaseResources(conn, stmt, null);
        }
    }
}

Department Addition Implementation

public class DepartmentAddServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter writer = response.getWriter();
        String appContext = request.getContextPath();
        
        writer.write("<html><body><h1>Add Department</h1>");
        writer.write("<form method='post' action='" + appContext + "/department/save'>");
        writer.write("Department ID: <input type='text' name='id'><br>");
        writer.write("Department Name: <input type='text' name='name'><br>");
        writer.write("Location: <input type='text' name='location'><br>");
        writer.write("<input type='submit' value='Save'>");
        writer.write("</form></body></html>");
    }
    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        String id = request.getParameter("id");
        String name = request.getParameter("name");
        String location = request.getParameter("location");
        
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DatabaseManager.getConnection();
            stmt = conn.prepareStatement("INSERT INTO department (dept_id, dept_name, location) VALUES (?, ?, ?)");
            stmt.setString(1, id);
            stmt.setString(2, name);
            stmt.setString(3, location);
            
            int affectedRows = stmt.executeUpdate();
            if (affectedRows > 0) {
                request.getRequestDispatcher("/department/list").forward(request, response);
            }
        } catch (SQLException e) {
            throw new ServletException("Insert operation failed", e);
        } finally {
            DatabaseManager.releaseResources(conn, stmt, null);
        }
    }
}

Department Update Implementation

public class DepartmentUpdateServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter writer = response.getWriter();
        String appContext = request.getContextPath();
        
        String id = request.getParameter("id");
        String name = request.getParameter("name");
        String location = request.getParameter("location");
        
        writer.write("<html><body><h1>Update Department</h1>");
        writer.write("<form method='post' action='" + appContext + "/department/update'>");
        writer.write("Department ID: <input type='text' name='id' value='" + id + "' readonly><br>");
        writer.write("Department Name: <input type='text' name='name' value='" + name + "'><br>");
        writer.write("Location: <input type='text' name='location' value='" + location + "'><br>");
        writer.write("<input type='submit' value='Update'>");
        writer.write("</form></body></html>");
    }
    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        
        String id = request.getParameter("id");
        String name = request.getParameter("name");
        String location = request.getParameter("location");
        
        Connection conn = null;
        PreparedStatement stmt = null;
        
        try {
            conn = DatabaseManager.getConnection();
            stmt = conn.prepareStatement("UPDATE department SET dept_name = ?, location = ? WHERE dept_id = ?");
            stmt.setString(1, name);
            stmt.setString(2, location);
            stmt.setString(3, id);
            
            int affectedRows = stmt.executeUpdate();
            if (affectedRows > 0) {
                request.getRequestDispatcher("/department/list").forward(request, response);
            }
        } catch (SQLException e) {
            throw new ServletException("Update operation failed", e);
        } finally {
            DatabaseManager.releaseResources(conn, stmt, null);
        }
    }
}