Mybatis-plus

使用复杂条件查询需要使用条件构造器

@Autowired
private UserMapper userMapper;

@Test
//查询年龄大于20小于30并且姓名包含a的用户
public void selectList(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("name","a").between("age",20,30);
    userMapper.selectList(queryWrapper);
}

使用条件构造器使用删除功能

@Test
public void test03(){
    //删除邮箱地址为null的用户信息
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.isNull("email");
    int delete = userMapper.delete(queryWrapper);
    System.out.println(delete);
}

条件查询链式编程条件之间默认是用and连接或者用or（）连接

使用条件查询进行删除操作

@Test
    public void test04(){
        //将年龄大于而是并且用户名中包含a或者邮箱为null的用户信息修改

        //1使用条件构造器查询出来要修改的结果
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.gt("age","20")
                .like("name","a")
                .or()
                .isNull("email");
        //将名字设为小明，邮箱设为1326580556@qq.com
        User user = new User();
        user.setName("小明");
        user.setEmail("1326580556@qq.com");
        int result = userMapper.update(user,queryWrapper);
        System.out.println("result"+result);
    }
}

设置条件执行的优先性

用and（lamada表达式）表示条件的优先执行

@Test
UPDATE user SET name=?, email=? WHERE (name LIKE ? AND (age > ? OR email IS NULL))
//条件的优先性
public void test05(){
    //将用户名中包含a并且（年龄大于20或者邮箱为null）的用户信息修改
    //lamada表达式中的条件优先执行
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("name","a").and(i->i.gt("age",20).or().isNull("email"));
    User user = new User();
    user.setName("小明");
    user.setEmail("1326580556@qq.com");
     int result = userMapper.update(user,queryWrapper); System.out.println("result"+result);

}

查询用户的部分信息

@Test
public void test06(){
    //查询部分条件
    //SELECT name,age,email FROM user
    //查询用户的用户名年龄和邮箱信息
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("name","age","email");
    List<Map<String,Object>> maps = userMapper.selectMaps(queryWrapper);
    maps.forEach(System.out::println);
}

子查询

用inSql("id","select uid from user where uid<=100");

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

通过updateWapper来设置条件

@Test
//通过updateWrapper来写条件构造
public void test08(){
    //==>  Preparing: UPDATE user SET name=?,email=? WHERE (name LIKE ? AND (age > ? AND email IS NULL))
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    updateWrapper.like("name","a").and(i -> i.gt("age",20).isNull("email"));
    updateWrapper.set("name","小黑").set("email","1326580556@qq.com");
    int result = userMapper.update(null, updateWrapper);
    System.out.println("result"+result);
}

条件判断查询

@Test
public void test09(){
    String username = "";
    Integer ageBegin = 20;
    Integer ageEnd = 30;
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    if(StringUtils.isNotBlank(username)){
        //isNotBlank表示某个字符串不为空，不为null，不为空白字符串
        queryWrapper.like("name",username);
    }
    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);
}

条件查询简化版

@Test
public void test10(){
     String username = "a";
     Integer ageBegin = null;
     Integer ageEnd = 30;
     QueryWrapper<User> queryWrapper = new QueryWrapper<>();
     queryWrapper.like(StringUtils.isNotBlank(username),"name",username)
             .ge(ageBegin != null,"age",ageBegin)
             .le(ageEnd!=null,"age",ageEnd);
     List<User> list = userMapper.selectList(queryWrapper);
     list.forEach(System.out::println);
}

lamadaQuerryMapper

对象的使用的是函数式接口

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

LambdaUpdateWarpper

@Test

public void test12(){
    //==>  Preparing: UPDATE user SET name=?,email=? WHERE (name LIKE ? AND (age > ? AND email IS NULL))
    LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
    updateWrapper.like(User::getName,"a").and(i -> i.gt(User::getAge,20).isNull(User::getEmail));
    updateWrapper.set(User::getName,"小黑").set(User::getEmail,"1326580556@qq.com");
    int result = userMapper.update(null, updateWrapper);
    System.out.println("result"+result);
}

分页设置

要先配置一个config配置类

@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //添加分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        //添加乐观锁插件
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

然后在通过page对象传参

@SpringBootTest
public class MybatisPlusPluginTest {
    @Autowired
    private UserMapper userMapper;

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

自定义分页语句

现在usermapper接口里设置返回类型为page，并且第一个参数为page

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

然后在配置usermapepr.xml文件

<select id="selectPageVo" resultType="com.xv.mybatisplus.pojo.User">
     select  id,name,age,email from user where age > #{age}
</select>

在配置文件配置别名就可以在xml文件返回值类型返回值写类名不区分大小写（User）

type-aliases-package: com.xv.mybatisplus.pojo

@Test
public void testPageVo(){
    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注解

@Data
public class Product {
    private Long id;
    private String name;
    private Integer price;
    @Version
    private Integer version;
}

然后在配置类配置乐观锁插件

@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //添加分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        //添加乐观锁插件
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

+

@Test
public void testProduct01(){
    //小李查询商品价格
    Product productLi = productMapper.selectById(1);
    System.out.println(" 小李查询的商品价格是："+productLi.getPrice());

    //小王才查询的商品价格
    Product productWang = productMapper.selectById(1);

    //小李将商品价格+50
    productLi.setPrice(productLi.getPrice()+50);
    productMapper.updateById(productLi);
    //小王将价格-30
    productWang.setPrice(productWang.getPrice()-30);
    int result = productMapper.updateById(productWang);
    if (result == 0){
        //操作失败，重试
        Product productNew = productMapper.selectById(1);
        productNew.setPrice(productNew.getPrice()-30);
        productMapper.updateById(productNew);

    }

    //老板查询商品价格
    Product productBoss = productMapper.selectById(1);
    System.out.println(" 老版查询的商品价格是："+productBoss.getPrice());

}