express mysql 怎么分页

Question

sql语句应该怎么写

Answer 1

谢邀！简单写一个示例供参考：

路由层，也可以说控制层: routes/blog.js

var express = require('express');
var router = express.Router();

var blogDao = require('../dao/blogDao');

// 获取列表
router.get('/list', function(req, res, next) {
    blogDao.blogList(req, res, next); 
});

module.exports = router;

DAO层: dao/blogDao.js

var mysql = require('mysql');
var $conf = require('../config/config');
var $util = require('../util/util');
var $sql = require('../mapper/blogMapper');

// 使用连接池，提升性能
var pool = mysql.createPool($util.extend({}, $conf.mysql));

// 向前台返回JSON方法的简单封装
var jsonWrite = function(res, ret) {
    if (typeof ret === 'undefined') {
        res.json({
            code: '-1',
            msg: '后台异常'
        });
    } else {
        res.json(ret);
    }
};

module.exports = {
    blogList: function(req, res, next) {
        pool.getConnection(function(err, connection) {
            // 获取前台页面传过来的参数
            var param = req.query || req.params;
            var pageNum = parseInt(param.pageNum || 1);// 页码
            var end = parseInt(param.pageSize || 10); // 默认页数
            var start = (pageNum - 1) * end;
            connection.query($sql.queryAll, [start, end], function(err, result) {
                jsonWrite(res, result);
                connection.release();
            });
        });
    }
};

sql: mapper/blogMapper.js

var blogSql = {
    queryAll: 'select * from blog_list order by create_time desc limit ?, ?'
};

module.exports = blogSql;

希望对您有所帮助！

Answer 2

先获取数据总个数 select count(*) as sum from User
总页数 pages = Math.ceil(sum/n) //每页显示n个
再根据页数去查询 select * from User limit index,n //index 页码