通过条件构造器来设置操作数据库的条件
一. 创建实体类
package com.xdu.mybatisplus.pojo;
import lombok.*;
@Data
public class User {
private Integer id;
private String name;
private Integer age;
}
二. 创建Mapper接口
package com.xdu.mybatisplus.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
三. 测试
1. QueryWrapper
① 组装查询条件
package com.xdu.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.xdu.mybatisplus.mapper.UserMapper;
import com.xdu.mybatisplus.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class MyBatisPlusTest {
@Autowired
private UserMapper userMapper;
@Test
public void testWrapper(){
//查询用户名包含 o 且年龄21到23之间用户信息,结果按照年龄的降序排序,若年龄相同,则按照id升序排序
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "o").between("age", 21, 23).orderByDesc("age").orderByAsc("id"); //表中的字段名
List<User> list = userMapper.selectList(queryWrapper); //SELECT id,name,age FROM user WHERE (name LIKE ? AND age BETWEEN ? AND ?) ORDER BY age DESC,id ASC
list.forEach(System.out::println);
}
}
② 组装删除条件
package com.xdu.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.xdu.mybatisplus.mapper.UserMapper;
import com.xdu.mybatisplus.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class MyBatisPlusTest {
@Autowired
private UserMapper userMapper;
@Test
public void testWrapper(){
//删除姓名为null的用户
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("name"); //表中的字段名
int result = userMapper.delete(queryWrapper); //DELETE FROM user WHERE (name IS NULL)
System.out.println(result);
}
}
③ 组装更新条件
package com.xdu.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.xdu.mybatisplus.mapper.UserMapper;
import com.xdu.mybatisplus.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class MyBatisPlusTest {
@Autowired
private UserMapper userMapper;
@Test
public void testWrapper(){
//修改id大于5且姓名中包含o 或 年龄小于20的用户
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("id", 5).like("name", "o").or().lt("age", 20); //表中的字段名
User user = new User();
user.setName("Joe");
user.setAge(23);
//将满足条件的用户设置为user的内容
int result = userMapper.update(user, queryWrapper); //UPDATE user SET name=?, age=? WHERE (id > ? AND name LIKE ? OR age < ?)
System.out.println(result);
}
}
④ 设置条件优先级
package com.xdu.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.xdu.mybatisplus.mapper.UserMapper;
import com.xdu.mybatisplus.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class MyBatisPlusTest {
@Autowired
private UserMapper userMapper;
@Test
public void testWrapper(){
//修改id大于5且(姓名中包含o或年龄小于20)的用户
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//需要设置优先级 (or()中也可以这样设置)
queryWrapper.gt("id", 5).and(i -> i.like("name", "o").or().lt("age", 20));//表中的字段名
User user = new User();
user.setName("Tom");
user.setAge(22);
//将满足条件的用户设置为user的内容
int result = userMapper.update(user, queryWrapper); //UPDATE user SET name=?, age=? WHERE (id > ? AND (name LIKE ? OR age < ?))
System.out.println(result);
}
}
⑤ 查询某些字段
package com.xdu.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.xdu.mybatisplus.mapper.UserMapper;
import com.xdu.mybatisplus.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
import java.util.Map;
@SpringBootTest
public class MyBatisPlusTest {
@Autowired
private UserMapper userMapper;
@Test
public void testWrapper(){
//查询用户名和年龄
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("name", "age"); //表中的字段名
List<Map<String, Object>> mapList = userMapper.selectMaps(queryWrapper); //SELECT name,age FROM user
mapList.forEach(System.out::println);
}
}
⑥ 子查询
package com.xdu.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.xdu.mybatisplus.mapper.UserMapper;
import com.xdu.mybatisplus.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class MyBatisPlusTest {
@Autowired
private UserMapper userMapper;
@Test
public void testWrapper(){
//查询id小于等于5的用户 (展示子查询功能)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("id", "select id from user where id <= 100");
List<User> list = userMapper.selectList(queryWrapper); //SELECT id,name,age FROM user WHERE (id IN (select id from user where id <= 100))
list.forEach(System.out::println);
}
}
2. UpdateWrapper
① 组装更新条件
可以之间使用set方法设置更新内容,比QueryWrapper更方便
package com.xdu.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.xdu.mybatisplus.mapper.UserMapper;
import com.xdu.mybatisplus.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class MyBatisPlusTest {
@Autowired
private UserMapper userMapper;
@Test
public void testWrapper(){
//修改id大于5且(姓名中包含o或年龄小于20)的用户
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.gt("id", 5).and(i -> i.like("name", "o").or().lt("age", 20));//表中的字段名
updateWrapper.set("name", "Tom").set("age", 22);
//将满足条件的用户设置为user的内容
int result = userMapper.update(null, updateWrapper); //UPDATE user SET name=?,age=? WHERE (id > ? AND (name LIKE ? OR age < ?))
System.out.println(result);
}
}
3. LambdaQueryWrapper
可以直接获取实体类对象的属性
package com.xdu.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.xdu.mybatisplus.mapper.UserMapper;
import com.xdu.mybatisplus.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class MyBatisPlusTest {
@Autowired
private UserMapper userMapper;
@Test
public void testWrapper(){
//查询id大于5且姓名中包含o 或 年龄小于20的用户
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.gt(User::getId, 5).like(User::getName, "o").or().lt(User::getAge, 20);
List<User> list = userMapper.selectList(lambdaQueryWrapper);
list.forEach(System.out::println);
}
}
4. LambdaUpdateWrapper
package com.xdu.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.xdu.mybatisplus.mapper.UserMapper;
import com.xdu.mybatisplus.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class MyBatisPlusTest {
@Autowired
private UserMapper userMapper;
@Test
public void testWrapper(){
//修改id大于5且(姓名中包含o或年龄小于20)的用户
LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
lambdaUpdateWrapper.gt(User::getId, 5).and(i -> i.like(User::getName, "o").or().lt(User::getAge, 20));
lambdaUpdateWrapper.set(User::getName, "Tom").set(User::getAge, 22);
int result = userMapper.update(null, lambdaUpdateWrapper); //UPDATE user SET name=?,age=? WHERE (id > ? AND (name LIKE ? OR age < ?))
System.out.println(result);
}
}