QueryWrapper条件构造之apply、last、select
场景: 查询数据库限制条数时
mysql上的limit使用
1
2
3
4
|
QueryWrapper needSyn = new QueryWrapper(); needSyn.ge( "ModifyDate" ,lmtSynRec.getLatestDate()); needSyn. last ( "limit 200" ); List<CusCarInfo> needData = cusCarInfoMapper.selectList(needSyn); |
如果是oracle则可以用apply
1
|
needSyn.apply( "rownum < {0}" , 200); |
如果是sqlserver
建议自己写sql查询语句
1
2
3
4
5
|
< 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
1
2
3
|
QueryWrapper<CusCarInfo> wrapper = new QueryWrapper(); wrapper.select( "top 200 *" ); return this .list(wrapper); |
以上就是根据最新时间查询top条数据
传入的字段是时间类型,就用#{} 占位符接收。如果是具体的一个数字或字符可以用 ${ }来接收。
条件构造器QueryWrapper实例
我们再搞几个实例来深入理解下条件构造器QueryWrapper的用法:
案例一
查询姓李的,并且出生日期范围是1993-02-09到1994-04-09的员工
sql实现:
1
|
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实现:
1
2
3
4
5
6
7
8
9
10
11
12
|
/** * 查询姓李的,并且出生日期范围是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实现:
1
|
SELECT * FROM t_employee WHERE NAME LIKE '李%' OR (email IS NOT NULL AND gender = '女' ) |
mp实现:
1
2
3
4
5
6
7
8
9
10
11
12
|
/** * 查询姓李的或者邮箱不为空并且是女性的员工 * 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实现:
1
|
SELECT * FROM t_employee WHERE NAME LIKE '李%' AND (email IS NOT NULL OR gender = '女' ) |
mp实现:
1
2
3
4
5
6
7
8
9
10
11
12
|
/** * 查询姓李的并且邮箱不为空或者是女性的员工 * 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实现:
1
|
SELECT * FROM t_employee WHERE salary<=3500 AND departmentId IN (1,2,3) ORDER BY birthday ASC |
mp实现:
1
2
3
4
5
6
7
8
9
10
11
12
|
/** * 查询属于编号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); } |
以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。
原文链接:https://blog.csdn.net/u012078155/article/details/106760123