Implementing CRUD Operations in SpringBoot with MyBatis and MySQL

The article continues directly with CRUD (Create, Read, Update, Delete) implementation, assuming the project setup and MySQL connection have been covered in earlier posts.

After completing the steps, the project directory includes new or updated files highlighted in the accompanying diagram.

A new User.xml mapper file is added, containing dynamic SQL for selective updates of user fields.

<?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.example.runlife.mapper.UserMapper">
    <update id="update">
        update tt_user_user
        <set>
            <if test="uname != null">
                uname=#{uname},
            </if>
            <if test="upwd != null">
                upwd=#{upwd},
            </if>
            <if test="uemail!=null">
                uemail=#{uemail},
            </if>
            <if test="uphone != null">
                uphone=#{uphone},
            </if>
            <if test="uaddname != null">
                uaddname=#{uaddname},
            </if>
            <if test="uaddress != null">
                uaddress=#{uaddress},
            </if>
            <if test="upostcode != null">
                upostcode=#{upostcode},
            </if>
            <if test="uaddressee != null">
                uaddressee=#{uaddressee}
            </if>
        </set>
        <where>
            id =#{id}
        </where>
    </update>
</mapper>

The application.yml configuration file is extended to define the server port, datasource properties, and MyBatis mapper locations.

server:
  port: 9090

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/tiantian?serverTimezone=GMT%2b8
    username: root
    password: rootroot
mybatis:
  mapper-locations: classpath:mapper/*.xml #扫描所有mybatis的xml文件

The UserMapper.java interface now includes methods for querying all users, inserting a new user, updating (using the dynamic SQL defined in User.xml), and deleting by ID, annotated with MyBatis annotations.

package com.example.runlife.mapper;

import com.example.runlife.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface UserMapper {
    //查询
    @Select("select * from tt_user_user")
    List<User> findAll();

    //新增
    @Insert("insert into tt_user_user(uname,upwd,uemail,uphone,uaddname,uaddress,upostcode,uaddressee) values(#{uname},#{upwd},#{uemail},#{uphone},#{uaddname},#{uaddress},#{upostcode},#{uaddressee})")
    int insert(User user);

    //本次注释了语句，保证动态更新
    // @Update("update tt_user_user set uname=#{uname},upwd=#{upwd},uemail=#{uemail},uphone=#{uphone},uaddname=#{uaddname},uaddress=#{uaddress},upostcode=#{upostcode},uaddressee=#{uaddressee} where id=#{id}")
    //更新
    int update(User user);

    //删除
    @Delete("delete from tt_user_user where id = #{id}")
    Integer updateById(@Param("id") Integer id);
}

A UserService.java class is introduced to encapsulate the business logic that decides whether to insert or update a user based on the presence of an ID.

package com.example.runlife.service;

import com.example.runlife.entity.User;
import com.example.runlife.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public int save(User user) {
        if (user.getId() == null) {
            return userMapper.insert(user);
        } else {
            return userMapper.update(user);
        }
    }
}

The UserController.java provides REST endpoints for retrieving all users, saving (creating or updating) a user, and deleting a user by ID.

package com.example.runlife.controller;

import com.example.runlife.entity.User;
import com.example.runlife.mapper.UserMapper;
import com.example.runlife.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private UserService userService;

    // 查询所有数据
    @GetMapping
    public List<User> index() {
        List<User> all = userMapper.findAll();
        return all;
    }

    //新增和修改
    @PostMapping
    public Integer save(@RequestBody User user){
        // 新增或者更新
        return userService.save(user);
    }

    @DeleteMapping("/{id}")
    public Integer delete(@PathVariable Integer id){
        return userMapper.updateById(id);
    }
}

With these additions, the CRUD functionality for the user entity is fully operational.