美色商城 网站建设荆州网站建设厂家

背景

       在实际项目中，数据库条件查询经常需有一些复杂的查询条件的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 ?))