nodejs+vuejs实现表格分页与CRUD操作

最新推荐文章于 2022-07-29 18:21:56 发布

lillllllll

最新推荐文章于 2022-07-29 18:21:56 发布

阅读量746

点赞数 3

分类专栏： SpringBoot Web开发文章标签： nodejs vuejs 分页 CRUD

本文链接：https://blog.youkuaiyun.com/lillllllll/article/details/103268482

版权

SpringBoot Web开发专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一种轻量级的Web表格分页和数据库CRUD操作解决方案，通过在NodeJS后端定义分页类并传至前端VueJS页面，实现了数据与显示逻辑的分离。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文实现了最轻量级的Web表格分页和数据库CRUD操作。分页类在nodejs后端定义，然后传到前端vuejs页面，实现了数据和页面显示逻辑分离。

pager类：由pagerF类定义，代码如下（实现思路参考我博客文章https://blog.youkuaiyun.com/lillllllll/article/details/102636208）

module.exports =function(totalRows,pageSize,navigatePages, current,rows,column){   	
     	     this.totalRows=totalRows;
             this.pageSize=pageSize;
             this. navigatePages=navigatePages;
             this.current=current;
             this.rows=rows;  
             this.tableColumns=column;
            this.totalPages= Math.ceil(this.totalRows/this.pageSize);
            this.isFirstPage = this.current == 1;
            this.isLastPage = this.current == this.totalPages || this.totalPages == 0;
         //计算滑动窗口中的页号，不止一种生成策略，这里采用
         //http://git.oschina.net/free/Mybatis_PageHelper提供的方案来实现              
           this.navigatepageNumsFF=function(){
                var navigatepageNums = [];
                //当总页数小于或等于导航页码数时
                if (this.totalPages <=this.navigatePages) {
                       for (var i = 0; i < this.totalPages; i++) {
                        navigatepageNums[i] = i + 1;
                    }
                } else { //当总页数大于导航页码数时
                   
                    var startNum = this.current - this.navigatePages / 2;
                    var endNum = this.current + this.navigatePages / 2;
                        
                    if (startNum < 1) {
                        startNum = 1;
                        //(最前navigatePages页
                        for (var i = 0; i < this.navigatePages; i++) {
                            navigatepageNums[i] = startNum++;
                        }
                    } else if (endNum > this.totalPages) {
                        endNum = this.totalPages;
                        //最后navigatePages页
                        for (var i = this.navigatePages - 1; i >= 0; i--) {
                            navigatepageNums[i] = endNum--;
                        }
                    } else {
                        //所有中间页
                        for (var i = 0; i < this.navigatePages; i++) {
                            navigatepageNums[i] = startNum++;
                        }
                    }
                }
                return navigatepageNums;
             }; //所有导航页号
             this.navigatepageNums=this.navigatepageNumsFF()                            
             this.navigateFirstPage=0;
             this.navigateLastPage =this.totalPages;
             if (this.navigatepageNums != null && this.navigatepageNums.length > 0) {
                 this.navigateFirstPage = this.navigatepageNums[0];
                 this.navigateLastPage = this.navigatepageNums[this.navigatepageNums.length - 1];   
             }
    }

listvue.html:基于vuejs的html静态页面，与nodejs交互全部采用ajax

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<!-- <meta charset="UTF-8"> -->
<!-- <base href="/"> -->
<link rel="stylesheet" href="public/css/bootstrap.min.css">
<script src="public/js/jquery-1.11.3.min.js"></script>
<script src="public/js/bootstrap.min.js"></script>
<script src="https://cdn.staticfile.org/vue/2.2.2/vue.min.js"></script>
<script src="https://cdn.staticfile.org/axios/0.18.0/axios.min.js"></script>
<!-- <script src="../js/vee-validate.js"></script> -->
<!-- <script src="https://unpkg.com/vee-validate@2.0.0"></script> -->
<script src="https://unpkg.com/vee-validate@2.2.15/dist/vee-validate.js"></script>
<title></title>
</head>
<body>
	<div class="container">
		<div class="row clearfix">
			<div class="col-md-12 column" style="text-align: center;">
				<h1>最轻量级Web表格分页及CRUD操作</h1>
			</div>
			<div class="col-md-12 column">
				<!-- 模式对话框 begin -->
			  <div id="mydialog">
				<button @click="clearB()" class="btn btn-primary btn-xs"
					id="insertButton" style="width: 120px; height: 30px"
					data-toggle="modal" data-target="#userInsertAndUpdateDialog">添加用户</button>
				<div class="modal fade" id="userInsertAndUpdateDialog"
					data-backdrop="static" tabindex="-1" role="dialog"
					aria-labelledby="myModalLabel">
					<div class="modal-dialog" role="document">
						<div class="modal-content" id="contenter">
							<div class="modal-header">
								<!-- 右上角关闭按钮 -->
								<button type="button" class="close" data-dismiss="modal"
									aria-label="Close">
									<span aria-hidden="true">&times;</span>
								</button>
								<h4 class="modal-title" id="myModalLabel">添加用户信息</h4>
							</div>
							<div class="modal-body">
								<form class="form-horizontal" id="user_form"
									action="" method="get">
									<div class="form-group">
										<label for="id" class="col-sm-2 control-label">用户代码</label>
										<div class="col-sm-10">
											<input type="text" th:readonly="true" class="form-control"
												id="user_id" placeholder="客户代码" name="id" />
										</div>
									</div>
									<div class="form-group">
										<label for="user_name" class="col-sm-2 control-label">用户名</label>
										<div class="col-sm-10">
											<input type="text" class="form-control" id="user_name"
												v-validate="'required'" placeholder="客户名称" name="name" /> <span
												style="color: red" v-show="errors.has('name')">{{'不能为空'}}</span>
										</div>
									</div>

									<div class="form-group">
										<label for="user_age" class="col-sm-2 control-label">年&nbsp&nbsp&nbsp&nbsp龄</label>
										<div class="col-sm-10">
											<input type="text" class="form-control" id="user_age"
												placeholder="年龄" name="age" />
										</div>
									</div>
								</form>
							</div>
							<div class="modal-footer" id="footer">
								<button type="button" class="btn btn-default" data-dismiss="modal" >关闭</button>
								<button type="button" id="modifybutton" class="btn btn-primary" onclick="update()">添加</button>
							</div>
						</div>
					</div>
				</div>
			  </div>
			  	<!-- 模式对话框 end -->
				<!-- table begain -->
				<table class="table" id="usertable">
					<thead>
						<tr>
							<th>编号</th>
							<th>用户名</th>
							<th>年龄</th>
							<th>状态</th>
						</tr>
					</thead>
					<tbody>
						<tr v-for="(user,index) in userlist"
							v-bind:class="{success: index % 2 !=0,info: index % 2 ==0}"
							@click="change">
							<td>{{user.id}}</td>
							<td>{{user.name}}</td>
							<td>{{user.age}}</td>
							<td>default</td>
							<td style="width: 120px">
								<!-- 修改 -->
								<a href="#" class="btn btn-primary btn-xs" data-toggle="modal"
								data-target="#userInsertAndUpdateDialog" @click="editUserTitle(user.id)">修改</a> 
								<!-- 删除 -->
								<a href="#" class="btn btn-danger btn-xs" @click="deleteUserTitle(user.id)">删除</a>
							</td>
						</tr>
					</tbody>
				</table>
				<!-- table end -->
				<!-- pager begein -->
				<div id="pager">
					<nav class="pagination" role="navigation" aria-label="pagination">
						<ul class="pagination">
							<li v-bind:class="{disabled:page.isFirstPage}"><a
								@click="gopage(1)" href="#">首页</a></li>
							<li v-bind:class="{disabled:page.isFirstPage}"><a
								@click="gopage(page.current - 1)" href="#">上一页</a></li>
							<li v-if="page.navigateFirstPage > 1"><a href="#">&hellip;</a></li>
							<li v-for="(item,index) in page.navigatepageNums"
								v-bind:class="{active:item==page.current}"><a
								@click="gopage(item)" href="#">{{item}}</a></li>
							<li v-if="page.navigateLastPage < page.totalPages"><a
								href="#">&hellip;</a></li>
							<li v-bind:class="{disabled:page.isLastPage}"><a
								@click="gopage(page.current + 1)" href="#">下一页</a></li>
							<li v-bind:class="{disabled:page.isLastPage}"><a
								@click="gopage(page.totalPages)" href="#">末页</a></li>
						</ul>
					</nav>
				</div>
               <!-- pager end -->
			</div>
			<!-- "row clearfix"> -->
		</div>
		<!-- col-md-12 column" -->
	</div>
	<!-- container -->
<script>
Vue.use(VeeValidate);
 new Vue({
	  el: '#contenter',
	  data:{
		ccc: "test",	    
	  },
	  methods:{

	  }
	 });

var table=new Vue({
  el: '#usertable',
  data:{
	userlist:null
  },
  
  mounted () {
   $.post('/page?cpage=1',function(data) {	 
	         table.userlist=data.rows;
			 pager.page=data;	   			   
		});       
  },
  methods:{	  		 
	  change:function(){		  
		  this.isActive=!this.isActive;		
	  },
	  created: function(){
          //中文验证提示
          this.$validator.localize('zh_CN', dictionary);
	  },    
	  editUserTitle:function(id){
		$('#myModalLabel').html("修改用户信息")
		$('#modifybutton').html("更新")
        $.get('/getbyid?id='+id,function(data) {	 
			        $("#user_id").val(data.id);
					$("#user_name").val(data.name);
					$("#user_age").val(data.age);  			   
		});  
	  },
	  deleteUserTitle:function(id){ 	 
		$.get('/deletebyid?id='+id,function(data) {	 
			if (data == "OK") {
						alert("添加成功！");
						window.location.reload();
					} else {
						alert("添加失败！");
						window.location.reload();
					}					   
		});  		
	  }
    }
});
var pager=new Vue({
	  el: '#pager',
	  data:{
		page: null,		
	    isActive:true
	  }, 
	  methods:{	  
			gopage:function(pp){  
				if (pp>this.page.totalPages)
				   pp=this.page.totalPages;
				if (pp<=0)
				   pp=1;				
			  $.post('/page?cpage='+pp,function(data) {					
				     table.userlist=data.rows;					 
					 pager.page=data;
				});       
		   },		  
		 }
	});
uu=new Vue({
	el:'#mydialog',
	data:{
        title:''
	},
	methods:{
		clearB(){
			$("#user_id").val("");
			$('#user_id').attr("disabled",true);
			$("#user_name").val("");
			$("#user_age").val("");

		}		 
		}//end method
	});
function update(){			
	var flag = $("#modifybutton").text();
		console.log(flag)
		if (flag == "添加") {
			$.post("/addUser", $("#user_form").serialize(), function(				data) {
					if (data == "OK") {
						alert("添加成功！");
						window.location.reload();
					} else {
						alert("添加失败！");
						window.location.reload();
					}
				});
			} else {
				$.post("/update", $("#user_form").serialize(), function(
						data) {
					if (data == "OK") {
						alert("更新成功！");
						window.location.reload();
					} else {
						alert("更新失败！");
						window.location.reload();
					}
				});
			}
		}

  </script>
</body>
</html>

Nodejs部分：服务器端功能实现

DBConfig.js

module.exports =
 {  
             mysql: {   
                         host: 'localhost',     
                         user: 'root',   
                         password: 'root',  
                         database:'test', // 前面建的user表位于这个数据库中 
                          port: 3306 ,
                          multipleStatements: true
                    }
 };

usersql.js

var UserSQL = {  
                insert:'INSERT INTO user(name,age) VALUES(?,?)', 
                queryAll:'SELECT * FROM user',  
                getUserById:'SELECT * FROM user WHERE id = ? ',
                updateUserById:'update user set name=? , age=? WHERE id = ? ',
				getTotal:'SELECT COUNT(*) FROM user',
                getPages:'SELECT * FROM user order by id asc limit ?,?',
                getColumn:"select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA = (select database()) and table_name = 'user'",
                deletebyid:'delete from user where id=?',
                getPageObj:"SELECT COUNT(*) FROM user;SELECT * FROM user order by id asc limit ?,?;select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA = (select database()) and table_name = 'user'"
            };
 module.exports = UserSQL;

数据库表是user对应的字段为：id: 自增，关键字, name: varchar, age: int

server.js: nodejs的主程序，包含了接受前端来的请求

var express = require('express');
var app = express();
var url = require("url");
var PageVue=require('./pagerF')
 var mysql = require('mysql')
 var userSQL = require('./usersql'); 
var querystring = require("querystring");
var dbConfig = require('./DBConfig');
//import PageVue from './pager'
 // 使用DBConfig.js的配置信息创建一个MySQL连接池
 var pool = mysql.createPool(dbConfig.mysql); 
var router = express.Router();
var bodyParser = require('body-parser'); 
// 创建 application/x-www-form-urlencoded 编码解析
var urlencodedParser = bodyParser.urlencoded({ extended: false }) 
app.use('/public', express.static('public'));
 
app.get('/column', function (req, res) {
   column=['id','name','age'];
   res.send(JSON.stringify(column));
   })

app.get('/index.html', function (req, res) {
   res.sendFile( __dirname + "/" + "index.html" );
})
app.get('/listvue.html', function (req, res) {
   res.sendFile( __dirname + "/" + "listvue.html" );
})

app.post('/processpost', urlencodedParser, function (req, res) {
   var response = {
       "first_name":req.body.first_name,
       "last_name":req.body.last_name
   };
   console.log(response);
   res.end(JSON.stringify(response));    
}) 
 // 响应JSON数据
 var responseJSON = function (res, ret) {
      if(typeof ret === 'undefined') { 
           res.json({     code:'-200',     msg: '操作失败'   
         }); 
     } else { 
       res.json(ret); 
   }};
 // 添加用户
 app.post('/addUser', function(req, res, next){
  var formdata=''
  req.on('data',function(fdata){
     formdata+=fdata;
     var data=querystring.parse(formdata)       
    pool.getConnection(function(err, connection) {        
 // 建立连接 增加一个用户信息 
   connection.query(userSQL.insert, [data.name,data.age], function(err, result) {        
    if(result) {      
      result = 'OK'       
     }        
      // 以json形式，把操作结果返回给前台页面     
       responseJSON(res, result);         
      // 释放连接  
       connection.release();    
        });
     });
    });
  });
 
  app.post('/update', function(req, res, next){
    var formdata=''
    req.on('data',function(fdata){
       formdata+=fdata;
       var data=querystring.parse(formdata)       
      pool.getConnection(function(err, connection) {        
   // 建立连接 增加一个用户信息 
     connection.query(userSQL.updateUserById, [data.name,data.age,data.id], function(err, result) {        
      if(result) {      
        result = 'OK'       
       }        
        // 以json形式，把操作结果返回给前台页面     
         responseJSON(res, result);         
        // 释放连接  
         connection.release();    
          });
       });
      });
    });

 app.get('/getbyid', function(req, res, next){	 
   pool.getConnection(function(err, connection) { 

        var param = req.query || req.params;
        console.log('id='+ param.id)
	      connection.query(userSQL.getUserById,[param.id], function(err,result){             
          // 只有一条记录用result[0]
         responseJSON(res, result[0]); 
         //console.log('dd='+ JSON.stringify(result[0].name))
        });
      });	 
 }); 

 app.get('/deletebyid', function(req, res, next){	 
  pool.getConnection(function(err, connection) { 
       var param = req.query || req.params;
       console.log('deleteid='+ param.id)
       connection.query(userSQL.deletebyid,[param.id], function(err,result){             
        if(result) {      
          result = 'OK'       
         }    
        responseJSON(res, result); 
        //console.log('dd='+ JSON.stringify(result[0].name))
       });
     });	 
});

 app.post('/page', function(req, res, next){
   var param = req.query || req.params;
   var pageSize=5;   //页面显示行数，自行设置
   var navigatePages=6;//滑动窗口中格子个数，自行设置
   var cpage=1
   if(param.cpage!=null){
     cpage=param.cpage
   } 
   var columns=[]
   total=0
   var rows=[];
   var startrow=(cpage-1)*pageSize;  
   pool.getConnection(function(err, connection) {      
      connection.query(userSQL.getPageObj, [startrow,pageSize], function(err,result){   
         if(err){
        throw err;
      }else{
        total=result[0][0]['COUNT(*)']
        rows=result[1]
        columns=result[2]
        var page =new PageVue(total, pageSize, navigatePages, cpage, rows,columns);
        //responseJSON(res, page); 
        res.send(page)
        connection.release();    
    }
      });
   })	
 }); 
 
var server = app.listen(8888, function () { 
var host = server.address().address
var port = server.address().port 
console.log("应用实例，访问地址为 http://%s:%s", host, port) 
})

在vscode环境中的terminal下输入 node server.js启动服务程序。在浏览器中输入http://localhost:8888/listvue.html，界面显示如下：