mybatis QueryWrapper的条件构造之apply、last、select解析

mybatis QueryWrapper的条件构造之apply、last、select解析

目录QueryWrapper条件构造之apply、last、selectmysql上的limit使用 如果是sqlserver条件构造器QueryWrapper实例案例一案例二案例三案例四

QueryWrapper条件构造之apply、last、select

场景： 查询数据库限制条数时

mysql上的limit使用

QueryWrapper needSyn = new QueryWrapper();

needSyn.ge("ModifyDate",lmtSynRec.getLatestDate());

needSyn.last("limit 200");

List needData = cusCarInfoMapper.selectList(needSyn);

如果是oracle则可以用apply

needSyn.apply("rownum < {0}" , 200);

如果是sqlserver

建议自己写sql查询语句

select top ${top} * from CUS_CAR_INFO t where t.ModifyDate > cast(#{latestDate} as datetime) orderBy

t.ModifyDate asc

List getSynList(int top, Date latestDate);

或者使用QueryWrapper的select

QueryWrapper wrapper = new QueryWrapper();

wrapper.select("top 200 *");

return this.list(wrapper);

以上就是根据最新时间查询top条数据

传入的字段是时间类型，就用#{} 占位符接收。如果是具体的一个数字或字符可以用 ${ }来接收。

条件构造器QueryWrapper实例

我们再搞几个实例来深入理解下条件构造器QueryWrapper的用法：

案例一

查询姓李的，并且出生日期范围是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 '李%'

mp实现：

/**

* 查询姓李的，并且出生日期范围是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 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实现：

/**

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

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

*/

@Test

public 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实现：

/**

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

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

*/

@Test

public 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实现：

/**

* 查询属于编号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 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);

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。