Mybatis框架实现单表增删改查

一 数据库准备
在 MySQL 数据库中创建travel_application表：
CREATE TABLE travel_application (
ID varchar(8) NOT NULL,
name varchar(255) DEFAULT NULL,
department varchar(255) DEFAULT NULL,
destination varchar(255) DEFAULT NULL,
departuredate date DEFAULT NULL,
returndate date DEFAULT NULL,
Type varchar(255) DEFAULT NULL,
Typecontent varchar(255) DEFAULT NULL,
Reason varchar(255) DEFAULT NULL,
State varchar(255) DEFAULT NULL,
Statereason varchar(255) DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2. 创建 Spring Boot 项目
在 IDEA 中创建一个 Spring Boot 项目，选择Web和MyBatis Framework依赖。
3. 持久层（Dao）
实体类（Entity）：在com.example.entity包下创建TravelApplication实体类。
package com.example.entity;

import java.util.Date;

public class TravelApplication {
private String id;
private String name;
private String department;
private String destination;
private Date departuredate;
private Date returndate;
private String type;
private String typecontent;
private String reason;
private String state;
private String statereason;

// 生成 Getter 和 Setter 方法
//...

}
Mapper 接口（Dao）：在com.example.dao包下创建TravelApplicationMapper接口。
package com.example.dao;

import com.example.entity.TravelApplication;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface TravelApplicationMapper {
// 插入出差申请
@Insert("INSERT INTO travel_application (ID, name, department, destination, departuredate, returndate, Type, Typecontent, Reason, State) VALUES (#{id}, #{name}, #{department}, #{destination}, #{departuredate}, #{returndate}, #{type}, #{typecontent}, #{reason}, #{state})")
@Options(useGeneratedKeys = true, keyProperty = "id")
void insertTravelApplication(TravelApplication application);

// 根据ID删除出差申请
@Delete("DELETE FROM travel_application WHERE ID = #{id}")
void deleteTravelApplication(String id);

// 更新出差申请
@Update("UPDATE travel_application SET destination = #{destination}, departuredate = #{departuredate}, returndate = #{returndate}, Type = #{type}, Typecontent = #{typecontent}, Reason = #{reason}, State = #{state} WHERE ID = #{id}")
void updateTravelApplication(TravelApplication application);

// 查询所有出差申请
@Select("SELECT * FROM travel_application")
List<TravelApplication> selectAllTravelApplications();

// 根据条件查询出差申请
@Select("<script>SELECT * FROM travel_application WHERE 1=1 " +
        "<if test='name!= null and name!= \"\"'> AND name = #{name} </if>" +
        "<if test='department!= null and department!= \"\"'> AND department = #{department} </if>" +
        "<if test='state!= null and state!= \"\"'> AND State = #{state} </if>" +
        "</script>")
List<TravelApplication> selectTravelApplicationsByCondition(TravelApplication application);

}
Mapper XML 文件：在src/main/resources/mapper目录下创建TravelApplicationMapper.xml文件。

4. 业务层（Service） 在com.example.service包下创建TravelApplicationService类。 package com.example.service;

import com.example.dao.TravelApplicationMapper;
import com.example.entity.TravelApplication;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class TravelApplicationService {
@Autowired
private TravelApplicationMapper travelApplicationMapper;

public void insertTravelApplication(TravelApplication application) {
    travelApplicationMapper.insertTravelApplication(application);
}

public void deleteTravelApplication(String id) {
    travelApplicationMapper.deleteTravelApplication(id);
}

public void updateTravelApplication(TravelApplication application) {
    travelApplicationMapper.updateTravelApplication(application);
}

public List<TravelApplication> selectAllTravelApplications() {
    return travelApplicationMapper.selectAllTravelApplications();
}

public List<TravelApplication> selectTravelApplicationsByCondition(TravelApplication application) {
    return travelApplicationMapper.selectTravelApplicationsByCondition(application);
}

}
5. 控制层（Controller）
在com.example.controller包下创建TravelApplicationController类。
package com.example.controller;

import com.example.entity.TravelApplication;
import com.example.service.TravelApplicationService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/travel")
public class TravelApplicationController {
@Autowired
private TravelApplicationService travelApplicationService;

@PostMapping("/apply")
public void insertTravelApplication(@RequestBody TravelApplication application) {
    travelApplicationService.insertTravelApplication(application);
}

@DeleteMapping("/{id}")
public void deleteTravelApplication(@PathVariable("id") String id) {
    travelApplicationService.deleteTravelApplication(id);
}

@PutMapping("/update")
public void updateTravelApplication(@RequestBody TravelApplication application) {
    travelApplicationService.updateTravelApplication(application);
}

@GetMapping("/all")
public List<TravelApplication> selectAllTravelApplications() {
    return travelApplicationService.selectAllTravelApplications();
}

@GetMapping("/condition")
public List<TravelApplication> selectTravelApplicationsByCondition(TravelApplication application) {
    return travelApplicationService.selectTravelApplicationsByCondition(application);
}

}
6. HTML 页面
在src/main/resources/static目录下创建index.html页面。

Travel Application Management

Get All Applications Get By Condition Add Application Update Application Delete Application

<div id="result"></div>

<script>
    function getAll() {
        $.ajax({
            type: "GET",
            url: "/travel/all",
            success: function (data) {
                let resultDiv = $("#result");
                resultDiv.empty();
                data.forEach(function (app) {
                    resultDiv.append("<p>ID: " + app.id + ", Name: " + app.name + "</p>");
                });
            }
        });
    }

    function getByCondition() {
        let condition = {
            name: "John",
            department: "IT"
        };
        $.ajax({
            type: "GET",
            url: "/travel/condition",
            data: condition,
            success: function (data) {
                let resultDiv = $("#result");
                resultDiv.empty();
                data.forEach(function (app) {
                    resultDiv.append("<p>ID: " + app.id + ", Name: " + app.name + "</p>");
                });
            }
        });
    }

    function add() {
        let application = {
            id: "20230001",
            name: "Alice",
            department: "HR",
            destination: "New York",
            departuredate: "2023-01-01",
            returndate: "2023-01-05",
            type: "Business Trip",
            state: "Pending"
        };
        $.ajax({
            type: "POST",
            url: "/travel/apply",
            contentType: "application/json",
            data: JSON.stringify(application),
            success: function () {
                alert("Application added successfully");
            }
        });
    }

    function update() {
        let application = {
            id: "20230001",
            destination: "Los Angeles"
        };
        $.ajax({
            type: "PUT",
            url: "/travel/update",
            contentType: "application/json",
            data: JSON.stringify(application),
            success: function () {
                alert("Application updated successfully");
            }
        });
    }

    function deleteApp() {
        let id = "20230001";
        $.ajax({
            type: "DELETE",
            url: "/travel/" + id,
            success: function () {
                alert("Application deleted successfully");
            }
        });
    }
</script>

7. 配置文件 application.properties（在src/main/resources目录下）配置如下： spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name spring.datasource.username=your_username spring.datasource.password=your_password spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver mybatis.mapper-locations=classpath:mapper/*.xml