本章介绍了条件构造器的分类与组成元素,并且分别采用多种方式实现了信息的更新和删除操作,整体对于信息的持久层操作有了进一步的理解和深化。
标签方法 | 案例 | 等同sql |
---|---|---|
eq、allEq、ne | 等于:eq(“name”, “老王”) 不等于:ne(“name”, “老王”) | name = '老王’ name <> '老王 |
gt、lt | 大于:gt(“age”, 18) 小于:lt(“age”, 18) | age > 18 age < 18 |
ge、le | 大于等于:ge(“age”, 18) 小于等于:le(“age”, 18) | age >= 18 age <= 18 |
between、notBetween | between(“age”, 18, 30) notBetween(“age”, 18, 30) | age between 18 and 30 age not between 18 and 30 |
like、notLike、likeLeft、LikeRight | like(“name”, “王”) notLike(“name”, “王”) likeLeft(“name”, “王”) likeRight(“name”, “王”) | name like '%王%' name not like '%王%' name like '%王’ name like ‘王%’ |
isNull 、isNotNull | isNull(“name”) isNotNull(“name”) | name is null name is not null |
in 、notIn、inSql、notInSql | in(“age”,{1,2,3}) notIn(“age”,{1,2,3}) inSql(“id”, “select id from table where id < 3”) notInSql(“id”, “select id from table where id < 3”) | age in (1,2,3) age not in (1,2,3) id in (select id from table where id < 3) id not in (select id from table where id < 3) |
groupBy、having | groupBy(“id”, “name”) having(“sum(age) > {0}”, 11) | group by id,name having sum(age) > 11 |
orderByAsc、orderByDesc、orderBy | orderByAsc(“id”, “name”) orderByDesc(“id”, “name”) orderBy(true, true, “id”, “name”) | order by id ASC,name ASC order by id DESC,name DESC order by id ASC,name ASC |
or、and、exists、notExists | eq(“id”,1).or().eq(“name”,“老王”) and(i -> i.eq(“name”, “李白”).ne(“status”, “活着”)) exists(“select id from table where age = 1”) notExists(“select id from table where age = 1”) | id = 1 or name = '老王’ and (name = ‘李白’ and status <> ‘活着’) exists (select id from table where age = 1) not exists (select id from table where age = 1) |
// 根据 UpdateWrapper 条件,更新记录 需要设置sqlset boolean update(Wrapper
//根据id进行更新操作 void updateUser(User user);
@Override public void updateUser(User user) { updateById(user); }
//根据id进行用户更新 @RequestMapping("/updateById") public String testUpdateById(){ User user = new User(6,"李四",38,"lisinew@123.com"); userService.updateUser(user); return "更新1条记录成功!"; }
需求说明:基于用户ID实现用户信息的更新操作
//根据条件更新指定id用户信息 void updateByUserId(int id,User user);
@Override public void updateByUserId(int id, User user) { //封装条件参数 UpdateWrapper
//根据id进行条件更新 @RequestMapping("/updateById2") public String testUpdateById2(){ User user = new User("小明"); userService.updateByUserId(6,user); return "根据条件更新1条记录成功!"; }
public interface UserMapper extends BaseMapper
//根据原生sql进行更新操作 void updateUserByCondition(String oldEmailExt,String newEmailExt);
@Autowired private UserMapper userMapper; @Override public void updateUserByCondition(String oldEmailExt,String newEmailExt) { String sql="update user set email=concat(substr(email,1,POSITION('@'in email)),'"+newEmailExt+"') " +"where email like '%@"+oldEmailExt+"'"; userMapper.updateUserByCondition(sql); }
//更新用户邮箱为指定的163.com邮箱 @RequestMapping("/updateEmail") public String testUpdateEmail(){ userService.updateUserByCondition("123.com","163.com"); return "更新用户邮箱成功!"; }
开始实验
// 根据 entity 条件,删除记录 boolean remove(Wrapper
//根据id删除单条记录 void delById(int id); //根据ids批量删除记录 void delByIds(List
1.2 编写UserServiceImpl.java
@Override public void delById(int id) { removeById(id); } @Override public void delByIds(List
//根据id删除单记录 @RequestMapping("/del/{id}") public String testDelById(@PathVariable int id){ userService.delById(id); return "删除单条记录成功!"; } //根据id删除多记录 @RequestMapping("/del") public String testDelByIds(){ List
//根据条件删除 @Delete("${sql}") void delByCondition(@Param("sql") String sql);
//根据姓氏删除 void delByName(String name);
@Override public void delByName(String name) { String sql="delete from user where name like '"+name+"%'"; userMapper.delByCondition(sql); }
//根据姓氏删除记录 @RequestMapping("/delByName") public String testDelByName(){ userService.delByName("赵"); return "删除‘赵姓’记录成功!"; }
开始实验