一.查询(select)
1. 查询ID查询数据库 id=231 主键查询
public void selectById(){
int id = 231; //模拟用户参数.
User user = userMapper.selectById(id);
System.out.println(user);
2. 查询 name="小乔" 并且 性别 ="女"
思路: 如果将来有多个结果 则使用List进行接收.
Sql: select * from demo_user where name="小乔" and sex="女"
注意事项: 默认的连接符 and
public void select01(){
//1.通过对象封装数据
User user = new User();
user.setName("小乔").setSex("女");
//2.构建条件构造器 根据对象中不为null的属性充当where条件!
QueryWrapper<User> queryWrapper = new QueryWrapper(user);
//3.根据条件构造器 实现数据查询
List<User> userList = userMapper.selectList(queryWrapper);
System.out.println(userList);
}
3.查询 name="小乔" 并且 性别 ="女"
逻辑运算符:( = &eq;) ( > >) (< < ) (>= ≥) (<= ≤) (!= ≠)
public void select02(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name","小乔")
.eq("sex","女");
List<User> userList =
userMapper.selectList(queryWrapper);
System.out.println(userList);
}
4.查询age < 18岁 性别="女" 利用MP方式实现
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.lt("age",18)
.eq("sex","女");
List<User> userList2 = userMapper.selectList(queryWrapper);
System.out.println(userList2);
5.查询 name包含 '君'字的数据
关键字: like "%xxx%"
以君开头: likeRight "君%"
以君结尾: likeLeft "%君"
public void select04(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.like("name","君");
List<User> userList = userMapper.selectList(queryWrapper);
System.out.println(userList);
}
6.select 查询 sex="女" 按照年龄倒序排列.
public void select05(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("sex","女")
.orderByDesc("age");
List<User> userList = userMapper.selectList(queryWrapper);
System.out.println(userList);
}
7.需求: 动态Sql查询. 如果数据有值 则拼接where条件.
如果数据为null 则不拼接where条件
语法: condition: true 拼接where条件
false 不拼接where条件
public void select06(){
String name = "貂蝉";
int age = 19;
boolean nameFlag = name == null ? false : true;
boolean ageFlag = age == 0 ? false : true;
QueryWrapper<User> queryWrapper = new QueryWrapper();
queryWrapper.eq(nameFlag,"name",name)
.eq(ageFlag,"age",age);
List<User> userList = userMapper.selectList(queryWrapper);
System.out.println(userList);
}
8.批量查询 查询id= 1,4,5,6......的数据
public void selectIn(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("id",1,4,5,6);
List<User> userList = userMapper.selectList(queryWrapper);
System.out.println(userList);
//数组在未来由用户负责传递. 注意使用包装类型
Integer[] array = new Integer[]{1,4,5,6};
//数组转化为List集合
List ids = Arrays.asList(array);
List<User> userList2 = userMapper.selectBatchIds(ids);
System.out.println(userList2);
}
9.查询性别为男的用户,只查询ID字段
selectObjs(); 只查询第一列字段(主键)
实际用途: 根据业务只需要主键的查询
public void selectObjs(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("sex","男");
List<Object> ids = userMapper.selectObjs(queryWrapper);
System.out.println(ids);
}
二.更新(update)
1.将ID=231的数据 name改为 "xx"
Sql: update demo_user set name="xx" where id=231
public void testUpdate(){
User user = new User();
user.setId(231).setName("中秋节快乐").setAge(20).setSex("女");
//byId 表示ID只当作where条件.
//其它不为null的属性 当作set条件
userMapper.updateById(user);
}
运行结果
2.将name="中秋节快乐" 改为 name="国庆快乐" age=40 sex="男"
.update(arg1,arg2)
arg1: 实体对象 set条件的数据
arg2: updateWrapper 动态拼接where条件
public void testUpdate2(){
User user = new User();
user.setName("国庆快乐").setAge(40).setSex("男");
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("name","中秋节快乐");
userMapper.update(user,updateWrapper);
System.out.println("更新操作成功!!!!");
}