基于Servlet&JDBC的JSP页面员工表关联位置表超链接问题

Alright, let's get this sorted out—you're close! The issue is that your Department # hyperlink isn't sending over the specific employee's unique identifier (or relevant foreign key) to your LocationServlet, so it's pulling every location record instead of just the ones tied to that employee. Here's how to fix it step by step:

First, modify the Department # link in your employee listing JSP to include the current employee's ID as a query parameter. This tells your servlet exactly which employee's locations to fetch.

For example, if you're using JSTL to loop through employees:

<c:forEach var="employee" items="${employeeList}">
  <tr>
    <!-- Other employee columns here -->
    <td>
      <a href="LocationServlet?employeeId=${employee.id}">${employee.departmentNumber}</a>
    </td>
  </tr>
</c:forEach>

Note: Replace ${employee.id} with the actual primary key field from your Employee POJO (e.g., empId if that's what you named it).

Next, update your LocationServlet to read the employeeId parameter from the request, then run a filtered JDBC query to only get locations linked to that employee.

Here's a simplified example of the doGet method:

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String employeeIdParam = request.getParameter("employeeId");
    List<Location> employeeLocations = new ArrayList<>();

    if (employeeIdParam != null && !employeeIdParam.isEmpty()) {
        try {
            int employeeId = Integer.parseInt(employeeIdParam);
            // Use a prepared statement to avoid SQL injection
            String sql = "SELECT * FROM location WHERE employee_id = ?";
            
            try (Connection conn = YourDBConnectionClass.getConnection();
                 PreparedStatement pstmt = conn.prepareStatement(sql)) {
                
                pstmt.setInt(1, employeeId);
                ResultSet rs = pstmt.executeQuery();

                // Map ResultSet to your Location POJO
                while (rs.next()) {
                    Location loc = new Location();
                    loc.setId(rs.getInt("id"));
                    loc.setAddress(rs.getString("address"));
                    loc.setCity(rs.getString("city"));
                    // Set other Location fields from the result set
                    employeeLocations.add(loc);
                }
            } catch (SQLException e) {
                // Handle exception (log it, show error message, etc.)
                e.printStackTrace();
            }
        } catch (NumberFormatException e) {
            // Handle invalid employee ID parameter
            e.printStackTrace();
        }
    }

    // Pass the filtered locations to the JSP
    request.setAttribute("locationList", employeeLocations);
    request.getRequestDispatcher("location.jsp").forward(request, response);
}

Key point: We're using PreparedStatement instead of a raw query to prevent SQL injection, and we're only selecting locations where the foreign key (employee_id) matches the passed employee ID.

Make sure your two POJO classes reflect the database relationship:

• Your Employee class should have a primary key field (e.g., id) that matches the foreign key in the Location class.
• Your Location class should have a foreign key field (e.g., employeeId) that links back to the employee's primary key.

Example POJO snippets:

// Employee.java
public class Employee {
    private int id;
    private String name;
    private String departmentNumber;
    // Getters and setters for all fields
}

// Location.java
public class Location {
    private int id;
    private String address;
    private String city;
    private int employeeId; // Foreign key linking to Employee.id
    // Getters and setters for all fields
}

Finally, your location.jsp can use JSTL to loop through the filtered locationList attribute, which now only contains records for the selected employee:

<table border="1">
    <tr>
        <th>Address</th>
        <th>City</th>
        <!-- Other location columns -->
    </tr>
    <c:forEach var="location" items="${locationList}">
        <tr>
            <td>${location.address}</td>
            <td>${location.city}</td>
            <!-- Render other location fields -->
        </tr>
    </c:forEach>
</table>

That's it! Now when you click the Department # link for an employee, the servlet will fetch only that employee's location records and display them in the JSP.

内容的提问来源于stack exchange，提问作者Hasan007