springboot整合mybatisplus

一、mybatis-plus入门

官网：https://mp.baomidou.com

MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

1、创建一个spring boot工程

参考：https://www.cnblogs.com/zwh0910/p/15484263.html#autoid-3-0-0

2、导入pom文件

<!--spring boot 父工程-->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.zwh</groupId>
    <artifactId>mybatis-plus</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </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>
        </dependency>
    </dependencies>

3、创建数据库

#创建用户表
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');

4、启动类

启动类上加@MapperScan,指定要变成实现类的接口所在的包，然后包下面的所有接口在编译之后都会生成相应的实现类

@SpringBootApplication
@MapperScan("com.zwh.dao")
public class MySpringBootApplication {
    public static void main(String[] args) {
        SpringApplication.run(MySpringBootApplication.class);
    }
}

5、创建实体类User

@Data
public class User extends Model<User> {
    
    private Long id;
    
    private String name;

    private Integer age;

    private String email;

    private Long managerId;

    private LocalDateTime createTime;
    
}

6、dao接口

public interface UserMapper extends BaseMapper<User> {
}

继承BaseMapper

如果添加了mapper.xml，那么需要配置xml的位置，否则会出现无法绑定。

mybatis-plus.mapperLocations=classpath*:mapper/*.xml

7、配置数据源

#mysql
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf8&useoldAliasMetadataBehavior=true&allowMultiQueries=true&serverTimezone=CTT
spring.datasource.username=root
spring.datasource.password=123456

8、测试查询所有

新建测试类

@RunWith(SpringRunner.class)
@SpringBootTest
public class SampleTest {
    @Resource
    private UserMapper userMapper;
    @Test
    public void testSelect() {
        System.out.println(("----- selectAll method test ------"));
        List<User> userList = userMapper.selectList(null);
        userList.forEach(System.out::println);
    }
}

结果如下：

----- selectAll method test ------
User(id=1087982257332887553, name=大boss, age=40, email=boss@baomidou.com, managerId=null, createTime=2019-01-11T14:20:20)
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22)
User(id=1088250446457389058, name=李艺伟, age=28, email=lyw@baomidou.com, managerId=1088248166370832385, createTime=2019-02-14T08:31:16)
User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15)
User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16)

9、测试保存

@RunWith(SpringRunner.class)
@SpringBootTest
public class SampleTest {
    @Resource
    private UserMapper userMapper;

    @Test
    public void insert(){
        User user = new User();
        user.setName("马伟奇");
        user.setAge(18);
        user.setManagerId(1088248166370832385L);
        user.setCreateTime(LocalDateTime.now());

        int rows = userMapper.insert(user);
        System.out.println("影响记录数:" + rows);
    }
}

增加和修改操作，只会根据对象中存在不为null的属性来操作数据，当数据为null时不会改变数据库中的数据。

10、测试删除

@RunWith(SpringRunner.class)
@SpringBootTest
public class SampleTest {
    @Resource
    private UserMapper userMapper;

    @Test
    public void delete(){
        int i = userMapper.deleteById(1465286349850615810L);
        System.out.println("影响记录数:" + i);
    }
}

11、测试修改

@Test
    public void update(){
        User user = new User();
        user.setId(1465290164649811969L);
        user.setName("杨利伟");
        user.setAge(38);
        user.setManagerId(1088248166370832385L);
        user.setCreateTime(LocalDateTime.now());
        int rows = userMapper.updateById(user);
        System.out.println("影响记录数:" + rows);
    }

12、日志级别和日志格式

级别控制

#级别控制
logging.level.com.zwh=debug

logging.level设置日志级别，后面跟生效的区域，比如root表示整个项目，也可以设置为某个包下，也可以具体到某个类名（日志级别的值不区分大小写）

日志格式

logging.pattern.console='%d{yyyy-MM-dd HH:mm:ss,SSS} [%t] [%c]-[%p] %m%n'

测试

[DEBUG] ==>  Preparing: INSERT INTO user ( id, name, age, manager_id, create_time ) VALUES ( ?, ?, ?, ?, ? ) 
[DEBUG] ==> Parameters: 1465286349850615810(Long), 马伟奇(String), 18(Integer), 1088248166370832385(Long), 2021-11-29T19:47:43.788(LocalDateTime)
[DEBUG] <==    Updates: 1

二、常见注解

常用注解：@TableName、@TableId、@TableField

@TableName:其用来将实体对象与数据库表名进行对应。当实体名与数据库表名不一致时使用。

@TableId:其是主键注解，指明是主键，默认把id作为主键。属性type用来指定类型：

　　1）IdType.AUTO是自动增长；

　　2）IdType.ASSIGN_UUID是String类型的uuid

　　3）IdType.INPUT是根据用户的输入作为主键

@TableField:其是表的其他字段注解。属性exist用来指明是否是数据库的字段，值为false时不映射数据库表字段。

注意：MP开启了驼峰转下划线，操作的sql语句，会依据属性名转换为下划线的列名称，如，会将userId转换为user_id 会将userIdS转换为user_id_s

@Data
// 数据库表的名字叫mp_user ，类名字如果叫User，就需要添加当前注解
@TableName("mp_user")
public class User extends Model<User> {

    // 数据库主键id如果叫user_id，属性叫做userId，也找不到会报错，因为
    // mybatis-plus：默认会找属性叫id的为主键，如果把主键的换成别的名字，比如userId，那就需要添加当前注解
    @TableId
    private Long id; 
    // 数据库名字叫name，属性叫realName，就需要给属性添加当前注解
    @TableField("name")
    private String realName;

    private Integer age;

    private String email;

    private Long managerId;

    private LocalDateTime createTime;

}

将数据库中表mp_user的字段id改为t_id，然后修改实体User的属性id并设置自动增长

@Data
@TableName(value = "mp_user")
public class User {
    //指定自动增长
    @TableId(value = "t_id",type = IdType.AUTO)
    private Long id;
    ......
}

三、排除非表字段

1、添加属性remark

在javabean中添加一个属性 remark ，但是这个属性在数据表中并没有这个字段。

@Data
public class User extends Model<User> {

    private Long id;

    private String name;

    private Integer age;

    private String email;

    private Long managerId;

    private LocalDateTime createTime;

    private String remark;

}

数据表字段

测试没有排除非表字段得情况：

@Test
    public void insert(){
        User user = new User();
        user.setName("马伟奇");
        user.setAge(18);
        user.setManagerId(1088248166370832385L);
        user.setCreateTime(LocalDateTime.now());

        user.setRemark("备注信息");
        
        int rows = userMapper.insert(user);
        System.out.println("影响记录数:" + rows);
    }

报错如下：

报错原因是因为属性有这个字段，数据库没有这个列，但是mybatis-plus框架映射了这个属性，导致数据库找不到这个列，所以报错，我们就需要在bean对象里面排除这个对象

@Data
public class User extends Model<User> {


    private Long id;

    private String name;

    private Integer age;

    private String email;

    private Long managerId;


    private LocalDateTime createTime;
    /**
     * 排除非表字段的三种方式
     * 1、private transient String remark; 无法序列化
     * 2、private static String remark; 实例无法独有一份
     * 3、@TableField(exist=false)
     */
    @TableField(exist = false)
    private String remark;

}

四、mybatis-plus查询方法

user实体类

@Data
public class User extends Model<User> {

    private Long id;

    private String name;

    private Integer age;

    private String email;

    private Long managerId;

    private LocalDateTime createTime;
    @TableField(exist = false)
    private String remark;

}

1、根据id查询selectById

@RunWith(SpringRunner.class)
@SpringBootTest
public class SampleTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void selectById(){
        User user = userMapper.selectById(1094592041087729666L);
        System.out.println(user);
    }
}

结果：

User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16, remark=null)

2、根据多个id查询selectBatchIds

@Test
    public void selectIds(){
        List<Long> idsList = Arrays.asList(1088248166370832385L, 1153268754419863553L, 1094590409767661570L);
        List<User> userList = userMapper.selectBatchIds(idsList);
        userList.forEach(System.out::println);
    }

结果：

User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)
User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null)

3、根据map查询selectByMap

@Test
    public void selectByMap(){
        Map<String, Object> map = new HashMap<>();
        // 注意key是数据库里面的列，不是javabean的属性
        map.put("name","王天风");
        map.put("age",25);
        List<User> userLists = userMapper.selectByMap(map);
        userLists.forEach(System.out::println);
    }

结果：

User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)

4、Java命令里面的EQ、NE、GT、LT、GE、LE分别代表含义

EQ 就是 EQUAL等于 
NE 就是 NOT EQUAL不等于 
GT 就是 GREATER THAN大于　 
LT 就是 LESS THAN小于 
GE 就是 GREATER THAN OR EQUAL 大于等于 
LE 就是 LESS THAN OR EQUAL 小于等于

详细说明如下：

方法	说明	示例
eq	等于	`allEq({id:1,name:"老王",age:null})`---> `id = 1 and name = '老王' and age is null`
allEq	全等于	`eq("name", "老王")`--->`name = '老王'`
ne	不等于	`ne("name", "老王")`--->`name <> '老王'`
gt	大于	`gt("age", 18)`--->`age > 18`
ge	大于等于	`ge("age", 18)`--->`age >= 18`
lt	小于	`lt("age", 18)`--->`age < 18`
le	小于等于	`le("age", 18)`--->`age <= 18`
between	BETWEEN 值1 AND 值2	`between("age", 18, 30)`--->`age between 18 and 30`
notBetween	NOT BETWEEN 值1 AND 值2	`notBetween("age", 18, 30)`--->`age not between 18 and 30`
like	LIKE '%值%'	`like("name", "王")--->name like '%王%'`
notLike	NOT LIKE '%值%'	`notLike("name", "王")`--->`name not like '%王%'`
likeLeft	LIKE '%值'	`likeLeft("name", "王")`--->`name like '%王'`
likeRight	LIKE '值%'	`likeRight("name", "王")`--->`name like '王%'`
isNull	字段为null	`isNull("name")`--->`name is null`
isNotNull	字段不为null	`isNotNull("name")`--->`name is not null`
in	同数据库in	`in("age",{1,2,3})`--->`age in (1,2,3)`
notIn	同数据库not in	`notIn("age",{1,2,3})`--->`age not in (1,2,3)`
inSql	相当于子查询和in	`inSql("id", "select id from table where id < 3")`---> `id in (select id from table where id < 3)`
notInSql	相当于子查询和not in	`notInSql("id", "select id from table where id < 3")`---> `id not in (select id from table where id < 3)`
groupBy	同数据库group by	`groupBy("id", "name")`--->`group by id,name`
orderBy/orderByDesc	同数据库order by 字段/order by 字段 desc	`orderBy("id")`--->`order by id ASC`
having	同数据库having	`having("sum(age) > 10")`--->`having sum(age) > 10`
or/and	同数据库or/and	`略`
netsted	正常嵌套不带 AND 或者 OR	`nested(i -> i.eq("name", "李白").ne("status", "活着"))--->(name = '李白' and status <> '活着')`
apply	字符串拼接	`apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")`---> `date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")`
last	拼接在最后，谨慎使用	`last("limit 1")---> limit 1`
exists/notExists	拼接sql	`exists("select id from table where age = 1")`--->`exists (select id from table where age = 1)`
select	指定要查询的字段	`select("id", "name", "age")--->只查询这三个字段的内容`
set	全局修改	`set("name", "")`--->数据库字段值变为空字符串

5、条件构造查询

@Test
    public void selectByWrapper(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.like("name","雨").lt("age",40);
        List<User> userList = userMapper.selectList(userQueryWrapper);
        userList.forEach(System.out::println);
    }

结果：

User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null)
User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16, remark=null)

6、条件构造查询

/**
     * 名字中包含雨 年龄大于等于20 并且 小于等于40 并且email 不为空
     * name like '%雨%' and age between 20 and 40 and email is not null
     */
    @Test
    public void selectByWrapper2(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.like("name","雨").between("age",20,40).isNotNull("email");
        List<User> userList = userMapper.selectList(userQueryWrapper);
        userList.forEach(System.out::println);
    }

结果：

User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null)
User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16, remark=null)

selectCount：查询符合条件的条数，一般和selectList结合使用。

@Test
    public void selectByWrapper2(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.like("name","雨").between("age",20,40).isNotNull("email");
        List<User> userList = userMapper.selectList(userQueryWrapper);
        Integer integer = userMapper.selectCount(userQueryWrapper);
        userList.forEach(System.out::println);
        System.out.println(integer);
    }

7、条件构造查询

/**
     * 名字为王姓或者年龄大于等于25，按照年龄降序排列额，年龄相同按照id升序排列
     * name like '王%' or age>25 order by age desc,id asc
     */
    @Test
    public void selectByWrapper3(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.likeRight("name","王").or().ge("age",25).orderByDesc("age").orderByAsc("id");
        List<User> userList = userMapper.selectList(userQueryWrapper);
        userList.forEach(System.out::println);
    }

结果：

User(id=1087982257332887553, name=大boss, age=40, email=boss@baomidou.com, managerId=null, createTime=2019-01-11T14:20:20, remark=null)
User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16, remark=null)
User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null)
User(id=1088250446457389058, name=李艺伟, age=28, email=lyw@baomidou.com, managerId=1088248166370832385, createTime=2019-02-14T08:31:16, remark=null)
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)

8、条件构造查询

/**
     * 名字为王姓并且(年龄小于40或邮箱不为空)
     * name like '王%' and (age<40or email is not null)
     */
    @Test
    public void selectByWrapper5(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.likeRight("name","王").and(wq->wq.lt("age",40).or().isNotNull("email"));
        List<User> userList = userMapper.selectList(userQueryWrapper);
        userList.forEach(System.out::println);
    }

结果：

User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)

9、条件查询

@Test
    public void selectByWrapper6(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.likeRight("name","王").or(new Function<QueryWrapper<User>, QueryWrapper<User>>() {
            @Override
            public QueryWrapper<User> apply(QueryWrapper<User> userQueryWrapper) {
                return userQueryWrapper.lt("age",40).gt("age",20).isNotNull("email");
            }
        });
//        userQueryWrapper.likeRight("name","王")
//                .or(wq->wq.lt("age",40).gt("age",20).isNotNull("email"));
        List<User> userList = userMapper.selectList(userQueryWrapper);
        userList.forEach(System.out::println);
    }

结果

User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)
User(id=1088250446457389058, name=李艺伟, age=28, email=lyw@baomidou.com, managerId=1088248166370832385, createTime=2019-02-14T08:31:16, remark=null)
User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null)
User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16, remark=null)

10、条件查询

/**
     * (年龄小于40或邮箱不为空)并且名字为王姓
     * (age < 40 or email is not null) and name like '王%'
     */
    @Test
    public void selectByWrapper7(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.nested(new Function<QueryWrapper<User>, QueryWrapper<User>>() {
            @Override
            public QueryWrapper<User> apply(QueryWrapper<User> userQueryWrapper) {
                return userQueryWrapper.lt("age", 40).or().isNotNull("email");
            }
        }).likeRight("name", "王");
//        userQueryWrapper.nested(wq->wq.lt("age",40).or().isNotNull("email")).likeRight("name","王");
        List<User> userList = userMapper.selectList(userQueryWrapper);
        userList.forEach(System.out::println);
    }

结果

User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)

11、条件查询

/**
     * 年龄为30，31，34，35
     * age in(30，31，34，35)
     */
    @Test
    public void selectByWrapper8(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.in("age",Arrays.asList(30,31,34,35));
        List<User> userList = userMapper.selectList(userQueryWrapper);
        userList.forEach(System.out::println);
    }

结果

User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null)

12、条件查询

/**
     * 返回满足条件的一条语句
     */
    @Test
    public void selectByWrapper9(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.in("age",Arrays.asList(30,31,34,35)).last("limit 1");

        List<User> userList = userMapper.selectList(userQueryWrapper);
        userList.forEach(System.out::println);
    }

结果：

User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null)

13、不列出全部的列

/**
     * 名字中包含 雨 并且年龄小于40
     * name like '%雨%' and age < 40
     */
    @Test
    public void selectByWrapper10(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.select("id","name").like("name","雨").lt("age",40);
        List<User> userList = userMapper.selectList(userQueryWrapper);
        userList.forEach(System.out::println);
    }

结果：

User(id=1094590409767661570, name=张雨琪, age=null, email=null, managerId=null, createTime=null, remark=null)
User(id=1094592041087729666, name=刘红雨, age=null, email=null, managerId=null, createTime=null, remark=null)

14、不列出所有的列

/**
     * 名字中包含 雨 并且年龄小于40
     * name like '%雨%' and age < 40
     * 排除manager_id和create_time
     */
    @Test
    public void selectByWrapper11(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.like("name","雨").lt("age",40)
        .select(User.class,info->!info.getColumn().equals("create_time")&&
                !info.getColumn().equals("manager_id"));
        List<User> userList = userMapper.selectList(userQueryWrapper);
        userList.forEach(System.out::println);
    }

结果：

User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=null, createTime=null, remark=null)
User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=null, createTime=null, remark=null)

五、分页查询

1、单表分页查询

新建一个类配置其分页

@EnableTransactionManagement
@Configuration
@MapperScan("com.zwh.dao")
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        return paginationInterceptor;
    }
}

测试

@Test
    public void selectByWrapper12(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.likeRight("name","王").or(new Function<QueryWrapper<User>, QueryWrapper<User>>() {
            @Override
            public QueryWrapper<User> apply(QueryWrapper<User> userQueryWrapper) {
                return userQueryWrapper.lt("age",40).gt("age",20).isNotNull("email");
            }
        });
        IPage<User> page=new Page<>(1,2);
//        userQueryWrapper.likeRight("name","王")
//                .or(wq->wq.lt("age",40).gt("age",20).isNotNull("email"));
        IPage<User> userIPage = userMapper.selectPage(page, userQueryWrapper);
        List<User> records = userIPage.getRecords();
        records.forEach(System.out::println);
    }

结果：

User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)
User(id=1088250446457389058, name=李艺伟, age=28, email=lyw@baomidou.com, managerId=1088248166370832385, createTime=2019-02-14T08:31:16, remark=null)

2、单表自定义分页查询

@Test
    public void selectByWrapper13(){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        userQueryWrapper.likeRight("name","王").or(new Function<QueryWrapper<User>, QueryWrapper<User>>() {
            @Override
            public QueryWrapper<User> apply(QueryWrapper<User> userQueryWrapper) {
                return userQueryWrapper.lt("age",40).gt("age",20).isNotNull("email");
            }
        });
        IPage<User> page=new Page<>(1,2);
//        userQueryWrapper.likeRight("name","王")
//                .or(wq->wq.lt("age",40).gt("age",20).isNotNull("email"));
        IPage<User> userIPage = userMapper.selectPageList(page, userQueryWrapper);
        List<User> records = userIPage.getRecords();
        records.forEach(System.out::println);
    }

在dao的接口中定义方法

public interface UserMapper extends BaseMapper<User> {
    IPage<User> selectPageList(IPage<User> page, @Param(Constants.WRAPPER)  QueryWrapper<User> userQueryWrapper);
}

对第二个参数queryWrapper使用了@Param注解起别名，其名称使用的是常量的值（实际值是ew），常量的定义也是由官方定义好的，如下：

public interface Constants extends StringPool {
    String MD5 = "MD5";
    String ENTITY = "et";
    String ENTITY_DOT = "et.";
    String WRAPPER = "ew";
    String WRAPPER_DOT = "ew.";
    String WRAPPER_ENTITY = "ew.entity";
    String WRAPPER_SQLSEGMENT = "ew.sqlSegment";
    String WRAPPER_EMPTYOFNORMAL = "ew.emptyOfNormal";
    String WRAPPER_NONEMPTYOFNORMAL = "ew.nonEmptyOfNormal";
    String WRAPPER_NONEMPTYOFENTITY = "ew.nonEmptyOfEntity";
    String WRAPPER_EMPTYOFWHERE = "ew.emptyOfWhere";
    String WRAPPER_NONEMPTYOFWHERE = "ew.nonEmptyOfWhere";
    String WRAPPER_ENTITY_DOT = "ew.entity.";
    String U_WRAPPER_SQL_SET = "ew.sqlSet";
    String Q_WRAPPER_SQL_SELECT = "ew.sqlSelect";
    String COLUMN_MAP = "cm";
    String COLUMN_MAP_IS_EMPTY = "cm.isEmpty";
    String COLLECTION = "coll";
    String WHERE = "WHERE";
    String MP_OPTLOCK_VERSION_ORIGINAL = "MP_OPTLOCK_VERSION_ORIGINAL";
    String MP_OPTLOCK_VERSION_COLUMN = "MP_OPTLOCK_VERSION_COLUMN";
    String MP_OPTLOCK_ET_ORIGINAL = "MP_OPTLOCK_ET_ORIGINAL";
}

在xml中编写sql

<?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.zwh.dao.UserMapper">

    <select id="selectPageList" resultType="com.zwh.entity.User">
        select * from user ${ew.customSqlSegment}
    </select>
</mapper>

那么后面的条件就直接使用"${ew.customSqlSegment}"进行代替了，它会根据设置的条件去查询。

结果如下：

User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)
User(id=1088250446457389058, name=李艺伟, age=28, email=lyw@baomidou.com, managerId=1088248166370832385, createTime=2019-02-14T08:31:16, remark=null)

3、多表自定义分页查询

多表分页和单表的分页原来是一样的道理。现假设有两个表，t_student（学生表）和t_clazz（班级表）

1）先创建两个表

CREATE TABLE `clazz` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT '班级名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  `clazzId` int(11) DEFAULT NULL COMMENT '班级id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

insert into clazz values(1,'计算机1班'),(2,'计算机2班'),(3,'计算机3班');
insert into student values(null,'赵敏',1),(null,'张明',3),(null,'李慧',3),(null,'赵美美',2),(null,'张峰',2),(null,'孙强',2);

2）创建学生实体类

@Data
@TableName(value = "user")
public class Student {

    @TableId(value = "id",type = IdType.AUTO)
    private Long id;

    @TableField(value = "name")
    private String name;

    @TableField(value = "clazzId")
    private Integer clazzId;

    @TableField(exist = false)
    private String clazzName;
}

3）创建controller接口

 @GetMapping("/list")
    public JSONObject getList(Integer curr, Integer limit) {
        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("t1.name", "张");
        IPage<Student> page = new Page<>(curr, limit);
        IPage<Student> stu = studentDao.selectPageList(page, queryWrapper);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("data", stu.getRecords());
        jsonObject.put("total", stu.getTotal());
        return jsonObject;
        
    }

4）在dao接口定义方法

IPage<Student> selectPageList(IPage<Student> page, @Param(Constants.WRAPPER) QueryWrapper<Student> queryWrapper);

5）在xml编写sql

<select id="selectPageList" resultType="com.zys.mybatisplusdemo.entity.Student">
        select t1.*,t2.name "clazzName" from student t1
        left join clazz t2 on t1.clazzId = t2.id
         ${ew.customSqlSegment}
</select>

6）调用此接口，数据可以正常查询。执行日志如下：

当然，对于多表查询，如果使用默认的条件查询不能满足要求，可以把QueryMapper换成Mmap，那么在xml中使用对应的参数查询即可。不过这种方式有些麻烦，但是可以实现更为复杂的需求

dao接口的方法：

IPage<Student> selectPageList2(IPage<Student> page, @Param(Constants.WRAPPER)Map<String, Object> map);

xml的sql：

 <select id="selectPageList2" resultType="com.zys.mybatisplusdemo.entity.Student">
        select t1.*, t2.name "clazzName"
        from student t1
                 left join clazz t2 on t1.clazzId = t2.id
        <where>
            <if test="ew.name != null and ew.name != ''">
                and t1.name like concat('%',#{ew.name},'%')
            </if>
        </where>
    </select>

需要注意的是，在if标签中也需要带ew，否则会报错。

相关阅读:
3.8 java基础总结①多线程
 RPM Database 实战详解
 关于CentOS7.2 控制面板不显示输入法，或者无法调出输入的问题。(已解决)
mysqldump
一些有意思的Linux命令
 和docket的第一次亲密接触
 centos7根分区扩容(亲测有效)
相识mongodb
开机自动获取spark用户名和服务器
 Puppet日常总结
原文地址：https://www.cnblogs.com/zwh0910/p/15620765.html