初学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(); })
四、页面效果:
你有小脑斧么