目錄
- 條件構造器
-
- allEq
- eq
- ne 不等于
- gt 大于
- ge 大于等于
- lt 小于
- le 小于
- between
- notBetween(不在範圍區間内)
- like 比對某個值
- notLike 不比對某個值
- likeLeft "%值"
- likeRight "%值"
- isNull , 判斷字段是 null
- isNotNull , 判斷字段是 is not null
- in 值清單
- notIn 不在值清單
- in 值清單
- inSql() : 使用子查詢
- notInSql() : 使用子查詢
- groupBy:分組
- orderbyAsc : 按字段升序
- orderbyDesc : 按字段降序
- order :指定字段和排序方向
- and ,or方法
- last : 拼接sql語句到MP的sql語句的最後
- exists : 判斷條件
- 分頁
條件構造器
allEq
@Test
public void testAllEq() {
QueryWrapper<Student> qw = new QueryWrapper<>();
//組裝條件
Map<String, Object> param = new HashMap<>();
//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 條件
*/
@Test
public void testAllEq2() {
QueryWrapper<Student> qw = new QueryWrapper<>();
//組裝條件
Map<String, Object> param = new HashMap<>();
//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("列名",值)
*/
@Test
public void testEq() {
QueryWrapper<Student> qw = new QueryWrapper<>();
//組成條件
qw.eq("name", "李四");
//WHERE name = ?
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查詢eq:" + stu));
}
ne 不等于
/**
* ne使用
* ne表示不等于 <>
* <p>
* ne("列名",值)
*/
@Test
public void testNe() {
QueryWrapper<Student> qw = new QueryWrapper<>();
//組成條件
qw.ne("name", "張三");
// WHERE name <> ?
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查詢ne:" + stu));
}
gt 大于
/**
* gt 大于( > )
*/
@Test
public void testGt() {
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.gt("age", 30); //age > 30
// WHERE age > ?
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("stu:" + stu));
}
ge 大于等于
/**
* ge 大于等于 ( >=)
*/
@Test
public void testGe() {
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.ge("age", 31);// >=31
//WHERE age >= ?
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("student:" + stu));
}
lt 小于
/**
* lt 小于 ( < )
*/
@Test
public void testLt() {
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.lt("age", 32);
// WHERE age < ?
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("student:" + stu));
}
le 小于
/**
* le 小于 ( <= )
*/
@Test
public void testLe() {
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.le("age", 32);
// WHERE age <= ?
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("student:" + stu));
}
between
/**
* between ( ? and ? )
*/
@Test
public void testBetween() {
QueryWrapper<Student> qw = new QueryWrapper<>();
//between("列名",開始值,結束值)
qw.between("age", 22, 28);
// where age >= 12 and age < 28
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println(stu));
}
notBetween(不在範圍區間内)
/**
* notBetween(不在範圍區間内)
*/
@Test
public void testNotBetween() {
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.notBetween("age", 18, 28);
//WHERE age NOT BETWEEN ? AND ?
// where age < 18 or age > 28
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println(stu));
}
like 比對某個值
/**
* like 比對某個值
*/
@Test
public void testLike() {
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.like("name", "張");
// WHERE name LIKE %張%
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println(stu));
}
notLike 不比對某個值
/**
* notLike 不比對某個值
*/
@Test
public void testNotLike() {
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.notLike("name", "張");
// WHERE name NOT LIKE ? %張%
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println(stu));
}
likeLeft “%值”
/**
* likeLeft "%值"
*/
@Test
public void testLikeLeft() {
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.likeLeft("name", "張");
//WHERE name LIKE %張
List<Student> students = studentDao.selectList(qw);
students.forEach(student -> System.out.println(student));
}
likeRight “%值”
/**
* likeRight "%值"
*/
@Test
public void testLikeRight() {
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.likeRight("name", "李");
//WHERE name LIKE 李%
List<Student> students = studentDao.selectList(qw);
students.forEach(student -> System.out.println(student));
}
isNull , 判斷字段是 null
/**
* isNull , 判斷字段是 null
*/
@Test
public void testIsNull(){
QueryWrapper<Student> qw = new QueryWrapper<>();
//判斷email is null
//WHERE email IS NULL
qw.isNull("email");
print(qw);
}
isNotNull , 判斷字段是 is not null
/**
* isNotNull , 判斷字段是 is not null
*/
@Test
public void testIsNotNull(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// WHERE email IS NOT NULL
qw.isNotNull("email");
print(qw);
}
in 值清單
/**
* in 值清單
*/
@Test
public void testIn(){
QueryWrapper<Student> qw = new QueryWrapper<>();
//in(列名,多個值的清單)
//WHERE name IN (?,?,?)
qw.in("name","張三","李四","周麗");
print(qw);
}
notIn 不在值清單
/**
* notIn 不在值清單
*/
@Test
public void testNoIn(){
QueryWrapper<Student> qw = new QueryWrapper<>();
//in(列名,多個值的清單)
//WHERE name NOT IN (?,?,?)
qw.notIn("name","張三","李四","周麗");
print(qw);
}
in 值清單
/**
* in 值清單
*/
@Test
public void testIn2(){
QueryWrapper<Student> qw = new QueryWrapper<>();
List<Object> list = new ArrayList<>();
list.add(1);
list.add(2);
//WHERE status IN (?,?)
qw.in("status",list);
print(qw);
}
inSql() : 使用子查詢
/**
* inSql() : 使用子查詢
*/
@Test
public void testInSQL(){
QueryWrapper<Student> qw = new QueryWrapper<>();
//WHERE age IN (select age from student where id=1)
qw.inSql("age","select age from student where id=1");
print(qw);
}
notInSql() : 使用子查詢
/**
* notInSql() : 使用子查詢
*/
@Test
public void testNotInSQL(){
QueryWrapper<Student> qw = new QueryWrapper<>();
//WHERE age NOT IN (select age from student where id=1)
qw.notInSql("age","select age from student where id=1");
print(qw);
}
private void print(QueryWrapper qw){
List<Student> students = studentDao.selectList(qw);
students.forEach(student -> System.out.println(student));
}
groupBy:分組
/**
* groupBy:分組
*/
@Test
public void testGroupby(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.select("name,count(*) personNumbers");//select name,count(*) personNumbers
qw.groupBy("name");
// SELECT name,count(*) personNumbers FROM student GROUP BY name
print(qw);
}
orderbyAsc : 按字段升序
/**
* orderbyAsc : 按字段升序
*/
@Test
public void testOrderByAsc(){
QueryWrapper<Student> qw= new QueryWrapper<>();
//FROM student ORDER BY name ASC , age ASC
qw.orderByAsc("name","age");
print(qw);
}
orderbyDesc : 按字段降序
/**
* orderbyDesc : 按字段降序
*/
@Test
public void testOrderByDesc(){
QueryWrapper<Student> qw= new QueryWrapper<>();
// 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
*/
@Test
public void testOrder(){
QueryWrapper<Student> qw = new QueryWrapper<>();
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 DESC
print(qw);
}
and ,or方法
/**
* and ,or方法
*/
@Test
public void testOr(){
QueryWrapper<Student> qw= new QueryWrapper<>();
//WHERE name = ? OR age = ?
qw.eq("name","張三")
.or()
.eq("age",22);
print(qw);
}
last : 拼接sql語句到MP的sql語句的最後
/**
* last : 拼接sql語句到MP的sql語句的最後
*/
@Test
public void testLast(){
QueryWrapper<Student> qw = new QueryWrapper<>();
//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
*/
@Test
public void testExists(){
QueryWrapper<Student> qw= new QueryWrapper<>();
//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
*/
@Test
public void testPage(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.gt("age",22);
IPage<Student> page = new Page<>();
//設定分頁的資料
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());
}