MyBatis、JSP与MySQL协作：前端数据增删展示

2个月前发布 gsjqwyl

MyBatis实战指南（九）：借助MyBatis、JSP与MySQL实现前端数据的增删与展示

前言

在上一篇的博客里，我们深入钻研了MyBatis的核心应用要点，像日志配置、缓存优化以及映射机制等关键内容都有涉及。而本篇将会进一步把JSP前端技术和MySQL数据库结合起来，通过完整的示例代码来演示怎样实现数据的增删查操作，并且把结果实时呈现在Web页面上。

一、搭建项目框架与配置Maven

1. 配置Maven环境

首先得进行Maven的相关配置，具体的Maven导入方式可以参考对应的博客内容。接着创建相应的JSP项目框架，其目录结构大致如下：

Servlet-MyBatis-001/
├── src/
│   ├── main/
│   │   ├── java/
│   │   │   └── com/
│   │   │       └── niit/
│   │   │           ├── mapper/        # MyBatis映射接口存放处
│   │   │           ├── pojo/          # 实体类所在包
│   │   │           ├── service/       # 业务逻辑层目录
│   │   │           ├── servlet/       # Web Servlet相关代码
│   │   │           └── util/          # 工具类包
│   │   └── resources/
│   │       ├── com/
│   │       │   └── niit/
│   │       │       └── mapper/        # SQL映射XML文件位置
│   │       └── mybatis-config.xml     # MyBatis核心配置文件
│   └── test/
│       └── java/
├── pom.xml                            # Maven依赖配置文件
└── target/                           # 编译输出目录

2. 配置Maven依赖包

在pom.xml中添加如下依赖，这些依赖分别用于支持JSP、Servlet、数据库连接、MyBatis、日志、JSON处理等功能：

<!-- JSP API依赖 - 为JSP页面开发提供支持 -->
<dependency>
    <groupId>javax.servlet.jsp</groupId>
    <artifactId>javax.servlet.jsp-api</artifactId>
    <version>2.3.3</version>
    <scope>provided</scope> <!-- 由Servlet容器提供，无需打包进应用 -->
</dependency>

<!-- Servlet API依赖 - 用于Servlet开发 -->
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>javax.servlet-api</artifactId>
    <version>3.1.0</version>
    <scope>provided</scope> <!-- 由Servlet容器提供，无需打包进应用 -->
</dependency>

<!-- JSTL表达式语言依赖 - 简化JSP页面开发 -->
<dependency>
    <groupId>javax.servlet.jsp.jstl</groupId>
    <artifactId>jstl-api</artifactId>
    <version>1.2</version>
</dependency>

<!-- JSTL标准标签库实现 - 提供核心标签和格式化标签 -->
<dependency>
    <groupId>taglibs</groupId>
    <artifactId>standard</artifactId>
    <version>1.1.2</version>
</dependency>

<!-- JSTL 1.2实现 - 另一种JSTL选择 -->
<dependency>
    <groupId>jstl</groupId>
    <artifactId>jstl</artifactId>
    <version>1.2</version>
</dependency>

<!-- MySQL数据库驱动 - 连接MySQL 8.x数据库 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

<!-- MyBatis持久层框架 - 简化数据库操作 -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.19</version>
</dependency>

<!-- SLF4J日志API - 统一日志接口 -->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>2.0.7</version>
</dependency>

<!-- Jackson JSON处理库 - Java对象与JSON互相转换 -->
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.15.2</version>
</dependency>

<!-- Lombok代码简化工具 - 减少样板代码 -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.38</version>
    <scope>provided</scope> <!-- 编译时使用，无需打包进应用 -->
</dependency>

<!-- JUnit测试框架 - 编写单元测试 -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope> <!-- 仅测试时使用 -->
</dependency>

3. 创建web文件

按照步骤创建相关的web文件，包括创建工件和应用，同时创建与class同级的lib目录并导入相关库文件。

二、连接数据库

在resources目录下的mybatis-config.xml文件中配置数据库连接信息，示例代码如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="autoMappingBehavior" value="FULL"/>
        <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
    </settings>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/2023se3"/>
                <property name="username" value="root"/>
                <property name="password" value="    "/>
            </dataSource>

        </environment>
    </environments>
    <mappers>
        <mapper resource="com.niit/mapper/UserMapper.xml"/>
        <mapper resource="com.niit/mapper/MachineMapper.xml"/>

    </mappers>
</configuration>

其中数据库连接部分需要根据实际的数据库情况进行调整。

三、pojo包中的代码

在pojo包下创建Machine类，定义机器实体的结构，作为数据交互的基础：

package com.niit.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@AllArgsConstructor
@NoArgsConstructor
@Data

public class Machine {
    private int id;
    private String domain;
    private String ip;
    private String sphinxPath;
    private String remark;
    private String createTime;
}

四、mapper包中的代码

接口类MachineMapper

package com.niit.mapper;

import com.niit.pojo.Machine;

import java.util.List;
import java.util.Map;

public interface MachineMapper {
    List<Machine> findMachines();
    int addMachine(Machine machine);
    Machine findMachineById(int id);
    int updateMachine(Machine machine);
    int deleteMachine(int id);

    //搜索方法。使用Map接收参数
    List<Machine> searchMachines(Map<String, Object> params);
}

映射文件MachineMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.niit.mapper.MachineMapper">
    <select id="findMachines" resultType="com.niit.pojo.Machine">
        SELECT
            id,
            domain,
            ip,
            sphinx_path as sphinxPath,
            remark,
            create_time as createTime
        FROM machine_info;
    </select>

    <select id="findMachineById" resultType="com.niit.pojo.Machine" parameterType="int">
        SELECT
            id,
            domain,
            ip,
            sphinx_path as sphinxPath,
            remark,
            create_time as createTime
        FROM machine_info WHERE id = #{id};
    </select>

    <insert id="addMachine" parameterType="com.niit.pojo.Machine">
        INSERT INTO machine_info (domain, ip, sphinx_path, remark, create_time)
        VALUES (#{domain}, #{ip}, #{sphinxPath}, #{remark}, #{createTime})
    </insert>

    <update id="updateMachine" parameterType="com.niit.pojo.Machine">
        UPDATE machine_info
        SET domain = #{domain},
            ip = #{ip},
            sphinx_path = #{sphinxPath},
            remark = #{remark}
        WHERE id = #{id}
    </update>

    <delete id="deleteMachine" parameterType="int">
        DELETE FROM machine_info WHERE id = #{id}
    </delete>

    <!-- 搜索机器的SQL，支持按域名、IP搜索 -->
    <select id="searchMachines" resultType="com.niit.pojo.Machine" parameterType="map">
        SELECT
        id,
        domain,
        ip,
        sphinx_path as sphinxPath,
        remark,
        create_time as createTime
        FROM machine_info
        <where>
            <if test="keyword != null and keyword != ''">
                (domain LIKE CONCAT('%', #{keyword}, '%')
                OR ip LIKE CONCAT('%', #{keyword}, '%'))
            </if>
        </where>
        ORDER BY id DESC
    </select>
</mapper>

五、service包中的代码

创建MachineService类，封装机器管理的业务逻辑，处理数据库事务和异常：

package com.niit.service;

import com.niit.mapper.MachineMapper;
import com.niit.pojo.Machine;
import com.niit.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MachineService {

    public List<Machine> findMachines() {
        try (SqlSession sqlSession = MybatisUtil.getSqlSession()) {
            MachineMapper mapper = sqlSession.getMapper(MachineMapper.class);
            return mapper.findMachines();
        }
    }

    public Machine findMachineById(int id) {
        try (SqlSession sqlSession = MybatisUtil.getSqlSession()) {
            MachineMapper mapper = sqlSession.getMapper(MachineMapper.class);
            return mapper.findMachineById(id);
        }
    }

    public int addMachine(Machine machine) {
        try (SqlSession sqlSession = MybatisUtil.getSqlSession()) {
            MachineMapper mapper = sqlSession.getMapper(MachineMapper.class);
            int result = mapper.addMachine(machine);
            sqlSession.commit();
            return result;
        } catch (Exception e) {
            System.err.println("添加机器失败: " + e.getMessage());
            throw e; // 抛出异常，让调用者处理
        }
    }

    public int updateMachine(Machine machine) {
        try (SqlSession sqlSession = MybatisUtil.getSqlSession()) {
            MachineMapper mapper = sqlSession.getMapper(MachineMapper.class);
            int result = mapper.updateMachine(machine);
            sqlSession.commit();
            return result;
        } catch (Exception e) {
            System.err.println("更新机器失败: " + e.getMessage());
            throw e; // 抛出异常，让调用者处理
        }
    }

    public int deleteMachine(int id) {
        System.out.println("开始删除机器，ID: " + id);
        try (SqlSession sqlSession = MybatisUtil.getSqlSession()) {
            MachineMapper mapper = sqlSession.getMapper(MachineMapper.class);
            int result = mapper.deleteMachine(id);
            System.out.println("删除结果: " + result);
            sqlSession.commit();
            return result;
        } catch (Exception e) {
            System.err.println("删除机器失败: " + e.getMessage());
            e.printStackTrace(); // 打印完整堆栈信息
            throw e;
        }
    }

    // 添加搜索方法
    public List<Machine> searchMachines(String keyword) {
        try (SqlSession sqlSession = MybatisUtil.getSqlSession()) {
            MachineMapper mapper = sqlSession.getMapper(MachineMapper.class);
            Map<String, Object> params = new HashMap<>();
            params.put("keyword", "%" + keyword + "%");
            return mapper.searchMachines(params);
        }
    }
}

六、servlet包中的代码

创建MachineServlet，处理HTTP请求，协调服务层与视图层的数据交互：

“`java
package com.niit.servlet;

import com.niit.pojo.Machine;
import com.niit.service.MachineService;

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;
import java.util.List;

@WebServlet(“/machine”)
public class MachineServlet extends HttpServlet {
private MachineService machineService = new MachineService();

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    String action = req.getParameter("action");
    String keyword = req.getParameter("keyword");

    if ("add".equals(action)) {
        // 跳转到添加页面
        req.getRequestDispatcher("machine.jsp").forward(req, resp);
    } else if ("edit".equals(action)) {
        String idStr = req.getParameter("id");
        if (idStr != null) {
            int id = Integer.parseInt(idStr);
            Machine machine = machineService.findMachineById(id);
            if (machine != null) {
                req.setAttribute("machine", machine);
                System.out.println("已设置编辑机器: " + machine.getDomain()); // 添加调试输出
            } else {
                req.setAttribute("error", "未找到该机器信息");
            }
        } else {
            req.setAttribute("error", "缺少机器ID参数");
        }
        req.getRequestDispatcher("machine.jsp").forward(req, resp);

    } else if ("delete".equals(action)) {
        String idStr = req.getParameter("id");
        if (idStr != null) {
            int id = Integer.parseInt(idStr);
            try {
                int result = machineService.deleteMachine(id);
                if (result > 0) {
                    // 成功时直接返回JSON格式的成功标识（而非重定向）
                    resp.setContentType("application/json;charset=UTF-8");
                    resp.getWriter().write("{\"success\":true,\"message\":\"删除成功\"}");
                    return;
                } else {
                    resp.setContentType("application/json;charset=UTF-8");
                    resp.getWriter().write("{\"success\":false,\"message\":\"删除失败：记录不存在\"}");
                    return;
                }
            } catch (Exception e) {
                System.err.println("删除异常：" + e.getMessage());
                resp.setContentType("application/json;charset=UTF-8");
                resp.getWriter().write("{\"success\":false,\"message\":\"删除失败：" + e.getMessage() + "\"}");
                return;
            }
        }
        // ID为空的情况
        resp.setContentType("application/json;charset=UTF-8");
        resp.getWriter().write("{\"success\":false,\"message\":\"删除失败：缺少ID参数\"}");
    }else {
        List<Machine> machines;
        if (keyword != null && !keyword.trim().isEmpty()) {
            // 执行搜索
            machines = machineService.searchMachines(keyword.trim());
            req.setAttribute("searchKeyword", keyword.trim());
        } else {
            // 查询所有机器
            machines = machineService.findMachines();
        }
        req.setAttribute("machines", machines);
        req.getRequestDispatcher("machine.jsp").forward(req, resp);
    }
}

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    req.setCharacterEncoding("utf-8");
    String action = req.getParameter("action");

    if ("add".equals(action)) {
        // 处理添加机器请求
        String domain = req.getParameter("domain");
        String ip = req.getParameter("ip");
        String sphinxPath = req.getParameter("sphinxPath");
        String remark = req.getParameter("remark");

        // 数据验证
        if (domain == null || domain.trim().isEmpty() ||
                ip == null || ip.trim().isEmpty()) {
            req.setAttribute("error", "域名和IP为必填项");
            // 添加失败，返回添加页面
            req.getRequestDispatcher("machine.jsp").forward(req, resp);
            return;
        }

        // 设置创建时间
        java.text.SimpleDateFormat dateFormat =

文章版权归作者所有，未经允许请勿转载。

没有相关内容!

暂无评论

暂无评论...