app开发者平台在数字化时代的重要性与发展趋势解析
950
2023-01-31
MyBatis limit分页设置的实现
错误的写法:
SELECT
a.*,
FROM
tb_user a
WHERE 1=1
AND a.id IN
open="(" close=")" separator=","> dDihj #{id}
open="(" close=")" separator=",">
dDihj #{id}
AND a.status IN
open="(" close=")" separator=","> #{status}
open="(" close=")" separator=",">
#{status}
ORDER BY a.create_time desc
LIMIT (#{pageNo}-1)*#{pageSize},#{pageSize}; // 错误
在MyBatis中LIMIT之后的语句不允许的变量不允许进行算数运算,会报错。
正确的写法一:
SELECT
a.*,
FROM
tb_user a
WHERE 1=1
AND a.id IN
open="(" close=")" separator=","> #{id}
open="(" close=")" separator=",">
#{id}
AND a.status IN
open="(" close=")" separator=","> #{status}
open="(" close=")" separator=",">
#{status}
ORDER BY a.create_time desc
LIMIT ${(pageNo-1)*pageSize},${pageSize}; (正确)
正确的写法二:(推荐)
SELECT
a.*,
FROM
tb_user a
WHERE 1=1
AND a.id IN
open="(" close=")" separator=","> #{id}
open="(" close=")" separator=",">
#{id}
AND a.status IN
open="(" close=")" separator=","> #{status}
open="(" close=")" separator=",">
#{status}
ORDER BY a.create_time desc
LIMIT #{offSet},#{limit}; (推荐,代码层可控)
分析:方法二的写法,需要再请求参数中额外设置两个get函数,如下:
@Data
public class QueryParameterVO {
private List
private List
// 前端传入的页码
private int pageNo; // 从1开始
// 每页的条数
private int pageSize;
// 数据库的偏移
private int offSet;
// 数据库的大小限制
private int limit;
// 这里重写offSet和limit的get方法
public int getOffSet() {
return (pageNo-1)*pageSize;
}
public int getLimit() {
return pageSize;
}
}
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。
发表评论
暂时没有评论,来抢沙发吧~