一张表的增删改查 mybatis+springBoot(前后端分离后端，从项目创建到功能接口测试)

/*
 Navicat Premium Data Transfer

 Source Server         : javaEE
 Source Server Type    : MySQL
 Source Server Version : 80032 (8.0.32)
 Source Host           : localhost:3306
 Source Schema         : zwy

 Target Server Type    : MySQL
 Target Server Version : 80032 (8.0.32)
 File Encoding         : 65001

 Date: 05/04/2025 11:05:52
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `gender` enum('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '男',
  `title` char(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `birthday` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1005 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1001, '张三', '男', '高级工程师', '1985-01-01');
INSERT INTO `employee` VALUES (1002, '李四', '女', '助工', '1995-01-01');
INSERT INTO `employee` VALUES (1003, '王五', '男', '工程师', '1988-11-11');
INSERT INTO `employee` VALUES (1004, '赵六', '男', '工程师', '1988-12-12');

SET FOREIGN_KEY_CHECKS = 1;

1.2后端项目搭建

1.2.1使用idea内置的spring initializr创建项目

1.2.2项目的基本结构

1.2.2.1数据库连接测试

在application.properties中配置数据库（注意修改你的数据库名称和你mysql的用户和密码）

spring.application.name=zwy_manager_back_v1
# 数据库配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/zwy?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456

在ZwyManagerBackV1ApplicationTests.java 编写数据库连接测试方法

@SpringBootTest
class ZwyManagerBackV1ApplicationTests {
    @Resource
    private DataSource dataSource;

    @Test
    void contextLoads() {
    }
    @Test
    void testDataSource() throws Exception {
        System.out.println(dataSource.getClass().getName());
        System.out.println(dataSource.getConnection());
    }


}

正确测试结果（如果没有成功请仔细检查application.properties中配置数据库）

1.2.2.2创建实体类

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/entity

名称：Employee

@Data
public class Employee {
    private Integer id;
    private String name;
    private String gender;
    private String title;
    private Date birthday;
}

1.3使用mybatis对employee表进行增删改查

1.3.1配置端口号和mybatis实体和xml的映射

在application.properties中添加

# 端口号
server.port=9999
#配置mybatis实体和xml映射
mybatis.mapper-locations=classpath:mapper/*Mapper.xml

1.3.2编写mapper接口和它的xml映射文件（持久层）

1.3.2.1编写mapper接口

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/mapper

名称：EmployeeMapper

@Mapper
public interface EmployeeMapper {
    int insert(Employee employee);
    int deleteById(Integer id);
    int update(Employee employee);
    Employee selectById(Integer id);
    List<Employee> selectAll();
}

1.3.2.2编写xml映射文件

位置：src/main/resources/mapper

名称：EmployeeMapper.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.cdp.zwy.zwy_manager_back_v1.mapper.EmployeeMapper">
    <resultMap id="employeeResultMap" type="com.cdp.zwy.zwy_manager_back_v1.entity.Employee">
    </resultMap>
    <insert id="insert" parameterType="com.cdp.zwy.zwy_manager_back_v1.entity.Employee">
        insert into employee(name,gender,title,birthday) values(#{name},#{gender},#{title},#{birthday})
    </insert>
    <delete id="deleteById" parameterType="int">
        delete from employee where id=#{id}
    </delete>
    <update id="update" parameterType="com.cdp.zwy.zwy_manager_back_v1.entity.Employee">
        update employee set name=#{name},gender=#{gender},title=#{title},birthday=#{birthday} where id=#{id}
    </update>
    <select id="selectById" parameterType="int" resultMap="employeeResultMap">
        select * from employee where id=#{id}
    </select>
    <select id="selectAll" resultMap="employeeResultMap">
        select * from employee
    </select>

    
</mapper>

1.3.3编写sevice层调用mapper接口（业务层）

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/service

名称：EmployeeService

@Service
public class EmployeeService {
    @Resource
    private EmployeeMapper employeeMapper;
    public int add(Employee employee) {
        int row =  employeeMapper.insert(employee);
        if(row!=1){
            throw new RuntimeException("添加失败");
        }
        return row;
    }
    public int deleteById(Integer id) {
        int row =  employeeMapper.deleteById(id);
        if(row!=1){
            throw new RuntimeException("删除失败");
        }
        return row;
    }
    public int modify(Employee employee) {
        int row =  employeeMapper.update(employee);
        if(row!=1){
            throw new RuntimeException("更新失败");
        }
        return row;
    }
    public Employee findById(Integer id) {
        return employeeMapper.selectById(id);
    }
    public List<Employee> findAll() {
        return employeeMapper.selectAll();
    }
}

1.3.4编写controller层（控制层 api）

控制层调用业余层

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/controller

名称：EmployeeController

@RestController
@RequestMapping("/employee")
public class EmployeeController {
    @Resource
    private EmployeeService employeeService;
    @PostMapping("/add")
    public int add(@RequestBody Employee employee){
        return employeeService.add(employee);
    }
    @GetMapping("/findAll")
    public List<Employee> findAll(){
        return employeeService.findAll();
    }
    @GetMapping("/findById/{id}")
    public Employee findById(@PathVariable Integer id){
        return employeeService.findById(id);
    }
    @DeleteMapping("/deleteById/{id}")
    public int deleteById(@PathVariable Integer id){
        return employeeService.deleteById(id);
    }
    @PutMapping("/modify")
    public int modify(@RequestBody Employee employee){
        return employeeService.modify(employee);
    }

}

1.3.4测试接口层(控制层)

1.3.4.1浏览器测试Get请求方式的API

employee表数据

http://localhost:9999/employee/findAll

http://localhost:9999/employee/findById/1001

1.3.4.2postman测试delete,put,post请求方式的API(或者在API测试工具)

localhost:9999/employee/deleteById/1004

localhost:9999/employee/add

localhost:9999/employee/modify

到这里，你已经掌握了 MyBatis 对一张表的增删改查操作，但这还不够。虽然上面的代码实现了基本功能，但仍存在一些不足至少有以下两点：
第一点：接口返回值不够规范：
例如，find 方法返回实体对象或对象列表，增删改操作返回影响行数。这种返回值方式在前后端分离的项目中不够统一，我们需要定义一个通用的返回对象，便于前端处理。
第二点：异常处理不够完善：
当前代码缺乏自定义异常处理机制，这会导致问题定位困难。我们需要引入自定义异常，以便快速定位和处理问题。
通过优化这两点，我们可以让代码更加规范、健壮，并更好地支持前后端分离的开发模式。

拓展

1.4接口层的返回统一对象并且实施异常处理

1.4.1编写Result统一返回类

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/common

名称：Result

@Data
public class Result {
    private String code;
    private String msg;
    private Object data;

    public Result() {
    }

    public Result(String code, String msg, Object data) {
        this.code = code;
        this.msg = msg;
        this.data = data;
    }
    public static Result success() {
        return new Result("200", "success", null);
    }
    public static Result success(Object data) {
        return new Result("200", "success", data);
    }
    public static Result error() {
        return new Result("500", "请求失败", null);
    }
    public static Result error(String code,String msg) {
        return new Result(code, msg, null);
    }

}

1.4.2编写错误码枚举类

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/common

名称：ErrorCode

public enum ErrorCode {
        NOT_DATA("10000","没有数据"),
        EMPLOYEE_NOT_FOUND("10001", "该员工不存在"),
        EMPLOYEE_ADD_ERROR("10002", "添加员工失败"),
        EMPLOYEE_DELETE_ERROR("10003", "删除员工失败"),
        EMPLOYEE_MODIFY_ERROR("10004", "修改员工失败");


        private final String code;
        private final String msg;

        ErrorCode(String code, String msg) {
            this.code = code;
            this.msg = msg;
        }

        public String getCode() {
            return code;
        }

        public String getMsg() {
            return msg;
        }
    }

1.4.3编写业务层的异常处理类

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/common

名称：EmployeeServiceException

public class EmployeeServiceException extends RuntimeException {
    private final ErrorCode errorCode;
    public EmployeeServiceException(ErrorCode errorCode) {
        super(errorCode.getMsg());
        this.errorCode = errorCode;
    }
    public ErrorCode getErrorCode() {
        return errorCode;
    }
}

1.4.4修改业务层使用异常处理

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/service

名称：EmployeeService

@Service
public class EmployeeService {
    @Resource
    private EmployeeMapper employeeMapper;
    public int add(Employee employee) {
        int row =  employeeMapper.insert(employee);
        if(row!=1){
            throw new EmployeeServiceException(ErrorCode.EMPLOYEE_ADD_ERROR);
        }
        return row;
    }
    public int deleteById(Integer id) {
         Employee employee = employeeMapper.selectById(id);
         if (employee == null){
             throw new EmployeeServiceException(ErrorCode.EMPLOYEE_NOT_FOUND);
         }
        int row =  employeeMapper.deleteById(id);
        if(row!=1){
            throw new EmployeeServiceException(ErrorCode.EMPLOYEE_DELETE_ERROR);
        }
        return row;
    }
    public int modify(Employee employee) {
        Employee employee1 = employeeMapper.selectById(employee.getId());
        if (employee1 == null){
            throw new EmployeeServiceException(ErrorCode.EMPLOYEE_NOT_FOUND);
        }
        int row =  employeeMapper.update(employee);
        if(row!=1){
            throw new EmployeeServiceException(ErrorCode.EMPLOYEE_MODIFY_ERROR);
        }
        return row;
    }
    public Employee findById(Integer id) {
        Employee employee = employeeMapper.selectById(id);
        if(employee == null){
            throw new EmployeeServiceException(ErrorCode.EMPLOYEE_NOT_FOUND);
        }
        return employee;
    }
    public List<Employee> findAll() {
        List<Employee> employees = employeeMapper.selectAll();
        if(employees.size() == 0){
            throw new EmployeeServiceException(ErrorCode.NOT_DATA);
        }
        return employees;
    }
}

1.4.5修改控制层（接口层）的方法返回值

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/controller

名称：EmployeeController

@RestController
@RequestMapping("/employee")
public class EmployeeController {
    @Resource
    private EmployeeService employeeService;
    @PostMapping("/add")
    public Result add(@RequestBody Employee employee){
        return Result.success(employeeService.add(employee));
    }
    @GetMapping("/findAll")
    public Result findAll(){
        return Result.success(employeeService.findAll());
    }
    @GetMapping("/findById/{id}")
    public Result findById(@PathVariable Integer id){
        return Result.success(employeeService.findById(id)); 
    }
    @DeleteMapping("/deleteById/{id}")
    public Result deleteById(@PathVariable Integer id){
        return Result.success(employeeService.deleteById(id));
    }
    @PutMapping("/modify")
    public Result modify(@RequestBody Employee employee){
        return Result.success(employeeService.modify(employee));
    }

}

1.4.6编写全局异常处理类

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/exception

名称：GlobalExceptionHandler

@ControllerAdvice
public class GlobalExceptionHandler {
    private static final Logger log = LoggerFactory.getLogger(GlobalExceptionHandler.class);

    @ExceptionHandler(EmployeeServiceException.class)
    public ResponseEntity<Result> handleEmployeeServiceException(EmployeeServiceException ex) {
        // 记录日志
        log.error("业务异常: 错误码={}, 错误信息={}", ex.getErrorCode().getCode(), ex.getErrorCode().getMsg(), ex);

        // 返回统一错误响应
        Result response = new Result(
                ex.getErrorCode().getCode(),
                ex.getErrorCode().getMsg(),
                null
        );
        return new ResponseEntity<>(response, HttpStatus.BAD_REQUEST);
    }
}

还需要导入两个maven依赖坐标,在pom.xml<dependencies></dependencies>标签里加

    <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>2.1.0-alpha1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/ch.qos.logback/logback-classic -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.5.18</version>
            <scope>compile</scope>
        </dependency>

1.4.7测试修改后是够可以正常使用

许多bug是在更改中产生的,当然存在bug我肯定会改上面的代码，所以你是看不到bug的哈哈哈

依旧测试api，我这里测试和1.3.4是一样的操作我在重复一编保证逻辑的完整性，你如果很强可以不看这个自己尝试。

1.4.7.1浏览器测试Get请求方式的API

employee表数据

http://localhost:9999/employee/findAll

http://localhost:9999/employee/findById/1001

这里我只展示一个业务层的异常抛出哈，其他的异常触发你们自己去告一哈。

查询一个没有的员工

http://localhost:9999/employee/findById/100

1.4.7.2postman测试delete,put,post请求方式的API(或者在API测试工具)

localhost:9999/employee/deleteById/1005

localhost:9999/employee/add

localhost:9999/employee/modify

1.5加入分页查询

1.5.1maven引入pageHelper

位置：pom.xml <dependencies></dependencies>标签里

    <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>

1.5.2在业务层中添加分页查询方法

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/service/EmployeeService.java

   /**
     * 分页查询
     * @param pageNum
     * @param pageSize
     * @return
     */
    public PageInfo<Employee> selectPage(Integer pageNum, Integer pageSize) {
        PageHelper.startPage(pageNum,pageSize);
        List<Employee> list = employeeMapper.selectAll();

        return PageInfo.of(list);
    }

1.5.3在控制层调用该方法

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/controller/EmployeeController.java

    @GetMapping("/selectPage")
    public Result selectPage(@RequestParam(defaultValue = "1") Integer pageNum, @RequestParam(defaultValue = "2") Integer pageSize){
        return Result.success(employeeService.selectPage(pageNum,pageSize));
    }

1.5.4启动项目测试该接口

http://localhost:9999/employee/selectPage

1.5.5前端涉及到一个根据员工名称模糊分页查询，考虑到拓展直接传入Employee类型对象

所以要添加一个api接口

接口层：

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/controller/EmployeeController.java

  @PostMapping("/findByName")
    public Result findByName(@RequestBody Employee employee, @RequestParam(defaultValue = "1") Integer pageNum, @RequestParam(defaultValue = "2") Integer pageSize){
        return Result.success(employeeService.findByName(pageNum, pageSize,employee.getName()));
    }

业务层：

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/service/EmployeeService.java

    public PageInfo<Employee> findByName(Integer pageNum, Integer pageSize, String name) {
        PageHelper.startPage(pageNum,pageSize);
        List<Employee> list = employeeMapper.selectFuzzy(name);

        return PageInfo.of(list);
    }

持久层：

位置：src/main/java/com/cdp/zwy/zwy_manager_back_v1/mapper/EmployeeMapper.java

    List<Employee> selectFuzzy(String name);

位置:src/main/resources/mapper/EmployeeMapper.xml

    <select id="selectFuzzy" resultMap="employeeResultMap">
        select * from employee where 1 = 1
                                    and
                                   <if test="#{name}!=null">
                                   name like concat('%',#{name},'%')
                                  </if>


    </select>

测试该接口

1.6跨域请求

我们知道后端项目和前端项目是分离的，后端项目启动占用一个端口，前端项目启动占用一个端口

这是前端的一个按钮可能会调用有后端的一个api结构，这是就是跨域请求。这个项目是用的axios发送的请求，跨域配置在后端配置

位置：src/main/java/com/cdp/zwy/background_manager_back/config/CorsConfig.java

@Configuration
public class CorsFilter {
    @Bean
    public CorsFilter corsFiler(){
        UrlBasedCorsConfigurationSource source  = new UrlBasedCorsConfigurationSource();
        CorsConfiguration corsConfiguration = new CorsConfiguration();
        //1.设置访问源地址（任意地址可以访问，这里可以想象如果两台pc端链接是否可以直接在pc1部署后端，pc2部署前端）
        corsConfiguration.addAllowedOrigin("*");
        //2.设置访问源请求头
        corsConfiguration.addAllowedHeader("*");
        //3.设置访问源请求方法
        corsConfiguration.addAllowedMethod("*");
        //4.对接口配置跨域设置
        source.registerCorsConfiguration("/**",corsConfiguration);

        return new CorsFilter();
    }

pc: 前端文章正在写这两天搞完