Spring boot integrates the addition, deletion, modification and query operations of Mybatis on a single table

I. objectives

Spring boot integrates the addition, deletion, modification and query operations of Mybatis on a single table

2, Development tools and project environment

  • IDE: IntelliJ IDEA 2019.3

  • SQL: Navicat for MySQL

3, Basic environment configuration

  1. Create database: demodb

  2. Create data table and insert data

    DROP TABLE IF EXISTS t_employee;
    CREATE TABLE t_employee (
      id int PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary key number',
      name varchar(50) DEFAULT NULL COMMENT 'Employee name',
      sex varchar(2) DEFAULT NULL COMMENT 'Employee gender',
      phone varchar(11) DEFAULT NULL COMMENT 'Phone number'
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    
    INSERT INTO t_employee VALUES ('1', 'Zhang Sanfeng', 'male', '13812345678');
    INSERT INTO t_employee VALUES ('2', 'Guo Jing', 'male', '18898765432');
    INSERT INTO t_employee VALUES ('3', 'Little dragon maiden', 'female', '13965432188');
    INSERT INTO t_employee VALUES ('4', 'Zhao Min', 'female', '15896385278');
    
  3. The necessary Maven dependencies are as follows:

    <!--        MySQL rely on-->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
                <version>5.1.48</version>
            </dependency>
    
    <!--        Thymleaf rely on-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-thymeleaf</artifactId>
            </dependency>
    
    <!--        mybatis rely on-->
       <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.1.1</version>
            </dependency>
  4. To add a configuration file, you can use yaml configuration, that is, the configuration of application.yml connection pool is as follows:

    spring:
      ##Connection database configuration
      datasource:
        driver-class-name: com.mysql.jdbc.Driver
        ##jdbc:mysql: / / (ip address): (port number) / (database name)? useSSL=false
        url: jdbc:mysql://localhost:3306/demodb?useUnicode=true&useSSL=false&characterEncoding=UTF8
        ##Database login
        data-username: root
        ##Login password
        data-password:
    
      ##Path to static resources
      resources:
        static-locations=classpath:/templates/
    
  5. Test configuration

    In the com package of java, create a new package controller, and create a controller class in the package: = = EmployeeController==

    @Controller
    public class EmployeeController{
    //Test use
    @GetMapping("/test")
    //Comments: return to JSON format
    @ResponseBody
    public String test() {
    return "test";
    }
    }

    Start the main program class and enter: http://localhost:8080/test in the browser

4, Start writing

  1. Write ORM entity class

    In the com package of java, create a new package domain, and create the entity class in the package: Employee

    public class Employee {
        private Integer id;   //Primary key
        private String name;  //Employee name
        private String sex;   //Employee gender
        private String phone; //Phone number
    
        }

    ==Don't forget to package==

  2. Complete mapper layer = = add, delete, modify and query = = write

    In the com package of java, create a new package mapper, and create mapper interface file in the package: EmployeeMapper

    //Indicates that the class is a MyBatis interface file
    @Mapper
    //Represents the function of translating the native exception thrown by database operation into the persistent layer exception of spring
    @Repository
    public interface EmployeeMapper {
        //Query single data according to id
        @Select("SELECT*FROM t_employee WHERE id=#{id}")
        Employee findById(Integer id);
    
        //Query all data
        @Select("SELECT * FROM t_employee")
        public List<Employee> findAll();
    
        //Modify data according to id
        @UpdateProvider(type = EmployeeMapperSQL.class,method = "updateEmployee")
        int updateEmployee(Employee employee);
    
        //Add data
        @Insert("INSERT INTO t_employee(name,sex,phone)  values(#{name},#{sex},#{name})")
        public int inserEm(Employee employee);
    
        //Delete data by id
        @Delete("DELETE FROM t_employee WHERE id=#{id}")
        public int deleteEm(Integer id);
    }
    
  3. Complete the writing of the service layer, and provide the calling methods for the controller layer

    1. In the com package of java, create a new package service, and create the service interface file in the package: EmployeeServic

         ```java
        public interface EmployeeService {
            //Query all employee objects
            List<Employee> findAll();
            //Query single data according to id
            Employee findById(Integer id);
            //Modifying data
            int updateEmployee(Employee employee);
            //Add data
            int addEmployee(Employee employee);
            //delete
            int deleteEmployee(Integer id);
        }
    2. In the service package, create a new package impl, and create the implementation class of the interface in the package: EmployeeServiceImpl

      @Service
      //Affairs Management
      @Transactional
      public class EmployeeServiceImpl implements EmployeeService {
          //Inject EmployeeMapper interface
          @Autowired
          private EmployeeMapper employeeMapper;
          //Query all data
          public List<Employee> findAll() {
              return employeeMapper.findAll();
          }
      
          //Query single data according to id
          public Employee findById(Integer id) {
              return employeeMapper.findById(id);
          }
      
          //Modifying data
          public int updateEmployee(Employee employee) {
              return employeeMapper.updateEmployee(employee);
          }
      
          //Add to
          public int addEmployee(Employee employee) {
              return employeeMapper.inserEm(employee);
          }
      
          //Delete individual data by id
          public int deleteEmployee(Integer id) {
              return employeeMapper.deleteEm(id);
          }
      }
  4. Complete the writing of the Controller layer, call the function of the service layer, and respond to the page request

    Write the method in the controller.EmployeeController created previously

    @Controller
    public class EmployeeController {
    
        @Autowired
        private EmployeeService employeeService;
    
    //Main page  
        //Response queries all data, then displays all data
        @GetMapping("/getall")
        public String getAll(Model model) {
            List<Employee> employeeList = employeeService.findAll();
            model.addAttribute("employeeList", employeeList);
            return "showAllEmployees";
        }
    
    //Modify page
        //Response arrives at the page where the data is updated
        @GetMapping("/toUpdate/{id}")
        public String toUpdate(@PathVariable Integer id, Model model){
            //Query by id
            Employee employee=employeeService.findById(id);
            //Modified data
            model.addAttribute("employee",employee);
            //Jump modification
            return "update";
        }
    
        //Update data request and return getall
        @PostMapping("/update")
        public String update(Employee employee){
            //Report modification
            employeeService.updateEmployee(employee);
            return "redirect:/getall";
        }
    
    //Deleting function
        //The response deletes the individual data according to the id and then displays all the data
        @GetMapping("/delete/{id}")
        public String delete(@PathVariable Integer id){
            employeeService.deleteEmployee(id);
            return "redirect:/getall";
        }
    
    //Add page
        //Add data
        @PostMapping("/add")
        public String addEmployee(Employee employee){
            employeeService.addEmployee(employee);
            return "redirect:/getall";
        }
    }

5, Write front end

  1. Main page

    In templates of resources, create the main page: addEmployee.html

    <!DOCTYPE html>
    <html lang="en" xmlns:th="http://www.thymeleaf.prg">
    <head>
        <meta charset="UTF-8">
        <title>Add employee information</title>
    </head>
    <body>
    <h2>Add employee information</h2>
    <form action="/add" method="post">
        Full name:<input type="text" name="name"><br>
        Gender:<input type="radio" value="male" name="sex" checked="checked">male
        <input type="radio" value="female" name="sex" >female<br>
        Telephone:<input type="text" name="phone"><br>
        <input type="submit" value="Add to">
    </form>
    </body>
    </html>

    Note < HTML lang = "en" = = xmlns: th = "http://www.thymeleaf.prg" = = >

  2. Modify page

    In the templates of resouces, create the modification page: update.html

    <!DOCTYPE html>
    <html lang="en" xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8">
        <title>Modify information</title>
    </head>
    <body>
    <h2>Modify information</h2>
    <form th:action="@{/update}" th:object="${employee}" method="post">
        <input th:type="hidden" th:value="${employee.id}" th:field="*{id}">
        Full name:<input th:type="text" th:value="${employee.name}" th:field="*{name}"><br>
        Gender:<input th:type="radio" th:value="male" th:checked="${employee.sex=='male'}" th:field="*{sex}">male
        <input th:type="radio" th:value="female" th:checked="${employee.sex=='female'}" th:field="*{sex}">female<br>
        Telephone:<input th:type="text" th:value="${employee.phone}" th:field="*{phone}"><br>
        <input th:type="submit" value="To update">
    </form>
    
    </body>
    </html>
  3. Add page

    In templates of resources, create the add page: addEmployee.html

    <!DOCTYPE html>
    <html lang="en" xmlns:th="http://www.thymeleaf.prg">
    <head>
        <meta charset="UTF-8">
        <title>Add employee information</title>
    </head>
    <body>
    <h2>Add employee information</h2>
    <form action="/add" method="post">
        Full name:<input type="text" name="name"><br>
        Gender:<input type="radio" value="male" name="sex" checked="checked">male
        <input type="radio" value="female" name="sex" >female<br>
        Telephone:<input type="text" name="phone"><br>
        <input type="submit" value="Add to">
    </form>
    </body>
    </html>

    Start the main program class and enter: http://localhost:8080/getall in the browser

Tags: Java MySQL Spring Mybatis

Posted on Sat, 07 Mar 2020 11:03:50 -0500 by avatar.alex