1. Introduction
In order to simplify operations, mybatis-plus introduces conditional constructors to simplify basic sql operations. There are two main types of conditional constructors used, one is the query conditional constructor (QueryWrapper), and the other is (UpdateWrapper). The main relationships between these conditional constructors are as follows:
2. Query condition construction (QueryWrapper and LambdaQueryWrapper are compared together)
There are two query condition constructs, one is QueryWrapper and the other is LambdaQueryWrapper. LambdaQueryWrapper is to prevent QueryWrapper from writing wrong field names when constructing conditions and causing errors, so LambdaQueryWrapper is more commonly used. The following is a detailed introduction to the specific usage of the two conditional constructs.
2.1, eq method (same usage as ne method)
//Two ways of writing
// 1. queryWrapper
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name","Jack");
// 2. LambdaQueryWrapper (commonly used)
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.eq(User::getName,"Jack");
// 3. When the condition is null, we do not need to splice the query conditions, otherwise it will be impossible to query the results. This is judged when building lambdaQueryWrapper
lambdaQueryWrapper.eq(name != null,User::getName,name);
2.2, allEq method
// Equivalence and multiple eq
HashMap<String, Object> hashMap = new HashMap<>();
hashMap.put("name","Jone");
hashMap.put("age",null);
queryWrapper.allEq(hashMap,false);
// allEq(Map<R, V> params, boolean null2IsNull);
// Parameter params: represents the passed Map collection;
// Parameter null2IsNull: Indicates whether to judge isNul for null conditions
2.3, gt & amp; ge & amp; lt & amp; le & amp; between & amp; notBetween
The following uses LambdaQueryWrapper for demonstration. If you use QueryWrapper, you can write the field name obtained by the lambda expression into a specific field name.
lambdaQueryWrapper.gt(User::getAge,age); // Greater than lambdaQueryWrapper.ge(User::getAge,age); // Greater than or equal to lambdaQueryWrapper.lt(User::getAge,age); // Less than lambdaQueryWrapper.le(User::getAge,age); // Less than or equal to lambdaQueryWrapper.between(User::getAge,18,30); // Between lambdaQueryWrapper.notBetween(User::getAge,18,30); // Outside between
2.4, like & amp; notLike & amp; likeLeft & amp; likeRight
lambdaQueryWrapper.like(User::getName,"J"); // The encapsulated query condition is '%J%' lambdaQueryWrapper.notLike(User::getName,"J"); lambdaQueryWrapper.likeLeft(User::getName,"J"); // The encapsulated query condition is '%J' lambdaQueryWrapper.likeRight(User::getName,"J"); // The encapsulated query condition is 'J%'
2.5, isNull & amp; isNotNull
lambdaQueryWrapper.isNull(User::getName); // The spliced query condition is name is null lambdaQueryWrapper.isNotNull(User::getName); // The concatenated query condition is name is not null
2.6、in & amp; notIn & amp; inSql & amp; notInSql
// 1. Use list format ArrayList<Integer> arrayList = new ArrayList<>(); Collections.addAll(arrayList,18,20,21); lambdaQueryWrapper.in(User::getAge,arrayList); // 2. Use enumerated value form lambdaQueryWrapper.in(User::getAge,18,20,21); // ================= notIn ==================== The usage of notIn is the same as in // ================= inSql ==================== lambdaQueryWrapper.inSql(User::getAge,"18,20,22"); // The spliced sql is age in (18,20,22) lambdaQueryWrapper.inSql(User::getAge,"select age from user where age > 20"); //age in (select ...) // ================= notInSql ================= lambdaQueryWrapper.notInSql(User::getAge,"18,20,22"); // The spliced sql is age not in (18,20,22) lambdaQueryWrapper.notInSql(User::getAge,"select age from user where age > 20"); //age not in (select ...)
2.7, groupBy & amp; having
//Set conditions and specify query field names and grouping fields
queryWrapper.groupBy("age");
queryWrapper.select("age,count(*) as field_count");
queryWrapper.having("field_count = 1");
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
2.8, orderByAsc & amp; orderByDesc & amp; orderBy
//The specified fields are in ascending order, in order lambdaQueryWrapper.orderByAsc(User::getAge,User::getId); //The specified fields are all in descending order, in order lambdaQueryWrapper.orderByDesc(User::getAge,User::getId); // orderBy(boolean condition, boolean isAsc, R column, R... columns) lambdaQueryWrapper.orderBy(true,true,User::getId); lambdaQueryWrapper.orderBy(true,false,User::getAge);
2.9, func
lambdaQueryWrapper.func(i -> {
if(true) {
i.eq(User::getId, 1);
}else {
i.ne(User::getId, 1);
}
});
//The spliced sql is: where id = 1
2.10、and & amp; or & amp; nested
//The default for normal splicing is and lambdaQueryWrapper.gt(User::getAge,22).lt(User::getAge,30); //Use chain splicing, and nesting lambdaQueryWrapper.eq(User::getName,"wang").and(i -> i.gt(User::getAge,26).or().lt(User::getAge,22)); // or nested lambdaQueryWrapper.eq(User::getName,"wang").or(i -> i.gt(User::getAge,22).lt(User::getAge,26)); // nested TODO lambdaQueryWrapper.nested(i -> i.eq(User::getName, "Billie").ne(User::getAge, 22));
2.11. Custom query conditions (apply, last)
// apply
lambdaQueryWrapper.apply("id = 1"); // The spliced sql is where id = 1
// last
lambdaQueryWrapper.last("limit 0,2"); // The spliced sql is added at the end with limit 0, 2
2.12, exists & amp; notExists
// Determine whether the specified sql can query the data. If the query has a result, true will be returned, otherwise the query will not be performed. where exists...
lambdaQueryWrapper.exists("select id from powershop_user where age = 18");
// Determine whether the specified sql can query the data. If the query has no results, it will not be queried. where not exists ...
lambdaQueryWrapper.notExists("select id from powershop_user where age = 18");
2.13. Specified field query (select)
lambdaQueryWrapper.select(User::getId,User::getName); // The spliced sql is select id, name from ...
3. Update condition construction (UpdateWrapper and LambdaUpdateWrapper (commonly used))
UpdateWrapper and LambdaUpdateWrapper are basically the same as the query condition constructor when splicing conditions. The difference is that the value needs to be updated and set is used. The specific details will not be repeated.
UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
userUpdateWrapper.set("aa",0);
// After using the lambda method in UpdateWrapper, you can use lambda expressions and chain programming, which has the same effect as LambdaUpdateWrapper
userUpdateWrapper.lambda().set(User::getName,"aa");
LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
lambdaUpdateWrapper.set(User::getName,"bb");
Summary: This article introduces mybatis-plus to use conditional constructors to simplify SQL operations, which is convenient and fast.
I am a self-taught computer technology from scratch. I have some experience in operation and maintenance, back-end, various middleware technologies, big data, etc. I would like to obtain Self-study summary materials (pdf version) or hope to share the experience with others. To study, Follow the WeChat public account: Old Little Boy. You can get it by replying to the corresponding Technical Name/Technical Point in the background. (My learning purpose: once you learn it, share it for free)
