mybatis QueryWrapper的条件构造之apply、last、select解析
时间：2022-08-04 10:04:00|栏目：JAVA代码|点击： 次
QueryWrapper条件构造之apply、last、select
场景： 查询数据库限制条数时
mysql上的limit使用
QueryWrapper needSyn = new QueryWrapper();
needSyn.ge("ModifyDate",lmtSynRec.getLatestDate());
needSyn.last("limit 200");
List<CusCarInfo> needData = cusCarInfoMapper.selectList(needSyn);
如果是oracle则可以用apply
needSyn.apply("rownum < {0}" , 200);
如果是sqlserver
建议自己写sql查询语句
<select id="getSynList" resultType="com.xmeport.project.model.entity.CusCarInfo">
select top ${top} * from CUS_CAR_INFO t where t.ModifyDate > cast(#{latestDate} as datetime) orderBy
t.ModifyDate asc
</select>
List<CusCarInfo> getSynList(int top, Date latestDate);
或者使用QueryWrapper的select
QueryWrapper<CusCarInfo> 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<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);
}
上一篇：java基础二叉搜索树图文详解
栏 目：JAVA代码
下一篇：没有了
本文标题：mybatis QueryWrapper的条件构造之apply、last、select解析
本文地址：http://www.codeinn.net/misctech/209859.html
