MyBatis-Plus（三）条件构造器和插件

条件构造器

Wrapper介绍

QueryWrapper

组装查询条件

    @Test
    public void testSelectWrapper(){
        // 查询姓名带i的，年龄在20-30之间，且邮箱不为空的用户
        //SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","i")
                .between("age",20,30)
                .isNotNull("email");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(user -> {
            System.out.println(user);
        });
    }

组装排序条件

    @Test
    public void testSortQuery(){
        // 查询用户信息，按照年龄的降序排序，若年龄相同，则按照id升序进行排序
        //SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 ORDER BY age DESC,id ASC
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("age")
                .orderByAsc("id");
        userMapper.selectList(queryWrapper).forEach(System.out::println);
    }

组装删除条件

    @Test
    public void testDeleteByQueryMapper(){
        //UPDATE user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.isNull("email");
        int result = userMapper.delete(queryWrapper);
        System.out.println("删除的记录数是：" + result);
    }

条件优先级

    @Test
    public void testUpdateByQueryMapper(){
        // 将（年龄大于20并且用户名中包含a）或邮箱为null的用户信息进行修改
        // UPDATE user SET email=? WHERE is_deleted=0 AND (age > ? AND name LIKE ? OR email IS NULL)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.gt("age",20)
                .like("name","k")
                .or()
                .isNull("email");

        User user = new User();
        user.setEmail("test@huluwa.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("删除的记录数是：" + result);
    }

    @Test
    public void testUpdateByQueryMapper02(){
        // 将用户名中包含a且（年龄大于20或邮箱为null）的用户信息修改
        // lambda中的条件优先执行
        //UPDATE user SET name=?, email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","a")
                .and(i->i.gt("age",20).or().isNull("email"));
        User user = new User();
        user.setName("葫芦娃4");
        user.setEmail("test4@huluwa.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("删除的记录数是：" + result);
    }

组装select子句

    @Test
    public void testQueryClause(){
        //SELECT name,age,email FROM user WHERE is_deleted=0
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("name","age","email");
        //selectMaps()返回Map集合列表，通常配合select()使用，避免User对象中没有被查询到的列值为null
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        maps.forEach(System.out::println);
    }

实现子查询

    @Test
    public void testSubQuery(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.inSql("id","select id from user where id <= 10");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

UpdateWrapper

    @Test
    public void testUpdateWrapper(){
        //UPDATE user SET name=?,email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.like("name","a")
                .and(i -> i.gt("age",20).or().isNull("email"));
        updateWrapper.set("name","new Bean").set("email","newBean@AG.com");
        int result = userMapper.update(null, updateWrapper);
        System.out.println(result);
    }

Condition

真正开发的过程中，组装条件是常见的功能，而这些条件数据来源于用户输入，是可选的，因此我们在组装这些条件时，必须先判断用户是否选择了这些条件，若选择则需要组装该条件，若没有选择则一定不能组装，以免影响SQL执行的结果

    @Test
    public void testCondition(){
    // SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (name LIKE ? AND age >= ? AND age <= ?)
        String username = "i";
        Integer ageBegin = 20;
        Integer ageEnd = 30;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符组成
        if (StringUtils.isNotBlank(username)){
            queryWrapper.like("name","i");
        }
        if (ageBegin != null){
            queryWrapper.ge("age",ageBegin);
        }
        if (ageEnd != null){
            queryWrapper.le("age",ageEnd);
        }
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

上面这个代码有点复杂，可以使用带condition参数的重载方法构建查询条件，简化代码的编写。

    @Test
    public void testCondition01(){
        // SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (name LIKE ? AND age >= ? AND age <= ?)
        String username = "i";
        Integer ageBegin = 20;
        Integer ageEnd = 30;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(username),"name","i")
                .ge(ageBegin != null,"age",ageBegin)
                .le(ageEnd != null, "age",ageEnd);
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(user -> {
            System.out.println(user);
        });
    }

LambdaQueryWrapper

    @Test
    public void testLambdaQueryWrapper(){
        String username = "i";
        Integer ageBegin = 20;
        Integer ageEnd = 30;
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.like(StringUtils.isNotBlank(username),User::getName,username)
                .ge(ageBegin != null, User::getAge, ageBegin)
                .le(ageEnd != null, User::getAge, ageEnd);
        List<User> list = userMapper.selectList(lambdaQueryWrapper);
        list.forEach(System.out::println);
    }

LambdaUpdateWrapper

    @Test
    public void testLambdaUpdateWrapper(){
        LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
        lambdaUpdateWrapper.like(User::getName,"i")
                .and(i -> i.ge(User::getAge,20).or().isNull(User::getEmail));
        lambdaUpdateWrapper.set(User::getName,"Bean").set(User::getEmail,"Bean@AG.com");
        int result = userMapper.update(null, lambdaUpdateWrapper);
        System.out.println(result);
    }

插件

分页插件

MP自带分页插件，我们只需对其配置即可。

package com.atguigu.mybatis_plus.config;

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

/**
 * @version 1.0
 * @Description
 * @Author 月上叁竿
 * @Date 2022-04-05 9:26
 **/
@Configuration
// 扫描Mapper接口所在的包
@MapperScan("com.atguigu.mybatis_plus.mapper")
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

    @Test
    public void testPage(){
        //SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 LIMIT ?
        Page<User> page = new Page<>(2,3);
        userMapper.selectPage(page,null);
        System.out.println(page.getRecords());
        System.out.println(page.getPages());
        System.out.println(page.getTotal());
        System.out.println(page.hasNext());
        System.out.println(page.hasPrevious());
    }

XML自定义分页

UserMapper中定义接口方法

    /**
     * 通过年龄查询用户信息并分页
     * @param page
     * @param age
     * @return
     */
    Page<User> selectPageVo(@Param("page") Page<User> page, @Param("age") Integer age);

UserMapper.xml中编写sql语句

<!--    Page<User> selectPageVo(@Param("page") Page<User> page, @Param("age") Integer age);-->
    <select id="selectPageVo" resultType="user">
        select * from user where age > #{age}
    </select>

测试

    @Test
    public void testSelectPageVo(){
        Page<User> page = new Page<>(1,3);
        userMapper.selectPageVo(page, 20);
        System.out.println(page.getRecords());
        System.out.println(page.getPages());
        System.out.println(page.getTotal());
        System.out.println(page.hasNext());
        System.out.println(page.hasPrevious());
    }

乐观锁

乐观锁执行流程：

在数据库中添加version字段，取出记录时，获取当前的version。

select id,name,price,version from t_product where id = 1

更新时，version + 1，如果where语句中的version版本不同，则更新失败

update t_product set price = price + 50, version = version + 1 where id = 1 and version = 1

在MyBatis-Plus中实现乐观锁：

• 修改实体类

package com.atguigu.mybatis_plus.pojo;

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

/**
 * @version 1.0
 * @Description
 * @Author 月上叁竿
 * @Date 2022-04-05 16:20
 **/
@Data
@TableName(value = "t_product")
public class Product {
    private Long id;
    private String name;
    private Integer price;
    @Version // 标识乐观锁版本号字段
    private Integer version;
}

• 添加乐观锁插件配置

package com.atguigu.mybatis_plus.config;

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

/**
 * @version 1.0
 * @Description
 * @Author 月上叁竿
 * @Date 2022-04-05 9:26
 **/
@Configuration
// 扫描Mapper接口所在的包
@MapperScan("com.atguigu.mybatis_plus.mapper")
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}