使用LamQuerWapper自定义SQL语句

逃离我推掉我的手 2024-03-23 13:54 64阅读 0赞

项目中需要实现自定义的查询，但是仍然想用QueryWrapper对象里面的那些查询。

案例一（java代码）

LambdaQueryWrapper<xxxDO> queryWrapper = new QueryWrapper<xxxDO>()
                .select("sex, ifnull(count(id), 0) AS num, CASE WHEN sex = 1 THEN '男' WHEN sex = 0 THEN '女' ELSE '未知' END AS sexStr").lambda();
        queryWrapper.eq(xxxDO::getStatus, 0);
        queryWrapper.groupBy(xxxDO::getSex);
        List<Map<String, Object>> mapList = getBaseMapper().selectMaps(queryWrapper);
        Map<String, String> registerNumMap = new HashMap<>(mapList.size());
        for (Map<String, Object> map : mapList) {
            String key = String.valueOf(map.get("sexStr"));
            String num = String.valueOf(map.get("num"));
            registerNumMap.put(key, num);
        }

执行SQL

select sex, ifnull(count(id), 0) AS num, CASE WHEN sex = 1 THEN '男' WHEN sex = 0 THEN '女' ELSE '未知' END AS sexStr from xxx group by sex where status = 0

返回结果是一个list, 里面的map key是查询的属性名， value是参数值。伪代码示意：

list\[0\]

map\[0\] (sex=1) map\[1\] (num=5) map\[2\] (sexStr=男)

list\[1\]

map\[0\] (sex=1) map\[1\] (num=5) map\[2\] (sexStr=男)

示例二（代码）

SELECT * FROM t_employee WHERE DATE_FORMAT(birthday,'%Y-%m-%d')>='1993-02-09' AND DATE_FORMAT(birthday,'%Y-%m-%d')<='1994-04-09' AND NAME LIKE '李%'

执行SQL

/**
     * 查询姓李的，并且出生日期范围是1993-02-09到1994-04-09的员工
     * sql：SELECT * FROM t_employee WHERE DATE_FORMAT(birthday,'%Y-%m-%d')>='1993-02-09' AND DATE_FORMAT(birthday,'%Y-%m-%d')<='1994-04-09' AND NAME LIKE '李%'
     */
    @Test
    public void selectByQueryWrapper3(){
      QueryWrapper<Employee> queryWrapper=new QueryWrapper();
      // QueryWrapper<Employee> queryWrapper2=Wrappers.<Employee>query();
      queryWrapper.apply("DATE_FORMAT(birthday,'%Y-%m-%d')>={0} and DATE_FORMAT(birthday,'%Y-%m-%d')<={1}","1993-02-09","1994-04-09").likeRight("name","李");
      List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
      System.out.println(employeeList);
    }

sql实现：

SELECT * FROM t_employee WHERE NAME LIKE '李%' OR (email IS NOT NULL AND gender ='女')

mp实现：

/**
     * 查询姓李的或者邮箱不为空并且是女性的员工
     * sql：SELECT * FROM t_employee WHERE NAME LIKE '李%' OR (email IS NOT NULL AND gender ='女')
     */
    @Test
    public void selectByQueryWrapper4(){
      QueryWrapper<Employee> queryWrapper=new QueryWrapper();
      // QueryWrapper<Employee> queryWrapper2=Wrappers.<Employee>query();
      queryWrapper.likeRight("name","李").or(wq->wq.isNotNull("email").eq("gender","女"));
      List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
      System.out.println(employeeList);
    }

案例三

查询姓李的并且邮箱不为空或者是女性的员工

sql实现：

SELECT * FROM t_employee WHERE NAME LIKE '李%' AND (email IS NOT NULL OR gender ='女')

mp实现：

/**
     * 查询姓李的并且邮箱不为空或者是女性的员工
     * sql：SELECT * FROM t_employee WHERE NAME LIKE '李%' AND (email IS NOT NULL OR gender ='女')
     */
    @Test
    public void selectByQueryWrapper5(){
      QueryWrapper<Employee> queryWrapper=new QueryWrapper();
      // QueryWrapper<Employee> queryWrapper2=Wrappers.<Employee>query();
      queryWrapper.likeRight("name","李").and(wq->wq.isNotNull("email").or().eq("gender","女"));
      List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
      System.out.println(employeeList);
    }

案例四

查询属于编号1,2,3部门的并且薪水小于等于3500的员工 根据年龄从大到小排序显示

sql实现：

SELECT * FROM t_employee WHERE salary<=3500 AND departmentId IN (1,2,3) ORDER BY birthday ASC

mp实现：

/**
     * 查询属于编号1,2,3部门的并且薪水小于等于3500的员工 根据年龄从大到小排序显示
     * sql：SELECT * FROM t_employee WHERE salary<=3500 AND departmentId IN (1,2,3) ORDER BY birthday ASC
     */
    @Test
    public void selectByQueryWrapper6(){
      QueryWrapper<Employee> queryWrapper=new QueryWrapper();
      // QueryWrapper<Employee> queryWrapper2=Wrappers.<Employee>query();
      queryWrapper.likeRight("name","李").and(wq->wq.isNotNull("email").or().eq("gender","女"));
      List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
      System.out.println(employeeList);
    }