MyBatis connects to Mysql to complete addition, deletion, modification and query

1. Step 1: create a SpringBoot project

Select related dependencies
Note: if you forget to import the related dependencies: you can import them manually. Search the name of the related jar in the link below maven warehouse

Step 2: create database tables according to business logic

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, 'Teaching Department'),
       (1002, 'Marketing Department'),
       (1003, 'Teaching and Research Department'),
       (1004, 'Operation Department'),
       (1005, 'Logistics Department');
       
       
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 ('Haikang', '10086@qq.com', 1, NOW(), 1001),
       ('Zhanjiang', '10086@qq.com', 0, NOW(), 1002),
       ('Bridgehead', '10086@qq.com', 1, NOW(), 1003),
       ('Southern Guangdong', '10086@qq.com', 0, NOW(), 1004),
       ('the western part of Guang-dong province', '10086@qq.com', 1, NOW(), 1005);

Step 3: create pojo entity class

Department category

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

Employee category

@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;
}

Step 4: create dao layer

Department dao

@Mapper
@Repository
public interface DepartmentMapper {
    //Query all department information
    public Collection<Department> getAllDepartment();
}

Employee dao

@Mapper
@Repository
public interface EmployeeMapper {
    //Query all employee information
    public Collection<Employee> getAllEmployee();

    //Query employee by Id
    public Employee getEmployeeById(@Param("id") Integer id);

    //Add an employee
    int save(Employee employee);

    //By modifying employee information
    int updateById(Employee employee);

    //Delete by Id
    int delete(@Param("id") Integer id);
}

Step 5: create Servlet layer

Department servlet interface

public interface DepartmentService {

    //Query all employee information
    public Collection<Department> getAllDepartment();
}

Department servlet implementation class

@Service
public class DepartmentServiceImpl implements DepartmentService {

    @Autowired
    DepartmentMapper departmentMapper;

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

Human servlet interface

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);
}

Human servlet implementation class

@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);
    }
}

Step 6: create the interface implementation class - > mapper configuration file DepartmentMapper.xml and EmployeeMapper.xml under resources

Note that Mapper configuration files can be placed together with the interfaces in dao layer to facilitate project construction and prevent errors. If they are not placed together, be sure to pay attention to the construction relationship of the project, otherwise errors will be reported because Mapper configuration files cannot be found for mapping.

DepartmentMapper.xml file (Department)

<?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>

The namespace attribute indicates the location of the dao layer Department interface

EmployeeMapper.xml file (Department)

<?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">
<!--Query the information of all employees-->
    <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 employee information-->
    <insert id="save" parameterType="Employee">
        insert into employee (EmployeeName,email,gender,birthday,did) values (#{EmployeeName},#{email},#{gender},#{birthday},#{did})
    </insert>
<!--adopt Id Query employee information: used to modify employee information-->
    <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>
<!--Modify employee information-->
    <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>
<!--Modify employee-->
    <delete id="delete" parameterType="java.lang.Integer">
        delete from  employee where id=#{id}
    </delete>
</mapper>

**

Step 7: create an application.yml file under resources to configure the data source and mybatis related configurations

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

Step 8: create the employeecotroller control class

@Controller
public class EmployeeController {
    @Autowired
    DepartmentServiceImpl departmentService;

    @Autowired
    EmployeeServiceImpl employeeService;

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

Step 9: create a dashboard.html page under the templates folder in resources

<!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">Let's run first,Finish again CRUD Operation!</h1>
    <a class="button" th:href="@{/user/list}">Query all employees</a>
    <!--Attention due to A Label is Get Request mode, so in the visit Get Mode reception-->
</main>
</body>
</html>

Operation results

CRUD operation

1. Query all employees

In the employeecontroller controller: write a method to display all employee information

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

Write a list page

<!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>Employee list</h2>
    <p style="width: 650px;"></p>
    <a class="btn btn-sm btn-success" style="color:chartreuse" th:href="@{/emp/add}">add to</a>


  <div class="table-responsive">
    <table class="table table-striped table-sm">
    <thead>
      <tr>
          <th>number</th>
          <th>full name</th>
          <th>mailbox</th>
          <th>Gender</th>
          <th>Department name</th>
          <th>birthday</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?'female':'male'"></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()}">modify</a>
                <a class="button" style="color:green" th:href="@{/emp/delete/}+${emp.getId()}">delete</a>
            </td>
        </tr>
        </tbody>
    </table>
  </div>
  </form>
</main>
</body>
</html>

Operation results

2. Add employee

In the employeecontroller controller: write a method to add employee information
Note: when adding an employee, you should first go to the add employee page and ask the employee to fill in the information before adding

 //Add employee: go to the employee adding page first
    @GetMapping("/emp/add")
    public String toAdd(Model model){
        //To add an employee, you need to present the Department information to the user for selection
        Collection<Department> allDepartment = departmentService.getAllDepartment();
        model.addAttribute("dets",allDepartment);
        return "add";
    }

    //Formally add employees
    @PostMapping("/addEmp")
    public String add(Employee employee){
        System.out.println(employee);
        employeeService.save(employee);
        return "redirect:/user/list";//Redirect back to the / user/list request to present the information to the user
    }

Write add page

<!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="Haikang">
    </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">male</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">female</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">add to</button>
  </form>
</main>
</body>
</html>

Operation results

3. Modify employee operation

In the employeecontroller controller: write a method to modify employee information
Note: when modifying an employee, you should first go to the modify employee page and ask the employee to fill in the information before modifying

   //Modify employee information: go to the modify page first
    @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";
    }

    //Modify employee information
    @PostMapping("/addUpdate")
    public String update(Employee employee){
        System.out.println(employee);
        employeeService.updateById(employee);
        return "redirect:/user/list";
    }

Write update page

<!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="Haikang">
    </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">male</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">female</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">modify</button>
  </form>
</main>
</body>
</html>

Operation results

4. Delete employee

In the employeecontroller controller: write a method to delete an employee. Because the deletion is performed through Id, you do not need to jump to the page, but write and delete it directly

 //Delete employee

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

Operation result: delete Tibet Zhenmei and Xi'an
**
Final operation results**

Need source code friends, private letter me, there are unclear places, please leave a message