MyBatis-Plus 框架简单使用总结

最新推荐文章于 2023-09-12 17:57:06 发布

一线大码

最新推荐文章于 2023-09-12 17:57:06 发布

阅读量408

点赞数

分类专栏： MyBatis 文章标签： mybatis mybatisplus

本文链接：https://blog.csdn.net/wb1046329430/article/details/116455573

版权

MyBatis 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

文章目录

1. 基本使用

1、创建 Maven 工程
在这里插入图片描述 2、pom.xml 引入 MyBatis Plus 的依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.2</version>
</dependency>

3、创建实体类

package com.example.mybatisplus.entity;

import lombok.Data;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
}

4、创建 Mapper 接口

package com.example.mybatisplus.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.southwind.mybatisplus.entity.User;

public interface UserMapper extends BaseMapper<User> {
}

5、application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456789
#SQL语句输出，不设置这个，直接用下边的方法，设置对应包的日志级别也行
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#打印SQL语句，trace级别能把SQL执行结果打印出来，设置mapper包为trace级别
logging.level.com.example.demo.generator.mapper=trace

6、启动类需要添加 @MapperScan(“mapper 所在的包”)，否则无法加载 Mppaer bean。

package com.example.mybatisplus;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan(basePackages = {"com.example.mybatisplus.mapper"})
public class MybatisplusApplication {
    public static void main(String[] args) {
        SpringApplication.run(MybatisplusApplication.class, args);
    }
}

7、测试

package com.example.mybatisplus.mapper;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper mapper;

    @Test
    void test(){
        mapper.selectList(null).forEach(System.out::println);
    }

}

2. 常用注解

2.1. @TableName

mp 会自动将实体驼峰命名对应为数据库下划线命名，如果命名没有对应上，需要用该注解进行指定表名。

value属性映射数据库的表名。

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName(value = "user")
public class Account {
    private Long id;
    private String name;
    private Integer age;
}

2.2. @TableId

设置主键映射。mp 用来指定主键 id，默认会直接将 id 字段作为主键，如果主键不是 id，需要使用该注解进行指定。

value属性映射主键字段名。当主键名和实体类主键字段名不一致的时候使用。

type属性设置主键类型，即主键的生成策略，默认为IdType.NONE。

AUTO：数据库主键自增，开发者无需赋值。该类型请确保数据库设置了 ID自增，否则无效。主键值会回填到参数对象的主键字段中。如果开发者手动赋值了，赋值无效，依然是数据库自增。
NONE：type 的默认类型，如果开发者没有手动赋值，则数据库通过雪花算法算出一个 Long 值给主键赋值，主键值会回填到参数对象的主键字段中。如果开发者手动赋值，则存入该值。
INPUT：如果开发者没有手动赋值，则数据库通过自增的方式给主键赋值（该功能需要在数据库表的主键开启自动自增属性，否则会报 id 不能为 null 的错误了），主键值不会回填到参数对象的主键字段中。如果开发者手动赋值，则存入该值。
ASSIGN_ID：mp 自动赋值，雪花算法。主键类型可为数值和字符串。如果开发者没有手动赋值，则通过雪花算法的方式给主键赋值，主键值会回填到参数对象的主键字段中。如果开发者手动赋值，则存入该值。
ASSIGN_UUID：主键的数据类型必须是 String，自动生成 UUID 进行赋值，主键值会回填到参数对象的主键字段中。如果开发者手动赋值，则存入该值。

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

@Data
public class User {
	@TableId(value = "id", type = IdType.AUTO)
    private Long num;
    private String name;
    private Integer age;
}

2.3. @TableField

映射非主键字段。

value属性映射字段名。

exist属性表示是否为数据库字段，默认 true 是，false 不是。如果实体类中的成员变量在数据库中没有对应的字段，则可以使用该属性。主要用于 VO、DTO。

select属性表示是否查询该字段。大字段可设置为 false 不加入 select 查询范围，逻辑删除字段 deleted 也可以使用该属性。

fill属性表示是否自动填充，将对象存入数据库的时候，由 MyBatis-Plus 自动给某些字段赋值。常用于 create_time、update_time 赋值。也用于新增人、修改人字段赋值。

1、给表添加 create_time、update_time 字段

2、实体类中添加成员变量

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    @TableField(value = "name",select = false)
    private String title;
    private Integer age;
    @TableField(exist = false)
    private String gender;
    @TableField(fill = FieldFill.INSERT)
    private Date createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;
}

3、创建自动填充处理器

package com.example.mybatisplus.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.util.Date;

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        //判断表中是否有对应字段，没有对应字段的时候，不去执行自动填充，提高性能
        boolean hasCreateTimeSetter = metaObject.hasSetter("createTime");
        if (hasCreateTimeSetter){
            this.setFieldValByName("createTime", new Date(), metaObject);
        }
        boolean hasUpdateTimeSetter = metaObject.hasSetter("updateTime");
        if (hasUpdateTimeSetter){
            this.setFieldValByName("updateTime", new Date(), metaObject);
        }
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        //判断表中是否有对应字段，没有对应字段的时候，不去执行自动填充，提高性能
        boolean hasUpdateTimeSetter = metaObject.hasSetter("updateTime");
        if (hasUpdateTimeSetter) {
            //判断该字段是否已经被手动赋值，手动赋值的将不会被自动填充，否则会被覆盖的
            Object val = this.getFieldValByName("updateTime", metaObject);
            if (val == null){
                this.setFieldValByName("updateTime", new Date(), metaObject);
            }
        }
    }
}

2.4. @Version

标记乐观锁，通过version字段来保证数据的安全性，当修改数据的时候，会以version作为条件，当条件成立的时候才会修改成功。用于并发update操作。每次updaate语句都会在条件中拼接version字段。

version = 1，线程1和线程2，并发执行，只有一个能成功。
线程 1：update … set version = 2 where version = 1
线程 2：update … set version = 2 where version = 1

1、数据库表添加 version 字段，默认值为 1

2、实体类添加 version 成员变量，并且添加 @Version 注解。

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.Version;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    @Version
    private Integer version;
}

3、注册配置类

package com.example.mybatisplus.config;

import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisPlusConfig {
    //老版本
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor(){
        return new OptimisticLockerInterceptor();
    }
    //新版本（老版本提示已删除的则写这个）
	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor() {
	    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
	    interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
	    return interceptor;
	}
    
}

2.5. @EnumValue

1、通用枚举类注解，将数据库字段映射成实体类的枚举类型成员变量。

package com.example.mybatisplus.enums;

import com.baomidou.mybatisplus.annotation.EnumValue;

public enum StatusEnum {
    WORK(1,"上班"),
    REST(0,"休息");

    StatusEnum(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    @EnumValue
    private Integer code;
    private String msg;
}

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.Version;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    
    private StatusEnum status;
}

application.yml 配置枚举包扫描，枚举值和枚举自动转换。

mybatis-plus
  type-enums-package: 
  	com.example.mybatisplus.enums

2、还有一种方法是通过实现接口的方式

package com.example.mybatisplus.enums;

import com.baomidou.mybatisplus.annotation.IEnum;

public enum StatusEnum implements IEnum<Integer> {
    WORK(1,"上班"),
    REST(0,"休息");

    StatusEnum(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    private Integer code;
    private String msg;
	//这里配置的是转换的值，也就是时候数据库存的是code值，查询出来会被自动转换为枚举 WORK 或者 REST
    @Override
    public Integer getValue() {
        return this.code;
    }
}

2.6. @TableLogic

映射逻辑删除。

逻辑删除，则 delete 语句会变为 update 语句，会将 deleted 字段置为已删除的值，比如1。
查询语句和更新语句会自动加过滤条件，比如 deleted=0（0表示未删除，1表示删除）。
自定义 SQL 语句不会添加逻辑删除标志。需要自己添加这个过滤条件 deleted=0。

1、数据表添加 deleted 字段。

2、实体类添加注解和字段。

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.TableLogic;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    private String title;
    private Integer age;
    @TableLogic
    private Integer deleted;
}

3、application.yml 添加配置。配置0表示未删除，1表示已删除。

mybatis-plus
  global-config:
    db-config:
      logic-not-delete-value: 0
      logic-delete-value: 1

3. CRUD

3.1. 查询

3.1.1. selectList

3.1.1.1. 单条件查询示例

//SELECT * FROM user;
List<User> userList = userMapper.selectList(null);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name = '张三')
wrapper.eq("name", "张三");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (status = 1)
wrapper.eq("status", StatusEnum.WORK);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
Map<String, Object> map = new HashMap<>();
map.put("name", "张三");
map.put("age", 20);
//SELECT * FROM user WHERE (name = '张三' AND age = 20)
wrapper.allEq(map);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age < 20)
wrapper.lt("age", 20);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age > 20)
wrapper.gt("age", 20);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age <= 20)
wrapper.le("age", 20);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age >= 20)
wrapper.ge("age", 20);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age <> 20)
wrapper.ne("age", 20);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name LIKE '%马%')
wrapper.like("name", "马");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name LIKE '张%')
wrapper.likeRight("name", "张");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name LIKE '%三')
wrapper.likeLeft("name", "三");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (
// id IN (select id from user where id < 10) AND
// age IN (select age from user where age > 3)
// )
wrapper.inSql("id", "select id from user where id < 10");
wrapper.inSql("age", "select age from user where age > 3");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user ORDER BY age DESC
wrapper.orderByDesc("age");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user ORDER BY age ASC
wrapper.orderByAsc("age");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user HAVING id > 5 ORDER BY age ASC
wrapper.orderByAsc("age");
wrapper.having("id > 5" );
List<User> userList = userMapper.selectList(wrapper);

3.1.1.2. 多条件查询示例

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//QueryWrapper<User> queryWrapper1 = Wrappers.query();
queryWrapper.like("name", "雨").lt("age", 40);
//WHERE (name LIKE '%雨%' AND age < 40?)
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
//WHERE (name LIKE '%雨%' AND age BETWEEN 20 AND 40 AND email IS NOT NULL)
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").or().ge("age", 25)
        .orderByDesc("age").orderByAsc("id");
//WHERE (name LIKE '王%' OR age >= 25) ORDER BY age DESC,id ASC
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2020-11-03")
       .inSql("manager_id", "select id from user where name like '王%'");
//WHERE (date_format(create_time,'%Y-%m-%d') = '2020-11-03' AND manager_id IN (select id from user where name like '王%'))
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("create_time", "2020-11-03 14:42:22").le("create_time", "2020-11-13 14:42:22");
//WHERE (create_time >= ? AND create_time <= ?)
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").and(
        wq -> wq.lt("age", 40).or().isNotNull("email")
);
//WHERE (name LIKE '王%' AND (age < 40 OR email IS NOT NULL))
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").or(
        wq -> wq.lt("age", 40).isNotNull("email")
);
//WHERE (name LIKE '王%' OR (age < 40 AND email IS NOT NULL))
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(wq -> wq.lt("age", 40).or().isNotNull("email")).likeRight("name", "王");
//WHERE ((age < 40 OR email IS NOT NULL) AND name LIKE '王%')
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", Arrays.asList(30,31,32,33)).last("limit 1");
//WHERE (age IN (30,31,32,33)) limit 1
List<User> userList = userMapper.selectList(queryWrapper);

3.1.1.3. 查询结果列的控制

只查询某几列

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);
//SELECT id,name FROM user WHERE (name LIKE '%雨%' AND age < 40?)
List<User> userList = userMapper.selectList(queryWrapper);

不查询某几列

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select(User.class,
        info-> !info.getColumn().equals("create_time") && !info.getColumn().equals("manager_id")
).like("name", "雨").lt("age", 40);
//SELECT id,name,age,email FROM user WHERE (name LIKE '%雨%' AND age < 40?)
List<User> userList = userMapper.selectList(queryWrapper);

3.1.1.4. 条件作用，非空判断

@Test
public void conditionTest(){
    String name = "王";
    String email = "";
    condition(name, email);
}
public void condition(String name, String email){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like(StringUtils.isNotBlank(name),"name", name)
            .eq(StringUtils.isNotBlank(email),"email", email);
    //WHERE (name LIKE ?)
    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

3.1.1.5. allEq

Map<String, Object> params = new HashMap<>();
params.put("name", "王天风");
params.put("age", 25);
params.put("email", null);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.allEq(params);
//FROM user WHERE (name = '王天风' AND age = 25 AND email IS NULL)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);

QueryWrapper<User> queryWrapper1 = new QueryWrapper<>();
queryWrapper1.allEq(params, false);
//WHERE (name = '王天风' AND age = ?)
List<User> userList1 = userMapper.selectList(queryWrapper1);
userList1.forEach(System.out::println);

//将name字段过滤掉
QueryWrapper<User> queryWrapper2 = new QueryWrapper<>();
queryWrapper2.allEq((k,v)->!k.equals("name"), params);
//WHERE (age = 25 AND email IS NULL)
List<User> userList2 = userMapper.selectList(queryWrapper2);
userList2.forEach(System.out::println);

3.1.2. selectById

//SELECT * FROM user WHERE id=7
User user = userMapper.selectById(7);

3.1.3. selectBatchIds

 //SELECT * FROM user WHERE id IN ( 7 , 8 , 9 )
List<User> userList = userMapper.selectBatchIds(Arrays.asList(7,8,9));

3.1.4. selectByMap

Map<String, Object> map = new HashMap<>();
map.put("name", "张三");
map.put("age", 20);
//只能做等值判断，和allEq用法类似
//SELECT * FROM user WHERE name = '张三' AND age = 20
List<User> userList = userMapper.selectByMap(map);

3.1.5. selectCount

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT COUNT( * ) FROM user WHERE (id > ?)
wrapper.gt("id", 1);
Integer count = userMapper.selectCount(wrapper);

3.1.6. selectMaps

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (id > ?)
wrapper.gt("id", 1);
//返回的是map，不是user对象
List<Map<String, Object>> mapList = userMapper.selectMaps(wrapper);

只查询某几列，返回 map，不会出现没查询的字段为 null

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);
//SELECT id,name FROM user WHERE (name LIKE '%雨%' AND age < 40?)
List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
userList.forEach(System.out::println);

分组查询统计，使用 map

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("manager_id", "avg(age) avg_age", "min(age) min_age", "max(age) max_age")
        .groupBy("manager_id").having("sum(age)<{0}", 500);
//SELECT manager_id,avg(age) avg_age,min(age) min_age,max(age) max_age FROM user GROUP BY manager_id HAVING sum(age)<500
List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
userList.forEach(System.out::println);

3.1.7. selectPage（分页查询）

分页查询都需要先配置 PaginationInterceptor 。

package com.example.mybatisplus.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisPlusConfig {
    /**
     * 分页查询
     */
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
}

新版本配置：

package com.example.demo.common.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @author wangbo
 * @date 2021/5/12
 */
@Configuration
public class MyBatisPlusConfig {

    /**
     * 分页插件配置
     * @return MybatisPlusInterceptor
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

然后直接使用 Page 对象进行分页查询。

//SELECT * FROM user LIMIT 3,3
Page<User> page = new Page<>(2,3);
Page<User> result = userMapper.selectPage(page, null);
//每页大小
System.out.println(result.getSize());
//总记录数
System.out.println(result.getTotal());
//记录
result.getRecords().forEach(System.out::println);

详细版本写法：

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 26);

Page<User> page = new Page<>(3, 2);
IPage<User> iPage = userMapper.selectPage(page, queryWrapper);
//SELECT COUNT(1) FROM user WHERE (age >= 26)
//SELECT * FROM user WHERE (age >= 26) LIMIT 4,2
System.out.println("总页数："+ iPage.getPages());
System.out.println("总记录数：" + iPage.getTotal());
List<User> userList = iPage.getRecords();
userList.forEach(System.out::println);

不查总记录数，可少执行一条SQL

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 26);

Page<User> page2 = new Page<>(3, 2, false);
IPage<User> iPage2 = userMapper.selectPage(page2, queryWrapper);
//SELECT * FROM user WHERE (age >= 26) LIMIT 4,2
System.out.println("总页数："+ iPage2.getPages());
System.out.println("总记录数：" + iPage2.getTotal());
List<User> userList2 = iPage2.getRecords();
userList2.forEach(System.out::println);

3.1.8. selectMapsPage（分页查询）

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 26);

Page<Map<String, Object>> page1 = new Page<>(3, 2);
IPage<Map<String, Object>> iPage1 = userMapper.selectMapsPage(page1, queryWrapper);
//SELECT COUNT(1) FROM user WHERE (age >= 26)
//SELECT * FROM user WHERE (age >= 26) LIMIT 4,2
System.out.println("总页数1："+ iPage.getPages());
System.out.println("总记录数1：" + iPage.getTotal());
List<Map<String, Object>> userList1 = iPage1.getRecords();
userList1.forEach(System.out::println);

3.1.9. selectObjs

只返回了主键

//SELECT * FROM user
List<Object> list = userMapper.selectObjs(null);

只返回第一列

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("manager_id", "avg(age) avg_age", "min(age) min_age", "max(age) max_age")
        .groupBy("manager_id").having("sum(age)<{0}", 500);
//SELECT manager_id,avg(age) avg_age,min(age) min_age,max(age) max_age FROM user GROUP BY manager_id HAVING sum(age)<500
List<Object> userList = userMapper.selectObjs(queryWrapper);
userList.forEach(System.out::println);

3.1.10. selectOne

查询一条记录数，可以为null，注意不能为多条

//SELECT * FROM user WHERE (id = 1)
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("id", 1);
//结果集必须是一条记录
User user = userMapper.selectOne(wrapper);
System.out.println(user);

3.2. 添加

3.2.1. insert

User user = new User();
user.setName("小明");
user.setAge(22);
int i = userMapper.insert(user);
System.out.println(i);
System.out.println(user);

3.2.2. insertOrUpdate

无ID 就是插入，有 ID 会先执行查询，有则更新，无则插入。

User user = new User();
user.setId(3L);
user.setAge(32);
user.setName("史蒂夫");
boolean result = user.insertOrUpdate();
System.out.println("插入是否成功：" + result);
//SELECT * FROM user WHERE id=3
//INSERT INTO user ( name, age) VALUES ( '史蒂夫', 32 )
//自动插入的记录ID按照自增原则，生成的是8，并不是3

3.3. 删除

3.3.1. deleteById

//DELETE FROM user WHERE id=1
userMapper.deleteById(1);

3.3.2. deleteBatchIds

//DELETE FROM user WHERE id IN ( 1 , 2, 3 )
userMapper.deleteBatchIds(Arrays.asList(1,2,3));

3.3.3. delete

QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("age", 14);
//DELETE FROM user WHERE (age = 14)
userMapper.delete(wrapper);

3.3.4. deleteByMap

Map<String, Object> map = new HashMap<>();
map.put("name", "张三");
//DELETE FROM user WHERE name = '张三'
userMapper.deleteByMap(map);

3.4. 修改

3.4.1. updateById

User user = userMapper.selectById(6);
user.setName("内马尔休闲鞋");
//UPDATE user SET age=22, name='内马尔休闲鞋' WHERE id=6
userMapper.updateById(user);

3.4.2. update

更新大量字段使用实体

User user = new User();
user.setAge(30);
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("name", "张三");
//UPDATE user SET age=30 WHERE (name = '张三')
userMapper.update(user, wrapper);

User whereUser = new User();
whereUser.setEmail("lyw2@baomidou");

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>(whereUser);
updateWrapper.eq("name", "李艺伟").eq("age", 30);

User user = new User();
user.setAge(32);
user.setEmail("lyw3@baomidou");

int rows = userMapper.update(user, updateWrapper);
//UPDATE user SET age=?, email=? WHERE email=? AND (name = ? AND age = ?)
System.out.println("修改记录数：" + rows);

更新少量字段，不用使用实体，直接使用 set

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("name", "李艺伟").eq("age", 32).set("age", 50);

int rows = userMapper.update(null, updateWrapper);
//UPDATE user SET age=? WHERE (name = ? AND age = ?)
System.out.println("修改记录数：" + rows);

Lambda 可防止误写字段

LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
updateWrapper.eq(User::getName, "李艺伟").eq(User::getAge, 50).set(User::getAge, 51);

int rows = userMapper.update(null, updateWrapper);
//UPDATE user SET age=? WHERE (name = ? AND age = ?)
System.out.println("修改记录数：" + rows);

Lambda可防止误写字段，链式调用

boolean update = new LambdaUpdateChainWrapper<User>(userMapper)
	.eq(User::getName, "李艺伟").eq(User::getAge, 50)
	.set(User::getAge, 51).update();
//UPDATE user SET age=? WHERE (name = ? AND age = ?)
System.out.println("是否修改成功：" + update);

4. Lambda 条件构造器

Lambda 条件构造器，防止写错列名。

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
//LambdaQueryWrapper<User> lambdaQueryWrapper1 = new QueryWrapper<User>().lambda();
//LambdaQueryWrapper<User> lambdaQueryWrapper2 = Wrappers.lambdaQuery();
lambdaQueryWrapper.like(User::getName, "雨").lt(User::getAge, 40);
//WHERE (name LIKE '%雨%' AND age < 40)
List<User> userList = userMapper.selectList(lambdaQueryWrapper);
userList.forEach(System.out::println);

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.likeRight(User::getName, "王").and(
        lwq -> lwq.lt(User::getAge, 40).or().isNotNull(User::getEmail)
);
//WHERE (name LIKE '王' AND (age < 40 OR email IS NOT NULL))
List<User> userList = userMapper.selectList(lambdaQueryWrapper);
userList.forEach(System.out::println);

链式调用

//WHERE (name LIKE '%雨%' AND age >= 20)
List<User> userList = new LambdaQueryChainWrapper<>(userMapper)
	.like(User::getName, "雨").ge(User::getAge, 20).list();

//SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name = ?)
User user = new LambdaQueryChainWrapper<>(userMapper).eq(User::getName, "张三").one();

5. 自定义方法 SQL

5.1. 简单自定义

package com.example.mybatisplus.entity;

import lombok.Data;

@Data
public class ProductVO {
    private Integer category;
    private Integer count;
    private String description;
    private Integer userId;
    private String userName;
}

package com.example.mybatisplus.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.southwind.mybatisplus.entity.ProductVO;
import com.southwind.mybatisplus.entity.User;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper extends BaseMapper<User> {
    @Select("select p.*, u.name as userName from product p, user u where p.user_id = u.id and u.id = #{id}")
    List<ProductVO> productList(Integer id);
}

List<ProductVO> productVOList = userMapper.productList(7);
productVOList.forEach(System.out::println);

5.2. 复杂自定义

public interface UserMapper extends BaseMapper<User> {
    //自定义方法，可以直接使用注解，也可以xml里配置，都是可以实现的
    //@Select("select * from user ${ew.customSqlSegment}")
    List<User> selectCustom(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
}

xml 中配置 SQL：

<select id="selectCustom" resultType="com.example.demo.entity.User">
    select * from user ${ew.customSqlSegment}
</select>

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.likeRight(User::getName, "王").and(
        lwq -> lwq.lt(User::getAge, 40).or().isNotNull(User::getEmail)
);
//select * from user WHERE (name LIKE '王%' AND (age < 40 OR email IS NOT NULL))
List<User> userList = userMapper.selectCustom(lambdaQueryWrapper);
userList.forEach(System.out::println);

5.3. 自定义分页

public interface UserMapper extends BaseMapper<User> {
    //自定义分页
    IPage<User> selectUserPage(Page<User> page, @Param(Constants.WRAPPER) Wrapper<User> wrapper);

    //自定义分页
    IPage<User> selectUserPage1(Page<User> page, @Param("user") User user);
}

xml 中配置 SQL：

<select id="selectUserPage" resultType="com.example.demo.entity.User">
    select * FROM user ${ew.customSqlSegment}
</select>

<select id="selectUserPage1" resultType="com.example.demo.entity.User">
    select * FROM user
    <where>
        <if test="user.id != null">
            and id &gt; #{user.id}
        </if>
        <if test="user.name != null">
            and name = #{user.name}
        </if>
    </where>
</select>

@Test
public void selectPageCustom() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.ge("age", 26);

    Page<User> page = new Page<>(1, 2);
    IPage<User> iPage = userMapper.selectUserPage(page, queryWrapper);
    //SELECT COUNT(1) FROM user WHERE (age >= 26)
    //select * FROM user WHERE (age >= 26) LIMIT 2
    System.out.println("总页数："+ iPage.getPages());
    System.out.println("总记录数：" + iPage.getTotal());
    List<User> userList = iPage.getRecords();
    userList.forEach(System.out::println);
}

@Test
public void selectPageCustom1() {
    User user = new User();
    user.setId(2L);
    //user.setName("王伯当");

    Page<User> page = new Page<>(3, 2);
    IPage<User> iPage = userMapper.selectUserPage1(page, user);
    //SELECT COUNT(1) FROM user WHERE (age >= 26)
    //select * FROM user WHERE (age >= 26) LIMIT 2
    System.out.println("总页数："+ iPage.getPages());
    System.out.println("总记录数：" + iPage.getTotal());
    List<User> userList = iPage.getRecords();
    userList.forEach(System.out::println);
}

6. service 方法调用

和直接调用 mapper 的方法有略微的差别。

package com.example.demo.mp;

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Arrays;
import java.util.List;

/**
 * @auther wangbo
 * @date 2020-11-03 16:50
 */
@SpringBootTest
public class ServiceTest {
    @Autowired
    private UserService userService;

	//只查询一个
    @Test
    public void select(){
        User one = userService.getOne(Wrappers.<User>lambdaQuery().gt(User::getAge, 25),false);
        //SELECT * FROM user WHERE age > 25
        System.out.println(one);
    }

	//批量插入
    @Test
    public void batch(){
        User user1 = new User();
        user1.setName("张三1");
        user1.setAge(28);

        User user2 = new User();
        user2.setName("张三2");
        user2.setAge(29);

        List<User> list = Arrays.asList(user1, user2);
        boolean savebatch = userService.saveBatch(list);
        System.out.println("批量插入是否成功："+ savebatch);
    }


    //链式操作
    //查询
    @Test
    public void chain(){
        List<User> list = userService.lambdaQuery().gt(User::getAge, 25).like(User::getName, "雨").list();
        list.forEach(System.out::println);
    }
    
    //更新
    @Test
    public void chain1(){
        boolean update = userService.lambdaUpdate().gt(User::getAge, 25).like(User::getName, "雨").set(User::getAge, 20).update();
        System.out.println("更新是否成功："+ update);
        //UPDATE user SET age=? WHERE (age > ? AND name LIKE ?)
    }
    //删除
    @Test
    public void chain2(){
        boolean update = userService.lambdaUpdate().gt(User::getAge, 25).like(User::getName, "雨").remove();
        System.out.println("删除是否成功："+ update);
        //DELETE FROM user WHERE (age > ? AND name LIKE ?)
    }
}

7. MyBatis-Plus 自动生成

根据数据库表自动生成实体类、Mapper、Service、ServiceImpl、Controller。

1、pom.xml 导入 MyBatis Plus Generator 依赖和模板依赖。

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.4.1</version>
</dependency>
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity-engine-core</artifactId>
    <version>2.3</version>
</dependency>

三种模板：Velocity（默认）、Freemarker、Beetl。

2、启动类，配置相关项后直接执行 main 方法即可生成对应的文件。

package com.example.mybatisplus;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.GlobalConfig;
import com.baomidou.mybatisplus.generator.config.PackageConfig;
import com.baomidou.mybatisplus.generator.config.StrategyConfig;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;

public class Main {
    public static void main(String[] args) {
        //创建 Generator对象
        AutoGenerator autoGenerator = new AutoGenerator();
        //数据源
        DataSourceConfig dataSourceConfig = new DataSourceConfig();
        dataSourceConfig.setDbType(DbType.MYSQL);
        dataSourceConfig.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8");
        dataSourceConfig.setUsername("root");
        dataSourceConfig.setPassword("123456789");
        dataSourceConfig.setDriverName("com.mysql.cj.jdbc.Driver");
        autoGenerator.setDataSource(dataSourceConfig);
        //全局配置
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setOutputDir(System.getProperty("user.dir")+"/src/main/java");
        globalConfig.setOpen(false);
        globalConfig.setAuthor("wangbo");//设置作者
        globalConfig.setServiceName("%sService");//指定Service名字，不指定前面会有I
        autoGenerator.setGlobalConfig(globalConfig);
        //包信息
        PackageConfig packageConfig = new PackageConfig();
        packageConfig.setParent("com.example.mybatisplus");
        packageConfig.setModuleName("generator");//生成的文件所在的包目录，直接在父包com.example.mybatisplus下就不用写了
        packageConfig.setController("controller");
        packageConfig.setService("service");
        packageConfig.setServiceImpl("service.impl");
        packageConfig.setMapper("mapper");
        packageConfig.setEntity("entity");
        autoGenerator.setPackageInfo(packageConfig);
        //配置策略
        StrategyConfig strategyConfig = new StrategyConfig();
        strategyConfig.setInclude("product");//这里配置需要生成的目标表名，不配置则默认生成全部。
        strategyConfig.setEntityLombokModel(true);
        strategyConfig.setRestControllerStyle(true);
        strategyConfig.setNaming(NamingStrategy.underline_to_camel);//下划线转驼峰
        strategyConfig.setColumnNaming(NamingStrategy.underline_to_camel);//下划线转驼峰
        autoGenerator.setStrategy(strategyConfig);

        autoGenerator.execute();
    }
}

8. 其他配置

# 配置mybatis-plus自定义SQL的xml文件位置
mybatis-plus.mapper-locations=classpath:com/example/demo/mapper/*.xml
# 配置mybatis-plus的全局主键策略，这里设置为数据库自增。
# 实体类上的局部配置优先级高于这里的全局配置。
mybatis-plus.global-config.db-config.id-type=auto
# 在自定义SQL的xml文件中resultType直接写实体的简写，可省略包名
mybatis-plus.type-aliases-package=com.example.demo.entity

9. 注意事项

在 XML 中使用自定义 SQL，注意要在配置文件中配置 xml 文件的位置：

mybatis-plus:
  mapper-locations: classpath:com/hny/pspp/**/mapper/xml/*.xml

否则在调用自定义SQL的时候会出现以下错误：

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.hny.pspp.album.mapper.CloudAlbumMapper.pageUserIdList

这样配置的话正常启动项目调用自定义SQL没有问题了，但是在使用单元测试调用自定义SQL的时候还是会出现上述异常：

package com.hny.pspp.album.scheduled;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ClearTaskTest {

    @Autowired
    private ClearTask clearTask;
    
    @Test
    void clear() {
        clearTask.clear();
    }
}

实测可以通过补全locations路径解决问题：

mybatis-plus:
  mapper-locations: classpath:com/hny/pspp/album/mapper/xml/*.xml

一线大码

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
MyBatis-Plus 框架简单使用总结

文章目录1. 基本使用2. 常用注解2.1. @TableName2.2. @TableId2.3. @TableField2.4. @Version2.5. @EnumValue2.6. @TableLogic3. CRUD3.1. 查询自定义 SQL（多表关联查询）添加删除修改MyBatisPlus 自动生成Spring Boot + MyBatis Plus 打包应用，直接发布阿里云上云1. 基本使用1、创建 Maven 工程2、pom.xml 引入 MyBatis Plus 的依赖&
复制链接

扫一扫