基于Wrapper查询

一、类图

 二、基于QueryWrapper的测试

2.1 实体类

@Data               // set get 方法
@AllArgsConstructor // 有参构造
@NoArgsConstructor  // 无参构造
@ToString           // toString方法
@TableName("user")  // @TableName 标识实体类对应的表名
public class User {

    @TableId(value = "id") // 对应表结构中的主键字段 对应的雪花算法    // 表明id就是主键字段对应的属性
    private Long id;
    @TableField(value = "name")  //表结构中的name属性和name属性对应
    private String name;
    private Integer age;
    private String email;

    @TableField(value = "is_deleted")
    // @TableLogic是用来完成 `逻辑删除`操作的
    @TableLogic(value = "0",delval = "1")
    private Integer isDeleted;
}

2.2 UserMapper 接口

/**
 * 继承 BaseMapper 其中的泛型指定返回值类型
 */
public interface UserMapper extends BaseMapper<User> {

}

2.3 测试

 @Test
    public void testQueryWrapper(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.like("name","a")
                .gt("age",20)
                .isNotNull("email");
        List<User> users = userMapper.selectList(wrapper);
        for (User user : users) {
            System.out.println(user);
        }
    }

    @Test
    public void testOrder(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.orderByAsc("age")
                .orderByDesc("id");

        List<User> users = userMapper.selectList(wrapper);
        for (User user : users) {
            System.out.println(user);
        }
    }

    @Test
    public void testDeleteWrapper(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.lt("age",18);
        int delete = userMapper.delete(wrapper);
        System.out.println(delete);
    }

    /**
     * 查询出年龄大于20并且姓名中包含的有'o'或者邮箱地址为空的记录
     */
    @Test
    void queryUser() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.gt("age",20)
                .like("name","o")
                .or() // 默认是通过and连接 显示加上 or()方法表示or连接
                .isNotNull("email");
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }

    @Test
    void queryUser1() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.and((i)->{
            i.gt("age",20).like("name","o");
        }).or((i)->{
            i.isNotNull("email");
        });
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }

    /**
     * 查询出年龄大于20并且姓名中包含的有'o'或者邮箱地址为空的记录
     */
    @Test
    public void queryUsers() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.gt("age",20)
                .like("name","o")
                .or() // 默认是通过and连接 显示加上 or()方法表示or连接
                .isNotNull("email")
                .select("id","name","age") // 指定特定的字段
        ;
        //selectMaps()返回Map集合列表，通常配合select()使用，避免User对象中没有被查询到的列值为null
        List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);
//        maps.forEach(System.out::println);
        for (Map<String, Object> map : maps) {
            System.out.println(map);
        }
    }

    /**
     * 子查询
     * SELECT uid,name,age,email,is_deleted
     * FROM t_user
     * WHERE (
     *          uid IN (select uid from t_user where uid < 5)
     *      )
     */
    @Test
    void queryUserInSQL() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.inSql("id","select id from user where id < 5")
        ;
        List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);
        maps.forEach(System.out::println);
    }

    /**
     * 更新用户Tom的age和邮箱信息
     * UPDATE t_user SET age=?,email=? WHERE (name = ?)
     */
    @Test
    void updateUser() {
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        wrapper.set("age",25)
                .set("email","bobo@qq.com")
                .eq("name","Tom");
        int update = userMapper.update(null, wrapper);
        System.out.println("update = " + update);
    }

    //动态SQL
    @Test
    public void testDynamicSQL(){

        String name = "Tom";
        Integer age = null;
        String email = null;

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        if(!StringUtils.isEmpty(name)){
            wrapper.eq("name",name);
        }
        if (age != null && age > 0){
            wrapper.eq("age",age);
        }
        if (!StringUtils.isEmpty(email)){
            wrapper.eq("email",email);
        }

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }
    @Test
    public void testDynamicSQL1(){

        String name = "Tom";
        Integer age = null;
        String email = null;

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        /*if(!StringUtils.isEmpty(name)){
            wrapper.eq("name",name);
        }
        if (age != null && age > 0){
            wrapper.eq("age",age);
        }
        if (!StringUtils.isEmpty(email)){
            wrapper.eq("email",email);
        }*/

        wrapper.eq(StringUtils.isNotBlank(name),"name",name);
        wrapper.eq(age != null && age > 0,"age",age);
        wrapper.eq(StringUtils.isNotBlank(email),"email",email);

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }