初学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这个类来代替，再暴露出去
<("/studentlist.do",stuController.studentList);
route.post("/studentdelete.do",stuController.studentDelete);
<("/studentadd.do",stuController.studentAdd);
<("/studentupdate.do",stuController.studentUpdate);
<("/studentsearch.do",stuController.studentSearch);
<("/studentcount.do",stuController.studentCount);
controller.js 业务操作代码：
var stuModal = require('./../modal/stuModal.js');
//加载表单数据
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 = id;
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 =id;
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();
stuList: function (num,current,callback) {
let arr = [(current-1)*num,parseInt(num)];
},
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);
}
}
},
stuDelete: function (myid,callback) {
},
stuAdd: function (username,pwd,callback) {
var sql = "INSERT INTO user(id,username,password) VALUES(NULL,?,?)";
console.log(username+"---"+pwd);
},
stuUpdate: function (id,newusername,newpwd,callback) {
if(newusername==""){
}else if(newpwd==""){
}else{
}
},
stuRearch: function (name,sex,age,num,current,callback) {
let sql = "select * from user where 1=1";
let arr = [];
if(name!=""){
name = "%"+name+"%";jquery框架搭建
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));
}
}
多条件查询使⽤了模糊查询 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));
最后在 modal ⽂件创建⼀个 SQLcool.js⽂件，⽤于连接MySQL服务器的配置，使⽤连接池的⽅式
const mysql = require("mysql");
let pool = {
config:{
host:"localhost",
user:"root",
password:"root",
port:3306,
database:"text"
},
connect: function (sql,arr,fn) {
//创建链接池对象
const pool= fig);
//获取链接池对象
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 = ElementById("stu-tb-main");
// var ajaxObj = JSON.sponseText);
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 = il(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();
})
四、页⾯效果：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论