mybatis-plus入门_mybatis-plus wrapper.apply-CSDN博客

这篇博客介绍了mybatis-plus的基本用法，包括数据库创建、配置文件、Mapper接口、实体类等设置。详细讲解了插入、查询、更新、删除操作，以及如何使用Wrapper自定义SQL，实现分页和主键生成策略。还涵盖了AR模式和通用Service的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mybatis-plus简介

快速入门

创建数据库

#创建用户表
CREATE TABLE user (
    id BIGINT(20) PRIMARY KEY NOT NULL COMMENT '主键',
    name VARCHAR(30) DEFAULT NULL COMMENT '姓名',
    age INT(11) DEFAULT NULL COMMENT '年龄',
    email VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    manager_id BIGINT(20) DEFAULT NULL COMMENT '直属上级id',
    create_time DATETIME DEFAULT NULL COMMENT '创建时间',
    CONSTRAINT manager_fk FOREIGN KEY (manager_id)
        REFERENCES user (id)
)  ENGINE=INNODB CHARSET=UTF8;

#初始化数据：
INSERT INTO user (id, name, age, email, manager_id
	, create_time)
VALUES (1087982257332887553, '大boss', 40, 'boss@baomidou.com', NULL
		, '2019-01-11 14:20:20'),
	(1088248166370832385, '王天风', 25, 'wtf@baomidou.com', 1087982257332887553
		, '2019-02-05 11:12:22'),
	(1088250446457389058, '李艺伟', 28, 'lyw@baomidou.com', 1088248166370832385
		, '2019-02-14 08:31:16'),
	(1094590409767661570, '张雨琪', 31, 'zjq@baomidou.com', 1088248166370832385
		, '2019-01-14 09:15:15'),
	(1094592041087729666, '刘红雨', 32, 'lhm@baomidou.com', 1088248166370832385
		, '2019-01-14 09:48:16');

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.chaolong</groupId>
    <artifactId>first</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>first</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!--引入mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

domian下的User.class

package com.chaolong.first.domian;

import lombok.Data;

import java.util.Date;

/**
 * @author: Chaolong
 * @create: 2020/06/07
 **/
@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
    // 上级ID
    private Long managerId;
    private Date createTime;
}

dao下的UserMapper.class

package com.chaolong.first.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.chaolong.first.domian.User;


/**
 * @author: Chaolong
 * @create: 2020/06/07
 **/
public interface UserMapper extends BaseMapper<User> {
}

配置文件 application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
    username: root
    password: 123456

启动类（application.class）

package com.chaolong.first;

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

@SpringBootApplication
@MapperScan("com.chaolong.first.dao") // 指定扫描Mapper接口的包路径
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

}

测试 ApplicationTests.class

package com.chaolong.first;

import com.chaolong.first.dao.UserMapper;
import com.chaolong.first.domian.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class ApplicationTests {

    @Autowired
    private UserMapper userMapper;
    @Test
    void select() {
        List<User> users = userMapper.selectList(null);
        //users.forEach(System.out::println);
        users.forEach((user)->{
            System.out.println(user);
        });
    }

}

插入操作

application.yml 添加开启sql语句显示

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 开启sql 语句日志显示

insertTest.class

@SpringBootTest
class InsertTest {

    @Autowired
    private UserMapper userMapper;
    @Test
    void insert() {
        User user = new User();
        user.setName("向dong");
        user.setAge(40);
        user.setManagerId(1088248166370832385L);
        user.setCreateTime(new Date());
        userMapper.insert(user);
    }

}

@TableName @TableId @TableField

@Data
@TableName("user_2")  // 与数据库中的哪一张表进行映射
public class User {
    @TableId("id")
    private Long id;
    @TableField("name")  // 与表中的字段进行映射
    private String name;
    private Integer age;
    private String email;
    // 上级ID
    private Long managerId;
    private Date createTime;
}

排除非表字段

@TableField(exist =false)
private int flag;

查询操作

普通查询

/**
 * 根据 ID 查询
 *
 * @param id 主键ID
 */
T selectById(Serializable id);

/**
 * 查询（根据ID 批量查询）
 *
 * @param idList 主键ID列表(不能为 null 以及 empty)
 */
List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);

/**
 * 查询（根据 columnMap 条件）
 *
 * @param columnMap 表字段 map 对象
 */
List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);

@SpringBootTest
class SelectTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    void selectById() {
        User user = userMapper.selectById(1269485636327424002L);
        System.out.println(user);
        // SELECT id,name,age,email,manager_id,create_time FROM user WHERE id=?
    }

    @Test
    void selectByIds() {
        List<Long> idList = Arrays.asList(1094590409767661570L, 1088248166370832385L, 1269485636327424002L);
        List<User> users = userMapper.selectBatchIds(idList);
        users.forEach(System.out::println);
        // SELECT id,name,age,email,manager_id,create_time FROM user WHERE id IN ( ? , ? , ? )
    }

    @Test
    void selectByMap() {
        Map<String,Object> map = new HashMap<>();
        map.put("name","向南");  // key 为数据库中的字段
        map.put("age","20");
        List<User> users = userMapper.selectByMap(map);
        users.forEach(System.out::println);
        //  SELECT id,name,age,email,manager_id,create_time FROM user WHERE name = ? AND age = ?
    }


}

以条件构造器为参数的查询

/**
 * 根据 entity 条件，查询全部记录
 *
 * @param queryWrapper 实体对象封装操作类（可以为 null）
 */
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

  /**
     * 1、名字中包含雨并且年龄小于40
     * name like '%雨%' and age<40
     */
    @Test
    void select1() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("name", "雨").lt("age", 40);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
        //==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name LIKE ? AND age < ?)
        //==> Parameters: %雨%(String), 40(Integer)
    }

    /**
     * 2、名字中包含雨年并且龄大于等于20且小于等于40并且email不为空
     * name like '%雨%' and age between 20 and 40 and email is not null
     */
    @Test
    void select2() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
        //==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
        //==> Parameters: %雨%(String), 20(Integer), 40(Integer)
    }


    /**
     * 3、名字为王姓或者年龄大于等于25，按照年龄降序排列，年龄相同按照id升序排列
     * name like '王%' or age>=25 order by age desc,id asc
     */
    @Test
    void select3() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.likeRight("name", "王").or().gt("age", 25).orderByDesc("age").orderByAsc("id");
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
//        ==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name LIKE ? OR age > ?) ORDER BY age DESC,id ASC
//==> Parameters: 王%(String), 25(Integer)
    }


    /**
     * 4、创建日期为2019年2月14日并且直属上级为名字为王姓
     * date_format(create_time,'%Y-%m-%d')='2019-02-14' and manager_id in (select id from user where name like '王%')
     */
    @Test
    void select4() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2019-02-14").inSql("manager_id", "select id from user where name like '王%'");
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
//==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (date_format(create_time,'%Y-%m-%d')=? AND manager_id IN (select id from user where name like '王%'))
//==> Parameters: 2019-02-14(String)
    }


    /**
     * 5、名字为王姓并且（年龄小于40或邮箱不为空）
     * name like '王%' and (age<40 or email is not null)
     */
    @Test
    void select5() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.likeRight("name", "王").and((qw) -> {
            qw.lt("age", 40).or().isNotNull("email");
        });
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
//==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name LIKE ? AND (age < ? OR email IS NOT NULL))
//==> Parameters: 王%(String), 40(Integer)
    }

    /**
     * 6、名字为王姓或者（年龄小于40并且年龄大于20并且邮箱不为空）
     * name like '王%' or (age<40 and age>20 and email is not null)
     */
    @Test
    void select6() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.likeRight("name", "王").or((qw) -> {
            qw.between("age", 20, 40).isNotNull("email");
        });
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
//==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name LIKE ? OR (age BETWEEN ? AND ? AND email IS NOT NULL))
//==> Parameters: 王%(String), 20(Integer), 40(Integer)
    }

    /**
     * 7、（年龄小于40或邮箱不为空）并且名字为王姓
     * (age<40 or email is not null) and name like '王%'
     */
    @Test
    void select7() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.nested((qw) -> {
            qw.lt("age", 40).or().isNotNull("email");
        }).likeRight("name", "王");
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
//==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE ((age < ? OR email IS NOT NULL) AND name LIKE ?)
//==> Parameters: 40(Integer), 王%(String)
    }

    /**
     * 8、年龄为30、31、34、35
     * age in (30、31、34、35)
     */
    @Test
    void select8() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.in("age", Arrays.asList(31, 30, 34, 35));
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
//==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (age IN (?,?,?,?))
//==> Parameters: 31(Integer), 30(Integer), 34(Integer), 35(Integer)
    }

    /**
     * 9、只返回满足条件的其中一条语句即可
     * limit 1
     */
    @Test
    void select9() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.last("limit 1"); // 有sql注入风险
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
//==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user limit 1

    }

select中字段不全出现的处理方法

    /**
     * 10、名字中包含雨并且年龄小于40(需求1加强版)
     * 第一种情况：select id,name
     * from user
     * where name like '%雨%' and age<40
     */
    @Test
    void select1Supper() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("name", "雨").lt("age", 40).select("id", "name");
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
//==>  Preparing: SELECT id,name FROM user WHERE (name LIKE ? AND age < ?)
//==> Parameters: %雨%(String), 40(Integer)
    }

    /**
     * 第二种情况：select id,name,age,email
     * from user
     * where name like '%雨%' and age<40
     */
    @Test
    void select2Supper() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select(User.class, (info) -> {
            return !info.getColumn().equals("create_time")&&!info.getColumn().equals("manager_id");
        }).like("name", "雨").lt("age", 40);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
        
//==>  Preparing: SELECT id,name,age,email FROM user WHERE (name LIKE ? AND age < ?) 
//==> Parameters: %雨%(String), 40(Integer)
    }

condition 的使用

    void selectCondition(String name,String email) {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like(name!=null && name!="","name",name)
        .like(email!=null && email!="","email",email);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
//==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name LIKE ?)
//==> Parameters: %王%(String)
    }

    @Test
    void testCondition(){
        String name = "王";
        String email = "";
        selectCondition(name,email);
    }

实体对象作为条件构造器参数

public QueryWrapper(T entity) {
    super.setEntity(entity);
    super.initNeed();
}

    @Test
    void selectByEntity() {
        User userEntity = new User();
        userEntity.setName("刘红雨");
        userEntity.setAge(32);
        // 传入实体类
        QueryWrapper<User> wrapper = new QueryWrapper<>(userEntity);
//        wrapper.like("name", "雨").lt("age", 40);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);


//==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name=? AND age=?
//==> Parameters: 刘红雨(String), 32(Integer)
    }

allEq

allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)

个别参数说明:

params : key为数据库字段名,value为字段值
null2IsNull : 为true则在map的value为null时调用 isNull 方法,为false时则忽略value为null的

    @Test
    void selectAllEq() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();

        Map<String, Object> map = new HashMap<>();
        map.put("name", "刘红雨");
        map.put("email", null);
        wrapper.allEq(map, false);  // false 为空的字段将会忽略掉
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
        // true
        // SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name = ? AND email IS NULL)
        // false
        // SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name = ?)
    }

allEq(BiPredicate<R, V> filter, Map<R, V> params)
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull) 
个别参数说明:

filter : 过滤函数,是否允许字段传入比对条件中
params 与 null2IsNull : 同上

    @Test
    void selectAllEq2() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();

        Map<String, Object> map = new HashMap<>();
        map.put("name", "刘红雨");
        map.put("email", null);
        // 过滤掉 name
        wrapper.allEq((k, v) -> {
            return !k.equals("name");
        }, map, true); 
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
//SELECT id,name,age,email,manager_id,create_time FROM user WHERE (email IS NULL) 

    }

selectMaps

/**
 * 根据 Wrapper 条件，查询全部记录
 *
 * @param queryWrapper 实体对象封装操作类（可以为 null）
 */
List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

    @Test
    void selectWrapperByMap() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select("name","age","manager_id").like("name","雨");
        List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);
        maps.forEach(System.out::println);
        // SELECT name,age,manager_id FROM user WHERE (name LIKE ?) 
        // 查询结果
        // {manager_id=1088248166370832385, name=张雨琪, age=31}
        //{manager_id=1088248166370832385, name=刘红雨, age=32}
    }

selectObjs

/**
 * 根据 Wrapper 条件，查询全部记录
 * <p>注意： 只返回第一个字段的值</p>
 *
 * @param queryWrapper 实体对象封装操作类（可以为 null）
 */
List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

    @Test
    void selectWrapperByObjs() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("name", "雨");
        // 返回查询结果的第一列
        List<Object> objects = userMapper.selectObjs(wrapper);
        objects.forEach(System.out::println);
//==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name LIKE ?)
//==> Parameters: %雨%(String)
//<==    Columns: id, name, age, email, manager_id, create_time
//<==        Row: 1094590409767661570, 张雨琪, 31, zjq@baomidou.com, 1088248166370832385, 2019-01-14 09:15:15
//<==        Row: 1094592041087729666, 刘红雨, 32, lhm@baomidou.com, 1088248166370832385, 2019-01-14 09:48:16
//<==      Total: 2

// 查询结果
//1094590409767661570
//1094592041087729666
    }

selectCount

/**
 * 根据 Wrapper 条件，查询总记录数
 *
 * @param queryWrapper 实体对象封装操作类（可以为 null）
 */
Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

    @Test
    void selectWrapperByCount() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("name", "雨");
        // 返回查询结果的第一列
        Integer count = userMapper.selectCount(wrapper);
        System.out.println(count); // 2
//        ==>  Preparing: SELECT COUNT( 1 ) FROM user WHERE (name LIKE ?) 
//==> Parameters: %雨%(String)
    }

selectOne

/**
 * 根据 entity 条件，查询一条记录
 *
 * @param queryWrapper 实体对象封装操作类（可以为 null）
 */
T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

   @Test
    void selectWrapperByOne() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("name", "张红雨");
        // 返回多条语句会报错 Expected one result (or null) to be returned by selectOne(), but found: 2
        User user = userMapper.selectOne(wrapper);
        System.out.println(user);
    }

使用 Wrapper 自定义SQL

注解方式 UserMapper.java

public interface UserMapper extends BaseMapper<User> {
    @Select("select * from user ${ew.customSqlSegment}")
    List<User> getAll(@Param(Constants.WRAPPER) Wrapper<User> queryWrapper);

}

XML形式 UserMapper.xml

public interface UserMapper extends BaseMapper<User> {
    List<User> getAll(@Param(Constants.WRAPPER) Wrapper<User> queryWrapper);

}

application.yml

mybatis-plus:
  mapper-locations: classpath*:/mapper/**/*.xml # mapper的位置

UserMapper.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.chaolong.first.dao.UserMapper">
    <select id="getAll" resultType="com.chaolong.first.domian.User">
        select * from user ${ew.customSqlSegment}
    </select>
</mapper>

分页插件

/**
 * 根据 entity 条件，查询全部记录（并翻页）
 *
 * @param page         分页查询条件（可以为 RowBounds.DEFAULT）
 * @param queryWrapper 实体对象封装操作类（可以为 null）
 */
<E extends IPage<T>> E selectPage(E page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

/**
 * 根据 Wrapper 条件，查询全部记录（并翻页）
 *
 * @param page         分页查询条件
 * @param queryWrapper 实体对象封装操作类
 */
<E extends IPage<Map<String, Object>>> E selectMapsPage(E page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

config/MybatisPlusConfig

package com.chaolong.first.config;

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

/**
 * @author: Chaolong
 * @create: 2020/06/08
 **/
@Configuration
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        // paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }
}

selelctPage测试

    @Test
    void selectByPage() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        IPage<User> page = new Page<>(1,2);
        IPage<User> userIPage = userMapper.selectPage(page, wrapper);
        long total = userIPage.getTotal();  
        System.out.println("total="+total); // total=8
        List<User> records = userIPage.getRecords();
        records.forEach(System.out::println);
        
        // ==>  Preparing: SELECT COUNT(1) FROM user
        // ==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user LIMIT ?,?
    }

selectMapsPage测试

    @Test
    void selectByMapsPage() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        // false 不查询总记录数
        Page<Map<String, Object>> page = new Page<>(1, 2, false);
        Page<Map<String, Object>> mapPage = userMapper.selectMapsPage(page, wrapper);
        List<Map<String, Object>> records = mapPage.getRecords();
        records.forEach(System.out::println);
        // ==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user LIMIT ?,?
        // 结果
        //{create_time=2019-01-11 22:20:20.0, name=大boss, id=1087982257332887553, age=40, email=boss@baomidou.com}
        //{create_time=2019-02-05 19:12:22.0, manager_id=1087982257332887553, name=王天风, id=1088248166370832385, age=25, email=wtf@baomidou.com}
    }

XML 自定义分页

UserMapper.java 方法内容

public interface UserMapper {//可以继承或者不继承BaseMapper
    /**
     * <p>
     * 查询 : 根据state状态查询用户列表，分页显示
     * </p>
     *
     * @param page 分页对象,xml中可以从里面进行取值,传递参数 Page 即自动分页,必须放在第一位(你可以继承Page实现自己的分页对象)
     * @param state 状态
     * @return 分页对象
     */
    IPage<User> selectPageVo(Page<?> page, Integer state);
}

UserMapper.xml 等同于编写一个普通 list 查询，mybatis-plus 自动替你分页

<select id="selectPageVo" resultType="com.baomidou.cloud.entity.UserVo">
    SELECT id,name FROM user WHERE state=#{state}
</select>

UserServiceImpl.java 调用分页方法

public IPage<User> selectUserPage(Page<User> page, Integer state) {
    // 不进行 count sql 优化，解决 MP 无法自动优化 SQL 问题，这时候你需要自己查询 count 部分
    // page.setOptimizeCountSql(false);
    // 当 total 为小于 0 或者设置 setSearchCount(false) 分页插件不会进行 count 查询
    // 要点!! 分页返回的对象与传入的对象是同一个
    return userMapper.selectPageVo(page, state);
}

Update

/**
 * 根据 ID 修改
 *
 * @param entity 实体对象
 */
int updateById(@Param(Constants.ENTITY) T entity);

/**
 * 根据 whereEntity 条件，更新记录
 *
 * @param entity        实体对象 (set 条件值,可以为 null)
 * @param updateWrapper 实体对象封装操作类（可以为 null,里面的 entity 用于生成 where 语句）
 */
int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);

updateById 测试

    @Test
    void updateById() {
        User user = new User();
        user.setId(1088250446457389058L);
        user.setAge(30);
        user.setEmail("lyw123@163.com");
        int res = userMapper.updateById(user);
        System.out.println(res);
        
//        ==>  Preparing: UPDATE user SET age=?, email=? WHERE id=? 
//        ==> Parameters: 30(Integer), lyw123@163.com(String), 1088250446457389058(Long)

    }

update 测试

    @Test
    void update() {

        //  entity        实体对象 (set 条件值,可以为 null)
        User userSet = new User();
        userSet.setEmail("lyw123@qq.com");
        userSet.setAge(32);
        // updateWrapper 实体对象封装操作类（可以为 null,里面的 entity 用于生成 where 语句）
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        wrapper.eq("id", 1088250446457389058L);
        userMapper.update(userSet, wrapper);

//==>  Preparing: UPDATE user SET age=?, email=? WHERE (id = ?) 
//==> Parameters: 32(Integer), lyw123@qq.com(String), 1088250446457389058(Long)
    }

简化user

    @Test
    void update2() {

        // updateWrapper 实体对象封装操作类（可以为 null,里面的 entity 用于生成 where 语句）
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        wrapper.eq("id", 1088250446457389058L).
                set("email", "lyw123@baidu.com").set("age", 33);
        userMapper.update(null, wrapper);

//==>  Preparing: UPDATE user SET age=?, email=? WHERE (id = ?)
    }

Delete

/**
 * 根据 ID 删除
 *
 * @param id 主键ID
 */
int deleteById(Serializable id);

/**
 * 根据 columnMap 条件，删除记录
 *
 * @param columnMap 表字段 map 对象
 */
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);

/**
 * 根据 entity 条件，删除记录
 *
 * @param wrapper 实体对象封装操作类（可以为 null）
 */
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);

/**
 * 删除（根据ID 批量删除）
 *
 * @param idList 主键ID列表(不能为 null 以及 empty)
 */
int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);

    @Test
    void deleteById() {
        int i = userMapper.deleteById(1269485636327424002L);
        System.out.println(i);
        // DELETE FROM user WHERE id=?
    }

    @Test
    void deleteByMap() {
        Map<String,Object> map = new HashMap<>();
        map.put("name","向南");
        map.put("age",20);
        int i = userMapper.deleteByMap(map);
        System.out.println(i);
        
        // ==>  Preparing: DELETE FROM user WHERE name = ? AND age = ? 
    }

AR模式

通过实体类对象直接进行表的增删改查操作。

MP中AR实现

一是实体需要继承Model类，二是必须存在对应的原始mapper并继承baseMapper并且可以使用的前提下，才能使用此 AR 模式。

User.java

@Data
@TableName("user")  // 与数据库中的哪一张表进行映射
public class User extends Model<User> {
    @TableId("id")
    private Long id;
    @TableField("name")  // 与表中的字段进行映射
    private String name;
    private Integer age;
    private String email;
    // 上级ID
    private Long managerId;
    private Date createTime;

    @TableField(exist =false)
    private int flag;
}

UserMapper.java

public interface UserMapper extends BaseMapper<User> {

}

测试


@SpringBootTest
class ARTest {

    @Test
    void insert() {
        User user = new User();
        user.setName("赵明");
        user.setAge(55);
        user.setManagerId(1088248166370832385L);
        user.setCreateTime(new Date());
        // 插入
        boolean res = user.insert();
        System.out.println(res);  // true
        // ==>INSERT INTO user ( id, name, age, manager_id, create_time ) VALUES ( ?, ?, ?, ?, ? )
    }

    @Test
    void select() {
        User user = new User();
        // 查找
        User selectUser = user.selectById(1088248166370832385L);
        System.out.println(selectUser);
        //==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE id=?
    }

    @Test
    void update() {
        User user = new User();
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        wrapper.eq("name", "赵明").set("age", 66).set("email", "zhaoming@123.com");
        // 更新
        boolean res = user.update(wrapper);
        System.out.println(res);
        // ==>  Preparing: UPDATE user SET age=?,email=? WHERE (name = ?)
    }

    @Test
    void delete() {
        User user = new User();
        user.setId(1269956828209352706L);
        boolean res = user.deleteById();
        System.out.println(res);
        // ==>  Preparing: DELETE FROM user WHERE id=? 
    }

}

主键生成策略

/**
 * 数据库ID自增
 */
AUTO(0),
/**
 * 该类型为未设置主键类型(注解里等于跟随全局,全局里约等于 INPUT)
 */
NONE(1),
/**
 * 用户输入ID
 * <p>该类型可以通过自己注册自动填充插件进行填充</p>
 */
INPUT(2),

/* 以下3种类型、只有当插入对象ID 为空，才自动填充。 */
/**
 * 分配ID (主键类型为number或string）,
 * 默认实现类 {@link com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator}(雪花算法)
 *
 * @since 3.3.0
 */
ASSIGN_ID(3),
/**
 * 分配UUID (主键类型为 string)
 * 默认实现类 {@link com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator}(UUID.replace("-",""))
 */
ASSIGN_UUID(4),
/**
 * @deprecated 3.3.0 please use {@link #ASSIGN_ID}
 */
@Deprecated
ID_WORKER(3),
/**
 * @deprecated 3.3.0 please use {@link #ASSIGN_ID}
 */
@Deprecated
ID_WORKER_STR(3),
/**
 * @deprecated 3.3.0 please use {@link #ASSIGN_UUID}
 */
@Deprecated
UUID(4);

局部主键策略设置

@Data
@TableName("user")  // 与数据库中的哪一张表进行映射
public class User extends Model<User> {
    @TableId(type=AUTO)
    private Long id;

}

全局主键策略设置

mybatis-plus:
  global-config:
    db-config:
      id-type: uuid

基本配置

详细请看官方文档https://mybatis.plus/config/#%E5%9F%BA%E6%9C%AC%E9%85%8D%E7%BD%AE

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 开启sql 语句日志显示
  mapper-locations: classpath*:/mapper/**/*.xml # MyBatis Mapper 所对应的 XML 文件位置
  #config-location: classpath*:/mybatis-conf.xml  # mybatis 的配置文件
  global-config:
    db-config:
      id-type: none  # 全局默认主键类型
      # table-prefix: mp_  # 数据库表名默认前缀

通用Service

1. servcie接口继承 IService

2. 接口的实现类继承 ServiceImpl<M extends BaseMapper<T>, T>

UserServicec.class 接口

import com.baomidou.mybatisplus.extension.service.IService;
import com.chaolong.first.domian.User;

/**
 * @author: Chaolong
 * @create: 2020/06/09
 **/
public interface UserService extends IService<User> {
}

UserServiceImpl.class 实现类

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.chaolong.first.dao.UserMapper;
import com.chaolong.first.domian.User;
import com.chaolong.first.service.UserService;
import org.springframework.stereotype.Service;
/**
 * @author: Chaolong
 * @create: 2020/06/09
 **/
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

}

ServiceTest 测试

/**
 * @author: Chaolong
 * @create: 2020/06/09
 **/
@SpringBootTest
public class ServiceTest {
    @Autowired
    private UserService userService;

    @Test
    void bath() {
        User user1 = new User();
        user1.setName("序序");
        user1.setAge(20);

        User user2 = new User();
        user2.setName("序序2");
        user2.setAge(30);

        List<User> list = Arrays.asList(user1, user2);
        // 批量保存
        boolean res = userService.saveBatch(list);
        System.out.println(res);

        // ==>  Preparing: INSERT INTO user ( id, name, age ) VALUES ( ?, ?, ? )
    }

    @Test
    void chain() {
        // 链式操作
        List<User> users = userService.lambdaQuery().gt(User::getAge, 25).
                like(User::getName, "雨").list();
        users.forEach(System.out::println);
        // ==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (age > ? AND name LIKE ?)
    }

    @Test
    void chain2() {
        boolean update = userService.lambdaUpdate().eq(User::getAge, 25)
                .set(User::getAge, 0).update();
        System.out.println(update);

        // ==>  Preparing: UPDATE user SET age=? WHERE (age = ?)
    }

}