初学node，配合数据库实现分页和多条件查询

学习了下node.js，和简单的数据库操作，写了一个前后端相连的小demo，做个一个基于 node.js+MySQL+jQuery 的分页和多条件查询功能。熟悉业务中前后端的数据交流运作！

 

一、配置环境

因采用的是node.js+express框架搭建的服务器：使用npm命令下载好了相关包，还有和ES6的相关babel包

下面是node.js+express框架搭建的服务器时，在app.js中的配置：

//1.引用框架
const myexpress = require("express");
const logger = require('morgan');  //HTTP请求日志中间件
const favicon = require("serve-favicon"); //icon设置模块
const cookieParser = require('cookie-parser'); //cookie操作中间件,用户获取用户输入的信息
const bodyParser = require("body-parser"); //把提交的数据封装到body-parser中，用于post方式
const indexRouter = require("./routers/indexRouter.js"); //模块化 引用文件，自己的加个./，indexRouter代表这个文件
const myapp = myexpress(); //执行express中的全局函数，返回一个express的服务对象

myapp.use(logger('dev')); //日志设置
myapp.use(cookieParser()); //cookie设置
myapp.use(bodyParser.urlencoded({ extended: false })); //解析urlencoeded编码的post参数，URLEncoded编码中,所有的字符均为ANSCI

myapp.use(myexpress.static(__dirname+"/public")); //静态文件的路径配置
myapp.use(indexRouter);

//添加监听的端口号
myapp.listen("6068", function () {
    console.log("启动服务");
});

　二、node关于js模块化，以及数据请求和交互流程

自己也才学后端，描述能力也差，所以只能用小学生的绘画能力把自己的理解画出来了：^--^

route.js 路由配置：

const express = require('express');
const stuController = require('./../controller/stuController.js');// 引用下一个模块post get方法
const route = express.Router();//本来是myapp，但myapp是服务对象，在模块里不能再
//创建个，所以我们用express的Router这个类来代替，再暴露出去

route.get("/studentlist.do",stuController.studentList);
route.post("/studentdelete.do",stuController.studentDelete);
route.get("/studentadd.do",stuController.studentAdd);
route.get("/studentupdate.do",stuController.studentUpdate);
route.get("/studentsearch.do",stuController.studentSearch);
route.get("/studentcount.do",stuController.studentCount);

module.exports = route;//暴露  接口 

　　

 controller.js 业务操作代码：

var stuModal = require('./../modal/stuModal.js');
module.exports = {
    //加载表单数据
    studentList: function (request,reponse) {
        let num = request.query.num;
        let current = request.query.current;
        stuModal.stuList(num,current,function (err,data) {
            reponse.send(data);
        })
    },
    //分页
    studentCount: function (request,reponse) {
        let name = request.query.stuname;
        let sex = request.query.sex;
        let age = request.query.age;
        stuModal.stuCount(name,sex,age,function (err,data) {
            reponse.send(data);
        })
    },
    studentDelete: function (request,reponse) {
        let myid = request.body.myid;
        stuModal.stuDelete(myid,function (err, data) {

            if(err){
                reponse.send("失败");
            }else{
                reponse.send("ok");
            }
        })
    },
    studentAdd: function (request,reponse) {
        let username = request.query.addname;
        let pwd = request.query.addpwd;
        stuModal.stuAdd(username,pwd,function (err,data) {
            reponse.send("添加成功");
        })
    },
    studentUpdate: function (request,reponse) {
        let id =request.query.myid;
        let username = request.query.updatename;
        let pwd = request.query.updatepwd;
        stuModal.stuUpdate(id,username,pwd,function (err,data) {
            reponse.send("修改成功");
        })
    },
    //多条件搜索
    studentSearch: function (request,reponse) {
        let name = request.query.stuname;
        let sex = request.query.sex;
        let age = request.query.age;
        let num = request.query.num;
        let current = request.query.current;
        stuModal.stuRearch(name,sex,age,num,current,function (err,data) {
            reponse.send(data);
        })
    }
}

　　modal.js ：嵌套SQL语句，在数据库操作相关数据，回调函数在返回结果数据给 controller.js

const mysql = require('mysql');
const sqlPoolObj = require('./sqlpool.js');
const sqlPool = sqlPoolObj.sqlpool();

module.exports = {
    stuList: function (num,current,callback) {
        let arr = [(current-1)*num,parseInt(num)];
        sqlPool.connect("select * from user limit ?,?",arr,callback);
    },
    stuCount: function (name,sex,age,callback) {
        let sql = "select count(1) as num from user where 1=1";
        let arr = [];
        if(name!=""){
            name = "%"+name+"%";
            sql += " and username like ?";
            arr.push(name);
        }
        if(age!=""){
            age = "%"+age+"%";
            sql += " and age like ?";
            arr.push(age);
        }
        if(sex!=-1){
            if(sex==1){
                sex = "%男%";
                sql += " and sex like ?";
                arr.push(sex);
            }else{
                sex = "%女%";
                sql += " and sex like ?";
                arr.push(sex);
            }
        }
        sqlPool.connect(sql,arr,callback);
    },
    stuDelete: function (myid,callback) {
        sqlPool.connect("delete from user where id=?",[myid],callback);
    },
    stuAdd: function (username,pwd,callback) {
        var sql = "INSERT INTO user(id,username,password) VALUES(NULL,?,?)";
        console.log(username+"---"+pwd);
        sqlPool.connect(sql,[username,pwd],callback);
    },
    stuUpdate: function (id,newusername,newpwd,callback) {
        if(newusername==""){
            sqlPool.connect("UPDATE USER SET PASSWORD=? WHERE id=?",[newpwd,id],callback);
        }else if(newpwd==""){
            sqlPool.connect("UPDATE USER SET username=? WHERE id=?",[newusername,id],callback);
        }else{
            sqlPool.connect("UPDATE USER SET username=?,PASSWORD=? WHERE id=?",[newusername,newpwd,id],callback);
        }
    },
    stuRearch: function (name,sex,age,num,current,callback) {
        let sql = "select * from user where 1=1";
        let arr = [];
        if(name!=""){
            name = "%"+name+"%";
            sql += " and username like ?";
            arr.push(name);
        }
        if(age!=""){
            age = "%"+age+"%";
            sql += " and age like ?";
            arr.push(age);
        }
        if(sex!=-1){
            if(sex==1){
                sex = "%男%";
                sql += " and sex like ?";
                arr.push(sex);
            }else{
                sex = "%女%";
                sql += " and sex like ?";
                arr.push(sex);
            }
        }
        sql += " limit ?,?";
        arr.push((current-1)*num,parseInt(num));
        sqlPool.connect(sql,arr,callback);
    }
}

　多条件查询 使用了 模糊查询 like “%字段%”，还有 where 1=1 手段，这样可以在后面根据用于输入的情况，拼接在SQL语句后面

let sql = "select * from user where 1=1";// where 1=1 可以满足下面所以情况的拼接
        let arr = [];
        if(name!=""){
            name = "%"+name+"%";
            sql += " and username like ?";
            arr.push(name);
        }
        if(age!=""){
            age = "%"+age+"%";
            sql += " and age like ?";
            arr.push(age);
        }
        if(sex!=-1){
            if(sex==1){
                sex = "%男%";
                sql += " and sex like ?";
                arr.push(sex);
            }else{
                sex = "%女%";
                sql += " and sex like ?";
                arr.push(sex);
            }
        }
        sql += " limit ?,?";
        arr.push((current-1)*num,parseInt(num));
        sqlPool.connect(sql,arr,callback);

　　

最后 在 modal 文件 创建一个 SQLcool.js文件，用于连接MySQL服务器的配置，使用连接池的方式

const mysql = require("mysql");
module.exports.sqlpool = function () {
    let pool = {
        config:{
            host:"localhost",
            user:"root",
            password:"root",
            port:3306,
            database:"text"
        },
        connect: function (sql,arr,fn) {
            //创建链接池对象
            const pool= mysql.createPool(this.config);
            //获取链接池对象
            pool.getConnection(function (err,connect) {
                if(err){
                    console.log(err);
                }else{
                    connect.query(sql,arr,fn);
                }
            });
        }
    }
    return pool;
}

　　

 三、前台的HTML页面，使用ajax，实现页面的局部跟新，当然HTML结构和 css样式就忽略吧

首先,定义全局变量，用于记录当前页数，每页显示的数据，多条件搜索的信息等等

然后将从数据库查询的数据，一次加入在页面的表格中，次操作封装一个方法：

function stuList(){
                $.ajax({
                    type: "get",
                    url: "/studentsearch.do",
                    dataType:"json",
                    data: {
                        num:pageNum,
                        current:currentPage,
                        stuname:stuName,
                        sex:stuSex,
                        age:stuAge
                    },
                    success: function(ajaxObj){
//                        console.log(typeof ajax);
                        let stuMain = document.getElementById("stu-tb-main");
//                        var ajaxObj = JSON.parse(ajax.responseText);
                        stuMain.innerHTML="";
                        for(let i=0; i<ajaxObj.length; i++){
                            stuMain.innerHTML += "<tr><td class='stuname'>"+
                            ajaxObj[i].username+"</td><td>"+
                            ajaxObj[i].password+"</td><td>"+
                            ajaxObj[i].sex+"</td><td>"+
                            ajaxObj[i].age+"</td><td><a class='btndelete' myid='" +
                            ajaxObj[i].id+"'>删除</a><a class='btnupdate' myid='"+
                            ajaxObj[i].id+"'>编辑</a></td></tr>"
                        }
                        pageIndex();
                    },

　将每次ajax请求后的，实时更新分页按钮封装成方法

 function getTotalpage(){
                $.ajax({
                    type: "get",
                    url: "/studentcount.do",
                    dataType:"json",
                    data: {
                        stuname:stuName,
                        sex:stuSex,
                        age:stuAge
                    },
                    success: function(ajaxObj){
                        totalNum = ajaxObj[0].num;
//                        console.log(totalNum);
                        totalPage = Math.ceil(totalNum/pageNum);
//                        console.log(totalPage);
                        var pagebtn = $("#pagebtn").html("");
                        for(var i=1;i<=totalPage;i++){
                            pagebtn.append("<span class='btnspan'>"+i+"</span>");
                        }
                        pageIndex();
                    }
                })
            };

　高亮当前页的页码方法：

 

之后给相关dom元素添加点击事件，因为dom是每次ajax请求重新加载的，所用用事件委托的方式添加点击事件

分页：

//分页跳转到点击页
            $("#pagebtn").on("click",".btnspan", function () {
                    currentPage = $(this).text();
                    stuList();
            })
            //首页
            $("#pageindex").on("click", function () {
                currentPage = 1;
                stuList();
            })
            //尾页
            $("#pagelast").on("click", function () {
                if(totalPage!=undefined){
                    currentPage = totalPage;
                    stuList();
                }
            })
            //上一页
            $("#pageprev").on("click", function () {
                if(currentPage>1){
                    boolean = false;
                    currentPage--;
                    stuList();
                }
            })
            //下一页
            $("#pagenext").on("click", function () {
                if(currentPage<totalPage){
                    boolean = false;
                    currentPage++;
                    stuList();
                }
            })

多条件查询：

$("#search").on("click", function () {
                stuName = $("#ipt-search").val();
                stuAge = $("#ipt-search-age").val();
                stuSex = $("#search-sex").val();
                currentPage = 1;
                stuList();
                getTotalpage();
            })

　　

 四、页面效果：