Mybatis-Plus Wrapper条件构造器使用

一、MyBatis与Mybatis-Plus的区别

Mybatis

手动编写mapper接口，手动编写CRUD语句。同时编写映射的mapper.xml文件，为每个方法写具体的SQL语句。

Mybatis-Plus

创建mapper 接口并继承 BaseMapper<实体类>，BaseMapper中提供了很多通用的方法，可以直接使用。如果需要一些特殊的操作，仍然可以手动编写SQL语句。

二、代码实现

1、实体类

@Data
@TableName(value = "tb_employee")
public class Employee {

    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;

    @TableField(value = "last_name")
    private String lastName;

    @TableField(value ="email")
    private String email;

    @TableField(value ="gender")
    private String gender;

    @TableField(value = "age")
    private Integer age;
}

@TableName 设置实体类关联的数据库表名。
@TableId 设置主键，type为主键类型，自增。
@TableField 设置其他字段

2、mapper接口

mapper接口继承BaseMapper

public interface EmployeeMapper extends BaseMapper<Employee> {
}

此时就可以看到mapper有了很多通用的方法。

3、Service 层

service 层继承 ServiceImpl

@Service
public class EmployeeService extends ServiceImpl<EmployeeMapper, Employee> {
}

4、Wrapper条件构造器

自动生成的方法不光能实现一些简单的操作语句，也可以实现一些复杂的判断条件。
可以看到好多Wrapper参数。

测试Service代码

@Service
public class EmployeeService extends ServiceImpl<EmployeeMapper, Employee> {

    @Autowired
    private EmployeeMapper employeeMapper;

    public int deleteById(Integer id) {
        return employeeMapper.deleteById(id);
    }
    public Employee selectById(Integer id) {
        return employeeMapper.selectById(id);
    }

    /**
     * 判断字段是否与指定值相等
     * 判断字段所在区间
     * @return
     */
    public List<Employee> selectByCondition() {
        return employeeMapper.selectList(new QueryWrapper<Employee>()
                .select("last_name,email,gender,age")
                .eq("gender", "1")
                .between("age", 13, 50)
        );
    }

    /**
     * 批量判断多个字段
     * 判断字段是否为空
     * @return
     */
    public List<Employee> selectByCondition2() {
        HashMap<String, Object> map = new HashMap<>();
        map.put("age",18);
        map.put("gender",1);
        return employeeMapper.selectList(new QueryWrapper<Employee>()
                .select("last_name,email,gender,age")
                .allEq(map)
                .isNotNull("last_name")
        );
    }

    /**
     * like 模糊查询
     * @return
     */
    public List<Employee> selectByCondition3() {
        return employeeMapper.selectList(new QueryWrapper<Employee>()
                .select("last_name,email,gender,age")
//                .like("last_name","王")          // "%王%"
//                .likeLeft("last_name","王小")    // "王小%"
//                .likeRight("last_name","二")     // “%二”
                .notLike("last_name","王")       //不包含 “王”
        );
    }

    /**
     * <p>子查询</p>
     * <p>降序、升序</p>
     * @return
     */
    public List<Employee> selectByCondition4() {

        return employeeMapper.selectList(new QueryWrapper<Employee>()
                .select("last_name,email,gender,age")
                .inSql("id","select id from tb_employee where gender = '1'")
                .orderByAsc("age")  //升序
//                .orderByAsc(false,"age")    //不生效
//                .orderByDesc("age","height")    //多个字段降序排序
        );
    }

    /**
     * 组合条件 and or
     * @return
     */
    public List<Employee> selectByCondition5() {
        
        // 查询条件 gender = 1 OR (gender = 0 AND age > 26)
        return employeeMapper.selectList(new QueryWrapper<Employee>()
                .select("last_name,email,gender,age")
                .eq("gender","1")
                .or(qw->qw.eq("gender","0").gt("age",26))
        );
    }
}

5、单元测试

@SpringBootTest
@Slf4j
@DisplayName("employee test")
public class EmployeeTest {

    @Autowired
    private EmployeeService employeeService;

    @Test
    @DisplayName("select by id")
    void selectByIdTest(){
        Employee employee = employeeService.selectById(1);
        log.info(String.valueOf(employee));
    }


    @Test
    @DisplayName("selectList test")
    void selectList(){
        List<Employee> employees = employeeService.selectByCondition();
        log.info(String.valueOf(employees));
    }

    @Test
    @DisplayName("selectList2 test")
    void selectList2(){
        List<Employee> employees = employeeService.selectByCondition2();
        log.info(String.valueOf(employees));
    }
    @Test
    @DisplayName("selectList3 test")
    void selectList3(){
        List<Employee> employees = employeeService.selectByCondition3();
        log.info(String.valueOf(employees));
    }

    @Test
    @DisplayName("selectList4 test")
    void selectList4(){
        List<Employee> employees = employeeService.selectByCondition4();
        log.info(String.valueOf(employees));
    }

    @Test
    @DisplayName("selectList5 test")
    void selectList5(){
        List<Employee> employees = employeeService.selectByCondition5();
        log.info(String.valueOf(employees));
    }
}