基于nodejs的简单分页的实现

一、项目描述

这个demo是基于昨天我写的增删改查的例子的。在昨天的例子的基础上，我增加了基本的分页功能。由于本人对于nodejs的理解不够深入所以在分页实现上，有很多不足之处。在存储分页信息上我使用的是request层存储，也就是用get传值的古老办法，主要是由于对于nodejs的session了解还不够深入。

二、详细代码

由于是基于昨天的项目所以很多地方就请参看上一篇的文章。

1、Config.js

module.exports={
    host:'localhost',
    user:'root',
    password:'root',
    database:'db_test_',
    ports:3306
};

2、DBUtil

var config=require('./config.js');//引入配置文件
var mysql=require('mysql');//引入mysql驱动
var pool=mysql.createPool(config);//创建数据库连接池
//dql函数（用于查询的函数）
exports.executeQuery=function(sql, data, callback){
    pool.getConnection(function(err,conn){
        if(err){
            callback(err,null,null);
        }else{
            conn.query(sql,data,function(qerr, vals, fields){
                conn.release();
                callback(qerr,vals,fields);
            });
        }
    });
};
//dml函数（用于增加、修改、删除的函数）
exports.executeUpdate = function(sql, data, callback) {
    pool.getConnection(function(err, conn) {
        if (err) {
            callback(err, null, null);
        } else {
            conn.query(sql, data, function(qerr, result) {
                conn.release();
                callback(qerr, result);
            });
        }
    });
};

3、Pagedao.js

var DaoBase = require('./DBUtil');//引入dbutil
var user_DB = require('../VO/User');//引入模型模块
var pageVO=require('../VO/PageInfo');//引入分页模型
var pageSize=5;
var userDB = new user_DB();//实例化模型模块
var pageInfo = new pageVO();//实例化模型模块
//总页数
var  numPage=exports.pageNum=function(VO,callback){
    var sql='SELECT CEILING(COUNT(id)/'+VO.pageSize+') AS sum FROM tb_test_user_ ';
    if(VO.static){
        sql=sql+'where name like '+VO.queryInfo;
    }
    var data=[];
    DaoBase.executeQuery(sql,data,callback);
};
//首页
exports.firstPage=function(VO,callback){
    var sql='select * from tb_test_user_ ';
    if(VO.static){
        sql=sql+'where name like '+VO.queryInfo;
    }
    sql=sql+' limit ?,?';
    console.log(sql);
    var data=[];
    data.push(0);
    console.log(VO.pageSize);
    data.push(VO.pageSize);
    DaoBase.executeQuery(sql,data,callback);
};
//尾页
exports.endPage=function(VO,callback){
    numPage(VO,function(qerr, vals,fields){
        if(!(qerr==null)){
            console.log('error');
        }
        var sql='select * from tb_test_user_ ';
        if(VO.static){
            sql=sql+'where name like '+VO.queryInfo;
        }
        sql=sql+' limit ?,?';
        var data=[];
        var pageStartRow= (vals[0].sum-1)*VO.pageSize;
        data.push(pageStartRow);
        data.push(VO.pageSize);
        DaoBase.executeQuery(sql,data,callback);
    });
};
//当前页
exports.page=function(VO,callback){
    var currentPage=VO.currentPage;
    var sql='select * from tb_test_user_ ';
    if(VO.static){
        sql=sql+'where name like '+VO.queryInfo;
    }
    sql=sql+' limit ?,?';
    var data=[];
    var pageStartRow= (currentPage-1)*VO.pageSize;
    data.push(pageStartRow);
    data.push(VO.pageSize);
    DaoBase.executeQuery(sql,data,callback);
};

4、pageinfo.js

module.exports=PageInfo;
function PageInfo(){
        this.pageNum='pageNum',                 //总页数
        this.currentPage='currentPage',       //当前页数
        this.pageSize='pageSize',               //每页显示的条数
        this.result='result',                   //查询结果

        this.totalRows = 'totalRows',           // 总数据数
        this.pageStartRow ='pageStartRow',   // 每页的起始数
        this.pageEndRow = 'pageEndRow',      // 每页显示数据的终止数

        this.queryInfo='queryInfo',             //查询条件
        this.static='static'                     //状态信息
}

5、Index.js

var express = require('express');
var express = require('express');//引入express包
var userDao=require('../dao/UserDao.js');//引入userdao文件
var user_DB = require('../VO/User');//引入user实体
var pageDB=require('../dao/PageDao.js');//引入pagedao
var pageInfo=require('../VO/PageInfo'); //引入page实体
var page=new pageInfo();
//首页
var firtPage=exports.firstPage=function(req,res){
  page.pageSize=4;
  page.static=false;
  page.currentPage=1;
  var total=req.query.total;
  pageDB.firstPage(page,function(qerr, vals,fields){
    if(!(qerr==null)){
      console.log('error');
    }
    var result=vals;
    res.render('success',{
      title:'这是第一页',
      result:result,
      page:1,
      total:total
    });
  });
};
//尾页
var lastPage=exports.endPage=function(req,res){
  page.pageSize=4;
  page.static=false;
  var total=req.query.total;
  pageDB.endPage(page,function(qerr, vals,fields){
    if(!(qerr==null)){
      console.log('error');
    }
    var result=vals;
    pageDB.pageNum(page,function(qerr, vals,fields){
      console.log(vals[0].sum);
      res.render('success',{
        title:'这是最后一页',
        result:result,
        page:vals[0].sum,
        total:vals[0].sum
      });
    });
  });
};
//上一页
exports.upPage=function(req,res){
  page.pageSize=4;
  page.static=false;
  var total=req.query.total;
  var oldPage=req.query.page;
  if((oldPage-1)<=1){
    firtPage(req,res);
  }else{
    page.currentPage=oldPage-1;
    pageDB.page(page,function(qerr, vals,fields){
      if(!(qerr==null)){
        console.log('error');
      }
      var result=vals;
      res.render('success',{
        title:'这是第一页',
        result:result,
        page:oldPage-1,
        total:total
      });
    });
  }
};
//下一页
exports.downPage=function(req,res){
  page.pageSize=4;
  page.static=false;
  var oldPage=req.query.page;
  var total=req.query.total;
  var pageNum=Number(oldPage)+1;
  console.log("???????"+pageNum+"!!!!!"+oldPage);
  if(pageNum>=total){
    lastPage(req,res);
  }else{
    page.currentPage=pageNum;
    pageDB.page(page,function(qerr, vals,fields){
      if(!(qerr==null)){
        console.log('error');
      }
      var result=vals;
      res.render('success',{
        title:'这是第一页',
        result:result,
        page:pageNum,
        total:total
      });
    });
  }
};

//登录验证

exports.doLogin=function(req,res){
  var username=req.body.username;
  var password=req.body.password;
  var user=new user_DB();
  user.name=username;
  user.password=password;
  userDao.UserLogin(user,function(qerr, vals,fields){
    if((!(qerr==null))||(vals.length<1)){
      res.render('error',{
        message:'登录失败'
      });
    }else{
      page.pageSize=4;
      page.static=false;
      page.currentPage=1;
      pageDB.firstPage(page,function(qerr, vals,fields){
        if(!(qerr==null)){
          console.log('error');
        }
        var result=vals;
        pageDB.pageNum(page,function(qerr, vals,fields){
          if(!(qerr==null)){
            console.log('error');
          }
          console.log(vals);
          console.log( vals[0].sum);//求总页数
          res.render('success',{
            title:'这是第一页',
            result:result,
            page:1,
            total:vals[0].sum
          });
        });
      });
    }
  });
};

6、App.js

app.use('/first',routes.firstPage);
app.use('/end',routes.endPage);
app.use('/up',routes.upPage);
app.use('/down',routes.downPage);

7、Success.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title><%= title %></title>
    <link rel='stylesheet' href='/stylesheets/style.css' />
</head>
<body>
<h1><%= title %></h1>

<table>

    <% result.forEach(function(name){ %>
    <tr>

        <td><%= name.name %></td>
        <td><%= name.password %></td>
        <td></td>
        <td><a href="/delete?id=<%= name.id %>">删除</a></td>
        <td><a href="/update?id=<%= name.id %>">修改</a></td>
    </tr>
    <% }) %>
</table>
        <p><a href="/Insert">添加</a></p>
        <p><a href="/first?page=<%= page %>&total=<%= total %>">首页</a></p>
        <p><a href="/up?page=<%= page %>&total=<%= total %>">上一页</a></p>
        <p><a href="/down?page=<%= page %>&total=<%= total %>">下一页</a></p>
        <p><a href="/end?page=<%= page %>&total=<%= total %>">尾页</a></p>
        <p>当前页数:<%= page %></p>
        <p>总页数：<%= total %></p>
</body>
</html>

8、Pagetest.js

var userDao=require('../dao/PageDao.js');
var user_DB = require('../VO/User');
var pageVO=require('../VO/PageInfo');
var pageInfo = new pageVO();
pageInfo.currentPage=2;
pageInfo.static=true;
pageInfo.queryInfo='1';

/*userDao.page(pageInfo,function(qerr, vals,fields){
    if(!(qerr==null)){
        console.log('error');
    }
    console.log(vals);
});*/
/*userDao.endPage(pageInfo,function(qerr, vals,fields){
    if(!(qerr==null)){
        console.log('error');
    }
    console.log(vals);
});*/
/*userDao.firstPage(pageInfo,function(qerr, vals,fields){
    if(!(qerr==null)){
        console.log('error');
    }
    console.log(vals);
});*/
/*userDao.pageNum(pageInfo,function(qerr, vals,fields){
    if(!(qerr==null)){
        console.log('error');
    }
    console.log(vals);
    console.log( vals[0].sum);//求总页数
});*/