原文連結:https://mp.weixin.qq.com/s/9WOmclZjELTNdmdoG6H4oA
哈喽,大家好,我是指北君。
之前有一篇文章,我們介紹過通過 Mybatis Plus 進行增删改查。如下這段代碼:
/**
* 根據id修改
* UPDATE user SET user_name=?, user_age=? WHERE (id = ?)
*/
@Test
public void testudpateById(){
User user = new User();
user.setUserAge("25");
user.setUserName("test update");
UpdateWrapper updateWrapper = new UpdateWrapper();
updateWrapper.eq("id","3");
int num = userMapper.update(user, updateWrapper);
System.out.println("修改的記錄數為:"+num);
}
/**
* 查詢指定記錄
* SELECT id,user_name,user_age FROM user WHERE (user_name = ?)
*/
@Test
public void testSelectWrapper(){
QueryWrapper wrapper = new QueryWrapper();
wrapper.eq("user_name","IT可樂");
List<User> users = userMapper.selectList(wrapper);
users.forEach(x-> System.out.println(x.getId()+"-"+x.getUserName()+"-"+x.getUserAge()));
}
上面兩個方法分别是根據id修改表記錄,和根據user_name查詢記錄。構造的條件使用了 UpdateWrapper 和 QueryWrapper ,那麼這是什麼呢?其實 mybatis plus 通過條件構造器可以組成複雜的SQL語句。本篇部落格我們将詳細介紹。
1、Wrapper
Mybatis Plus 提供的幾種條件構造器,關系如下:
我們主要通過 QueryWrapper 和 UpdateWrapper 進行條件構造,這兩個和 LambdaQueryWrapper、LambdaUpdateWrapper 差不多是等價的,隻不過後者采用了 JDK1.8 提供的lambda 文法,使用起來更簡潔。
2、文法詳情總結
關于條件構造器的各個用法介紹,可以參考官網:https://mp.baomidou.com/guide/wrapper.html#abstractwrapper
這裡我們做一下總結:
方法名說明用法執行個體等價SQL官網位址https://mp.baomidou.com/guide/wrapper.html#abstractwrapper----::----:allEq(Map<R, V> params)全部等于map.put("id","3");map.put("user_name","IT可樂");allEq(map)user_name = "IT可樂" AND id = 3eq(R column, Object val)等于 =eq("id","3")id = 3ne(R column, Object val)不等于 <>ne("id", "3")id <> 3gt(R column, Object val)大于 >gt("user_age","18")user_age > 18ge(R column, Object val)大于等于 >=ge("user_age","18")user_age >= 18lt(R column, Object val)小于 <lt("user_age","18")user_age < 18le(R column, Object val)小于等于 <=le("user_age","18")user_age <= 18between(R column, Object val1, Object val2)BETWEEN 值1 AND 值2between("user_age","18","25")user_age BETWEEN 18 AND 25notBetween(R column, Object val1, Object val2)NOT BETWEEN 值1 AND 值2notBetween("user_age","18","25")user_age NOT BETWEEN 18 AND 25like(R column, Object val)LIKE '%值%'like("user_name","可樂")like ‘%可樂%’notLike(R column, Object val)NOT LIKE '%值%'notLike("user_name","可樂")not like ‘%可樂%’likeLeft(R column, Object val)LIKE '%值'likeLeft("user_name","可樂")like ‘%可樂’likeRight(R column, Object val)LIKE '值%'likeRight("user_name","可樂")like ‘可樂%’isNull(R column)字段 IS NULLisNull("user_name")user_name IS NULLisNotNull(R column)字段 IS NOT NULLisNotNull("user_name")user_name IS NOT NULLin(R column, Collection<?> value)字段 IN (value.get(0), value.get(1), ...)in("user_age",{1,2,3})user_age IN (?,?,?)notIn(R column, Collection<?> value)字段 NOT IN (value.get(0), value.get(1), ...)notIn("user_age",{1,2,3})user_age NOT IN (?,?,?)inSql(R column, String inValue)字段 IN ( sql語句 )inSql("id","select id from user")id IN (select id from user)notInSql(R column, String inValue)字段 NOT IN ( sql語句 )notInSql("id","select id from user where id > 2")id NOT IN (select id from user where id > 2groupBy(R... columns)分組:GROUP BY 字段, ...groupBy("id","user_age")GROUP BY id,user_ageorderByAsc(R... columns)排序:ORDER BY 字段, ... ASCorderByAsc("id","user_age")ORDER BY id ASC,user_age ASCorderByDesc(R... columns)排序:ORDER BY 字段, ... DESCorderByDesc("id","user_age")ORDER BY id DESC,user_age DESCorderBy(boolean condition, boolean isAsc, R... columns)ORDER BY 字段, ...orderBy(true,true,"id","user_age")ORDER BY id ASC,user_age ASChaving(String sqlHaving, Object... params)HAVING ( sql語句 )having("sum(user_age)>{0}","25")HAVING sum(user_age)>25or()拼接 OReq("id",1).or().eq("user_age",25)id = 1 OR user_age = 25and(Consumerconsumer)AND 嵌套and(i->i.eq("id",1).ne("user_age",18))id = 1 AND user_age <> 25nested(Consumerconsumer)正常嵌套 不帶 AND 或者 ORnested(i->i.eq("id",1).ne("user_age",18))id = 1 AND user_age <> 25apply(String applySql, Object... params)拼接 sql(不會有SQL注入風險)apply("user_age>{0}","25 or 1=1")user_age >'25 or 1=1'last(String lastSql)拼接到 sql 的最後,多次調用以最後一次為準(有sql注入的風險)last("limit 1")limit 1exists(String existsSql)拼接 EXISTS ( sql語句 )exists("select id from user where user_age = 1")EXISTS (select id from user where user_age = 1)notExists(String notExistsSql)拼接 NOT EXISTS ( sql語句 )notExists("select id from user where user_age = 1")NOT EXISTS (select id from user where user_age = 1)
3、文法詳情示範
對于上表出現的每個文法,這裡通過代碼展示出來。
更多可以參考位址:https://github.com/YSOcean/mybatisplusstudy.git
package com.ys.mybatisplusstudy;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ys.mybatisplusstudy.entry.User;
import com.ys.mybatisplusstudy.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@SpringBootTest
public class WrapperTest {
@Autowired
private UserMapper userMapper;
/**
* 新增一條記錄
*/
@Test
public void testInsert(){
User user = new User();
user.setId(4L);
user.setUserName("test insert");
user.setUserAge("1");
int insert = userMapper.insert(user);
System.out.println("影響記錄數:"+insert);
}
/**
* allEq 全部等于
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_name = ? AND id = ?)
*/
@Test
public void testAllEq(){
QueryWrapper queryWrapper = new QueryWrapper();
Map map = new HashMap<>();
map.put("id","3");
map.put("user_name","IT可樂");
queryWrapper.allEq(map);
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* eq 等于
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (id = ?)
*/
@Test
public void testEq(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.eq("id","3");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* ne 不等于
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (id <> ?)
*/
@Test
public void testNe(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.ne("id","3");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* gt 大于
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_age > ?)
*/
@Test
public void testGt(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.gt("user_age","18");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* ge 大于等于
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_age >= ?)
*/
@Test
public void testGe(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.ge("user_age","18");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* lt 小于
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_age < ?)
*/
@Test
public void testLt(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.lt("user_age","18");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* le 小于等于
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_age <= ?)
*/
@Test
public void testLe(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.le("user_age","18");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* between 值1和值2之間,兩邊臨界值都包含
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_age BETWEEN ? AND ?)
*/
@Test
public void testBetween(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.between("user_age","18","25");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* notBetween 不在值1和值2之間,兩邊臨界值都包含
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_age NOT BETWEEN ? AND ?)
*/
@Test
public void testNoBetween(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.notBetween("user_age","18","25");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* like 模糊查詢,會在參數左右兩邊加上 %
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_name LIKE ?)
*/
@Test
public void testLike(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.like("user_name","可樂");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* notLike NOT LIKE ‘%parameter%’
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_name NOT LIKE ?)
*/
@Test
public void testNotLike(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.notLike("user_name","可樂");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* likeLeft LIKE ‘%parameter’
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_name LIKE '%parameter')
*/
@Test
public void testLikeLeft(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.likeLeft("user_name","可樂");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* likeRight LIKE ‘parameter%’
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_name LIKE 'parameter%')
*/
@Test
public void testLikeRight(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.likeRight("user_name","可樂");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* isNull 判斷字段為null
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_name IS NULL)
*/
@Test
public void testIsNull(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.isNull("user_name");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* isNotNull 判斷字段不為null
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_name IS NOT NULL)
*/
@Test
public void testIsNotNull(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.isNotNull("user_name");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* in 範圍定值查詢
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_age IN (?,?,?))
*/
@Test
public void testIn(){
QueryWrapper queryWrapper = new QueryWrapper();
List<Integer> queryList = new ArrayList<>();
queryList.add(18);
queryList.add(1);
queryList.add(25);
queryWrapper.in("user_age",queryList);
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* notIn
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_age IN (?,?,?))
*/
@Test
public void testNotIn(){
QueryWrapper queryWrapper = new QueryWrapper();
List<Integer> queryList = new ArrayList<>();
queryList.add(18);
queryList.add(1);
queryList.add(25);
queryWrapper.notIn("user_age",queryList);
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* inSql
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (id IN (select id from user))
*/
@Test
public void testInSql(){
QueryWrapper queryWrapper = new QueryWrapper();
//查詢所有資料
queryWrapper.inSql("id","select id from user");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* notInSql
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (id NOT IN (select id from user where id > 2))
*/
@Test
public void testNotInSql(){
QueryWrapper queryWrapper = new QueryWrapper();
//查詢所有資料
queryWrapper.notInSql("id","select id from user where id > 2");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* groupBy 分組
* 下面SQL有個問題,在MySQL8.0版本中,是可以執行下面SQL語句的,select user_name并沒有出現在group by 語句中
* 執行個體SQL:SELECT id,user_name,user_age FROM user GROUP BY id,user_age
*/
@Test
public void testGroupBy(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.groupBy("id","user_age");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* orderByAsc 升序
* 執行個體SQL:SELECT id,user_name,user_age FROM user ORDER BY id ASC,user_age ASC
*/
@Test
public void testOrderByAsc(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.orderByAsc("id","user_age");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* orderByDesc 降序
* 執行個體SQL:SELECT id,user_name,user_age FROM user ORDER BY id DESC,user_age DESC
*/
@Test
public void testOrderByDesc(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.orderByDesc("id","user_age");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* orderBy 指定順序排序
* 執行個體SQL:SELECT id,user_name,user_age FROM user ORDER BY id ASC,user_age ASC
*/
@Test
public void testOrderBy(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.orderBy(true,true,"id","user_age");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* having
* 執行個體SQL:SELECT id,user_name,user_age FROM user GROUP BY id,user_age HAVING sum(user_age)>?
*/
@Test
public void testHaving(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.groupBy("id","user_age");
queryWrapper.having("sum(user_age)>{0}","25");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* having
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (id = ? OR user_age = ?)
*/
@Test
public void testOr(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.eq("id",1);
queryWrapper.or();
queryWrapper.eq("user_age",25);
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* and
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE ((id = ? AND user_age <> ?))
*/
@Test
public void testAnd(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.and(i->i.eq("id",1).ne("user_age",18));
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* nested
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE ((id = ? AND user_age <> ?))
*/
@Test
public void testNested(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(i->i.eq("id",1).ne("user_age",18));
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* apply
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (user_age>?)
*/
@Test
public void testApplyd(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("user_age>{0}","25 or 1=1");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* last
* 執行個體SQL:SELECT id,user_name,user_age FROM user limit 1
*/
@Test
public void testLast(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.last("limit 1 ");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* exists
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (EXISTS (select id from user where user_age = 1))
*/
@Test
public void testExists(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.exists("select id from user where user_age = 1");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* notExists
* 執行個體SQL:SELECT id,user_name,user_age FROM user WHERE (EXISTS (select id from user where user_age = 1))
*/
@Test
public void testNotExists(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notExists("select id from user where user_age = 1");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
}
4、LambdaQueryWrapper和LambdaUpdateWrapper(推薦)
LambdaQueryWrapper 和 LambdaUpdateWrapper 這是相對于 QueryWrapper 及 UpdateWrapper 的 Lmbda 文法實作方式。
分别通過如下兩種方式擷取:
//兩種方式
LambdaQueryWrapper queryLambda = new QueryWrapper().lambda();
LambdaQueryWrapper lambdaQueryWrapper = new LambdaQueryWrapper<>();
//兩種方式
LambdaUpdateWrapper updateLambda = new UpdateWrapper().lambda();
LambdaUpdateWrapper lambdaUpdateWrapper = new LambdaUpdateWrapper();
注意:擷取LambdaQueryWrapper 和 LambdaUpdateWrapper 對象時,為了使用lambda文法,要使用泛型。
下面我示範幾個執行個體:
/**
* LambdaQueryWrapper
* SQL執行個體:SELECT id,user_name,user_age FROM user WHERE (id = ? AND user_age <> ?)
*/
@Test
public void testLambdaQueryWrapper(){
LambdaQueryWrapper<User> queryLambda = new LambdaQueryWrapper<>();
queryLambda.eq(User::getId,"1").ne(User::getUserAge,25);
List<User> users = userMapper.selectList(queryLambda);
System.out.println(users);
}
/**
* LambdaQueryWrapper
* SQL執行個體:UPDATE user SET user_name=? WHERE (user_name = ?)
*/
@Test
public void testLambdaUpdateWrapper(){
User user = new User();
user.setUserName("LambdaUpdateWrapper");
LambdaUpdateWrapper<User> userLambdaUpdateWrapper = new LambdaUpdateWrapper<>();
userLambdaUpdateWrapper.eq(User::getUserName,"IT可樂");
userMapper.update(user,userLambdaUpdateWrapper);
}
5、總結
對于mybatis plus 中的四種條件構造器,我們就到此結束了,大家可以按照我的執行個體敲一遍代碼,基本上就沒啥問題了。
有沒有發現使用 Lambda 文法很爽,文法簡潔,另外有個優點是,使用QueryWrapper或者UpdateWrapper時,對于條件的某個列,我們是寫的字元串配置,比如 QueryWrapper.eq("id",1);這裡的id是資料庫表的列名,很有可能我們會寫錯,但是通過lambda 的方式,LambdaQueryWrapper.eq(User::getId,1),這樣就不會有寫錯的可能了。是以推薦大家使用Lambda 的方式。
至此,mybatis plus 的正常用法就全部介紹結束了,當然,事情還遠沒有結束,為了讓大家用得更爽,後續将給大家介紹一些高階玩法。