@TestpublicvoidtestAllEq(){QueryWrapper<Student> qw =newQueryWrapper<>();//组装条件Map<String,Object> param =newHashMap<>();//map<key,value> key列名 , value:查询的值
param.put("name","张三");
param.put("age",22);
param.put("status",1);
qw.allEq(param);//调用MP自己的查询方法//SELECT id,name,age,email,status FROM student WHERE name = ? AND age = ?//WHERE name = ? AND age = ? AND status = ?List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println(stu));}/**
* 1) Map对象中有 key的value是null
* 使用的是 qw.allEq(param,true);
* 结果:WHERE name = ? AND age IS NULL
* <p>
* 2) Map对象中有 key的value是null
* qw.allEq(param,false);
* 结果:WHERE name = ?
* <p>
* 结论:
* allEq(map,boolean)
* true:处理null值,where 条件加入 字段 is null
* false:忽略null ,不作为where 条件
*/@TestpublicvoidtestAllEq2(){QueryWrapper<Student> qw =newQueryWrapper<>();//组装条件Map<String,Object> param =newHashMap<>();//map<key,value> key列名 , value:查询的值
param.put("name","张三");//age 是 null
param.put("age",null);//allEq第二个参数为true
qw.allEq(param,false);//调用MP自己的查询方法List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println(stu));}
eq
/**
* eq使用
* eq("列名",值)
*/@TestpublicvoidtestEq(){QueryWrapper<Student> qw =newQueryWrapper<>();//组成条件
qw.eq("name","李四");//WHERE name = ?List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println("查询eq:"+ stu));}
ne 不等于
/**
* ne使用
* ne表示不等于 <>
* <p>
* ne("列名",值)
*/@TestpublicvoidtestNe(){QueryWrapper<Student> qw =newQueryWrapper<>();//组成条件
qw.ne("name","张三");// WHERE name <> ?List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println("查询ne:"+ stu));}
gt 大于
/**
* gt 大于( > )
*/@TestpublicvoidtestGt(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.gt("age",30);//age > 30// WHERE age > ?List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println("stu:"+ stu));}
ge 大于等于
/**
* ge 大于等于 ( >=)
*/@TestpublicvoidtestGe(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.ge("age",31);// >=31//WHERE age >= ?List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println("student:"+ stu));}
lt 小于
/**
* lt 小于 ( < )
*/@TestpublicvoidtestLt(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.lt("age",32);// WHERE age < ?List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println("student:"+ stu));}
le 小于
/**
* le 小于 ( <= )
*/@TestpublicvoidtestLe(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.le("age",32);// WHERE age <= ?List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println("student:"+ stu));}
between
/**
* between ( ? and ? )
*/@TestpublicvoidtestBetween(){QueryWrapper<Student> qw =newQueryWrapper<>();//between("列名",开始值,结束值)
qw.between("age",22,28);// where age >= 12 and age < 28List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println(stu));}
notBetween(不在范围区间内)
/**
* notBetween(不在范围区间内)
*/@TestpublicvoidtestNotBetween(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.notBetween("age",18,28);//WHERE age NOT BETWEEN ? AND ?// where age < 18 or age > 28List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println(stu));}
like 匹配某个值
/**
* like 匹配某个值
*/@TestpublicvoidtestLike(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.like("name","张");// WHERE name LIKE %张%List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println(stu));}
notLike 不匹配某个值
/**
* notLike 不匹配某个值
*/@TestpublicvoidtestNotLike(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.notLike("name","张");// WHERE name NOT LIKE ? %张%List<Student> students = studentDao.selectList(qw);
students.forEach(stu ->System.out.println(stu));}
likeLeft “%值”
/**
* likeLeft "%值"
*/@TestpublicvoidtestLikeLeft(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.likeLeft("name","张");//WHERE name LIKE %张List<Student> students = studentDao.selectList(qw);
students.forEach(student ->System.out.println(student));}
likeRight “%值”
/**
* likeRight "%值"
*/@TestpublicvoidtestLikeRight(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.likeRight("name","李");//WHERE name LIKE 李%List<Student> students = studentDao.selectList(qw);
students.forEach(student ->System.out.println(student));}
isNull , 判断字段是 null
/**
* isNull , 判断字段是 null
*/@TestpublicvoidtestIsNull(){QueryWrapper<Student> qw =newQueryWrapper<>();//判断email is null//WHERE email IS NULL
qw.isNull("email");print(qw);}
isNotNull , 判断字段是 is not null
/**
* isNotNull , 判断字段是 is not null
*/@TestpublicvoidtestIsNotNull(){QueryWrapper<Student> qw =newQueryWrapper<>();// WHERE email IS NOT NULL
qw.isNotNull("email");print(qw);}
in 值列表
/**
* in 值列表
*/@TestpublicvoidtestIn(){QueryWrapper<Student> qw =newQueryWrapper<>();//in(列名,多个值的列表)//WHERE name IN (?,?,?)
qw.in("name","张三","李四","周丽");print(qw);}
notIn 不在值列表
/**
* notIn 不在值列表
*/@TestpublicvoidtestNoIn(){QueryWrapper<Student> qw =newQueryWrapper<>();//in(列名,多个值的列表)//WHERE name NOT IN (?,?,?)
qw.notIn("name","张三","李四","周丽");print(qw);}
in 值列表
/**
* in 值列表
*/@TestpublicvoidtestIn2(){QueryWrapper<Student> qw =newQueryWrapper<>();List<Object> list =newArrayList<>();
list.add(1);
list.add(2);//WHERE status IN (?,?)
qw.in("status",list);print(qw);}
inSql() : 使用子查询
/**
* inSql() : 使用子查询
*/@TestpublicvoidtestInSQL(){QueryWrapper<Student> qw =newQueryWrapper<>();//WHERE age IN (select age from student where id=1)
qw.inSql("age","select age from student where id=1");print(qw);}
notInSql() : 使用子查询
/**
* notInSql() : 使用子查询
*/@TestpublicvoidtestNotInSQL(){QueryWrapper<Student> qw =newQueryWrapper<>();//WHERE age NOT IN (select age from student where id=1)
qw.notInSql("age","select age from student where id=1");print(qw);}privatevoidprint(QueryWrapper qw){List<Student> students = studentDao.selectList(qw);
students.forEach(student ->System.out.println(student));}
groupBy:分组
/**
* groupBy:分组
*/@TestpublicvoidtestGroupby(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.select("name,count(*) personNumbers");//select name,count(*) personNumbers
qw.groupBy("name");// SELECT name,count(*) personNumbers FROM student GROUP BY nameprint(qw);}
orderbyAsc : 按字段升序
/**
* orderbyAsc : 按字段升序
*/@TestpublicvoidtestOrderByAsc(){QueryWrapper<Student> qw=newQueryWrapper<>();//FROM student ORDER BY name ASC , age ASC
qw.orderByAsc("name","age");print(qw);}
orderbyDesc : 按字段降序
/**
* orderbyDesc : 按字段降序
*/@TestpublicvoidtestOrderByDesc(){QueryWrapper<Student> qw=newQueryWrapper<>();// ORDER BY name DESC , id DESC
qw.orderByDesc("name","id");print(qw);}
order :指定字段和排序方向
/**
* order :指定字段和排序方向
*
* boolean condition : 条件内容是否加入到 sql语句的后面。
* true:条件加入到sql语句
* FROM student ORDER BY name ASC
*
* false:条件不加入到sql语句
* FROM student
*/@TestpublicvoidtestOrder(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.orderBy(true,true,"name").orderBy(true,false,"age").orderBy(true,false,"email");// name asc, age desc , email desc//FROM student ORDER BY name ASC , age DESC , email DESCprint(qw);}
and ,or方法
/**
* and ,or方法
*/@TestpublicvoidtestOr(){QueryWrapper<Student> qw=newQueryWrapper<>();//WHERE name = ? OR age = ?
qw.eq("name","张三").or().eq("age",22);print(qw);}
last : 拼接sql语句到MP的sql语句的最后
/**
* last : 拼接sql语句到MP的sql语句的最后
*/@TestpublicvoidtestLast(){QueryWrapper<Student> qw =newQueryWrapper<>();//SELECT id,name,age,email,status FROM student WHERE name = ? OR age = ? limit 1
qw.eq("name","张三").or().eq("age",22).last("limit 1");print(qw);}
exists : 判断条件
/**
* exists : 判断条件
*
* notExists
*/@TestpublicvoidtestExists(){QueryWrapper<Student> qw=newQueryWrapper<>();//SELECT id,name,age,email,status FROM student// WHERE EXISTS (select id from student where age > 20)//qw.exists("select id from student where age > 90");//SELECT id,name,age,email,status FROM student WHERE// NOT EXISTS (select id from student where age > 90)
qw.notExists("select id from student where age > 90");print(qw);}
分页
/**
* 分页:
* 1.统计记录数,使用count(1)
* SELECT COUNT(1) FROM student WHERE age > ?
* 2.实现分页,在sql语句的末尾加入 limit 0,3
* SELECT id,name,age,email,status FROM student WHERE age > ? LIMIT 0,3
*/@TestpublicvoidtestPage(){QueryWrapper<Student> qw =newQueryWrapper<>();
qw.gt("age",22);IPage<Student> page =newPage<>();//设置分页的数据
page.setCurrent(1);//第一页
page.setSize(3);// 每页的记录数IPage<Student> result = studentDao.selectPage(page,qw);//获取分页后的记录List<Student> students = result.getRecords();System.out.println("students.size()="+students.size());//分页的信息long pages = result.getPages();System.out.println("页数:"+pages);System.out.println("总记录数:"+result.getTotal());System.out.println("当前页码:"+result.getCurrent());System.out.println("每页的记录数:"+result.getSize());}