学习进度条

巡检计划功能开发：Spring Boot与MyBatis实践总结

今日所花时间：一小时
今日代码量：100行
博客量：一篇
了解到的知识点：

项目背景

最近在团队项目中负责开发巡检计划管理功能模块，主要包括巡检计划的增删改查以及条件筛选等功能。通过这个功能的开发，我对Spring Boot和MyBatis的整合使用有了更深入的理解。下面我将详细介绍这个功能的实现过程和使用到的技术点。

完整代码实现

1. 实体类(InspectionPlan.java)

package com.example.littlebabydemo0425.pojo;

import jakarta.persistence.*;
import java.util.Date;

@Entity
@Table(name = "inspection_plan")
public class InspectionPlan {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "plan_code", length = 64, nullable = false)
    private String planCode;

    @Column(name = "plan_name", length = 128, nullable = false)
    private String planName;

    @Column(name = "device_type", length = 32, nullable = false)
    private String deviceType;

    @Column(name = "start_date", nullable = false)
    @Temporal(TemporalType.DATE)
    private Date startDate;

    @Column(name = "end_date")
    @Temporal(TemporalType.DATE)
    private Date endDate;

    @Column(name = "frequency", length = 16, nullable = false)
    private String frequency;

    @Column(name = "duration")
    private Integer duration;

    @Column(name = "status", columnDefinition = "tinyint default 1")
    private Integer status;

    @Column(name = "create_time", columnDefinition = "datetime default CURRENT_TIMESTAMP")
    private Date createTime;

    @Column(name = "update_time", columnDefinition = "datetime default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP")
    private Date updateTime;

    // 省略getter和setter方法...
}

2. Mapper接口(InspectionPlanMapper.java)

package com.example.littlebabydemo0425.mapper;

import com.example.littlebabydemo0425.pojo.InspectionPlan;
import com.example.littlebabydemo0425.pojo.PageBean;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

@Mapper
public interface InspectionPlanMapper {

    @Insert("INSERT INTO inspection_plan(plan_code, plan_name, device_type, start_date, " +
            "end_date, frequency, duration, status) " +
            "VALUES(#{planCode}, #{planName}, #{deviceType}, #{startDate}, " +
            "#{endDate}, #{frequency}, #{duration}, #{status})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(InspectionPlan inspectionPlan);

    @Delete("DELETE FROM inspection_plan WHERE plan_code = #{planCode}")
    int deleteByPlanCode(String planCode);

    @Update("UPDATE inspection_plan SET " +
            "plan_code = #{planCode}, " +
            "plan_name = #{planName}, " +
            "device_type = #{deviceType}, " +
            "start_date = #{startDate}, " +
            "end_date = #{endDate}, " +
            "frequency = #{frequency}, " +
            "duration = #{duration}, " +
            "status = #{status} " +
            "WHERE id = #{id}")
    int update(InspectionPlan inspectionPlan);

    @Select("SELECT * FROM inspection_plan WHERE id = #{id}")
    InspectionPlan selectById(Long id);

    @Select("SELECT * FROM inspection_plan")
    List<InspectionPlan> selectAll();

    @Select("SELECT * FROM inspection_plan WHERE device_type = #{deviceType}")
    List<InspectionPlan> selectByDeviceType(String deviceType);

    @Select("SELECT * FROM inspection_plan WHERE status = #{status}")
    List<InspectionPlan> selectByStatus(Integer status);

    @Select("SELECT * FROM inspection_plan WHERE plan_code = #{planCode}")
    InspectionPlan selectByPlanCode(String planCode);

    @SelectProvider(type = InspectionPlanSqlProvider.class, method = "selectByCondition")
    List<InspectionPlan> selectByCondition(
            @Param("deviceType") String deviceType,
            @Param("planName") String planName,
            @Param("status") Integer status,
            @Param("offset") int offset,
            @Param("pageSize") int pageSize);

    @SelectProvider(type = InspectionPlanSqlProvider.class, method = "countByCondition")
    long countByCondition(
            @Param("deviceType") String deviceType,
            @Param("planName") String planName,
            @Param("status") Integer status);

    default PageBean<InspectionPlan> getInspectionPlans(Integer pageNum, Integer pageSize, String deviceType, String planName, Integer status) {
        PageBean<InspectionPlan> pb = new PageBean<>();

        int offset = (pageNum - 1) * pageSize;
        List<InspectionPlan> inspectionPlans = selectByCondition(deviceType, planName, status, offset, pageSize);
        pb.setItems(inspectionPlans);

        long total = countByCondition(deviceType, planName, status);
        pb.setTotal(total);

        return pb;
    }

    class InspectionPlanSqlProvider {
        public String selectByCondition(
                @Param("deviceType") String deviceType,
                @Param("planName") String planName,
                @Param("status") Integer status,
                @Param("offset") int offset,
                @Param("pageSize") int pageSize) {
            return new SQL() {{
                SELECT("*");
                FROM("inspection_plan");
                if (deviceType != null && !deviceType.isEmpty()) {
                    WHERE("device_type LIKE CONCAT('%', #{deviceType}, '%')");
                }
                if (planName != null && !planName.isEmpty()) {
                    WHERE("plan_name LIKE CONCAT('%', #{planName}, '%')");
                }
                if (status != null) {
                    WHERE("status = #{status}");
                }
                ORDER_BY("id DESC");
            }}.toString() + " LIMIT #{offset}, #{pageSize}";
        }

        public String countByCondition(
                @Param("deviceType") String deviceType,
                @Param("planName") String planName,
                @Param("status") Integer status) {
            return new SQL() {{
                SELECT("COUNT(*)");
                FROM("inspection_plan");
                if (deviceType != null && !deviceType.isEmpty()) {
                    WHERE("device_type LIKE CONCAT('%', #{deviceType}, '%')");
                }
                if (planName != null && !planName.isEmpty()) {
                    WHERE("plan_name LIKE CONCAT('%', #{planName}, '%')");
                }
                if (status != null) {
                    WHERE("status = #{status}");
                }
            }}.toString();
        }
    }
}

3. 控制器(InspectionPlanController.java)

package com.example.littlebabydemo0425.controller;

import com.example.littlebabydemo0425.mapper.InspectionPlanMapper;
import com.example.littlebabydemo0425.pojo.InspectionPlan;
import com.example.littlebabydemo0425.pojo.PageBean;
import com.example.littlebabydemo0425.pojo.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/inspection-plans/")
@CrossOrigin(origins = "http://localhost:5173")
public class InspectionPlanController {

    @Autowired
    private InspectionPlanMapper inspectionPlanMapper;

    @GetMapping
    public Result<PageBean<InspectionPlan>> getInspectionPlans(Integer pageNum,
                                                               Integer pageSize,
                                                               @RequestParam(required = false) String deviceType,
                                                               @RequestParam(required = false) String planName,
                                                               @RequestParam(required = false) Integer status) {
        PageBean<InspectionPlan> pageBean = inspectionPlanMapper.getInspectionPlans(pageNum, pageSize, deviceType, planName, status);
        return Result.success(pageBean);
    }

    @PostMapping
    @Transactional
    public Result<InspectionPlan> addInspectionPlan(@RequestBody InspectionPlan inspectionPlan) {
        try {
            if (inspectionPlan.getPlanName() == null || inspectionPlan.getDeviceType() == null) {
                return Result.error("计划名称和设备类型不能为空");
            }

            int rowsInserted = inspectionPlanMapper.insert(inspectionPlan);
            if (rowsInserted > 0) {
                return Result.success(inspectionPlan);
            } else {
                return Result.error("添加巡检计划失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return Result.error("添加巡检计划时发生异常: " + e.getMessage());
        }
    }

    @DeleteMapping("/{planCode}")
    public Result<String> deletePlan(@PathVariable String planCode) {
        InspectionPlan existingPlan = inspectionPlanMapper.selectByPlanCode(planCode);
        if (existingPlan == null) {
            return Result.error("未找到编号为" + planCode + "的巡检计划");
        }

        int result = inspectionPlanMapper.deleteByPlanCode(planCode);
        if (result > 0) {
            return Result.success("巡检计划删除成功");
        } else {
            return Result.error("删除巡检计划失败");
        }
    }

    @PutMapping("/{id}")
    public Result<String> updateInspectionPlan(@PathVariable Long id, @RequestBody InspectionPlan inspectionPlan) {
        try {
            inspectionPlan.setId(id);
            int result = inspectionPlanMapper.update(inspectionPlan);
            if (result > 0) {
                return Result.success("巡检计划更新成功");
            } else {
                return Result.error("未找到对应巡检计划");
            }
        } catch (Exception e) {
            return Result.error("服务器错误: " + e.getMessage());
        }
    }

    @GetMapping("/{id}")
    public Result<InspectionPlan> getInspectionPlanById(@PathVariable Long id) {
        try {
            InspectionPlan inspectionPlan = inspectionPlanMapper.selectById(id);
            if (inspectionPlan != null) {
                return Result.success(inspectionPlan);
            } else {
                return Result.error("未找到对应巡检计划");
            }
        } catch (Exception e) {
            return Result.error("服务器错误: " + e.getMessage());
        }
    }

    @GetMapping("/all")
    public Result<List<InspectionPlan>> getAllInspectionPlans() {
        try {
            List<InspectionPlan> inspectionPlans = inspectionPlanMapper.selectAll();
            return Result.success(inspectionPlans);
        } catch (Exception e) {
            return Result.error("服务器错误: " + e.getMessage());
        }
    }

    @GetMapping("/status/{status}")
    public Result<List<InspectionPlan>> getInspectionPlansByStatus(@PathVariable Integer status) {
        try {
            List<InspectionPlan> inspectionPlans = inspectionPlanMapper.selectByStatus(status);
            return Result.success(inspectionPlans);
        } catch (Exception e) {
            return Result.error("服务器错误: " + e.getMessage());
        }
    }

    @GetMapping("/device-type/{deviceType}")
    public Result<List<InspectionPlan>> getInspectionPlansByDeviceType(@PathVariable String deviceType) {
        try {
            List<InspectionPlan> inspectionPlans = inspectionPlanMapper.selectByDeviceType(deviceType);
            return Result.success(inspectionPlans);
        } catch (Exception e) {
            return Result.error("服务器错误: " + e.getMessage());
        }
    }
}

使用到的技术和知识点

1. Spring Boot框架

Spring Boot是开发这个功能的基础框架，它简化了Spring应用的初始搭建和开发过程。通过自动配置和起步依赖，我能够快速构建RESTful API服务。

• @RestController：组合了@Controller和@ResponseBody，用于创建RESTful控制器
• @RequestMapping：映射HTTP请求路径
• @Autowired：自动注入依赖
• @Transactional：声明式事务管理

2. MyBatis持久层框架

MyBatis是一个优秀的持久层框架，它支持定制化SQL、存储过程以及高级映射。

• @Mapper：标识接口为MyBatis的Mapper接口
• @Insert/@Delete/@Update/@Select：基本的CRUD注解
• @SelectProvider：动态SQL生成，用于复杂查询条件
• @Options：配置主键回填等选项
• @Param：指定参数名称

3. JPA注解

虽然主要使用MyBatis，但在实体类中使用了JPA注解来定义表结构：

• @Entity：标识这是一个JPA实体
• @Table：指定对应的数据库表名
• @Id：标识主键
• @GeneratedValue：主键生成策略
• @Column：定义列属性
• @Temporal：处理日期类型

4. 动态SQL构建

使用MyBatis的SQL类构建动态SQL，实现了条件查询和分页功能：

new SQL() {{
    SELECT("*");
    FROM("inspection_plan");
    if (deviceType != null && !deviceType.isEmpty()) {
        WHERE("device_type LIKE CONCAT('%', #{deviceType}, '%')");
    }
    // 其他条件...
    ORDER_BY("id DESC");
}}.toString() + " LIMIT #{offset}, #{pageSize}";

5. 分页实现

实现了基于MyBatis的分页查询功能：

1. 计算偏移量：offset = (pageNum - 1) * pageSize
2. 使用LIMIT进行分页
3. 同时查询总数用于前端分页显示

6. RESTful API设计

遵循RESTful风格设计API：

• GET /api/inspection-plans/ - 分页查询巡检计划
• POST /api/inspection-plans/ - 新增巡检计划
• PUT /api/inspection-plans/{id} - 更新巡检计划
• DELETE /api/inspection-plans/{planCode} - 删除巡检计划
• GET /api/inspection-plans/{id} - 获取单个巡检计划详情

7. 跨域处理

使用@CrossOrigin注解处理前端跨域请求：

@CrossOrigin(origins = "http://localhost:5173")

8. 异常处理

在Controller层对异常进行捕获，返回统一的错误信息格式：

try {
    // 业务逻辑
} catch (Exception e) {
    return Result.error("服务器错误: " + e.getMessage());
}

开发心得

通过这个功能的开发，我深入理解了以下内容：

1. MyBatis注解和XML配置的两种使用方式及其适用场景
2. 动态SQL的构建方法和优势
3. 分页查询的实现原理
4. RESTful API的设计规范
5. Spring Boot与MyBatis的整合方式

特别是在处理复杂条件查询时，MyBatis的@SelectProvider提供了很大的灵活性，能够根据不同的参数动态生成SQL语句，这比编写多个固定查询方法要优雅得多。

总结

本次开发耗时约1小时，代码量约100行。通过实践，我巩固了Spring Boot和MyBatis的使用技巧，掌握了动态SQL构建和分页查询的实现方法。这些经验对今后开发更复杂的功能模块打下了良好基础。

在未来的开发中，我计划进一步学习MyBatis的缓存机制、批量操作等高级特性，以提升系统性能和开发效率。