在Java Web（JSP、Servlet）MVC架构中如何连接不同数据库表？

Hey there! Let's walk through how to wire up your HRMS project's MVC structure to interact with different database tables, aligning perfectly with your existing package setup: com.hrms.model, com.hrms.service, com.hrms.servlet, and com.hrms.utility.

1. Start with the Utility Package: Database Connection Foundation #

Your utility package is the right home for centralized, reusable database connection logic. Create a DBConnection utility class to handle getting and closing connections—you can use raw JDBC for simplicity, or a connection pool like HikariCP/Druid for better production performance.

Example DBConnection snippet:

package com.hrms.utility;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/hrms_db";
    private static final String USER = "db_user";
    private static final String PASSWORD = "db_password";

    static {
        // Load JDBC driver
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("Failed to load JDBC driver");
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void closeResources(Connection conn, java.sql.Statement stmt, java.sql.ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Pro tip: Swap DriverManager with a connection pool in production—it reuses connections and avoids resource leaks.

2. Model Package: Map Tables to Java Classes #

Each class in your model package should directly mirror a single database table. Match class attributes to table column names (or follow consistent conventions like camelCase for attributes and snake_case for columns, which you can account for in SQL queries).

For example:

• Employee class → employee table (columns: employee_id, name, email, department)
• Project class → project table (columns: project_id, project_name, start_date, end_date)
• Training class → training table (columns: training_id, training_name, employee_id, conducted_date)

Make sure each model has proper getters/setters—this lets you easily populate objects from database result sets, and vice versa.

3. Service Package: Implement Table Operations with Business Logic #

Your service layer acts as the middleman between models and servlets, handling all database interactions (via DBConnection) and business rules. For each table, write service methods that target the corresponding table.

Example: Employee CRUD (targeting employee table) #

In EmployeeServiceImpl:

package com.hrms.service;

import com.hrms.model.Employee;
import com.hrms.utility.DBConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployeeServiceImpl implements EmployeeService {

    @Override
    public Employee getEmployeeById(int empId) {
        Employee emp = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBConnection.getConnection();
            String sql = "SELECT employee_id, name, email, department FROM employee WHERE employee_id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, empId);
            rs = stmt.executeQuery();

            if (rs.next()) {
                emp = new Employee();
                emp.setEmployeeId(rs.getInt("employee_id"));
                emp.setName(rs.getString("name"));
                emp.setEmail(rs.getString("email"));
                emp.setDepartment(rs.getString("department"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // Add proper error handling (logging, custom exceptions) here
        } finally {
            DBConnection.closeResources(conn, stmt, rs);
        }
        return emp;
    }

    // Repeat pattern for addEmployee(), updateEmployee(), deleteEmployee()
}

Example: Project Query (targeting project table) #

In ProjectServiceImpl:

@Override
public List<Project> getAllProjects() {
    List<Project> projects = new ArrayList<>();
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = DBConnection.getConnection();
        String sql = "SELECT project_id, project_name, start_date, end_date FROM project";
        stmt = conn.prepareStatement(sql);
        rs = stmt.executeQuery();

        while (rs.next()) {
            Project project = new Project();
            project.setProjectId(rs.getInt("project_id"));
            project.setProjectName(rs.getString("project_name"));
            project.setStartDate(rs.getDate("start_date"));
            project.setEndDate(rs.getDate("end_date"));
            projects.add(project);
        }
    } catch (SQLException e) {
        e.printStackTrace();
        // Handle error (e.g., log and throw a custom service exception)
    } finally {
        DBConnection.closeResources(conn, stmt, rs);
    }
    return projects;
}

*Quick note on your project module error: If project operations are failing, check these common issues:

• Typos in SQL table/column names
• Unclosed database resources causing connection leaks
• Mismatched data types between model attributes and table columns
• Incorrect credentials in DBConnection*

4. Servlet Package: Delegate to the Service Layer #

Servlets don't interact directly with the database—they handle HTTP requests, pass data to the service layer, and send responses back to the client.

Example ViewEmployeeServlet:

package com.hrms.servlet;

import com.hrms.model.Employee;
import com.hrms.service.EmployeeService;
import com.hrms.service.EmployeeServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@WebServlet("/view-employee")
public class ViewEmployeeServlet extends HttpServlet {
    private EmployeeService employeeService = new EmployeeServiceImpl();

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        int empId = Integer.parseInt(request.getParameter("empId"));
        Employee employee = employeeService.getEmployeeById(empId);
        
        request.setAttribute("employee", employee);
        request.getRequestDispatcher("/employee-details.jsp").forward(request, response);
    }
}

Best Practices to Follow #

• Always use PreparedStatement instead of Statement to prevent SQL injection.
• Close all database resources (ResultSet, Statement, Connection) in a finally block to avoid leaks.
• For cross-table operations (e.g., assigning an employee to a project), handle transactions in the service layer using conn.setAutoCommit(false), conn.commit(), and conn.rollback().
• Later, you can simplify boilerplate code with an ORM like Hibernate, but this raw MVC approach works great for your current setup.

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