MybatisPlus-Wrapper (conditional constructor)
1, introduction to wapper
Wrapper : conditionally constructed abstract class, the topmost parent class
? AbstractWrapper: used for encapsulating query conditions and generating sql where conditions
? QueryWrapper: encapsulation of query conditions
? UpdateWrapper: Update conditional wrapper
? AbstractLambdaWrapper: use Lambda syntax
? LambdaQueryWrapper: Query Wrapper for Lambda syntax
? LambdaUpdateWrapper: Lambda update package Wrapper
2, QueryWrapper
Entity class
@Data
//Set the table name corresponding to the entity class
//@TableName("t_user")
public class User {<!-- -->
//Assign the field corresponding to the attribute as the primary key
//The value attribute of the @TableId annotation is used to specify the field of the primary key
//The type attribute of the @TableId annotation sets the primary key generation strategy
//@TableId(value = "uid", type = IdType.AUTO)
@TableId("uid")
private Long id;
//Specify the field name corresponding to the attribute
@TableField("user_name")
private String name;
private Integer age;
private String email;
private SexEnum sex;
@TableLogic
private Integer isDeleted;
}
Example 1: Assembling query conditions
public void test01(){<!-- -->
//Query user information whose user name contains a, age is between 20 and 30, and email information is not null
//SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (user_name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("user_name", "a")
.between("age", 20, 30)
.isNotNull("email");
List<User> list = userMapper. selectList(queryWrapper);
list.forEach(System.out::println);
}
Example 2: Assembling sort conditions
public void test02(){<!-- -->
//Query user information, sort in descending order of age, if the age is the same, sort in ascending order of id
//SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 ORDER BY age DESC,uid ASC
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper. orderByDesc("age")
.orderByAsc("uid");
List<User> list = userMapper. selectList(queryWrapper);
list.forEach(System.out::println);
}
Example 3: Assembly delete condition
public void test03(){<!-- -->
//Delete user information whose email address is null
//UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("email");
int result = userMapper. delete(queryWrapper);
System.out.println("result:" + result);
}
Example 4: Priority of conditions
public void test04(){<!-- -->
//Modify the user information (the age is greater than 20 and the user name contains a) or the email address is null
//UPDATE t_user SET user_name=?, email=? WHERE is_deleted=0 AND (age > ? AND user_name LIKE ? OR email IS NULL)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("age", 20)
.like("user_name", "a")
.or()
.isNull("email");
User user = new User();
user.setName("Xiao Ming");
user.setEmail("[email protected]");
int result = userMapper. update(user, queryWrapper);
System.out.println("result:" + result);
}
public void test05(){<!-- -->
//Modify the user information that contains a in the user name and (the age is greater than 20 or the email address is null)
//conditions in lambda are executed first
//UPDATE t_user SET user_name=?, email=? WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("user_name", "a")
.and(i->i.gt("age",20).or().isNull("email"));
User user = new User();
user.setName("Xiaohong");
user.setEmail("[email protected]");
int result = userMapper. update(user, queryWrapper);
System.out.println("result:" + result);
}
Example 5: Assemble select clause
public void test06(){<!-- -->
//Query the user's username, age, email information
//SELECT user_name,age,email FROM t_user WHERE is_deleted=0
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper. select("user_name", "age", "email");
List<Map<String, Object>> maps = userMapper. selectMaps(queryWrapper);
maps.forEach(System.out::println);
}
Example 6: Assembly subquery
public void test07(){<!-- -->
//Query user information whose id is less than or equal to 100
//SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (uid IN (select uid from t_user where uid <= 100))
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("uid", "select uid from t_user where uid <= 100");
List<User> list = userMapper. selectList(queryWrapper);
list.forEach(System.out::println);
}
LambdaQueryWrapper
@Test
public void test11(){<!-- -->
//SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (user_name LIKE ? AND age <= ?)
String username = "a";
Integer ageBegin = null;
Integer ageEnd = 30;
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.like(StringUtils.isNotBlank(username), User::getName, username)
.ge(ageBegin != null, User::getAge, ageBegin)
.le(ageEnd != null, User::getAge, ageEnd);
List<User> list = userMapper. selectList(queryWrapper);
list.forEach(System.out::println);
}
LambdaUpdateWrapper
@Test
public void test12(){<!-- -->
//Modify the user information that contains a in the user name and (the age is greater than 20 or the email address is null)
LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
updateWrapper. like(User::getName, "a")
.and(i -> i.gt(User::getAge, 20).or().isNull(User::getEmail));
updateWrapper.set(User::getName, "Xiaohei").set(User::getEmail,"[email protected]");
int result = userMapper. update(null, updateWrapper);
System.out.println("result: " + result);
}
Paging
@Test
public void testPage(){<!-- -->
Page<User> page = new Page<>(2, 3);
userMapper. selectPage(page, null);
System.out.println(page.getRecords());
System.out.println(page.getPages());
System.out.println(page.getTotal());
System.out.println(page.hasNext());
System.out.println(page.hasPrevious());
}
@Test
public void testPageVo(){<!-- -->
Page<User> page = new Page<>(1, 3);
userMapper.selectPageVo(page, 20);
System.out.println(page.getRecords());
System.out.println(page.getPages());
System.out.println(page.getTotal());
System.out.println(page.hasNext());
System.out.println(page.hasPrevious());
}