package com.zjh.myabatiesplus;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.zjh.myabatiesplus.mapper.UserMapper;
import com.zjh.myabatiesplus.pojo.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;
import java.util.Map;

@SpringBootTest
public class MybatisPlusQueryWrapper {
    //mp的查询条件组装器
    @Autowired
    UserMapper userMapper;
    @Test
    void t1(){
        //泛型中是实体类
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        //条件组装器是可以先嵌套来定义组合查询条件,然后扔进对应的Mapper中
        queryWrapper.like("NAME","1")//模糊查询 字段名与字段值 然后作为一个整体 然后调用下一个方法
                .between("age",1,1) //两者范围之间 第一个参数是字段名 然后是 大于等于值 小于等于值
                .isNotNull("email");//查询非空字段
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }
    //查询一组数据把年龄升序,若年龄相同id降序查询
    @Test
    void t2(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<User>();
        queryWrapper.orderByAsc("age")//升序序age已经排列好
                .orderByDesc("id")//
                .between("id",0L,6L);
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
    //查询条件组装器也可以用来来修改数据
    //将年龄大于20且用户名中含有o 或 邮箱为1的用户进行修改
    @Test
    void t3(){
        QueryWrapper<User> userQueryWrapper=new QueryWrapper<User>();
        userQueryWrapper.gt("age",20) //gt 是大于 lt是小于  ,将它查到的数据作为一个整体 下一个条件就相当于直接有and拼接
                .like("NAME","O")
                .or()
                .like("email","1");
        User user=new User();
        user.setEmail("1@qq.com");
        int update = userMapper.update(user, userQueryWrapper);
        System.out.println(update);
    }
    //优先条件
    //将用户名中含有o并且 邮箱为1@qq.com或年龄大于20的用户修改
    @Test
    void t4(){
    QueryWrapper<User> userQueryWrapper=new QueryWrapper<User>();
    userQueryWrapper.like("NAME","o")
            .and(param->param.like("email","1@qq.com").or().gt("age",20));
    //and中lamda表达式的语句会优先执行
        User user=new User();
        user.setEmail("20");
        int update = userMapper.update(user, userQueryWrapper);
        System.out.println(update);
    }
    //查询你想要的字段
    @Test
    void t5(){
        QueryWrapper<User> userQueryWrapper=new QueryWrapper<User>();
        userQueryWrapper.select("NAME","age","email");
        List<Map<String, Object>> mapList = userMapper.selectMaps(userQueryWrapper);
        mapList.forEach(System.out::println);
    }
    //子查询语句
    @Test
    void t6(){
        QueryWrapper<User> userQueryWrapper=new QueryWrapper<>();
        userQueryWrapper.inSql("id","select id from user where id<=6");
        //他的意思是这样
        // SELECT id,name,age,email,isDelete AS isDel FROM user WHERE isDelete=0 AND (id IN (select id from user where id<=6))
        //第一个参数要写字段名并且对应子查询SQL语句中查询出来的字段名 子查询中查出来的所有字段值被当做对照然后再查询遍历
        List<User> users = userMapper.selectList(userQueryWrapper);
        users.forEach(System.out::println);
    }
    //UpdataWrapper的使用
    //将用户名中含有o并且 邮箱为1@qq.com或年龄大于20的用户修改
    @Test
    void T1(){
        UpdateWrapper<User> userUpdateWrapper=new UpdateWrapper<>();
        userUpdateWrapper.like("NAME","o")
                .and(i->i.like("email","1@qq.com").or().gt("age",20))
                .set("age",99).set("email","77@qq.com");
        int update = userMapper.update(null, userUpdateWrapper);
        System.out.println(update);
    }

}