购物网站建设网站wordpress 模板选择
背景
在实际项目中，数据库条件查询经常需有一些复杂的查询条件的SQL语句,将这些SQL语句用mybatis-plus 组件的实现的时候经常会费一些时间，下面对几种常见的SQL语句实现做个介绍以方便以后遇到时少走弯路提高开发效率。
案例
@Data
public class User{
private Integer id;
private String name;
private Integer age;
private DateTime updateTime;
private Integer deleted;
private String status;
}
QueryWrapper<User> wrapper=new QueryWrapper<>();
例1：like("name", "BNTang").or().between("age", 20, 30);
// 多条件Update修改修改值
User user = new User();
user.setAge(99);
user.setName("BNTang6666");
// 修改条件
UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
userUpdateWrapper
.like("name", "BNTang").or().between("age", 20, 30);
int result = userMapper.update(user, userUpdateWrapper);
System.out.println(result);
sql 为：
UPDATE USER SET NAME = 'BNTang6666,age=99,update_time = '2021-03-27 00:40:27'
WHERE deleted = 0 AND NAME LIKE '%BNTang%' OR age BETWEEN 20 AND 30
例2：eq(“id”,1).or().eq(“name”,“老王”)
sql ：id = 1 or name = ‘老王’
例3：wrapper.eq(“age”,30).or(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))
sql:
age=30 or (name = ‘李白’ and status <> ‘活着’)
例4: and(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))
sql: and (name = ‘李白’ and status <> ‘活着’)
例5：wrapper.like(“name”, “BNTang”).or().between(“age”, 20, 30);
sql:
NAME LIKE '%BNTang%' OR age BETWEEN 20 AND 30
例6：wrapper.and(wrapper->wrapper.eq(“SEND_USER_ID”,“001”).eq(“RECEIVE_USER_ID”,“002”))
.or(wrapper->wrapper.eq(“SEND_USER_ID”,“003”).eq(“RECEIVE_USER_ID”,“004”));
sql:
AND ((SEND USER ID = “001” AND RECEIVE USER ID = “002”) OR (SEND USER ID =“003” AND RECEIVE USER ID = “004”))
例7：wrapper.eq(“name”, “wangsf”).nested(w->w.and(wp->wp.in(“contract_mode”, (Object[]) “5,6”.split(“,”)).ge(“actual_completion_time”, now.minusYears(2).format(fmt) + " 23:59:59")).or(wp->wp.notIn(“contract_mode”, (Object[]) “5,6”.split(“,”)).ge(“actual_completion_time”, now.minusYears(5).format(fmt) + " 23:59:59")));
sql:
name = ? AND ((contract_mode IN (?,?) AND actual_completion_time BETWEEN ? AND ?) OR (contract_mode NOT IN (?,?) AND actual_completion_time BETWEEN ? AND ?))