Springboot整合Mybatis进行MySQL数据库的增删改查

Springboot整合Mybatis进行MySQL数据库的增删改查

一、数据库建表

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT 'yourname',
  `age` int DEFAULT '0',
  `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '中国',
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO `teacher` VALUES (15,'olilgei',20,'美国','2021-06-25 09:36:58','2021-06-25 09:46:24'),(16,'huhaha',13,'法国','2021-06-25 09:45:49','2021-06-25 09:45:49'),(17,'wulala',19,'日本','2021-06-25 09:45:49','2021-06-25 09:45:49'),(18,'gogogo',16,'乌克兰','2021-06-25 09:45:49','2021-06-25 09:45:49'),(19,'aiaiai',18,'韩国','2021-06-25 09:45:49','2021-06-25 09:45:49');

二、创建Springboot项目mybatisdemo，导入依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

三、mybatisdemo.entity.User

import lombok.Data;

import java.io.Serializable;
import java.util.Date;

@Data
public class User implements Serializable {
    private Integer id;
    private String name;
    private Integer age;
    private String address;
    private Date createtime;
    private Date updatetime;
}

四、mybatisdemo.mapper.UserMapper

import com.yyf.mybatisdemo.entity.User;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserMapper {
    User queryById(int id);

    List<User> getAllUsers();

    void insertUser(User user);

    void updateUser(User user);

    void deleteUser(int id);
}

五、resources/mapper/Mapper.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.yyf.mybatisdemo.mapper.UserMapper">
    <select id="queryById" resultType="com.yyf.mybatisdemo.entity.User">
        select *
        from teacher
        where id = #{id}
    </select>
    <select id="getAllUsers" resultType="com.yyf.mybatisdemo.entity.User">
        select *
        from teacher
    </select>
    <insert id="insertUser" parameterType="com.yyf.mybatisdemo.entity.User"
            useGeneratedKeys="true" keyProperty="id"> <!--这行的作用是执行sql插入数据的语句之后，把主键id再回填到实体对象user中，这样就能获取到新插入的数据的主键并返回-->
    insert into teacher (name, age, address)
    values (#{name}, #{age}, #{address})
    </insert>
    <update id="updateUser" parameterType="com.yyf.mybatisdemo.entity.User">
        update teacher
        <set>
            <if test="name!=null">name=#{name},</if>
            <if test="age!=null">age=#{age},</if>
            <if test="address!=null">address=#{address}</if>
        </set>
        where id=#{id}
    </update>
    <delete id="deleteUser">
        delete
        from teacher
        where id = #{id}
    </delete>
</mapper>

MyBatis mapper.xml文件中的SQL语句标签中间不能夹杂乱七八糟的注释，只能这样注释：

<!-- 注释内容 -->

MyBatis动态SQL

update teacher
        <set>
            <if test="name!=null">name=#{name},</if>
            <if test="age!=null">age=#{age},</if>
            <if test="address!=null">address=#{address}</if>
        </set>
        where id=#{id}

六、mybatisdemo.Service.UserService

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

import java.util.List;

@Service
public class UserService {
    @Autowired
    UserMapper userMapper;

    public User queryById(int id) {
        return userMapper.queryById(id);
    }

    public List<User> getAllUsers() {
        return userMapper.getAllUsers();
    }

    public int insertUser(User user){
        userMapper.insertUser(user);
        return user.getId();//插入数据并返回主键
    }

    public void updateUser(User user){
        userMapper.updateUser(user);
    }

    public void deleteUser(int id){
        userMapper.deleteUser(id);
    }

}

七、mybatisdemo.controller.UserController

import com.yyf.mybatisdemo.Service.UserService;
import com.yyf.mybatisdemo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
public class UserController {
    @Autowired
    UserService userService;

    @GetMapping("user/{id}")
    public User queryById(@PathVariable int id){
        return userService.queryById(id);
    }

    @GetMapping("/users")
    public List<User> getAllUsers(){
        return userService.getAllUsers();
    }

    @PostMapping("/user/add")
    public int insertUser(@RequestBody User user){ //因为落下注解@RequestBody，导致实体对象user的数据拿不到
        return userService.insertUser(user);
    }

    @PostMapping("/user/update")
    public void updateUser(@RequestBody User user){
        userService.updateUser(user);
    }

    @DeleteMapping("/user/{id}")
    public String deleteUser(@PathVariable int id){
        userService.deleteUser(id);
        return "删除成功";
    }
}

八、Springboot配置文件，src/main/resources/application.yml

server:
  port: 8090
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/study?characterEncoding=utf8&serverTimezone=UTC
    username: 
    password: 
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.yyf.mybatisdemo.entity

九、测试

查

get

localhost:8090/users

增

post

localhost:8090/user/add

{
    "name": "mybatis3",
    "age": 25,
    "address": "比利时"
}

改

post

localhost:8090/user/update

{
    "id":18,
    "address":"葡萄牙"
}

删

delete

localhost:8090/user/15