nodejs 数据交互（增删改查）+Mysql+路由+bodyParser(中间件)+ejs模板

最新推荐文章于 2023-10-31 22:28:42 发布

weixin_42975115

最新推荐文章于 2023-10-31 22:28:42 发布

阅读量679

点赞数

分类专栏： nodejs ejs 路由 mysql 增删改查

本文链接：https://blog.csdn.net/weixin_42975115/article/details/86699147

版权

nodejs 同时被 3 个专栏收录

21 篇文章 0 订阅

订阅专栏

mysql

10 篇文章 0 订阅

订阅专栏

路由

3 篇文章 0 订阅

订阅专栏

前提

安装好nodejs环境和Mysql
安装好bodyParser(中间件)+ejs模板
创建好数据库
如图：
创建一个工程目录

设计思路

一个功能对应一个页面，修改和删除为了方便把它们都放在一起了

代码块

app.js

var express = require('express');
var path = require('path');
var app = express();
var bodyParser = require('body-parser');
//body-parser只能解析单层对象
var select = require('./routes/select');
var all = require('./routes/selectall');
var add = require('./routes/add');
var updata = require('./routes/updata');
//模板引擎设置
app.set('views', path.join(__dirname, 'views'));
//app.set('view engine', 'jade');
app.engine("html",require("ejs").__express);
//设置视图模板引擎为html
app.set('view engine', 'html');

app.use(bodyParser.json()); 
app.use(bodyParser.urlencoded({
    extended: true
})); 

//设置路由

app.use('/select',select);
app.use ('/all',all);
app.use('/add',add);
app.use('/updata',updata);
app.listen(3000);
console.log("启动端口:3000");

select.js

var express = require('express');
var router = express.Router();
var Data = require("./db.js");

router.get('/', function(req, res, next) {
	
          res.render('select.html', {result:""});
     
});
 
 
router.post('/',function(req, res){
	var selectdata = new Data();
	// 对前端页面进行匹配，匹配到则输出
	selectdata.select(function(err, result){
			
		for (var i = 0 ; i< result.length; i++) {
			
			var resultdata;
			if (req.body.msg==result[i].name) {
				resultdata = result[i];
			    break;
			}
			else
			{
				resultdata = "没找到相应数据";

			}
			
		}
        
        res.render('select.html', {result:resultdata});       
	});
	
});

module.exports = router;

select.html

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title>ajax</title>
</head>

<body>
  <form action="/select" method="post">
  	<h5>查询</h5>
    <input name="msg" type="text" id="msg">
    <input name="submit" type="submit">
  </form>
  <div style="border:1px solid red;height:30px;margin-top:10px;line-height:30px;" id="select"> 
       
  </div>
   <div style="border:1px solid black;height:300px;margin-top:10px;line-height:30px;">
   
   </div>
</body>

</html>
<script type="text/javascript">
var Id = "<%=result.id%>";
var name = "<%=result.name%>";
var result ="<%=result%>"

var select = document.getElementById("select");
    if(result=="没找到相应数据")
    {
       select.innerHTML = result;
    }
    else
    {
        select.innerHTML = Id+" "+name;
    }
</script>

add.js

var express = require('express');
var router = express.Router();
var Data = require("./db.js");

router.get('/', function(req, res, next) {
	
          res.render('add.html', {result:""});
     
});
 
 
router.post('/',function(req, res){
	// 获取前端数据
	var add = new Data();
	var data = [
	   req.body.id,
	   req.body.name,
	   req.body.phone,
	   req.body.addr,
	   req.body.username,
	   req.body.password
	   ];
		
	

	console.log(JSON.stringify(data));
	       var addsel = new Data();
	       // 查询数据是否存在
	    addsel.select(function(err,result){
           var resultdata =false;
	    	for ( var i = 0 ; i< result.length; i++) {
			      var str = result[i].id; 
	              var b=req.body.id;
                  if (b==parseInt(str)){

				      resultdata = true;
			        }
              }
              if(resultdata){
              	  var message = "id信息已经存在";
                  res.render('add.html', {result:message});
              }
              else{
              	// 若果数据不存在，那么就进行·插入操作·
              	   add.add(data,function(err, result){
                   var addsel = new Data();
	               addsel.select(function(err,result){
                   var resultdata = false;
	    	    for (var i = 0 ; i< result.length; i++) {
			          var str = result[i].id; 
	                  var b=req.body.id;
                      if (b==parseInt(str)){
				        resultdata = true;
			          }
              }
			
             if (resultdata) {
             	 var message = "信息已经插入";

             	 res.render('add.html', {result:message}); 
             }
	   });    
    });
   }			
 });
	

	
});

module.exports = router;

add.html

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title>ajax</title>
</head>

<body>
	<form action="/add" method="post">
  	<h5>增加</h5>
    <input name="id" placeholder="id"  type="text" >
    <input name="name" placeholder="name" type="text" >
    <input name="phone" placeholder="phone" type="text" >
    <input name="addr" placeholder="addr" type="text" >
    <input name="username" placeholder="username" type="text" >
    <input name="password" placeholder="password" type="text" >
    <input name="submit" type="submit">
  </form> 
   <div style="border:1px solid black;height:300px;margin-top:10px;line-height:30px;">
    <%= result%>
   </div>
</body>

</html>

selectall.js

var express = require('express');
var router = express.Router();
var Data = require("./db.js");

router.get('/', function(req, res, next) {
   
      var all = new Data();
      // 查询所有数据
      all.selectall(function(err,result){
        res.render('all.html', {all:result});
      });
   
});
 
module.exports = router;

all.html

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title>ajax</title>
</head>

<body>
   <div style="border:1px solid black;height:300px;margin-top:10px;line-height:30px;">
    <% for(var i = 0; i<all.length;i++){%>
       <%= all[i].id%>    <%= all[i].name%>    <%= all[i].phone%>    <%= all[i].addr%>    <%= all[i].username%>    <%= all[i].password%><br>
    <%}%>
    %>
   </div>
</body>

</html>

updata.js

var express = require('express');
var router = express.Router();
var Data = require("./db.js");
const qs = require('qs');

router.get('/', function(req, res, next) {
	      //前端页面获取数据
	       var updataSql = new Data();
         updataSql.select(function(err,result){

         	res.render('updata.html', {medata:result,udinfo:""});
         });

          
     
});
 
 
router.post('/',function(req, res){
	//获取前端信息
	 var data = {
	   	 "id":qs.parse(req.body.id),
	   	 //多层对象解析
	   	 "name" :qs.parse(req.body.name),
	   	 "phone" :qs.parse(req.body.phone),
	   	 "addr" :qs.parse(req.body.addr),
	   	 "username" :qs.parse(req.body.username),
	   	 "password" :qs.parse(req.body.password)
	   }
	   console.log(req.body.id);
	   var obj = req.body.obj;
	   var del = req.body.del;
	   
	  //求对象长度
	  function objLength(obj){
          var count = 0;
            for(var i in obj){
                  count ++;
                }
          return count;
      }
      //获取id对象长度
      var lh= objLength(data.id[0]);
      //更新的信息
      var info =[];
      
       for(var i=0;i<lh;i++)
       {   
       	 if(parseInt(data.id[i])==obj)
       	 {
       	 	info[0]=data.name[i];
       	 	info[1]=data.phone[i];
       	 	info[2]=data.addr[i];
       	 	info[3]=data.username[i];
       	 	info[4]=data.password[i];
       	 	info[5]=data.id[i];

       	 }
            
       }
	 console.log(info);
	 console.log(obj);
     console.log(del);
	//req.body通过识别name属性 获得value值
	var updata = new Data();
	var updataSql = new Data();
	var dele = new Data();
	var udinfo;
	//数据更新
    if(obj!="none")
    {   
          udinfo = "更改成功";
          updata.updata(info,function(err,result){
             
       
             updataSql.select(function(err,result){

         	  res.render('updata.html', {medata:result,udinfo:udinfo});
            });
         });


    }
    // 数据删除
    if(del!="none")
    {
    	
    	dele.dele(del,function(err,result){
    		var udinfo ="删除成功";
           updataSql.select(function(err,result){    	
         	  res.render('updata.html', {medata:result,udinfo:udinfo});
            });
    	});
    }

                 
});

module.exports = router;

updata.html

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title>ajax</title>
</head>

<body >
	<form action="/updata" method="post">
    <h5>更新--删除</h5>
    <input name="submit" type="submit" value="获取数据" id="submit" style="display: none"><%= udinfo%>
    <br>
   <% for(var i=0;i<medata.length;i++){%>
          <input name="id" placeholder="id"  type="text" id="id" value="<%=medata[i].id%>" readonly unselectable="on" >
         <input name="name" placeholder="name" type="text" id="name" value="<%=medata[i].name%>">
         <input name="phone" placeholder="phone" type="text" id="phone" value="<%=medata[i].phone%>">
         <input name="addr" placeholder="addr" type="text" id="addr" value="<%=medata[i].addr%>">
         <input name="username" placeholder="username" type="text" id="username" value="<%=medata[i].username%>">
         <input name="password" placeholder="password" type="text" id="password" value="<%=medata[i].password%>">
         <input type="button" id="<%= i%>" value="更新" onclick="mytr_id(this.id)">
         <input type="button" id="<%= i%>" value="删除" onclick="mydel_id(this.id)">
        <br>
        <%}%>
        <input type="text" name="obj" id="obj" value="none" style="display: none">
        <input type="text" name="del" id="del" value="none" style="display: none">
  </form> 

</body>

</html>
<script type="text/javascript">
  function mytr_id(self)
  {
         
       var obj =document.getElementById("obj");
       obj.value = self;
        var sub = document.getElementById("submit");
        sub.click();
       
   
  }
  function mydel_id(self)
  {
       var del =document.getElementById("del");
       del.value = self;
        var sub = document.getElementById("submit");
        sub.click();   
  }

</script>

核心部件
db.js 数据库连接的密码要手动输入

//连接数据库
var mysql = require('mysql');
//创建连接池
var pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '********',
    database:'test'
});
// 封装数据对象
function Data()
{  
   // 定义查找成员函数
   this.select = function(callback)
   {    
       var sql = " select * from user";
       pool.getConnection(function(conn_err, conn) {
        if(conn_err) {
            callback(err,null,null);
        }
          else {
            conn.query(sql, function(query_err, result) {
            if (query_err) {
              console.log(" Error: " + query_err.message);
              return;
              }
                conn.release();
                // 释放连接池
                callback(query_err,result);
            });
        }
    });
   }
  // 定义检索成员函数
  this.selectall = function(callback)
   {    
       var sql = " select * from user";
       pool.getConnection(function(conn_err, conn) {
        if(conn_err) {
            callback(err,null,null);
        }
          else {
            conn.query(sql, function(query_err, result) {
            if (query_err) {
              console.log(" Error: " + query_err.message);
              return;
              }
                conn.release();
                callback(query_err,result);
            });
        }
    });

   }

// 定义添加成员函数
   this.add= function(data,callback)
   {    

      var  addSql = 'INSERT INTO user(id,name,phone,addr,username,password) VALUES(?,?,?,?,?,?)';
       pool.getConnection(function(conn_err, conn) {
        if(conn_err) {
            callback(err,null,null);
        }
          else {
            conn.query(addSql,data, function(query_err, result) {
            if (query_err) {
              console.log(" Error: " + query_err.message);
              return;
              }
                conn.release();
                callback(query_err,result);
            });
        }
    });
   }


   // 意义一个更新成员函数
   this.updata= function(data,callback)
   {    

      var  updataSql = 'UPDATE user SET name =?,phone=?,addr=?,username=?,password=? WHERE Id = ?';
       pool.getConnection(function(conn_err, conn) {
        if(conn_err) {
            callback(err,null,null);
        }
          else {
            conn.query(updataSql,data, function(query_err, result) {
            if (query_err) {
              console.log(" Error: " + query_err.message);
              return;
              }
                conn.release();
                callback(query_err,result);
            });
        }
    });
   }
   
   // 定义删除对象
    this.dele= function(data,callback)
   {    

      var  delSql = 'DELETE FROM user where id=?';
       pool.getConnection(function(conn_err, conn) {
        if(conn_err) {
            callback(err,null,null);
        }
          else {
            conn.query(delSql,data, function(query_err, result) {
            if (query_err) {
              console.log(" Error: " + query_err.message);
              return;
              }
                conn.release();
                callback(query_err,result);
            });
        }
    });
   }
};

module.exports = Data;