MyBatis 实战指南（九）—— MyBatis + JSP + MySQL 前端页面实现数据库的增加与删除显示数据

在本篇实战中，我们将使用 MyBatis + JSP + MySQL 来实现一个简单的 CRUD（增、删、改、查）功能，主要用于展示如何通过前端页面与数据库进行交互，实现数据增加、删除、显示。

目标

1. 使用 MyBatis 作为数据库访问层。
2. 使用 JSP 和 Servlet 实现前端页面交互。
3. 使用 MySQL 数据库来存储数据。

---

🛠️ 技术栈

• 前端：JSP、HTML、CSS
• 后端：Servlet、MyBatis
• 数据库：MySQL

---

项目结构

mybatis-jsp-mysql-crud/
├── src/
│   ├── main/
│   │   ├── java/com/example/servlet/
│   │   │   ├── AddEmployeeServlet.java
│   │   │   ├── DeleteEmployeeServlet.java
│   │   │   ├── EmployeeListServlet.java
│   │   └── resources/
│   │       ├── mybatis-config.xml
│   │       ├── mapper/
│   │       │   ├── EmployeeMapper.xml
│   │       └── dao/
│   │           └── EmployeeMapper.java
├── webapp/
│   ├── WEB-INF/
│   │   └── web.xml
│   ├── index.jsp
│   ├── addEmployee.jsp
│   └── deleteEmployee.jsp
└── pom.xml

---

1. MySQL 数据库设置

首先，你需要在 MySQL 中创建一个数据库和表：

创建数据库和表

CREATE DATABASE employee_db;

USE employee_db;

CREATE TABLE employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100)
);

---

2. 配置 MyBatis

mybatis-config.xml

MyBatis 的配置文件，我们需要在其中配置数据源、全局配置以及 Mapper 文件的位置。

<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
    <!-- 配置全局的映射器 -->
    <mappers>
        <mapper resource="mapper/EmployeeMapper.xml"/>
    </mappers>
</configuration>

EmployeeMapper.xml

在 MyBatis 中，Mapper 文件定义了 SQL 查询、更新操作。

<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="com.example.dao.EmployeeMapper">

    <!-- 查询所有员工 -->
    <select id="getAllEmployees" resultType="com.example.model.Employee">
        SELECT * FROM employee
    </select>

    <!-- 添加新员工 -->
    <insert id="addEmployee" parameterType="com.example.model.Employee">
        INSERT INTO employee (name, position) 
        VALUES (#{name}, #{position})
    </insert>

    <!-- 删除员工 -->
    <delete id="deleteEmployee" parameterType="int">
        DELETE FROM employee WHERE id = #{id}
    </delete>

</mapper>

EmployeeMapper.java

Mapper 接口定义了与数据库交互的方法。

package com.example.dao;

import com.example.model.Employee;
import java.util.List;

public interface EmployeeMapper {
    List<Employee> getAllEmployees();
    void addEmployee(Employee employee);
    void deleteEmployee(int id);
}

---

3. 配置 Servlet 和 JSP

web.xml

配置 Servlet，并映射到相应的 JSP 页面。

<web-app xmlns="http://java.sun.com/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
             http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
         version="3.0">
    <servlet>
        <servlet-name>EmployeeListServlet</servlet-name>
        <servlet-class>com.example.servlet.EmployeeListServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>EmployeeListServlet</servlet-name>
        <url-pattern>/employeeList</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>AddEmployeeServlet</servlet-name>
        <servlet-class>com.example.servlet.AddEmployeeServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>AddEmployeeServlet</servlet-name>
        <url-pattern>/addEmployee</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>DeleteEmployeeServlet</servlet-name>
        <servlet-class>com.example.servlet.DeleteEmployeeServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>DeleteEmployeeServlet</servlet-name>
        <url-pattern>/deleteEmployee</url-pattern>
    </servlet-mapping>
</web-app>

---

AddEmployeeServlet.java

添加员工的 Servlet。

package com.example.servlet;

import com.example.dao.EmployeeMapper;
import com.example.model.Employee;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.io.IOException;

public class AddEmployeeServlet extends HttpServlet {
    private SqlSessionFactory sqlSessionFactory;

    public void init() {
        sqlSessionFactory = (SqlSessionFactory) getServletContext().getAttribute("SqlSessionFactory");
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String name = request.getParameter("name");
        String position = request.getParameter("position");

        SqlSession session = sqlSessionFactory.openSession();
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        Employee employee = new Employee(name, position);
        mapper.addEmployee(employee);
        session.commit();
        session.close();

        response.sendRedirect("employeeList");
    }
}

DeleteEmployeeServlet.java

删除员工的 Servlet。

package com.example.servlet;

import com.example.dao.EmployeeMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.io.IOException;

public class DeleteEmployeeServlet extends HttpServlet {
    private SqlSessionFactory sqlSessionFactory;

    public void init() {
        sqlSessionFactory = (SqlSessionFactory) getServletContext().getAttribute("SqlSessionFactory");
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        int id = Integer.parseInt(request.getParameter("id"));

        SqlSession session = sqlSessionFactory.openSession();
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        mapper.deleteEmployee(id);
        session.commit();
        session.close();

        response.sendRedirect("employeeList");
    }
}

---

EmployeeListServlet.java

展示所有员工的 Servlet。

package com.example.servlet;

import com.example.dao.EmployeeMapper;
import com.example.model.Employee;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.io.IOException;
import java.util.List;

public class EmployeeListServlet extends HttpServlet {
    private SqlSessionFactory sqlSessionFactory;

    public void init() {
        sqlSessionFactory = (SqlSessionFactory) getServletContext().getAttribute("SqlSessionFactory");
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        SqlSession session = sqlSessionFactory.openSession();
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        List<Employee> employees = mapper.getAllEmployees();
        session.close();

        request.setAttribute("employees", employees);
        request.getRequestDispatcher("/index.jsp").forward(request, response);
    }
}

---

4. JSP 页面实现

index.jsp

用于显示员工列表，并提供添加、删除功能。

<!DOCTYPE html>
<html>
<head>
    <title>Employee List</title>
</head>
<body>
    <h2>Employee List</h2>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Position</th>
            <th>Actions</th>
        </tr>
        <c:forEach var="employee" items="${employees}">
            <tr>
                <td>${employee.id}</td>
                <td>${employee.name}</td>
                <td>${employee.position}</td>
                <td>
                    <a href="deleteEmployee?id=${employee.id}">Delete</a>
                </td>
            </tr>
        </c:forEach>
    </table>
    <br>
    <a href="addEmployee.jsp">Add New Employee</a>
</body>
</html>

addEmployee.jsp

用于添加新员工。

<!DOCTYPE html>
<html>
<head>
    <title>Add Employee</title>
</head>
<body>
    <h2>Add New Employee</h2>
    <form action="addEmployee" method="POST">
        Name: <input type="text" name="name" required><br>
        Position: <

input type=”text” name=”position” required>



Back to Employee List

---

5. 总结

通过 MyBatis 与 JSP 技术的结合，我们实现了一个简单的员工管理系统，支持以下功能：

• 显示：展示员工列表。
• 增加：通过表单增加新员工。
• 删除：通过点击链接删除员工。

以上便是基于 MyBatis、JSP 和 MySQL 实现前端页面增加、删除、显示数据的全过程。如果你需要进一步了解其他功能或扩展该项目，请告诉我！