mybatis-plus-查詢和分頁
- 1.查詢構造器:wapper
- 2.查詢
-
- 2.1初始化表MyStudent
- 2.2進行查詢
- 3.分頁
1.查詢構造器:wapper
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnL2cjN3ETYyUDZ5QWZykzM4Q2MzQjZ1EGMkVGZ4EjMilzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
QueryWrapper(LambdaQueryWrapper)和UpdateWrapper(LambdaUpdateWrapper)的父類用于生成sql的where條件,entity屬性也用于生成sql的where條件。MP3.X開始支援lambda表達式,LambdaQueryWrapper,LambdaUpdateWrapper支援lambda表達式的構造條件查詢。
- 條件:
條件 | 說明 |
---|---|
allEq | 基于map的相等 |
eq | 等于= |
ne | 不等于<> |
gt | 大于> |
ge | 大于等于>= |
lt | 小于 < |
le | 小于等于<= |
between | BETWEEN 值1 AND 值2 |
notBetween | NOT BETWEEN 值1 AND 值2 |
like | LIKE ‘%值%’ |
notLike | NOT LIKE ‘%值%’ |
likeLeft | LIKE ‘%值’ |
likeRight | LIKE ‘值%’ |
isNull | 字段 IS NULL |
isNotNull | 字段 IS NOT NULL |
in | 字段 IN(value1,value2,…) |
notIn | 字段 NOT IN(value1,value2,…) |
inSql | 字段 IN(sql語句) 例:InSql(“age”,“1,2,3”) --> age in(1,2,3) 例:inSql(“id”,“select id from table where id<3”) --> id in (select id from table where id<3) |
notInSql | 字段 NOT IN (sql語句) |
groupBy | GROUP BY 字段 |
orderByAsc | 升序 ORDER BY 字段,…ASC |
orderByDesc | 降序 ORDER BY 字段,…DESC |
orderBy | 自定義字段排序 orderBy(true,true,“id”,“name”) --> order by id ASC,name ASC |
having | 條件分組 |
or | OR語句,拼接 +OR 字段=值 |
and | AND語句,拼接 +AND 字段加值 |
apply | 拼接sql |
last | 在sql語句後拼接自定義條件 |
exists | 拼接EXISTS(sql語句)例:exists(“select id from table where age = 1”) --> exists(select id from table where age = 1) |
notExists | 拼接 NOT EXISTS(sql語句) |
nested | 正常嵌套 不帶AND或者OR |
- 1)QueryWrapper:查詢條件封裝類
方法 | 說明 |
---|---|
select | 設定查詢字段select後面的内容 |
- 2)UpdateWrapper:更新條件封裝類
方法 | 說明 |
---|---|
set | 設定要更新的字段,MP拼接sql語句 |
setSql | 參數是sql語句,MP不再處理語句 |
2.查詢
2.1初始化表MyStudent
2.2進行查詢
-
1)allEq
以Map為參數條件
@Test
public void testAllEq(){
QueryWrapper<MyStudent> 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 my_student WHERE name = ? AND age = ?
//WHERE name = ? AND age = ? AND status = ?
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println(item));
}
1.Map對象中有key的value是null
使用的是qw.allEq(param,true);
結果:WHERE name = ? AND age IS NULL
2.Map對象中有key的value是null
qw.allEq(param,false);
結果:WHERE name = ?
結論:
allEq(map,boolean)
true:處理null值,where條件加入字段is null
false:忽略null,不作為where條件
@Test
public void testAllEq2(){
QueryWrapper<MyStudent> 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<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println(item));
}
- 2)eq
/**
* eq使用
* eq("列名",值)
*/
@Test
public void testEq(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//組成條件
qw.eq("name","李四");
//WHERE name = ?
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println("查詢eq:"+item));
}
- 3)ne
/**
* ne使用
* ne表示不等于<>
* ne("列名",值)
*/
@Test
public void testNe(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//組成條件
qw.ne("name","張三");
//WHERE name <> ?
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println("查詢ne:"+item));
}
- 4)gt
/**
* gt使用
* gt表示大于(>)
*/
@Test
public void testGt(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.gt("age",30);//age>30
//WHERE age > ?
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println("stu:"+item));
}
- 5)ge
/**
* ge 大于等于 (>=)
*/
@Test
public void testGe(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.ge("age",31);//>=31
//WHERE age >= ?
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println("stu:"+item));
}
- 6)lt
/**
* lt 小于 (<)
*/
@Test
public void testLt(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.lt("age",32);//age<32
//WHERE age < ?
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println("stu:"+item));
}
- 7)le
/**
* le 小于等于 (<=)
*/
@Test
public void testLe(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.le("age",32);//age<=32
//WHERE age <= ?
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println("stu:"+item));
}
- 8)between
/**
* between (? and ?)
*/
@Test
public void testBetween(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//between("列名",開始值,結束值)
qw.between("age",22,28);
//WHERE age BETWEEN ? AND ?
//WHERE age >= 22 and age <= 28
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println("stu:"+item));
}
- 9)notBetween
/**
* notBetween(不在範圍區間内)
*/
@Test
public void tsetNotBetween(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.notBetween("age",18,28);
//WHERE age NOT BETWEEN ? AND ?
//where age < 18 or age >28
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println(item));
}
- 10)like
/**
* like 比對某個值
*/
@Test
public void testLike(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.like("name","張");
//WHERE name LIKE %張%
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println(item));
}
- 11)notLike
/**
* notLike 不比對某個值
*/
@Test
public void testNotLike(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.notLike("name","張");
//WHERE name NOT LIKE %張%
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println(item));
}
- 12)likeLeft
/**
* likeLeft "%值"
*/
@Test
public void testLikeLeft(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.likeLeft("name","張");
//WHERE name LIKE %張
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println(item));
}
- 13)likeRight
/**
* likeRight "%值"
*/
@Test
public void testLikeRight(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.likeRight("name","李");
//WHERE name LIKE 李%
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(item-> System.out.println(item));
}
- 14)isNull
/**
* isNull,判斷換字段是null
*/
@Test
public void testIsNull(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//判斷email is null
//WHERE email IS NULL
qw.isNull("email");
print(qw);
}
- 15)isNotNull
/**
* isNotNull,判斷換字段不是null
*/
@Test
public void testIsNotNull(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//判斷email is not null
//WHERE email IS NOT NULL
qw.isNotNull("email");
print(qw);
}
- 16)in
/**
*in 值清單
*/
@Test
public void testIn(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//in(列名,多個值的清單)
//WHERE name IN (?,?,?)
qw.in("name","張三","李四","周麗");
print(qw);
}
/**
*in 值清單
*/
@Test
public void testIn2(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
List<Object> list = new ArrayList<>();
list.add(1);
list.add(2);
//WHERE status IN (?,?)
qw.in("status",list);
print(qw);
}
- 17notIn
/**
*notIn 值清單
*/
@Test
public void testNotIn(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//notIn(列名,多個值的清單)
//WHERE name NOT IN (?,?,?)
qw.notIn("name","張三","李四","周麗");
print(qw);
}
- 18)inSql
/**
*inSql():使用子查詢
*/
@Test
public void testInSql(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//WHERE age IN (select age from my_student where id=1)
qw.inSql("age","select age from my_student where id=1");
print(qw);
}
- 19)notInSql
/**
*notInSql():使用子查詢
*/
@Test
public void testNotInSql(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//WHERE age NOT IN (select age from my_student where id=1)
qw.notInSql("age","select age from my_student where id=1");
print(qw);
}
private void print(QueryWrapper qw){
List<MyStudent> myStudents = myStudentsDao.selectList(qw);
myStudents.forEach(System.out::println);
}
- 20)groupBy
/**
* groupBy:分組
*/
@Test
public void testGroupBy(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.select("name,count(*) personNumbers");//select name count(*) personNumbers
qw.groupBy("name");
//SELECT name,count(*) personNumbers FROM my_student GROUP BY name
print(qw);
}
- 21)orderByAsc
/**
* orderByAsc:按升序排序
*/
@Test
public void testOrderByAsc(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.orderByAsc("name","age");
//FROM my_student ORDER BY name ASC , age ASC
print(qw);
}
- 22)orderByDesc
/**
* orderByDesc:按降序排序
*/
@Test
public void testOrderByDesc(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.orderByDesc("name","id");
//FROM my_student ORDER BY name DESC , id DESC
print(qw);
}
-
23)orderBy
orderBy:指定字段和排序的方向
boolean condition:條件内容是否加入到sql語句的後面。
true:條件添加到sql語句
FROM my_student ORDER BY name ASC
false:條件不添加到sql語句
FROM my_student
@Test
public void testOrderBy(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.orderBy(true,true,"name")
.orderBy(true,false,"age")
.orderBy(true,false,"email");
//name asc,age desc,email desc
//FROM my_student ORDER BY name ASC , age DESC , email DESC
print(qw);
}
- 24)and,or
/**
* and,or方法
*/
@Test
public void testOr(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//WHERE name = ? OR age = ?
qw.eq("name","張三")
.or()
.eq("age",22);
print(qw);
}
- 25)last
/**
* last:拼接sql語句到MP的sql語句的最後
*/
@Test
public void testLast(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//SELECT id,name,age,email,status FROM my_student WHERE name = ? OR age = ? limit 1
qw.eq("name","張三")
.or()
.eq("age",22)
.last("limit 1");
print(qw);
}
- 26)exists
/**
* exists:判斷條件
*
* notExists
*/
@Test
public void testExists(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
//SELECT id,name,age,email,status FROM my_student
// WHERE EXISTS (select id from my_student where age > 20)
qw.exists("select id from my_student where age > 20");
//SELECT id,name,age,email,status FROM my_student
//WHERE NOT EXISTS (select id from my_student where age > 90)
qw.notExists("select id from my_student where age > 90");
print(qw);
}
3.分頁
- 前提:配置分頁插件,實作實體分頁。預設是記憶體分頁
package com.putao.plus.config;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* @Configuration标注是類就相當于xml配置檔案
*/
@Configuration
public class Config {
/**
* 定義方法,傳回值是java對象,這個對象放入到spring容器中
* 使用@Bean修飾方法
* @Bean等同于<bean></bean>
*/
@Bean
public PaginationInterceptor paginationInterceptor(){
return new PaginationInterceptor();
}
}
-
分頁查詢
分頁:
1.統計記錄數,使用count(1)
SELECT COUNT(1) FROM my_student WHERE age > ?
2.實作分頁,在sql語句末尾加入limit0,3
SELECT id,name,age,email,status FROM my_student WHERE age > ? LIMIT 0,3
@Test
public void testPage(){
QueryWrapper<MyStudent> qw = new QueryWrapper<>();
qw.gt("age",22);
IPage<MyStudent> page = new Page<>();
//設定分頁的資料
page.setCurrent(1);//第一頁
page.setSize(3);//每頁的記錄數
IPage<MyStudent> result = myStudentsDao.selectPage(page, qw);
//擷取分頁後的記錄
List<MyStudent> myStudents = result.getRecords();
System.out.println("myStudents.size:"+myStudents.size());
//分頁的資訊
long pages = result.getPages();
System.out.println("頁數:"+pages);
System.out.println("總記錄"+result.getTotal());
System.out.println("目前頁碼:"+result.getCurrent());
System.out.println("每頁的記錄"+result.getSize());
}