mybatis-plus实现增删改查（新手理解版）

目标：API（增删改查）常用记住、restful风格增删查改查、再学习一些细节

视频学习链接：黑马mybatis-plus

只是跟到了11，我是springboot3实现的，
git链接

1、新建数据库用于mybatis-plus

输入所给的sql语句即可建立表

2、IDEA引入依赖

脚手架使用的springboot3.3.12 +依赖 myql驱动,手动加入mybatisplus 和 lombok依赖

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.5</version><!--这是一个坑，3。5.5 支持springboot3-->
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.30</version><!--这个版本号也得注意-->
        </dependency>

3、连接好数据库，利用mybatisX生成器生成对应的代码

4、配置yml

spring:
  application:
    name: demo-02-mybatis-plus


  # 数据源配置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatis_plus
    username: root
    password: xxxx
    type: com.zaxxer.hikari.HikariDataSource

mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package:
  configuration:
    # 必须开启驼峰，查的时候会用到
    map-underscore-to-camel-case: true
    cache-enabled: false #是否开启二级缓存默认不开启

5、理解Wrapper(条件构造器)

调用baseMapper.XXX(Wrapper对象)

Wrapper对象细致分类：

AbstractWrapper抽象类方法 where条件

QueryWrapper 扩展了AbstractWrappe功能，可以指定查找哪些字段

5.1 userMapper对QueryWrapper的使用方法

5.2 userMapper对UpdateWrapper的使用方法

5.3 userMapper对LambdaQueryWrapper的使用方法

非常推荐这种方式，没有写死

6、理解Iservice

复杂条件的查询和更新，要用lamadaQuery和lamdaUpdate

也有增删改查，不过它比userMapper优点支持批量化操作,更加丰富，常规就够用了

在serviceImpl类中直接用

7、实战CRUD

7.1 理解VO查询返回前端的结果，DTO是前端传给控制器的，pojo是传给数据库的

一个小细节：自己加的第三方库，未在父项目或 dependencyManagement 中定义版本，要加版本号

7.2 API测试工具注意是springboot3,引入以下依赖，并不需要在yml配置

<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.22</version> <!-- 或者使用最新版本 -->
        </dependency>
<dependency>
    <groupId>org.springdoc</groupId>
    <artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
    <version>2.3.0</version> <!-- 或查看最新版 -->
</dependency>

在controller中，写出相应的注解@Tag、@Operation、@Parameter，通过url: 这里改成自己的端口号
http://localhost:8080/swagger-ui.html
http://localhost:8080/swagger-ui/index.html

7.3一个经典版本问题的报错

Error starting ApplicationContext. To display the condition evaluation report re-run your application with ‘debug’ enabled.
2025-05-28T21:45:01.126+08:00 ERROR 26064 — [ main] o.s.boot.SpringApplication : Application run failed

org.springframework.beans.factory.BeanDefinitionStoreException: Invalid bean definition with name ‘addressMapper’ defined in file [D:\IDEA\workspace\mybatis-plus\demo-02-mybatis-plus\target\classes\com\zhenbang\demo02mybatisplus\mapper\AddressMapper.class]: Invalid value type for attribute ‘factoryBeanObjectType’: java.lang.String
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getTypeForFactoryBean(AbstractAutowireCapableBeanFactory.java:857) ~[spring-beans-6.1.20.jar:6.1.20]
at org.springframework.beans.factory.support.AbstractBeanFactory.getType(AbstractBeanFactory.java:743) ~[spring-beans-6.1.20.jar:6.1.20]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAnnotationOnBean(DefaultListableBeanFactory.java:735) ~[spring-beans-6.1.20.jar:6.1.20]
at org.springframework.boot.sql.init.dependency.AnnotationDependsOnDatabaseInitializationDetector.detect(AnnotationDependsOnDatabaseInitializationDetector.java:36) ~[spring-boot-3.3.12.jar:3.3.12]

注意哈，咱的mybatis-plus-boot后面有个3，因为咱用的springboot3

    <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
            <version>3.5.7</version>
        </dependency>

7.4 前端传过来是一个对象，其中这个对象一个属性也是对象（json传过来的），要存到数据库中，当然，这个数据库中字段也是json类型。

报错如下：

2025-05-29T12:24:36.335+08:00 ERROR 17720 — [demo-02-mybatis-plus] [nio-8081-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.DataIntegrityViolationException:
Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET ‘binary’.
The error may exist in com/zhenbang/demo02mybatisplus/mapper/UserMapper.java (best guess)
The error may involve com.zhenbang.demo02mybatisplus.mapper.UserMapper.insert-Inline
The error occurred while setting parameters
SQL: INSERT INTO user ( id, username, password, phone, info, balance ) VALUES ( ?, ?, ?, ?, ?, ? )
Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET ‘binary’.
; Data truncation: Cannot create a JSON value from a string with CHARACTER SET ‘binary’.] with root cause

com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot create a JSON value from a string with CHARACTER SET ‘binary’.
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:912) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:48) ~[mybatis-3.5.16.jar:3.5.16]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:75) ~[mybatis-3.5.16.jar:3.5.16]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.16.jar:3.5.16]
解决办法：把对象转换成json即可

@TableField(value = "info",typeHandler = JacksonTypeHandler.class)
    private Object info;

7.5 controller中所有基本功能的实现

package com.zhenbang.demo02mybatisplus.controller;



import cn.hutool.core.bean.BeanUtil;


import cn.hutool.json.JSONUtil;
import com.zhenbang.demo02mybatisplus.domain.dto.UserFormDTO;
import com.zhenbang.demo02mybatisplus.domain.po.User;
import com.zhenbang.demo02mybatisplus.domain.vo.UserVO;
import com.zhenbang.demo02mybatisplus.service.UserService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.annotation.Resource;

import org.springframework.web.bind.annotation.*;

import java.util.List;

@Tag(name = "用户管理接口", description = "用户的增删改查相关接口")
@RequestMapping("/users")
@RestController

public class UserController {

    @Resource
    private  UserService userService;

    @Operation(summary = "新增用户接口")
    @PostMapping
    public void saveUser(@RequestBody UserFormDTO userDTO){
        System.out.println(userDTO);
        // 前端传过来对象，其中一个属性字段为json,自动转化为对象吗
        User user = BeanUtil.copyProperties(userDTO, User.class);
        userService.save(user);
    }

    @Operation(summary = "删除用户接口")
    @DeleteMapping("/{id}")
    public void deleteUserById(
            @Parameter(description = "用户id") @PathVariable("id") Long id){
        userService.removeById(id);
    }

    @Operation(summary = "根据id查询用户接口")
    @GetMapping("/{id}")
    public UserVO queryUserById(
            @Parameter(description = "用户id") @PathVariable("id") Long id){

        System.out.println(id);
        User user = userService.getById(id);
        System.out.println(user);

        UserVO userVO = BeanUtil.copyProperties(user, UserVO.class);
        System.out.println(userVO);
        return userVO;
    }

    @Operation(summary = "根据id批量查询用户接口")
    @GetMapping
    public List<UserVO> queryUserByIds(
            @Parameter(description = "用户id集合") @RequestParam("ids") List<Long> ids){

        List<User> users = userService.listByIds(ids);
        return BeanUtil.copyToList(users, UserVO.class);

    }

 @Operation(summary = "扣减用户余额接口")
    @PutMapping("/{id}/deduction/{money}")
    public void deductBalance(
            @Parameter(description = "用户id") @PathVariable("id") Long id,
            @Parameter(description = "扣减的金额") @PathVariable("money") Integer money){
        userService.deductBalance(id, money);
    }

    /*@Operation(summary = "根据复杂条件查询用户接口")
    @GetMapping("/list")
    public List<UserVO> queryUsers(UserQuery query){
        List<User> users = userService.queryUsers(
                query.getName(), query.getStatus(), query.getMinBalance(), query.getMaxBalance());
        return BeanUtil.copyToList(users, UserVO.class);
    }

    @Operation(summary = "根据条件分页查询用户接口")
    @GetMapping("/page")
    public PageDTO<UserVO> queryUsersPage(UserQuery query){
        return userService.queryUsersPage(query);
    }*/
}

7.6 以上是比较简单的直接调用，复杂条件的查询，尽量使用lamdaQuery和lamdaUpdate

如果要手写SQL，对应语句如下：

java语句

    /*复杂条件查询*/
    @Override
    public List<User> queryUsers(String name, Integer status, Integer minBalance, Integer maxBalance) {
        //使用lambdaQuery不用new Wrapper 对象

        List<User> list = lambdaQuery()
                .like(name != null, User::getUsername, name)
                .eq(status != null, User::getStatus, status)
                .ge(minBalance != null, User::getBalance, maxBalance)
                .le(maxBalance != null, User::getBalance, minBalance)
                .list();

        return list;
    }

手写SQL：

 <update id="deuctBanlance"> 
UPDATE user
SET balance = balance - #{money}
WHERE id = #{id}
</update>

java代码：

        // 方式2 lambdaUpdate
        lambdaUpdate()
                .set(User::getBalance, remainBalance)
                .set(remainBalance == 0, User::getStatus, UserStatus.FROZEN)
                .eq(User::getId, id)
                .eq(User::getBalance, user.getBalance()) // 乐观锁
                .update();

7.7 批量增加

注意啦，要在yml文件中datasource开启以下配置

    url: jdbc:mysql://localhost:3306/mybatis_plus?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true

第二种方式就是动态sql，如果不让用mybatisplus，可以考虑for-each，实现批量增加。