❤Node07-分页查询
1、优化分页查询接口
之前我们的接口并没有进行分页的参数设置,在大批量的数据之中一般我们为了性能以及用户的体验,一般还是需要对于表格数据进行分页和优化。因此我们查询接口的分页是十分有必要的,接下来我们就优化一下我们分页查询的方式:
分页参数设置(前端的参数)
分页参数有两个,一个是当前页的页数,另一个是当前页有多少条。
js
pageNum: 1 //页码数(也就是当前是多少页)
pageSize:10 //页条数(也就是当前页是多少条)
我们可以先打印出来看看我们查询蚕食,这边我们可以看到前台传过来的查询参数里面加了 pageNum: '1', pageSize: '10' }
的参数,我们把这个参数打印如下图:
console.log(req.query,'req.query');
{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]
{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]
{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]
先看看我们的数据库查询语句是否正确
js
SELECT * FROM user WHERE age = 18 LIMIT 0, 10
2、拼接上我们的参数
当我们pageNum:1 pageSize10
的时候返回数据如图
js
{"total": 10,"code": 200,"data": [{"id": 1,"name": "管理员","age": "1888","sex": 1,"address": "地址","state": 1,},{"id": 2,"name": "后台开发人员","age": "18","sex": 2,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 3,"name": "教师","age": "18","sex": 2,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 4,"name": "普通人员","age": "20","sex": 1,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 7,"name": "张三","age": "18","sex": 1,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 12,"name": "可爱","age": "18","sex": 2,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 20,"name": "san娟","age": "20","sex": 1,"createtime": null,"address": null,"state": 2,"phone": null,"username": null,},{"id": 21,"name": "乐乐","age": "8","sex": 1,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 38,"name": "lin","age": "19","sex": null,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 39,"name": "童年的自己","age": "80","sex": null,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,}]
}
修改查询的分页为2,这个时候发现页码已经更改为2 了,查询无误
js
{"total": 10,"code": 200,"data": [{"id": 1,"name": "管理员","age": "1888","sex": 1,"address": "地址","state": 1,},{"id": 2,"name": "后台开发人员","age": "18","sex": 2,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,}]
}
3、offset偏移量参数
这里需要注意的就是这个offset参数 offset是偏移量,通过计算偏移量 (pageNum - 1) * pageSize
来动态生成偏移量,然后将 age、name、偏移量和每页的行数作为参数传递给 SQL 查询的 LIMIT
子句和 WHERE
子句。
我们将查询语句放进去尽心查询,这个时候报错了,查询语句如下:
js
if (pageNum !== undefined&&pageSize !== ''&&pageSize !== null) {query += params.length ? ' AND' : ' WHERE';query += ' LIMIT ?, ?';let offset = (pageNum - 1) * pageSize;params.push(offset);params.push(parseInt(pageSize));}
查询语句之中我们多加了' AND' : ' WHERE'
,排查修改以后我们更改我们的分页查询方式:
js
const { name, age,sex,pageNum,pageSize } = req.query;
if (pageNum !== undefined&&pageSize !== ''&&pageSize !== null) {query += ' LIMIT ?, ?';let offset = (pageNum - 1) * pageSize;params.push(offset);params.push(10);}
查询无误!
这个时候我将pageSize也给添加的时候发现错误
这个时候一直报错提示我You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' at line 1", sqlState: '42000', index: 0, sql: "SELECT * FROM user LIMIT 0, '10'" }
大致意思就是因为我的数据类型为字符串,转化方式有以下三种,我选择了parseInt
方式
js
parseInt
parseFloat
Number
- 需要注意的是查询方式,这里需要使用
O,10
的限制页码条数:
js
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '10' at line 1",
sqlState: '42000',
index: 0,
sql: "SELECT * FROM user LIMIT O,'10'"
ok更改以后查询无误!
js
{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]
{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]
{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]
4、返回总条数total
先试试我们的Sql查询语句
js
SELECT COUNT(*) AS total FROM user;
查询效果如下
接下来我们把这个查询加到我们的查询条件里面去
js
connectionpool.query(query,params,(err, results) => {// console.log(err,'err');// console.log(results,'results');if (err) {console.error('Error querying database:', err);res.status(500).json({ error: 'Internal server error' });return;}else{let sqltotal = `SELECT COUNT(*) AS total FROM user;`// 查询数据库并返回数据connectionpool.query(sqltotal,(errtotal, total) => { if (err) {console.error('Error querying database:', err);res.status(500).json({ error: 'Internal server error' });return;}else{res.json({total,code:'200',data:results,});}});}});
查询以后可以看出我们的查询结果如下,这里的返回结果条数是嵌套在数组里面。
js
{"total": [{"total": 12}],"code": 200,"data": [{"id": 1,"name": "管理员","age": "1888","sex": 1,"address": "地址","state": 1,},...]
}
优化一下我们的查询方式
js
connectionpool.query(sqltotal,(errtotal, rows) => { let total = rows[0]['total'];if (err) {console.error('Error querying database:', err);res.status(500).json({ error: 'Internal server error' });return;}else{res.json({total:total,code:'200',data:results,});}});
查询成功!
js
{"total": 12,"code": 200,"data": [{"id": 1,"name": "管理员","age": "1888","sex": 1,"address": "地址","state": 1,},{"id": 2,"name": "后台开发人员","age": "18","sex": 2,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},...]
}