SpringBoot整合mybatis实现增删改查

一、前言

  学习SpringBoot的旅途中肯定离不开数据库的操作，那么这里简单使用springboot框架整合mybatis实现对用户的增删改查操作。

二、配置pom文件引入依赖

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

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.google.code.gson/gson -->
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.9.0</version>
        </dependency>
    </dependencies>

三、实现增删改查

1. 目录结构

.
├── http.http
├── pom.xml
├── src
│   ├── main
│   │   ├── java
│   │   │   └── io
│   │   │       └── github
│   │   │           └── yishuoit
│   │   │               ├── Main.java
│   │   │               ├── controller
│   │   │               │   └── UserController.java
│   │   │               ├── entity
│   │   │               │   └── UserEntity.java
│   │   │               ├── mapper
│   │   │               │   └── UserMapper.java
│   │   │               └── service
│   │   │                   ├── UserService.java
│   │   │                   └── impl
│   │   │                       └── UserServiceImpl.java
│   │   └── resources
│   │       └── application.yml
│   └── test
│       └── java
└── target
    ├── classes
    │   ├── application.yml
    │   └── io
    │       └── github
    │           └── yishuoit
    │               ├── Main.class
    │               ├── controller
    │               │   └── UserController.class
    │               ├── entity
    │               │   └── UserEntity.class
    │               ├── mapper
    │               │   └── UserMapper.class
    │               └── service
    │                   ├── UserService.class
    │                   └── impl
    │                       └── UserServiceImpl.class
    └── generated-sources
        └── annotations

26 directories, 16 files

2. application.yml 配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/blag
    username: root
    password: root

mybatis:
  configuration:
    map-underscore-to-camel-case: true
  type-aliases-package: io.github.yishuoit.mapper

logging:
  level:
    io.github.yishuoit.mapper: DEBUG

3. entity

UserEntity

package io.github.yishuoit.entity;

import lombok.Data;

/**
 * userEntity
 *
 * @author 艺说IT
 * @version 0.1
 * @date 2022年12月06日 22:51
 **/

@Data
public class UserEntity {
    private Integer id;
    private String username;
    private String password;
}

4. mapper

Interface UserMapper

package io.github.yishuoit.mapper;

import io.github.yishuoit.entity.UserEntity;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * userMapperr
 *
 * @author 艺说IT
 * @version 0.1
 * @date 2022年12月06日 22:59
 **/
@Mapper
public interface UserMapper {

    // 增
    @Insert("INSERT INTO user VALUES(null,#{username},#{password}) ")
    Integer addUser(@Param("username") String username,@Param("password") String password);

    // 删
    @Delete("DELETE FROM user WHERE id=${id}")
    Integer deleteUser(@Param("id") Integer id);

    // 改
    @Update("UPDATE user SET password=#{password} WHERE username=#{username}")
    Integer updateUser(@Param("username") String username,@Param("password") String password);

    // 查
    @Select("SELECT id,username,password FROM user WHERE id=#{id} ")
    List<UserEntity> queryUser(@Param("id") Integer id);

}

5. service

interface UserService

package io.github.yishuoit.service;

import io.github.yishuoit.entity.UserEntity;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * UserService
 *
 * @author 艺说IT
 * @version 0.1
 * @date 2022年12月06日 23:13
 **/
@Component
public interface UserService {
    String addUser(String username, String password);
    String deleteUser(Integer id);
    String updateUser(String username, String password);
    List<UserEntity> queryUser(Integer id);
}

实现类 UserServiceImpl

package io.github.yishuoit.service.impl;

import io.github.yishuoit.entity.UserEntity;
import io.github.yishuoit.mapper.UserMapper;
import io.github.yishuoit.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * UserServiceImpl
 *
 * @author 艺说IT
 * @version 0.1
 * @date 2022年12月06日 23:14
 **/
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    UserMapper userMapper;

    @Override
    public String addUser(String username, String password) {
        Integer res = userMapper.addUser(username, password);
        if(res == 1){
            return "添加成功";
        }
        return "添加失败";
    }

    @Override
    public String deleteUser(Integer id) {
        Integer res = userMapper.deleteUser(id);
        if(res == 1){
            return "删除成功";
        }
        return "删除失败";
    }

    @Override
    public String updateUser(String username, String password) {
        Integer res = userMapper.updateUser(username,password);
        if(res == 1){
            return "修改成功";
        }
        return "修改失败";
    }

    @Override
    public List<UserEntity> queryUser(Integer id) {
        return userMapper.queryUser(id);
    }
}

6. controller

package io.github.yishuoit.controller;

import com.google.gson.Gson;
import io.github.yishuoit.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

/**
 * UserController
 *
 * @author 艺说IT
 * @version 0.1
 * @date 2022年12月06日 23:20
 **/
@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    UserService userService;

    /**
     * @Description: 添加用户接口
     * @Author: 艺说IT
     * @Date: 2022/12/7 01:04 
     * @Param: [username, password]
     * @Return: java.lang.String
     */
    

    @PostMapping("/add")
    public String addUser(@RequestParam String username,@RequestParam String password){
        return userService.addUser(username,password);
    }

    /**
     * @Description: 删除用户接口
     * @Author: 艺说IT
     * @Date: 2022/12/7 01:04 
     * @Param: [id]
     * @Return: java.lang.String
     */
    

    @DeleteMapping("/delete")
    public String deleteUser(@RequestParam Integer id){
        return userService.deleteUser(id);
    }
    
    /**
     * @Description: 修改用户接口
     * @Author: 艺说IT
     * @Date: 2022/12/7 01:03
     * @Param: [id, username, password]
     * @Return: java.lang.String
     */

    @PostMapping("/update")
    public String updateUser(@RequestParam String username,@RequestParam String password){
        return userService.updateUser(username,password);
    }
    
    /**
     * @Description: 查询用户接口
     * @Author: 艺说IT
     * @Date: 2022/12/7 01:04 
     * @Param: [id]
     * @Return: java.lang.String
     */
    
    @GetMapping("/query")
    public String getUser(@RequestParam Integer id){
        return new Gson().toJson(userService.queryUser(id));
    }
    
}

四、mysql建库建库语句

create database blog;
use blog;
/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80031 (8.0.31)
 Source Host           : localhost:3306
 Source Schema         : blog

 Target Server Type    : MySQL
 Target Server Version : 80031 (8.0.31)
 File Encoding         : 65001

 Date: 07/12/2022 01:31:53
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL COMMENT '账号',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` (`id`, `username`, `password`) VALUES (1, 'root', 'root');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

五、调试接口

  之前用一直用postman调试，结果某一天发现postman太占用cpu资源了，直接占了100%风扇呼呼响，还是用idea自带的调试api功能。

1.创建.http文件

新建http.http文件复制粘贴即可。

# 查询用户
###
GET http://localhost:8080/user/query?id=1

# 删除用户
###
DELETE http://localhost:8080/user/delete?id=1

# 添加用户
###
POST http://localhost:8080/user/add?username=temp&password=temp

# 修改用户
###
POST http://localhost:8080/user/update?username=temp&password=password

2. 打开Endpoints工具

也可以点击View->Tool Windows->Endpoints

打开之后在下栏功能条就能看到了。

六、实现效果

1. 查询用户接口

2. 添加用户接口

3. 修改用户接口

4. 删除用户接口

七、总结

  循序渐进，不骄不躁，方得始终，人生苦短，学自己想学的，开心就好。微信公众搜索艺说IT，对你有帮助的话请给个一键三连感谢，关注我后续筹划写一个前后端分离的博客网站项目，先来个vue.js+SpringBoot，尽量采用全面的技术栈，快速领略技术用法，一起学习共同成长。