[Java知识库]手把手教会MyBatis连接Mysql完成增删改查（CRUD）

MyBatis连接Mysql完成增删改查

1.第一步：创建一个SpringBoot项目

选择相关的依赖
注意如果忘记导入相关依赖：可以手动导入在下面的链接搜索相关jar的名称maven仓库

第二步：根据业务逻辑创建数据库表

DROP DATABASE emp_dep;

CREATE DATABASE emp_dep;

USE emp_dep;

CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT,
	DepartmentName VARCHAR(50) NOT NULL
);

INSERT INTO department(id, DepartmentName)
VALUES (1001, '教学部'),
       (1002, '市场部'),
       (1003, '教研部'),
       (1004, '运营部'),
       (1005, '后勤部');
       
       
CREATE TABLE employee(
	id       INT PRIMARY KEY AUTO_INCREMENT,
	EmployeeName    VARCHAR(50) NOT NULL,
	email    VARCHAR(50),
	gender   INT,
	birthday DATETIME,
	did      INT REFERENCES department (id)
);

INSERT INTO employee (EmployeeName, email, gender, birthday, did)
VALUES ('海康', '10086@qq.com', 1, NOW(), 1001),
       ('湛江', '10086@qq.com', 0, NOW(), 1002),
       ('桥头', '10086@qq.com', 1, NOW(), 1003),
       ('南粤', '10086@qq.com', 0, NOW(), 1004),
       ('粤西', '10086@qq.com', 1, NOW(), 1005);

第三步：创建pojo实体类

部门类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Department {
  private Integer id;
  private String DepartmentName;
}

员工类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    private Integer id;
    private String EmployeeName;
    private String email;
    private Integer gender;
    private String birthday;
    private Integer did;
    private String DepartmentName;
}

第四步：创建dao层

部门dao

@Mapper
@Repository
public interface DepartmentMapper {
    //查询所有部门信息
    public Collection<Department> getAllDepartment();
}

员工dao

@Mapper
@Repository
public interface EmployeeMapper {
    //查询所有员工信息
    public Collection<Employee> getAllEmployee();

    //通过Id查询员工
    public Employee getEmployeeById(@Param("id") Integer id);

    //增加一个员工
    int save(Employee employee);

    //通过修改员工信息
    int updateById(Employee employee);

    //通过Id删除
    int delete(@Param("id") Integer id);
}

第五步：创建Servlet层

部门servlet接口

public interface DepartmentService {

    //查询所有员工信息
    public Collection<Department> getAllDepartment();
}

部门servlet实现类

@Service
public class DepartmentServiceImpl implements DepartmentService {

    @Autowired
    DepartmentMapper departmentMapper;

    @Override
    public Collection<Department> getAllDepartment() {
        return departmentMapper.getAllDepartment();
    }
}

人员servlet接口

public interface EmployeeService {
    public Collection<Employee> getAllEmployee();

    public Employee getEmployeeById(@Param("id") Integer id );

    public int save(Employee employee);

    public int updateById(Employee employee);

    public int delete(@Param("id")Integer id);
}

人员servlet实现类

@Service
public class EmployeeServiceImpl implements EmployeeService{

    @Autowired
    EmployeeMapper employeeMapper;

    @Override
    public Collection<Employee> getAllEmployee() {
        return employeeMapper.getAllEmployee();
    }

    @Override
    public Employee getEmployeeById(Integer id) {
        return employeeMapper.getEmployeeById(id);
    }

    @Override
    public int save(Employee employee) {
        return employeeMapper.save(employee);
    }

    @Override
    public int updateById(Employee employee) {
        return employeeMapper.updateById(employee);
    }

    @Override
    public int delete(Integer id) {
        return employeeMapper.delete(id);
    }
}

第六步：在resources下创建接口实现类->Mapper配置文件DepartmentMapper.xml和EmployeeMapper.xml文件

注意的是Mapper配置文件可以与dao层中的接口放在一起，方便项目构建，以防报错，如果没有放在一起，一定要注意项目的构建关系，否则会报错，报错的因为是无法找到Mapper配置文件进行映射。

DepartmentMapper.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.haikang.mybatis01.mapper.DepartmentMapper">
    <select id="getAllDepartment" resultType="Department">
        select * from department
    </select>
</mapper>

namespace的属性是指明dao层部门接口的位置

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.haikang.mybatis01.mapper.EmployeeMapper">
<!--查询所有员工的信息-->
    <select id="getAllEmployee" resultType="Employee">
        select emp.id,emp.EmployeeName,emp.email,emp.gender,emp.birthday,dep.DepartmentName
        from department dep , employee emp where dep.id=emp.did
    </select>

<!--插入员工信息-->
    <insert id="save" parameterType="Employee">
        insert into employee (EmployeeName,email,gender,birthday,did) values (#{EmployeeName},#{email},#{gender},#{birthday},#{did})
    </insert>
<!--通过Id查询员工信息：用来修改员工信息-->
    <select id="getEmployeeById" resultType="Employee">
        select emp.id,emp.EmployeeName,emp.email,emp.gender,emp.birthday,emp.did,dep.DepartmentName from
         employee emp , department dep where emp.did=dep.id and emp.id=#{id}
    </select>
<!--修改员工信息-->
    <update id="updateById" parameterType="Employee">
        update employee emp set emp.EmployeeName=#{EmployeeName},emp.email=#{email},
                                emp.gender=#{gender},emp.birthday=#{birthday},emp.did=#{did}
        where id=#{id}
    </update>
<!--修改员工-->
    <delete id="delete" parameterType="java.lang.Integer">
        delete from  employee where id=#{id}
    </delete>
</mapper>

**

第七步：在resources下创建application.yml文件用于配置数据源及mybatis相关的配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root
    url: jdbc:mysql://127.0.0.1:3306/emp_dep?useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=UTC
  mvc:
    format:
      date: yyyy-MM-dd


mybatis:
  type-aliases-package: com.haikang.mybatis01.pojo
  mapper-locations: classpath:com/haikang/mybatis01/mapper/*.xml

第八步：创建EmloyeeCotroller控制类

@Controller
public class EmployeeController {
    @Autowired
    DepartmentServiceImpl departmentService;

    @Autowired
    EmployeeServiceImpl employeeService;

    @RequestMapping("/hello")
    public String controller(){
        return "dashboard";
    }

第九步：在resources中的templates文件夹下创建dashboard.html页面

<!DOCTYPE html>
<html lang="en"  xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<main>
    <h1 style="color: red">先跑起来吧,再完成CRUD操作！</h1>
    <a class="button" th:href="@{/user/list}">查询所有员工</a>
    <!--注意由于A标签是Get请求方式，所以在访求在以Get方式接收-->
</main>
</body>
</html>

运行结果

CRUD操作

1.查询所有员工

在EmloyeeController控制器中：编写展现所有员工信息方法

    @GetMapping("/user/list")
    public String list(Model model){
        Collection<Employee> allEmployee = employeeService.getAllEmployee();
        model.addAttribute("emps",allEmployee);
//        System.out.println(allEmployee);
        return "list";
    }

编写list页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<main role="main" class="col-md-9 ml-sm-auto col-lg-10 pt-3 px-4">

  <form class="form-inline" style="float: left;">
    <h2>员工列表</h2>
    <p style="width: 650px;"></p>
    <a class="btn btn-sm btn-success" style="color:chartreuse" th:href="@{/emp/add}">添加</a>


  <div class="table-responsive">
    <table class="table table-striped table-sm">
    <thead>
      <tr>
          <th>编号</th>
          <th>姓名</th>
          <th>邮箱</th>
          <th>性别</th>
          <th>部门名称</th>
          <th>生日</th>
      </tr>
    </thead>

        <tbody>
        <tr th:each="emp:${emps}">
            <td th:text="${emp.getId()}"></td>
            <td th:text="${emp.getEmployeeName()}"></td>
            <td th:text="${emp.getEmail()}"></td>
            <td th:text="${emp.getGender()}==0?'女':'男'"></td>
            <td th:text="${emp.getDepartmentName()}"></td>
            <td th:text="${emp.getBirthday()}"></td>
            <td>
                <a class="button" style="color: red" th:href="@{/emp/update/}+${emp.getId()}">修改</a>
                <a class="button" style="color:green" th:href="@{/emp/delete/}+${emp.getId()}">删除</a>
            </td>
        </tr>
        </tbody>
    </table>
  </div>
  </form>
</main>
</body>
</html>

运行结果

2.添加员工操作

在EmloyeeController控制器中：编写添加员工信息方法
注意点是：在添加员工时应该先来到添加员工页面，让员工填写信息，后添加

 //添加员工：先来到员工添加页面
    @GetMapping("/emp/add")
    public String toAdd(Model model){
        //添加员工需要将部门信息展现给用户选择
        Collection<Department> allDepartment = departmentService.getAllDepartment();
        model.addAttribute("dets",allDepartment);
        return "add";
    }

    //正式添加员工
    @PostMapping("/addEmp")
    public String add(Employee employee){
        System.out.println(employee);
        employeeService.save(employee);
        return "redirect:/user/list";//重定向回/user/list请求，将信息展现给用户
    }

编写add页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<main>
  <form method="post" th:action="@{/addEmp}">
    <div class="form-group">
      <label>LastName</label>
      <input type="text" class="form-control" name="EmployeeName" placeholder="海康">
    </div>
    <div class="form-group">
      <label>Email</label>
      <input type="email" class="form-control" name="email" placeholder="10086qq.com">
    </div>
    <div class="form-group">
      <label>Gender</label><br/>
      <div class="form-check form-check-inline">
        <input class="form-check-input" type="radio" checked name="gender"  value="1">
        <label class="form-check-label">男</label>
      </div>
      <div class="form-check form-check-inline">
        <input class="form-check-input" type="radio" name="gender"  value="0">
        <label class="form-check-label">女</label>
      </div>
    </div>
    <div class="form-group">
      <label>department</label>
      <select class="form-control" name="did">
        <option th:each="dep:${dets}" th:text="${dep.getDepartmentName()}" th:value="${dep.getId()}"></option>
      </select>
    </div>
    <div class="form-group">
      <label>Birth</label>
      <input type="text" class="form-control" name="birthday" placeholder="yyyy-MM-dd">
    </div>
    <button type="submit" class="btn btn-primary">添加</button>
  </form>
</main>
</body>
</html>

运行结果

3.修改员工操作

在EmloyeeController控制器中：编写修改员工信息方法
注意点是：在修改员工时应该先来到修改员工页面，让员工填写信息，后修改

   //修改员工信息：先来到修改页面
    @GetMapping("/emp/update/{id}")
    public String toUpdate(@PathVariable("id")Integer id, Model model){
        Collection<Department> allDepartment = departmentService.getAllDepartment();
        model.addAttribute("dets",allDepartment);
        Employee employeeById = employeeService.getEmployeeById(id);
        model.addAttribute("emps",employeeById);
        System.out.println(allDepartment);
        System.out.println(employeeById);
        return "update";
    }

    //修改员工信息
    @PostMapping("/addUpdate")
    public String update(Employee employee){
        System.out.println(employee);
        employeeService.updateById(employee);
        return "redirect:/user/list";
    }

编写update页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<main>
  <form method="post" th:action="@{/addUpdate}">
    <div class="form-group">
      <input type="hidden" name="id" th:value="${emps.getId()}">
      <label>LastName</label>
      <input type="text" class="form-control" th:value="${emps.getEmployeeName()}" name="EmployeeName" placeholder="海康">
    </div>
    <div class="form-group">
      <label>Email</label>
      <input type="email" class="form-control" name="email" th:value="${emps.getEmail()}" placeholder="10086qq.com">
    </div>
    <div class="form-group">
      <label>Gender</label><br/>
      <div class="form-check form-check-inline">
        <input class="form-check-input" type="radio" checked name="gender" th:checked="${emps.getGender()}==1" value="1">
        <label class="form-check-label">男</label>
      </div>
      <div class="form-check form-check-inline">
        <input class="form-check-input" type="radio" name="gender" th:checked="${emps.getGender()}==0"  value="0">
        <label class="form-check-label">女</label>
      </div>
    </div>
    <div class="form-group">
      <label>department</label>
      <select class="form-control" name="did">
        <option th:each="dep:${dets}" th:text="${dep.getDepartmentName()}" th:selected="${emps.getDid()}==${dep.getId()}" th:value="${dep.getId()}"></option>
      </select>
    </div>
    <div class="form-group">
      <label>Birth</label>
      <input type="text" class="form-control" name="birthday" th:value="${emps.getBirthday()}" placeholder="yyyy-MM-dd">
    </div>
    <button type="submit" class="btn btn-primary">修改</button>
  </form>
</main>
</body>
</html>

运行结果

4.删除员工操作

在EmloyeeController控制器中：编写删除员工方法，由于删除是通过Id删除的所以不需要进行跳转到页面，直接编写删除即可

 //删除员工

    @GetMapping("/emp/delete/{id}")
    public String delete(@PathVariable("id")Integer id){
        employeeService.delete(id);
        return "redirect:/user/list";
    }

运行结果：删除西藏真美和西安
**
最终的运行结果**

需要源码的朋友，私信我，有不清楚的地方请留言