使用LamQuerWapper自定义SQL语句

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

案例一（java代码）

    LambdaQueryWrapper queryWrapper = new QueryWrapper()            .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> mapList = getBaseMapper().selectMaps(queryWrapper);    Map registerNumMap = new HashMap<>(mapList.size());    for (Map 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

@Testpublic void selectByQueryWrapper3(){  QueryWrapper queryWrapper=new QueryWrapper();  // QueryWrapper queryWrapper2=Wrappers.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 employeeList = employeeMapper.selectList(queryWrapper);  System.out.println(employeeList);}

sql实现：

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

mp实现：

@Testpublic void selectByQueryWrapper4(){  QueryWrapper queryWrapper=new QueryWrapper();  // QueryWrapper queryWrapper2=Wrappers.query();  queryWrapper.likeRight("name","李").or(wq->wq.isNotNull("email").eq("gender","女"));  List employeeList = employeeMapper.selectList(queryWrapper);  System.out.println(employeeList);}

案例三

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

sql实现：

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

mp实现：

@Testpublic void selectByQueryWrapper5(){  QueryWrapper queryWrapper=new QueryWrapper();  // QueryWrapper queryWrapper2=Wrappers.query();  queryWrapper.likeRight("name","李").and(wq->wq.isNotNull("email").or().eq("gender","女"));  List 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实现：

@Testpublic void selectByQueryWrapper6(){  QueryWrapper queryWrapper=new QueryWrapper();  // QueryWrapper queryWrapper2=Wrappers.query();  queryWrapper.likeRight("name","李").and(wq->wq.isNotNull("email").or().eq("gender","女"));  List employeeList = employeeMapper.selectList(queryWrapper);  System.out.println(employeeList);}

来源地址：https://blog.csdn.net/qq_49511498/article/details/130215715