Mybatis CRUD案例

任务

1. 技术栈:SpringBoot+ Spring + SpringMVC + MyBatis
2. 要求:

• 学生表 CRUD
• RESTfUL风格
• 数据校验
• 全局统一异常处理(业务异常、校验异常)
• 引入vo分层模型
• 引入knif4j文档
• 解诀跨越
• 在接口文档中测试

案例

整体实体关系概览 为了让你有一个全局视角，我们先明确一下这四个实体之间的关系：

• 班级 (Student) ↔ 学生 (Student)：一对多 (1:N)
• 学生 (Student) ↔ 学生卡 (StudentCard)：一对一 (1:1)
• 学生 (Student) ↔ 课程 (Course)：多对多 (N:M)

SQL文件

/*
 Navicat Premium Data Transfer

 Source Server         : local
 Source Server Type    : MySQL
 Source Server Version : 80017
 Source Host           : localhost:3306
 Source Schema         : mybatis_test

 Target Server Type    : MySQL
 Target Server Version : 80017
 File Encoding         : 65001

 Date: 03/12/2025 14:16:34
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for clazz
-- ----------------------------
DROP TABLE IF EXISTS `clazz`;
CREATE TABLE `clazz`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '班级id',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '班级名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of clazz
-- ----------------------------
INSERT INTO `clazz` VALUES (1, '23级移动通信1班');
INSERT INTO `clazz` VALUES (2, '23级移动通信2班');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '课程ID',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程名称',
  `credit` double(11, 0) NULL DEFAULT NULL COMMENT '学分',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, 'SSM框架开发与应用', 3);
INSERT INTO `course` VALUES (2, '虚拟化技术与应用', 3);
INSERT INTO `course` VALUES (3, 'Python程序设计', 1);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学生姓名',
  `no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学号',
  `tel` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '手机号',
  `clazz_id` bigint(20) NOT NULL COMMENT '关联的班级id',
  `create_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '注册时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', '202312135678', '13345065677', 1, '2025-12-01 15:48:43');
INSERT INTO `student` VALUES (2, '李四', '202312145678', '13345065678', 1, '2025-12-01 15:48:29');
INSERT INTO `student` VALUES (3, '王五', '202312155678', '13345065679', 2, '2025-12-01 15:48:45');

-- ----------------------------
-- Table structure for student_card
-- ----------------------------
DROP TABLE IF EXISTS `student_card`;
CREATE TABLE `student_card`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '学生卡ID',
  `no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '卡号',
  `student_id` bigint(20) NOT NULL COMMENT '学生编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学生卡表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student_card
-- ----------------------------
INSERT INTO `student_card` VALUES (1, '400701', 1);
INSERT INTO `student_card` VALUES (2, '400702', 2);
INSERT INTO `student_card` VALUES (3, '400703', 3);

-- ----------------------------
-- Table structure for student_course
-- ----------------------------
DROP TABLE IF EXISTS `student_course`;
CREATE TABLE `student_course`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `student_id` bigint(20) NOT NULL COMMENT '学生ID',
  `course_id` bigint(20) NOT NULL COMMENT '课程ID',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_student_course`(`student_id`, `course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学生课程关联表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student_course
-- ----------------------------
INSERT INTO `student_course` VALUES (1, 1, 1);
INSERT INTO `student_course` VALUES (2, 1, 2);
INSERT INTO `student_course` VALUES (3, 2, 1);
INSERT INTO `student_course` VALUES (6, 2, 2);
INSERT INTO `student_course` VALUES (7, 3, 3);
INSERT INTO `student_course` VALUES (8, 101, 101);

SET FOREIGN_KEY_CHECKS = 1;

Student类

@Data
public class Student {
    private Long id;
    private String name;
    private String no;
    private String tel;
    private Long clazzId;
    private LocalDateTime createTime;
}

Clazz

@Data
public class Clazz {
    private Long id;
    private String name;
}

Course

@Data
public class Course {
    private Long id;
    private String name;
    private Double credit;
}

StudentCard

@Data
public class StudentCard {
    private Long id;
    private String no;
    private Long studentId;
}

StudentCourse

@Data
public class StudentCourse {
    private Long id;
    private Long studentId;
    private Long courseId;
}

StudentMapper

@Mapper
public interface StudentMapper {

     List<Student> getAll();
     Student getById(Long id);
     void add(Student student);
     void update(Student student);
     void delete(Long id);
}

ClazzMapper

@Mapper
public interface ClazzMapper {

    List<Clazz> getAll();
    Clazz getById(Long id);
    void add(Clazz clazz);
    void update(Clazz clazz);
    void delete(Long id);
}

CourseMapper

@Mapper
public interface CourseMapper {

    List<Course> getAll();
    Course getById(Long id);
    void  add(Course course);
    void update(Course course);
    void delete(Long id);
}

StudentCardMapper

@Mapper
public interface StudentCardMapper {
    List<StudentCard> getAll();
    StudentCard getById(Long id);
    void  add(StudentCard card);
    void update(StudentCard card);
    void delete(Long id);
}

StudentCourseMapper

@Mapper
public interface StudentCourseMapper {
    List<StudentCourse> getAll();
    StudentCourse getById(Long id);
    void  add(StudentCourse card);
    void update(StudentCourse card);
    void delete(Long id);
}

StudentMapper.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.joe.mapper.StudentMapper">


    <select id="getAll" resultType="com.joe.model.Student">
        select * from student
    </select>
    <select id="getById" resultType="com.joe.model.Student">
        select * from student where id=#{id}
    </select>

    <insert id="add">
        insert into student(name,no,tel,clazz_id) values (#{name},#{no},,#{tel},#{clazzId})
    </insert>
    <update id="update">
        update student set name=#{name},no=#{no},tel=#{tel},clazz_id=#{clazzId} where id=#{id}
    </update>
    <delete id="delete">
        delete from student where id=#{id}
    </delete>

</mapper>

ClazzMapper.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.joe.mapper.ClazzMapper">
    
    <select id="getAll" resultType="com.joe.model.Clazz">
        select * from clazz
    </select>
    <select id="getById" resultType="com.joe.model.Clazz">
        select * from clazz where id=#{id}
    </select>

    <insert id="add">
        insert into clazz(name) value(#{name})
    </insert>

    <update id="update">
        update clazz set name=#{name} where id=#{id}
    </update>

    <delete id="delete">
        delete from clazz where id=#{id}
    </delete>
</mapper>

CourseMapper.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.joe.mapper.CourseMapper">


    <select id="getAll" resultType="com.joe.model.Course">
        select * from course
    </select>
    <select id="getById" resultType="com.joe.model.Course">
        select * from course where id=#{id}
    </select>
    <insert id="add">
        insert into course(name,credit) values (#{name},#{credit})
    </insert>
    <update id="update">
         update course set name=#{name},credit=#{credit} where id=#{id}
    </update>
    <delete id="delete">
        delete from course where id=#{id}
    </delete>
</mapper>

StudentCardMapper.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.joe.mapper.StudentCardMapper">

    <select id="getAll" resultType="com.joe.model.StudentCard">
        select * from  student_card
    </select>
    <select id="getById" resultType="com.joe.model.StudentCard">
        select * from  student_card where id=#{id}
    </select>
    <insert id="add">
        insert into student_card(no,student_id) values(#{no},#{studentId})
    </insert>
    <update id="update">
        update student_card set no=#{no},student_id=#{studentId} where id=#{id}
    </update>
    <delete id="delete">
        delete from student_card where id=#{id}
    </delete>
</mapper>

StudentCourseMapper.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.joe.mapper.StudentCourseMapper">
    <insert id="add">
        insert into student_course(student_id,course_id) values (#{studentId},#{courseId})
    </insert>
    <update id="update">
        update student_course set student_id=#{studentId},course_id=#{courseId} where id=#{id}
    </update>
    <delete id="delete">
        delete from student_course where id=#{id}
    </delete>
    <select id="getAll" resultType="com.joe.model.StudentCourse">
        select * from student_course
    </select>
    <select id="getById" resultType="com.joe.model.StudentCourse">
        select * from student_course where id=#{id}
    </select>
</mapper>

StudentMapperTest 其他类似

@SpringBootTest
public class StudentMapperTest {

    @Autowired
    StudentMapper studentMapper;

    @Test
    public void add() {
        Student student = new Student();
        student.setName("zhangsan1");
        student.setNo("123");
        student.setClazzId(1L);

        studentMapper.add(student);
    }

    @Test
    public void update() {
        Student student = new Student();
        student.setId(4L);
        student.setName("zhangsan11");
        student.setNo("123333");
        student.setClazzId(1L);

        studentMapper.update(student);
    }

    @Test
    public void delete(){

        studentMapper.delete(4L);
    }

    @Test
    public void getById() {
        Student student = studentMapper.getById(4L);
        System.out.println(student);
    }

    @Test
    public void getAll() {
        List<Student> allStudent = studentMapper.getAll();
        for (Student student : allStudent) {
            System.out.println(student);
        }
    }
}